Skip Headers
Oracle® Database Security Guide
10g Release 2 (10.2)

Part Number B14266-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

13 Introducing Database Security for Application Developers

Creating an application security policy is the first step when writing secure database applications. An application security policy is a list of application security requirements and rules that regulate user access to database objects.

This chapter discusses aspects of application security and Oracle Database features that you should consider when drafting security policies for database applications. It contains the following topics:

13.1 About Application Security Policies

You should draft security policies for each database application. For example, each database application should have one or more database roles that provide different levels of security when executing the application. The database roles can be granted to user roles or directly to specific user names.

Applications that potentially allow unrestricted SQL statement execution (through tools such as SQL*Plus) also need security policies that prevent malicious access to confidential or important schema objects.

The following sections describe aspects of application security and the Oracle Database features that you can use to plan and develop secure database applications.

See Also:

13.2 Considerations for Using Application-Based Security

Two main issues to consider when you formulate and implement application security are covered in the following sections:

13.2.1 Are Application Users Also Database Users?

Oracle recommends that, where possible, you build applications in which application users are database users. In this way, you can leverage the intrinsic security mechanisms of the database.

For many commercial packaged applications, application users are not database users. For these applications, multiple users authenticate themselves to the application, and the application then connects to the database as a single, highly-privileged user. We will call this the One Big Application User model.

Applications built in this fashion generally cannot use many of the intrinsic security features of the database, because the identity of the user is not known to the database.

For example, use of the following features is compromised by the One Big Application User model:

Oracle Feature Limitations of One Big Application User Model
Auditing A basic principle of security is accountability through auditing. If all actions in the database are performed by One Big Application User, then database auditing cannot hold individual users accountable for their actions. The application must implement its own auditing mechanisms to capture individual user actions.
Oracle Advanced Security enhanced authentication Strong forms of authentication supported by Oracle Advanced Security (such as client authentication over SSL, tokens, and so on) cannot be used if the client authenticating to the database is the application, rather than an individual user.
Roles Roles are assigned to database users. Enterprise roles are assigned to enterprise users who, though not created in the database, are known to the database. If application users are not database users, then the usefulness of roles is diminished. Applications must then craft their own mechanisms to distinguish between the privileges which various application users need to access data within the application.
Enterprise user management feature of Oracle Advanced Security This feature enables an Oracle database to use the Oracle Identity Management Infrastructure by securely storing and managing user information and authorizations in an LDAP-based directory such as Oracle Internet Directory. While enterprise users do not need to be created in the database, they do need to be known to the database. The One Big Application User model cannot take advantage of Oracle Identity Management.

13.2.2 Is Security Enforced in the Application or in the Database?

Applications, whose users are also database users, can either build security into the application, or rely upon intrinsic database security mechanisms such as granular privileges, virtual private databases (fine-grained access control with application context), roles, stored procedures, and auditing (including fine-grained auditing). Oracle recommends that applications utilize the security enforcement mechanisms of the database as far as possible.

When security is enforced in the database itself, rather than in the application, it cannot be bypassed. The main shortcoming of application-based security is that security is bypassed if the user bypasses the application to access data. For example, a user who has SQL*Plus access to the database can execute queries without going through the Human Resources application. The user, therefore, bypasses all of the security measures in the application.

Applications that use the One Big Application User model must build security enforcement into the application rather than use database security mechanisms. Because it is the application, and not the database, that recognizes users, the application itself must enforce security measures for each user.

This approach means that each application that accesses data must reimplement security. Security becomes expensive, because organizations must implement the same security policies in multiple applications. Each new application requires an expensive reimplementation.

13.3 Managing Application Privileges

Most database applications involve different privileges on 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, you can create a role for each application and grant that role all the privileges a user needs to run the application. In fact, an application might have a number of roles, each granted a specific subset of privileges that allow greater or lesser capabilities while running the application.

For example, suppose that every administrative assistant uses the Vacation application to record the vacation taken by members of the department. To best manage this application, 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_ASSISTS (if previously defined).

Grouping application privileges in a role aids privilege management. Consider the following administrative options:

13.4 Creating Secure Application Roles

After database access privileges are grouped into roles, the roles are granted to the application user. Securing these roles can be accomplished in two ways:

13.4.1 An Example of Creating a Secure Application Role

