home |  contact

Oracle Access Control

External attacks by hackers and internal abuses by employees are on the rise; CERT reported that computer security incidents doubled in 2001 over 2000. More specifically, hack attempts against Oracle have increased significantly since the Oracle "unbreakable" campaign began (as much as 15 times by some estimates), requiring even more diligence on the part of DBAs.

The privacy requirements under the Health Information Portability and Accountability Act (HIPAA) provide patients with the right to request restrictions on access to their medical information and also formalize the patient's right to access their own medical records. In addition, HIPAA requires that access to patient information be the minimum necessary. The Gramm-Leach-Bliley Act (GLB) provides for similar protection of personal financial information.

Databases and applications must provide the mechanisms to control and record information access. This article focuses on access control mechanisms and is the second in a series of articles that look at implementing a secure Oracle database. The first article in the series looked at authentication and future articles will look at auditing, configuration, and policy/procedure issues.

Access Control - An Overview

Access control deals with the concept of who has access to what, whether the what is a system or a set of information, and the types of operations that can be executed. There are three broad types, or models, of access control; discretionary which allows the user to specify privileges; non-discretionary in which a central authority specifies access; and mandatory in which the system controls access based upon user clearances and information classification levels.

Most organizations operate under the principle of Least Privilege, which allows users to access the information they need to do their jobs, but prohibit access to any other information. Unfortunately, most applications provide the control mechanism, which can lead to a higher than acceptable risk situation if ad-hoc query tools are used. This leaves the individual application's configuration to enforce the least privilege principle, rather than the database. If more than one application accesses the database, multiple sets of user privileges must be maintained. In addition, data warehouses are often configured in a more open fashion, further increasing the risk that the confidentiality of information will not be maintained. It is strongly recommended that access to information be provided at the data's source to assure that information is protected regardless of access mechanism (application, ad-hoc tools, direct database access).

Native Access Control Mechanisms

To enforce access control at the database level, Oracle provides support for all three models of access control through the use of roles, views, virtual private databases, and as of release 8.1.7, label security. These are discussed below.

Roles

Roles provide a means of assigning an organized collection of permissions to users. For instance, a manager role may be assigned a greater set of permissions and privileges than a staff-level employee. Rather than having to assign each user his or her own set of permissions, roles can be used to greatly reduce the time and effort required to create the proper permissions for any given user. Since job descriptions generally establish appropriate permissions and privileges, a DBA can easily assign a role without requiring management to specify each individual privilege. In addition, if permissions need to be changed, a role can be easily modified and applied to all users to which it is assigned.

Oracle roles are a named set of privileges that may be assigned (granted) to users or to other roles. Access may be granted on objects (table or views) or for database system privileges. Roles are created with the Create Role command and privileges are then granted to the role with the Grant command. Roles can be created with passwords, which means the password is needed to enable the role and its associated privileges. Object privileges can be granted with the "with grant option" and system privileges can be granted with the "with admin option". These "with grant" options allow the maintenance of privileges to be delegated and/or decentralized reducing the burden on DBAs and giving information owners the means to implement their information protection requirements.

Note: If a role is a default role for a user, the password is not needed to enable the role - it will be enabled at logon. When using password-protected roles, the DBA should assure that those roles are NOT in the user's default role list.

Three standard roles are available in Oracle, Connect, Resource, and DBA. In many cases, the Connect role is granted to all users, Resource is granted to developers, and the DBA role is reserved for those in the database administration group. However, it is strongly recommended that general users not be granted the Connect role as it contains system privileges for creating tables, views, etc. Instead, it is recommended that general users be granted the Create Session system privilege - this allows the user to connect to the database only. Additional object privileges should be granted through appropriate application and/or functional roles. It is recommended that privileges not be granted to individual users, i.e., always use roles for granting privileges to users.

In the same manner, developers should NOT have the Resource role in QA or Production databases - this role should be granted only in a development environment. The Resource role allows for the creation of triggers, stored procedures, synonyms, etc. In addition, the Resource role comes with the Unlimited Tablespace system privilege which a developer should not need in a Production database - care should be taken to limit tablespace quotas in a shared development environment to minimize space utilization issues.

Note: Extreme caution should be used when granting Select/Update/Insert ANY object privileges; it is recommended that these object privileges NOT be granted to any user or developer.

Application Access Control

