Oracle7 Server Application Developer's Guide

Application Security Policy

Draft a security policy for each database application. For example, each developed database application (such as a precompiler program or Oracle Forms form) should have one or more application roles that provide different levels of security when executing the application. The application roles can be granted to user roles or directly to specific usernames.

Applications that potentially allow unrestricted SQL statement execution (such as SQL*Plus or SQL*ReportWriter) also need tight control to prevent malicious access to confidential or important schema objects.

Application Administrators

In large database systems with many database applications (such as precompiler applications or Oracle Forms applications), it may be desirable to have application administrators. An application administrator is responsible for

creating roles for an application and managing the privileges of each application role

creating and managing the objects used by a database application

maintaining and updating the application code and Oracle procedures and packages, as necessary

As the application developer, you might also assume the responsibilities of the application administrator. However, these jobs might be designated to another individual familiar with the database applications.

Roles and Application Privilege Management

Because most database applications involve many different privileges on many different schema objects, keeping track of which privileges are required for each application can be complex. In addition, authorizing users to run an application can involve many GRANT operations. To simplify application privilege management, a role should be created and granted all the privileges required to run each application. In fact, an application might have a number of roles, each granted a specific subset of privileges that allow fewer or more capabilities while running the application.

Example

Assume that every administrative assistant uses the Vacation application to record vacation taken by members of the department. You should

1. Create a VACATION role.

2. Grant all privileges required by the Vacation application to the VACATION role.

3. Grant the VACATION role to all administrative assistants or to a role named ADMIN_ASSITS (if previously defined). Grouping application privileges in a role aids privilege management. Consider the following administrative options:

You can grant the role, rather than many individual privileges, to those users who run the application. Then, as employees change jobs, only one role grant or revoke (rather than many privilege grants and revokes) is necessary.

You can change the privileges associated with an application by modifying only the privileges granted to the role, rather than the privileges held by all users of the application.

You can determine which privileges are necessary to run a particular application by querying the ROLE_TAB_PRIVS and ROLE_SYS_PRIVS data dictionary views.

You can determine which users have privileges on which applications by querying the DBA_ROLE_PRIVS data dictionary view.

Enabling Application Roles

A single user can use many applications and associated roles. However, you should only allow a user to have the privileges associated with the currently running application role. For example, consider the following scenario:

The ORDER role (for the ORDER application) contains the UPDATE privilege for the INVENTORY table.

The INVENTORY role (for the INVENTORY application) contains the SELECT privilege for the INVENTORY table.

Several order entry clerks have been granted both the ORDER and INVENTORY roles.

Therefore, an order entry clerk who has been granted both roles can presumably use the privileges of the ORDER role when running the INVENTORY application to update the INVENTORY table. However, update modification to the INVENTORY table is not an authorized action when using the INVENTORY application, but only when using the ORDER application.

To avoid such problems, issue a SET ROLE statement at the beginning of each application to automatically enable its associated role and, consequently, disable all others. By using the SET ROLE command, each application dynamically enables particular privileges for a user only when required. A user can make use of an application's privileges only when running a given application, and is prevented from intentionally or unintentionally using them outside the context of an application.

The SET ROLE statement facilitates privilege management in that, in addition to letting you control what information a user can access, it allows you to control when a user can access it. In addition, the SET ROLE statement keeps users operating in a well defined privilege domain. If a user gets all privileges from roles, the user cannot combine them to perform unauthorized operations; see "Enabling and Disabling Roles" for more information.

SET_ROLE Procedure

The DBMS_SESSIONS.SET_ROLE procedure behaves similarly to the SET ROLE statement and can be accessed from PL/SQL. You cannot call SET_ROLE from a stored procedure. This restriction prevents a stored procedure from changing its security domain during its execution. A stored procedure executes under the security domain of the creator of the procedure.

DBMS_SESSION.SET_ROLE is only callable from anonymous PL/SQL blocks. Because PL/SQL does the security check on SQL when an anonymous block is compiled, SET_ROLE will not affect the security role (that is, will not affect the roles enabled) for embedded SQL statements or procedure calls.

For example, if you have a role named ACCT that has been granted privileges that allow you to select from table FINANCE in the JOE schema, the following block will fail:

DECLARE
    n NUMBER
BEGIN
    SYS.DBMS_SESSION.SET_ROLE('acct')
    SELECT empno INTO n FROM JOE.FINANCE
END;

This block fails because the security check that verifies that you have the SELECT privilege on table JOE.FINANCE happens at compile time. At compile time, you do not have the ACCT role enabled yet. The role is not enabled until the block is executed.