Steps to create a secure application role is as follows:

  1. Create the roles as application roles and specify the authorized package that will enable the roles. In this example, hr.hr_admin is the example authorized package.

    CREATE ROLE admin_role IDENTIFIED USING hr.hr_admin;
    CREATE ROLE staff_role IDENTIFIED USING hr.hr_admin;
    
    

    Note:

    You need to set up the following data structures for the examples in this section to work:
    CREATE OR REPLACE PACKAGE hr_logon IS
    PROCEDURE hr_set_responsibility;
    END;
    /
    
    CREATE OR REPLACE PACKAGE BODY hr_logon IS
    PROCEDURE hr_set_responsibility IS
       BEGIN
          DBMS_SESSION.SET_IDENTIFIER (1234);
       END;
    END;
    /
    
  2. Create an invoker's right procedure.

    /* Create a dedicated authentication function for manageability so that changes in authentication policies would not affect the source code of the application - this design is up to the application developers */
    /* the only policy in this function is that current user must have been authenticated using the proxy user 'SCOTT' */
    CREATE OR REPLACE FUNCTION hr.MySecurityCheck RETURN BOOLEAN 
    AS
    BEGIN
        /* a simple check to see if current session is authenticated                                
        by the proxy user 'SCOTT' */
        if (sys_context('userenv','proxy_user') = 'SCOTT')
        then
            return TRUE;
        else
            return FALSE;
        end IF;
    END;
    
    GRANT EXECUTE ON hr.MySecurityCheck TO PUBLIC;
    
    /*Create the procedure*/
    CREATE OR REPLACE PACKAGE hr_admin
    AUTHID CURRENT_USER
    IS
    PROCEDURE hr_app_report;
    END;
    /
    CREATE OR REPLACE PACKAGE BODY hr_admin IS
    PROCEDURE hr_app_report IS
    BEGIN
        /* set application context in 'responsibility' namespace */
        hr_logon.hr_set_responsibility; 
        /* authentication check here */
        if (hr.MySecurityCheck = TRUE)
        then
             /* check 'responsibility' being set, then enable the roles without
             supplying the password */
             if (sys_context('hr','role') = 'admin' ) 
        then
             dbms_session.set_role('admin_role');
        else
             dbms_session.set_role('staff_role');
            end if;
        end if;
    END;
    END;
    

When enabling the secure application role, the database verifies that the authorized PL/SQL package is on the calling stack. This step verifies that the authorized PL/SQL package is issuing the command to enable the role. Also, when enabling the default user roles, no checking is performed for application roles.

You can use secure application roles to ensure a database connection. Because a secure application role is a role implemented by a package, the package can validate that users can connect to the database through a middle tier or from a specific IP address. In this way, the secure application role prevents users from accessing data outside an application. They are forced to work within the framework of the application privileges that they have been granted.

13.5 Associating Privileges with User Database Roles

A single user can use many applications and associated roles. However, you should ensure that the user has only the privileges associated with the current database role. Consider the following scenario:

In this scenario, an order entry clerk who has been granted both roles, can use the privileges of the ORDER role when running the INVENTORY application to update the INVENTORY table. The problem is that updating the INVENTORY table is not an authorized action when using the INVENTORY application, but only when using the ORDER application.

To avoid such problems, consider using either the SET ROLE statement or the SET_ROLE procedure as explained in the following section. You can also use the secure application role feature to allow roles to be set based on criteria you define.

Topics in this section include:

13.5.1 Using the SET ROLE Statement

Use a SET ROLE statement at the beginning of each application to automatically enable its associated role and to disable all others. In this way, each application dynamically enables particular privileges for a user only when required.

The SET ROLE statement simplifies privilege management. You control what information users can access and when they can access it. The SET ROLE statement also keeps users operating in a well-defined privilege domain. If a user obtains privileges only from roles, then the user cannot combine these privileges to perform unauthorized operations.

See Also:

13.5.2 Using the SET_ROLE Procedure

The PL/SQL package DBMS_SESSION.SET_ROLE is functionally equivalent to the SET ROLE statement in SQL. Roles are not supported in definer's rights procedures, so the DBMS_SESSION.SET_ROLE command cannot be called from them. However, the DBMS_SESSION.SET_ROLE command can be called from the following:

  • Anonymous PL/SQL blocks

  • Invoker's rights stored procedures (except those invoked from within definer's rights procedures)

SET ROLE takes effect only at execution time. Because anonymous blocks compile and execute simultaneously, roles are set before security checks are performed, so the block completes successfully. With respect to invoker's rights stored procedures, if they contain static SQL statements and access to objects in the SQL are authorized through roles, then the procedure may fail during compilation (Because the roles are not enabled until the procedure executes). To resolve this problem, replace static SQL with dynamic SQL by using the DBMS_SQL package. Then, security checks are performed at execution, while at the same time the SET ROLE statement enables roles.

Note:

If you use DBMS_SESSION.SET_ROLE within an invoker's rights procedure, then the role remains in effect until you explicitly disable it. In keeping with the least privilege principle (that users should have the fewest privileges they need to do their jobs), you should explicitly disable roles set within an invoker's rights procedure, at the end of the procedure.

See Also:

PL/SQL Packages and Types Reference for information about the DBMS_SESSION and the DBMS_SQL packages

13.5.3 Examples of Assigning Roles with Static and Dynamic SQL

This section shows how static and dynamic SQL affect the assignment of roles.

Note:

You need to set up the following data structures for the examples in this section to work:
CONNECT system/manager
DROP USER joe CASCADE;
CREATE USER joe IDENTIFIED BY joe;
GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO joe;
GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO scott;
DROP ROLE acct;
CREATE ROLE acct;
GRANT acct TO scott;
ALTER USER scott DEFAULT ROLE ALL EXCEPT acct;

CONNECT joe/joe;
CREATE TABLE finance (empno NUMBER);
GRANT SELECT ON finance TO acct;
CONNECT scott/tiger

