S 2.129 Controlling access to database information

Initiation responsibility: Information Security Management, Head of IT

Implementation responsibility: Administrator

A host of safeguards must be implemented to effectively protect the confidentiality and integrity of the data in a database. In addition to controlling access to the database system, which is described in S 2.128 Controlling access to a database system, the most important methods for controlling access to the data in the database are listed in the following:

Protection of the database objects

The database objects, meaning the tables, indexes, database procedures, etc., should be assigned logically to the applications that use these objects. The resulting groups of database objects for each application are then assigned to user IDs specifically set up for this purpose. This allows setting of the access authorisations for the database objects in such a way that the objects can only be modified using these specific user IDs. If several applications access the same database objects, these applications should be isolated by placing them in a separate group.

For example, if the data of two applications A and B are administrated in the database, two database user IDs named AppA and AppB should be created. All database objects used exclusively by application A are then created and administrated using the AppA database user ID. The same also applies correspondingly to the database objects of application B.

One example of a central database object that would be used by both is a table containing all accessible printers. Database objects in this category should not be assigned to one of the identifiers used for the applications (AppA or AppB), such database objects should be consolidated under a separate identifier (for example Printers) and administrated using only this ID instead.

These special IDs are not assigned to a single person. Instead, only the persons authorised to perform the corresponding tasks (for example the database administrator or the administrator of the corresponding application) are provided with the password for the corresponding ID in case it becomes necessary to modify the database objects (for more information on this subject, see also S 4.68 Ensuring consistent database management).

Protection of the data

By defining views and procedures, it is possible to create special user views to the data so that the data in the database can be hidden and/or made visible for the users according to certain criteria. A view or a procedure can be used to explicitly specify which fields in one or more tables will be shown in which order to a user. It is possible in this case to filter the data according to special conditions and limit the amount of data returned using specific constraints. By granting data access rights (the grants described in the following) to such views and procedures restrictively, it is possible to protect confidential data against unauthorised access.

It is possible to increase the security further by separating the data from the functionalities, in this case separating the views and procedures from the real data by storing it in a separate database. The user or application can only access the views and procedures available in the stand-alone database. Only these views and procedures are able to access the data stored in the database. The data access rights for the users and applications are stored in the stand-alone database.

In this case, data access rights (grants) can be assigned for tables, views, etc., or even for individual fields of a table. These rights are always linked to certain users, roles, or user groups. It is preferable to clearly separate the access rights of the users (usually using a user ID and password) on the one hand, and the data access rights of user groups and roles to database objects on the other hand. Users are then linked to DB objects by assigning the individual users to the user groups or roles equipped with the necessary data access rights. The access right types that can be assigned are read, update, delete, insert, and create privileges, and for procedures it is possible to assign execute privileges as well. The steps to follow when assigning access authorisations should be described exactly in the database concept. In general, only the access authorisations actually necessary should actually be assigned. Otherwise, there is a risk of losing track of the current data access right assignments, which may then open additional security gaps. In particular, the possibility of granting rights to all users (GRANT ... TO PUBLIC) provided by the DBMS should not be used.

In general, only the owner of a database object is allowed to grant other users access to the database object. However, some database systems provide functionality that also enables the owner of a database object to authorise other users to grant the right to access his/her data. This capability should only be used in exceptional cases and only if reasons are provided for its use, since it is possible for the owner of the database object to lose control over the access to his/her data and database objects in this manner.

Restrictive access to data using applications

Applications should support restrictive access to the data, i.e. the user should only be provided with the functionality and data required by the user to perform his/her tasks depending on his/her user ID and group membership. One way of implementing such an application in the database is to use stored procedures.

Stored procedures are sequences of SQL statements that have been optimised and then stored in the database. When calling a stored procedure, the user only needs to enter the name and possible parameters to execute the statements stored in the procedure. The advantage of this method is that it is not necessary to transmit all the statements to the database server, which may place a high load on the network when the corresponding operations are complex.

Another advantage is that the statements are stored in the database system in an optimised and pre-compiled format so that they can be executed quickly when called. The most restrictive form of rights assignment is to grant data access rights to the stored procedures instead of to the tables or views. When data access rights are only granted to the stored procedures, the user will only be able to execute the operations selected by the person responsible for the database.

Examples:

• In Microsoft Access, a variety of authorisations can be granted for accessing the database itself (open/execute, exclusive, administer) or for the tables and queries (read data, update data, delete data, insert data). These authorisations can then be assigned to different users or different user groups. The "administrators" and "users" groups are set up by default in Microsoft Access, with the "user" group possessing "read data" and "update data" privileges for tables and queries, as well as the "open/execute" privilege for databases. In order to obtain more detailed control over the data access rights, it is possible to define different groups and assign each of them different rights.
• In an Oracle database, it is possible to create the "Department_1" group using the CREATE ROLE and GRANT commands and to then assign rights to this group, e.g. the right to establish a connection to the database (connect), open a session (create session), or execute read-only queries on certain tables (select).
  
  When existing database users are assigned to the "Department_1" group, these users are granted all authorisations available to the user group they are assigned to. In this example, a user assigned to the "Department_1" group would only be able to access the tables assigned to this group and this access would be read-only (select); the user would not be able to change the data (using insert, delete, update, etc.).
• A stored procedure with PL/SQL statements in Oracle has an input parameter specifying the article number of a product. The stored procedure then searches through all of the tables required to calculate the output parameter and returns the price of the article, amongst other information.
  
  Users are only assigned the rights necessary to use the stored procedure, but are not granted any rights to the corresponding tables. For example, this also prevents a user with the corresponding select privileges from executing a time-consuming selection directly in the corresponding tables.

Review questions:

• Have the database objects been assigned unambiguously to a database user ID and are the data access authorisations for the data objects grouped using this ID?
• Are data access rights (grants) preferably granted using user groups and roles individual users are assigned to?
• Are the steps for grating data access authorisations described in detail in the database concept so that only the data access authorisations required for performing the tasks are granted?
• Do the applications support restrictive access to the database (e.g. using stored procedures) depending on the user ID and the group membership?