To assure that information is updated only through the appropriate application, password protected roles can be used. The application enables the role at execution time by supplying the appropriate password. (The set role command is used to enable the role within the application; set role app_update_role identified by password;). Care should be taken to assure that the password is known only to the application. There are a number of ways to accomplish hiding the password, from stored procedures to encryption - those will be left to the reader's imagination and/or to future articles. However, the steps below will allow the role to be set up properly to control update access from an application:

1. Create a password protected role for update purposes.

   Create role app_update_role identified by password;

2. Create a non-password protected role for query purposes.

   Create role app_query_role;

3. Grant update privileges to the password protected role.

   Grant insert, update, delete on app.table1 to app_update_role;

4. Grant select privileges to the non-password protected role.

   Grant select on app.table1 to app_query_role;

5. Grant both roles to the users.

   Grant app_update_role, app_query_role to user1;

6. Issue the 'Alter user' command to assure the password protected role is NOT a default role.

   Alter user user1 default role app_query_role;

   OR

   Alter user user1 default role all except app_update_role;

Note:The role's password is stored in the SYS.EXU7ROL table; the password is encrypted. Make sure that only the SYS user has access to this table.

System Privilege Access Control

Roles can also be used to manage access to system privileges such as create table and alter index. It is recommended that these privileges be managed via roles rather than on a user-by-user basis. Care should be taken when granting users privileges with global or public implications such as create any... or create public... as these privileges are very powerful and may result in loss of data availability or confidentiality if used improperly.

Views

Views represent an excellent mechanism for controlling access to data. Views can limit access to only specified columns or rows within a single table or joined tables. Views can also ease application maintenance, for example, if data is accessed via a view, the underlying table can change without requiring application changes. Views can be either static or dynamic.

Static views are most often used to limit access to particular columns in a table. For example, if the employee table contains both home and office contact information, an emp_office view may exist that contains only office contact information. Static views may also exist to limit access to rows based on the content of a particular column. For example, the US_SALES view may contain all rows of the SALES table where the country column = 'US'. While this type of view is helpful, maintenance can be difficult.

Dynamic views are typically used where the results are based on some characteristic of the user executing the query, e.g., department, country, etc. Dynamic views frequently require the use of more than one table to gather the information needed, for example user information must be obtained from the employee table (country) in order to select only the correct rows from the sales table (where country = US). Queries of this type can be extremely complicated and performance may suffer if several large tables must be accessed.

Virtual Private Databases

A Virtual Private Database (VPD) provides the ability to modify queries based on a security policy that has been defined in a package and associated with a table or view. The VPD provides fine-grained access control which is data-driven, context-dependent, and row-based. Direct or indirect access to a table with an attached security policy causes the database to refer to the policy function which returns an access condition known as a predicate (a WHERE clause). The database then appends the WHERE clause to the user's SQL statement dynamically modifying the user's data access. For example, if the user (who belongs to Department 10) inputs the statement SELECT * FROM emp, the security policy function will add the clause WHERE DEPT = 10. There are two primary advantages in using VPD over dynamic views; 1) complex conditions are much easier to implement, and 2) the security policy can be changed without modifying the tables/views or applications. Complex conditions can be simplified by the use of application contexts; these conditions can specify the parameters of the query and can even be used to assure that all access is via an application.

Label Based Access Control

Another way to implement access controls within Oracle is via Label-based access control which is usually referred to as mandatory access control. Label-based access control allows organizations to assign sensitivity labels to data rows and control access to data based on those labels. Label security provides the functionality of the earlier "Trusted Oracle" without the requirement for a trusted operating system. The security classification system the government uses with labels such as CONFIDENTIAL, SECRET, or TOP SECRET is perhaps the most familiar example of label-based access control. The labels are assigned to data based on the sensitivity level of the information and access to the data labeled at a certain level (such as SECRET) is restricted to those users who have been granted that level of access or higher. Oracle Label Security is an add-on security option for the Oracle9i Enterprise Edition. The Oracle Policy Manager provides a graphical user interface for managing the labels. Oracle Label Security is built on the Virtual Private Database toolkit and mediates access to rows in database tables based on a label contained in the row, a label associated with each database session, and Oracle Label Security privileges assigned to the session.

Conclusion

Oracle provides several mechanisms for managing access to information, including roles, views, virtual private databases, and label based access. In the author's experience, very few organizations make use of password-protected roles, however, these provide a powerful mechanism for access control and should be considered, especially when developing applications in-house. Database access control is a critical component of an overall security strategy; providing access controls at the database level can minimize risk and lead to more efficient application development.

Home

Services

Partners

About Us

Contact Us