Suppose you have a role named ACCT that has been granted privileges allowing you to select from table FINANCE in the JOE schema. In this case, the following procedure that uses static SQL fails:

CREATE OR REPLACE PROCEDURE statSQL_proc
AUTHID CURRENT_USER AS
    n NUMBER;
BEGIN
    SYS.DBMS_SESSION.SET_ROLE('acct');
    SELECT empno INTO n FROM JOE.FINANCE;
END;

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

In contrast, the DBMS_SQL package, which uses dynamic SQL, is not subject to this restriction. When you use this package, the security checks are performed when the procedure executes, and not when it is compiled. Thus, the following block is successful:

CREATE OR REPLACE PROCEDURE dynSQL_proc
AUTHID CURRENT_USER AS
   n NUMBER;
BEGIN
   SYS.DBMS_SESSION.SET_ROLE('acct');
   EXECUTE IMMEDIATE 'select empno from joe.finance' INTO n;
    --other calls to SYS.DBMS_SQL
END;
/

See Also:

Choosing Between Native Dynamic SQL and the DBMS_SQL Package in Oracle Database Application Developer's Guide - Fundamentals

13.6 Protecting Database Objects by Using Schemas

A schema is a security domain that can contain database objects. The privileges granted to each user or role control access to these database objects. This section covers:

13.6.1 Unique Schemas

Most schemas can be thought of as user names: the accounts that enable users to connect to a database and access the database 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, and yet are not granted the CREATE SESSION system privilege (either explicitly or through a role). However, you must temporarily grant the CREATE SESSION and RESOURCE privilege to such schemas if you want to use the CREATE SCHEMA statement to create multiple tables and views in a single transaction.

For example, the schema objects for a specific application might be owned by a given schema. If application users have the privileges to do so, then they can connect to the database using typical database user names and use the application and the corresponding objects. However, no user can connect to the database using the schema set up for the application. This configuration prevents access to the associated objects through the schema, and provides another layer of protection for schema objects. In this case, the application could issue an ALTER SESSION SET CURRENT_SCHEMA statement to connect the user to the correct application schema.

13.6.2 Shared Schemas

For many applications, users do not need their own accounts or schemas in a database. These users only need to access an application schema. For example, users John, Firuzeh, and Jane are all users of the Payroll application, and they need access to the Payroll schema on the Finance database. None of them need to create their own objects in the database. They need only access Payroll objects. To address this issue, Oracle Advanced Security provides enterprise users (schema-independent users).

Enterprise users, users managed in a directory service, do not need to be created as database users because they use a shared database schema. To reduce administration costs, an administrator can create an enterprise user once in the directory and point the user at a shared schema that many other enterprise users can also access.

See Also:

Oracle Database Advanced Security Administrator's Guide for information about how shared schemas are created and used for Enterprise User Security

13.7 Managing Object Privileges

As part of designing your application, you need to determine the types of users who will be working with the application and the level of access that they need to accomplish their designated tasks. You must categorize these users into role groups, and then determine the privileges that must be granted to each role. This section covers:

13.7.1 What Application Developers Need to Know About Object Privileges

End users are typically granted object privileges. An object privilege allows a user to perform a particular action on a specific table, view, sequence, procedure, function, or package. Table 13-1 summarizes the object privileges available for each type of object.

Table 13-1 How Privileges Relate to Schema Objects

Object Privilege Applies to Table? Applies to View? Applies to Sequence? Applies to Procedure?Foot 1 
ALTER Yes No Yes No
DELETE Yes Yes No No
EXECUTE No No No Yes
INDEX YesFoot 2  No No No
INSERT Yes Yes No No
REFERENCES YesFootref 2 No No No
SELECT Yes YesFoot 3  Yes No
UPDATE Yes Yes No No

Footnote 1 Stand-alone stored procedures, functions, and public package constructs
Footnote 2 Privilege that cannot be granted to a role
Footnote 3 Can also be granted for snapshots

13.7.2 SQL Statements Permitted by Object Privileges

As you implement and test your application, you should create each necessary role. Test the usage scenario for each role to be certain that the users of your application will have proper access to the database. After completing your tests, coordinate with the administrator of the application to ensure that each user is assigned the proper roles.

Table 13-2 lists the SQL statements permitted by the object privileges shown in Table 13-1.

Table 13-2 SQL Statements Permitted by Database Object Privileges

Object Privilege SQL Statements Permitted
ALTER ALTER object (table or sequence)

CREATE TRIGGER ON object (tables only)

DELETE DELETE FROM object (table, view, or synonym)
EXECUTE EXECUTE object (procedure or function)

References to public package variables

INDEX CREATE INDEX ON object (table, view, or synonym)
INSERT INSERT INTO object (table, view, or synonym)
REFERENCES CREATE or ALTER TABLE statement defining a FOREIGN KEY integrity constraint on object (tables only)
SELECT SELECT...FROM object (table, view, synonym, or snapshot)

SQL statements using a sequence


See Also:

"Understanding User Privileges and Roles" for a discussion of object privileges