S 2.129 Controlling access to database information

Initiation responsibility: Head of IT Section, IT Security Management

Implementation responsibility: Administrators

A number of measures are required to effectively protect the confidentiality and integrity of data in a database. In addition to measures for controlling access to a database system, described in S 2.128 Controlling access to a database system, the following measures are essentially needed for controlling access to database information:

• Protection of database objects

The database objects, i.e. tables, indices, database procedures etc. should be assigned logically to the applications using these objects. The resulting groups of database objects pertaining to each application are each assigned an account configured specially for this purpose. This allows the access rights on the database objects to be defined such that the objects can only be modified via these special IDs. If several applications access the same database objects, these should be put into a separate group.

For example, if the data of two applications A and B are to be managed in the database, two database accounts - apnA and apnB - need to be created. All database objects which can be allocated uniquely to application A are configured and managed with database account apnA. The database objects of application B are handled similarly.

One example of a central database object used by both applications is a table which lists all the printers installed. Database objects in this category should not be assigned one of the existing accounts (apnA or apnB); instead, such database objects should be grouped and managed centrally under a separate account (e.g. print).

Such special IDs are not related to persons. Instead, staff members authorised specifically for this purpose (e.g. the administrator of the database or the corresponding application) receive the password of the required account if the database objects need to be modified.

• Data security

Special views can be configured for users, allowing data to be rendered visible or kept concealed in accordance with specified criteria. A view is used to explicitly define the fields of one or more tables which can be viewed by a user. A restrictive allocation of access rights (or grants, as described below) for such views allows confidential data to be protected against unauthorised access.

Access rights (grants) need to be allocated for tables, views and even individual fields of a table. These rights generally pertain to individual users, roles or user groups. However, such access rights should always be granted to user groups or roles, not to individual users, as a high number of users would require a great deal of administrative effort in this case. The following types of access rights are available: read, update, delete and insert. Access rights should be granted as sparingly as possible, otherwise it becomes increasingly difficult to retain a clear overview of the actual access rights, so that security pitfalls are created. In particular, the possibility of granting rights to all users (GRANT ... TO PUBLIC) should not be used.

In general, only the owner of a database object is allowed to grant access rights to other users. However, some database systems also allow the owner of a database object to authorise other users to grant access rights. This facility should only be made use of in exceptional cases, as it no longer allows the access control of data and database objects.

• Restrictive access to data via applications

Applications should support restrictive access to data, i.e. only those functions and data actually required by users for fulfilling their responsibilities should be made available to them (in accordance with the user IDs and the group memberships). One method of implementing this is through the use of stored procedures.

Stored procedures are sequences of SQL statements which have been stored in the database in a pre-optimised manner. To invoke a stored procedure, only its name and, if applicable, certain parameters need to be entered in order to execute the underlying SQL statements. This is advantageous, because not all of the SQL statements need to be transferred to the database server, thus reducing the load on the network when complex operations are involved. Furthermore, the database system is able to store the SQL statements in an optimised manner, so that they can be executed more rapidly. The greatest restriction which can be imposed for the purpose of access control is to allocate access rights for stored procedures instead of tables or views. If access rights are just allocated for stored procedures, then users can only invoke operations which have been enabled by the database administrators.

Examples:

1. In MS Access, different access rights can be granted for the database itself (open/execute, exclusive, administer) as well as for the tables and queries (read data, update data, delete data, add data). These rights can be assigned to various users and user groups. In MS Access, the groups named "administrators" and "users" have been configured by default; the "users" group contains the "read data" and "update data" rights for tables and queries, and the "open/execute" rights for databases. To allow a detailed control of access rights, it is possible to define separate groups which can be assigned different rights. This can be done in the menu titled Extras under the items Access rights and User and group accounts.

2. In an Oracle database, a group named "Department_1" can be created with the following instruction:

CREATE ROLE Department_1 IDENTIFIED BY ;

In the following example, the group named "Department_1" is granted the right to establish a connection with a database and to create a session:

GRANT CONNECT, CREATE SESSION TO Department_1;

In the following example, the same group is granted the right to perform a SELECT on the table named "Test":

GRANT SELECT ON Test TO Department_1;

In the following example, this group is granted the right to make modifications in the column titled "Comments" of the table named "Test":

GRANT UPDATE (Comments) ON Test TO Department_1;

3. An example of a stored procedure under Oracle with PL/SQL statements is provided in the following:

PROCEDURE Example (PArticleno IN NUMBER, PPrice OUT NUMBER) IS

BEGIN

BEGIN <>

SELECT price INTO PPrice

FROM TabB

WHERE articleno=PArticleno

END Block;

END;

The procedure named "Example" reads the price of an article in accordance with the article number from table TabB. Staff members who are to be allowed access to TabB exclusively by means of this method only are granted the right to use the stored procedure and no rights to access the table directly. This also prevents time-consuming search operations, for example.

Additional controls:

• Have database objects been protected against unauthorised access?

• Have views for individual users been defined and documented?

• Have access rights on data been allocated and documented?