The DBMS_SQL package, however, is not subject to this restriction. When you use this package, the security checks are performed at runtime. Thus, a call to SET_ROLE would affect the SQL executed using calls to the DBMS_SQL package. The following block would therefore be successful:

DECLARE
    n NUMBER
BEGIN
    SYS.DBMS_SESSION.SET_ROLE('acct');

    SYS.DBMS_SQL.PARSE
      ('SELECT empno FROM JOE.FINANCE');
    . . .
    --other calls to SYS.DBMS_SQL
    . . .
END;

Restricting Application Roles from Tool Users

Prebuilt database applications explicitly control the potential actions of a user, including the enabling and disabling of the user's roles while using the application. Alternatively, ad hoc query tools such as SQL*Plus allow a user to submit any SQL statement (which may or may not succeed), including the enabling and disabling of any granted role. This can pose a serious security problem. If you do not take precautions, an application user could have the ability to intentionally or unintentionally issue destructive SQL statements against database tables while using an ad hoc tool, using the privileges obtained through an application role.

For example, consider the following scenario:

The Vacation application has a corresponding VACATION role.

The VACATION role includes the privileges to issue SELECT, INSERT, UPDATE, and DELETE statements against the EMP table.

The Vacation application controls the use of the privileges obtained via the VACATION role; that is, the application controls when statements are issued.

Now consider a user who has been granted the VACATION role. However, instead of using the Vacation application, the user executes SQL*Plus. At this point, the user is restricted only by the privileges granted to him explicitly or via roles, including the VACATION role. Because SQL*Plus is an ad hoc query tool, the user is not restricted to a set of predefined actions, as with designed database applications. The user can query or modify data in the EMP table as he or she chooses.

To avoid potential problems like the one above, consider the following policy for application roles:

1. Each application should have distinct roles:

One role should contain all privileges necessary to use the application successfully. Depending on the situation, there might be several roles that contain more or fewer privileges to provide tighter or less restrictive security while executing the application. Each application role should be protected by a password (or by operating system authentication) to prevent unauthorized use.

Another role should contain only non-destructive privileges associated with the application (that is, SELECT privileges for specific tables or views associated with the application). The read-only role allows the application user to generate custom reports using ad hoc tools such as SQL*Plus, SQL*ReportWriter, SQL*Graph, etc. However, this role does not allow the application user to modify table data outside the application itself. A role designed for an ad hoc query tool may or may not be protected by a password (or operating system authentication).

2. At startup, each application should use the SET ROLE command to enable one of the application roles associated with that application. If a password is used to authorize the role, the password must be included in the SET ROLE statement within the application (encrypted by the application, if possible); if the role is authorized by the operating system, the system administrator must have set up user accounts and applications so that application users get the appropriate operating system privileges when using the application.

3. At termination, each application should disable the previously enabled application role.

4. Application users should be granted application roles, as required. The administrator can prohibit a user from using application data with ad hoc tools by not granting the non-destructive role to the user. Using this configuration, each application enables the proper role when the application is started, and disables the role when the application terminates. If an application user decides to use an ad hoc tool, the user can only enable the non-destructive role intended for that tool.

Additionally, you can

Specify the roles to enable when a user starts SQL*Plus, using the PRODUCT_USER_PROFILE table. This functionality is similar to that of a precompiler or OCI application that issues a SET ROLE statement to enable specific roles upon application startup.

Disable the use of the SET ROLE command for SQL*Plus users with the PRODUCT_USER_PROFILE table. This allows a SQL*Plus user only the privileges associated with the roles enabled when the user started SQL*Plus.

Other ad hoc query and reporting tools, such as SQL*ReportWriter, SQL*Graph, etc., can also make use of the PRODUCT_USER_PROFILE table to restrict the roles and commands that each user can use while running that product. For more information about these features, see the appropriate tool manual.

Schemas

Each database username is said to be a schema--a security domain that can contain schema objects. The access to the database and its objects is controlled by the privileges granted to each schema.

Most schemas can be thought of as usernames--the accounts set up to allow users to connect to a database and access the database's objects. However, unique schemas do not allow connections to the database, but are used to contain a related set of objects. Schemas of this sort are created as normal users, yet not granted the CREATE SESSION system privilege (either explicitly or via a role). However, you must temporarily grant the CREATE SESSION privilege to such schemas if you want to use the CREATE SCHEMA command to create multiple tables and views in a single transaction.

For example, the schema objects for a specific application might be owned by a schema. Application users can connect to the database using typical database usernames and use the application and the corresponding objects if they have the privileges to do so. However, no user can connect to the database using the schema set up for the application, thereby preventing access to the associated objects via this schema. This security configuration provides another layer of protection for objects.