There is no secret that information makes the world go around currently. If an enterprise takes care of its intellectual property and each employee can easily get the necessary information, the enterprise can hope for the growth. If there is chaos in data, the enterprise will fail despite the team spirit.
In this article, we are going to explore the database security basics and examples of information protection in Oracle. Actually, the theoretical basics for protecting information in the database, which we are going to consider in this article, will be also useful to people working with other databases.
In most companies I worked for, all administrators and developers had a full access to databases, as well as an employee of the IT department was a god and could do anything they want. Why does it happen? There are two reasons for this:
- While working in one department, employees can meet each other for 8 hours every day and become friends. How cannot they grant access? Friendship is one thing but business is business.
- Granting some access rights or modifying some configuration may require some privileges. Sometimes, administrators think that programmers configure settings better. Thus, they provide programmers with unnecessary access rights. Instead, programmers must not be involved in administration and must not have any rights for this.
In my experience, the second problem is very common, thus, we will analyze it in details.
When developing programs for databases, programmers know a superuser password or simply have database administrator rights. This is unnecessary and absolutely insecure. Only the database administrator must have full rights. Even the head of the department, director, and best friends can have fewer privileges.
For example, when developing programs, it is enough to have the schema owner rights in the Oracle database that stores work tables. These rights are enough to create new tables, packages, indexes and other objects, as well as to grant access rights to schema objects to other users. You do not need the system rights for full-time work.
If there is no database administrator on a full-time basis, it is very bad. It is better when there is someone responsible for database performance, optimization, and security. At least, you need to appoint one programmer who will be responsible for this and will have exclusive rights.
According to statistics, employees of the company cause data loss most often. Still, despite this fact, most companies continue ignoring this thread and different databases stored on disks at free access are sold even in the subway.
Users and Roles
There are two object types to grant access rights in databases: users and roles. Roles are some groups that combine users who must have similar rights. For example, all accountants may require access to financial documents. To prevent you from granting rights to each accountant, we can combine them into a role with the necessary access.
As you can see, the principle of roles is similar to groups in the Windows operating system. Still, it has some differences. Not without reason, all three object types such as users, groups, and roles can be implemented in the database. However, only users and roles are implemented in most databases. It is necessary to manage and monitor all these objects so that each user obtaining the rights granted by the roles can have access to the data that is actually required to solve tasks. It is necessary to use access rights as rules for a firewall. Using these rights, you can solve the same issue – to allow a user to perform only certain tasks and prevent possible loss or corruption.
With the help of roles, it is quite convenient to control access, provide permissions or block the whole group of users. One role can be included into the other, thus, inheriting its access rights. Therefore, we can create a hierarchical structure of rights.
All the employees with the access rights to a corporate database can be included into a single role with the minimum rights. Now, if you need additional privileges, access to particular tables, you will need to add a user to other roles (if a group requires the same access) or provide a particular account with the rights.
In the program, to control access to the tables, it is possible to check the result for errors after each attempt to open the dataset. If an access error occurs, the access to the specified table is blocked for a user. Thus, there is no need to implement the access rights to the client application. However, if you do want to implement this, there will be no harm done. At least, I do not see anything critical in this; it seems to have the opposite effect.
If each user is working under their own account in your database, you can call the user variable to determine the current user, for example:
SELECT user from dual
This query will return a username, under which the connection to the server is opened. If several people can log in using one name, this query will return the same name for both and it would be useless for the audit. Especially, if lockout control occurs at the server level.
If several users can log in using the same username, it is complicated, but still possible, to identify who logged into the account. The following query allows getting the detailed information on the session:
select s.user#, s.username, s.osuser, s.terminal, s.program from sys.v_$session s WHERE username=user
As you can see, the query returned the username, connected to the database, a name in the operating system, a terminal username and a program.
Here, you can access the v_$session view from the sys system schema. This view returns some information about the connections to the server. In the WHERE clause, we limit the output to the current user only. As a result, the query returns the user ID, name, name in the operating system, terminal and the program from which the connection was established.
When you know a username and a terminal name, you can identify a user for sure. To know what roles the current user was added to, you can run the following query:
SELECT GRANTED_ROLE FROM dba_role_privs WHERE grantee=user
We will not say that there should not be default passwords. Some databases, for example, MySQL do wrong when they create a simple and well-known system password after the installation. We also will not say that the password should be complicated. This rule applies to all IT areas and should be known even to a novice user. We are going to talk about database issues.
In my experience, when developing corporate programs, developers use the same account to access a database. The parameters of this account are registered directly in the program, while the access to particular modules, including accounting, warehouse, personnel department, etc. is implemented in a programmatic manner. On the one hand, this method is convenient, because the program can connect to the database with administrator rights and will not have to take care of the roles and access rights to the tables. On the other hand, this method is far from secure. The level of users is growing, and friends of your company’s employees can be educated in the field of security and hacking. After knowing the name and password, under which the program connects to the database, an ordinary user can get more opportunities than you wanted.
The SQL language is not a secret and complicated language. There are many programs using which you can easily view any data in the database. With the username and password, anyone can steal all your data. Thus, it will be sold in any stall that sells disks.
A username and password must never be stored in the program. The access to the program must also be limited to and impossible for the third parties. It is quite logical to combine both logins into one. It is necessary to create a separate account on the database server with the minimum necessary rights for each user in the organization. These names and passwords should be used when logging into the program. You can use a common and very effective logic – if you can log in to the database with the entered data, the access is allowed; if not, you must abort the program. This process is simple and effective because we used database authorization.
To check that users do not work simultaneously from two different computers under the same name, you can execute the following query:
select s.username, s.terminal, count(*) from sys.v_$session s WHERE username=user HAVING count(*)>1 GROUP BY s.username, s.terminal
In fact, it must not return any record.
Views are a very convenient way to switch off from the data structure and build an additional level of protection at the same time. That is true that views have a negative impact on productivity, however, they have a positive impact on security. We can grant their own access rights, while the tables from which the data is retrieved can remain inaccessible to this account.
When is it better to use views? First, let us define what their disadvantages are. A view is a SELECT statement to retrieve data. It can access both, one and several tables. If you just select the data from the view, there will be no performance loss. However, we can use views in other queries to select data and refer to them as to the tables. For example:
SELECT list of fields FROM view, table_1, table_2 WHERE some parameters
In this case, the query fetches data from the view and two tables. In addition, we can view a relationship between all the objects and an additional condition can be set.
Now, let’s look at the query as the database optimizer sees it:
SELECT list of fields FROM (SELECT ...), table1, table2 WHERE some parameters
In this case, I replaced the view with an abstract SELECT statement in parentheses that the view consists of. It turns out that the server sees a query with a subquery. If the subquery returns dynamic data, rather than a static value, this data selection will work more slowly than if we wrote the same query without the subquery.
You should never grant a full access to objects without a special need. I always start providing rights only to permit the data selection with the SELECT statement. If the user really needs to insert new records and they cannot perform the tasks assigned to them, in this case, add the permission on the INSERT statement of the specified table.
The most dangerous operations for the data are modification and deletion, namely UPDATE and DELETE, respectively. You need to grant these rights carefully. Make sure that the data can be modified or deleted. Only in this case, select the appropriate rights. Some tables are extended only by nature.
It is necessary to be sure that the data will be affected frequently. For example, the table of employees can be extended and modified, but a single record should be never deleted. The removal may influence the background of employees’ work, reporting, and data integrity. The case when a human resources officer accidentally adds an extra record and wants to delete it is still possible. However, such cases are rare and the error can be fixed by the database administrator. We understand that no one wants to overwork and it is easier to grant a permission, however, security is more valuable.
Granting rights is performed using the GRANT operator. In general, it looks like this:
GRANT what to give rights to ON objects TO users or roles
For example, the following query grants the right to insert and view the TestTable table to User1:
GRANT Select, Insert ON TestTable TO User1
Many users are afraid of foreign keys because usually, they do not allow deleting data when there is an outer join. The problem can be easily solved if a cascade deletion is established. However, most specialists dislike this possibility. One ridiculous action can corrupt very important data without any confirmation requests. Linked data must be deleted explicitly, and only if the user has consciously confirmed that the data can be deleted.
Do not be afraid of foreign keys. They provide us with a convenient way to control the data integrity from the database server, thus, this is security that never hurts. The fact that there may be issues with the deletion is only a benefit. It is better not to delete the data rather than to lose it forever. The foreign key along with the index does not reduce performance. This is just a small check when deleting the data or modifying the key field to which the data is connected in different tables.
Backup is also one of the security factors we ignore before the first data loss. However, personally, I would have included it in the main ones. The data loss can be caused not only by hackers and inapt users but also due to equipment malfunctions. Failures in the equipment can lead to database loss, the restoration of which can take hours or even days.
It is necessary to develop the most effective backup policy in advance. What is meant thereby? Downtime caused by data loss and until the moment of restoration of working capacity should be minimal. The data loss should be minimal as well, and the backup should not affect the user work. If there are money and opportunities, it is better to use such systems as RAID, cluster or even data replication.
Backup and recovery are a separate and interesting topic. We could not touch on it here, but we will not consider it in details.
We have considered security basics, in particular for Oracle. Do not forget that the protection provided by the database is only one level, while the protection must be multilevel. To sleep a bit with a clear mind, it is necessary to implement the entire security features on the network, servers and all computers, including antiviruses, anti-spyware, VPN, IDS, etc. The more levels of protection there are, the more difficult it will be to overcome them.
There should be a clear security policy and control. If an employee leaves, their account is deleted. If you have users working with the same password or using a group password to perform any tasks, all these passwords must be changed. For example, if an administrator leaves, and you have all administrators using the same account, you must change the password.
The security is necessary. You must protect yourself not only from hackers but also from “novice” users, who can corrupt data by their inexperience. A good security policy helps prevent such cases and this possibility cannot be excluded. It is better to provide the ability to secure data from inexperience in advance than to lose a lot of time to restore data and get unnecessary downtime.
dbForge Studio for Oracle is a powerful tool for developing and managing your Oracle database Increase your coding speed with a full set of features: code debugging, compiling of invalid objects, assigning user roles and many more.