Skip Headers
Oracle® Database Security Guide
11g Release 1 (11.1)

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

Go to previous page
Go to next page
View PDF

5 Managing Security for Application Developers

Creating an application security policy is the first step to create a secure database application. 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 you draft security policies for database applications. It contains the following topics:

5.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. You can then grant the database roles to user roles or directly to specific user names.

Applications that can potentially allow unrestricted SQL statement processing (through tools such as SQL*Plus or SQL Developer) 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.

5.2 Considerations for Using Application-Based Security

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

5.2.1 Are Application Users Also Database Users?

Where possible, you should 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. This is called the One Big Application User model.

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

Table 5-1 lists the features that the One Big Application User model compromises:

Table 5-1 Features Compromised by the One Big Application User Model

Oracle Database Feature Limitations of One Big Application User Model


A basic principle of security is accountability through auditing. If One Big Application User performs all actions in the database, 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 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

The Enterprise user management 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.

5.2.2 Is Security Better Enforced in the Application or in the Database?

Applications, whose users are also database users, can either build security into the application, or rely on 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 use the security enforcement mechanisms of the database as much 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, and each new application requires an expensive reimplementation.

5.3 Managing Application Privileges

Most database applications involve different privileges on different schema objects. Keeping track of the privileges that 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 can have several roles, each granted a specific subset of privileges that allow greater or lesser capabilities while running the application.

For example, suppose 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. Better yet, create a role that defines the privileges the administrative assistants have, and then grant the VACATION role to that role.

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

See Also:

5.4 Creating a Secure Application Role to Control Access to Applications

As explained in "Further Securing Role Privileges by Using Secure Application Roles", a secure application role is a role that is only enabled through its associated PL/SQL package. This package defines the policy needed to control access to an application.

This section includes the following topics:

See Also:

Oracle Database 2 Day + Security Guide for an example of how to create a secure application role

5.4.1 Step 1: Create the Secure Application Role

You create a secure application role by using the SQL statement CREATE ROLE with the IDENTIFIED USING clause. You must have the CREATE ROLE system privilege to execute this statement. Normally, you create this role and its associated package in the schema of the security administrator.

For example, to create a secure application role called hr_admin that is associated with the sec_mgr.hr_admin package, follow these steps:

  1. Create the security application role as follows:

    CREATE ROLE hr_admin IDENTIFIED USING sec_mgr.hr_admin;

    This statement indicates the following:

  2. Grant the security application role the privileges you would normally associate with this role.

    For example, to grant the hr_admin role SELECT, INSERT, UPDATE, and DELETE privileges on the HR.EMPLOYEES table, you enter the following statement:

    GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO hr_admin;

    You do not need to grant the role directly to the user. The PL/SQL package will do that for you, assuming the user passes its security policies. If your site requires that you directly grant users the role, then you must disable the role for that user. This is because the role needs to be initially disabled before the security policies in the package can begin performing their checks. To disable the role for user psmith (assuming psmith was granted it in the first place), enter the following statement:


5.4.2 Step 2: Create a PL/SQL Package to Define the Access Policy for the Application

To enable or disable the secure application role, you create the security policies of the role within a PL/SQL package. You also can create an individual function or procedure to do this, but a package lets you group a set of functions or procedure together. Normally, you create this package in the schema of the security administrator.

The package defines a simple, clear interface to a set of related procedures and types that can be accessed by SQL statements. Packages also make code more reusable and easier to maintain. The advantage here for secure application roles is that you can create a group of security policies that, used together, present a solid security strategy designed to protect your applications. For users (or potential intruders) who fail the security policies, you can add auditing checks to the package to record the failure.

The package must accomplish the following:

  • It must use invoker's rights to enable the role.

    To create the package using invoker's rights, include the AUTHID CURRENT_USER clause in the package definition. You must create the package using invoker's rights in order for the package to work. Invoker's rights allow the user to have EXECUTE privileges on all objects that the package accesses.

    Roles that are enabled inside an invoker's right procedure remain in effect even after the procedure exits. In this case, you can have a dedicated procedure that deals with enabling the role for the rest of the session to use. Because users cannot change the security domain inside definer's rights procedure, secure application roles can only be enabled inside invoker's rights procedures.

  • It must enable the application to perform the necessary validation.

    For example, the application must validate that the user is in a particular department, the user session was created by proxy, the request comes from a particular IP address, or that the user was authenticated using an X.509 certificate. To perform the validation, applications can use session information accessible by using the SYS_CONTEXT SQL function with the USERENV namespace attributes ('userenv', session_attribute). The information returned by this function can indicate the way in which the user was authenticated, the IP address of the client, and whether the user connected through proxy authentication. To find session information for a user, you can configure an application context. See Chapter 7, "Using Application Contexts to Retrieve User Information" for details on application context.

  • The application must issue a SET_ROLE procedure by using dynamic SQL (DBMS_SESSION.SET ROLE).

    See Also:

For example, suppose you wanted to restrict anyone using the hr_admin role to employees who are on site (that is, using certain terminals) and between the hours of 8 a.m. and 5 p.m. As the system or security administrator, follow these steps:

  1. Create the procedure as follows:

      2 AS
      3 BEGIN
      4 IF (SYS_CONTEXT ('userenv','ip_address') 
      5   BETWEEN '' and ''
      6    AND
      7   TO_CHAR (SYSDATE, 'HH24') BETWEEN 8 AND 17)
      8 THEN
      9   DBMS_SESSION.SET_ROLE('hr_admin');
     10 END IF;
     11 END;

    In this example:

    • Line 4: Validates the user by using the SYS_CONTEXT SQL function to retrieve the user session information with the USERENV namespace attributes ('userenv', session_attribute). The information returned by this function can indicate the way in which the user was authenticated, the IP address of the client, and whether the user was proxied. See Oracle Database SQL Language Reference for more information about SYS_CONTEXT.

    • Lines 5–7: Create a test to grant or deny access. The test restricts access to users who are on site (that is, using certain terminals) and working between the hours of 8:00 a.m. and 5:00 p.m. If the user passes this check, the hr_admin role is granted.

    • Lines 8–9: Assuming the user passes the test, grants the role to the user by using the DBMS_SESSION.SET_ROLE procedure.

  2. Grant EXECUTE permissions for the hr_admin_role_check package to any user who was assigned it.

    For example:

    GRANT EXECUTE ON hr_admin_role_check TO psmith;

5.5 Associating Privileges with User Database Roles

Ensure that users have only the privileges associated with the current database role.

Topics in this section include:

5.5.1 Why Users Should Only Have the Privileges of the Current Database Role

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:

  • The ORDER role (for an application called Order) contains the UPDATE privilege for the INVENTORY table.

  • The INVENTORY role (for an application called Inventory) contains the SELECT privilege for the INVENTORY table.

  • Several order entry clerks were granted both the ORDER and INVENTORY roles.

In this scenario, an order entry clerk who was 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 for the INVENTORY application. It is an authorized action for the ORDER application.

To avoid this problem, use either the SET ROLE statement or the DBMS_SESSION.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.

5.5.2 Using the SET ROLE Statement to Automatically Enable or Disable Roles

Use a SET ROLE statement at the beginning of each application to automatically enable its associated role and to disable all others. 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:

5.5.3 Using the DBMS_SESSION.SET_ROLE Procedure to Enable or Disable Roles

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 you cannot call the DBMS_SESSION.SET_ROLE procedure from them. However, the DBMS_SESSION.SET_ROLE procedure can be called from the following:

  • Anonymous PL/SQL blocks

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

DBMS_SESSION.SET ROLE takes effect only at run 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 run time, at the same time as the SET ROLE statement enables roles.


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:

5.5.4 Example of Assigning Roles with Static and Dynamic SQL

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

Follow these steps:

  1. Connect to SQL*Plus as SYSTEM and then run the following SQL statements:

    CONNECT system
    Enter password: password
    CREATE USER joe IDENTIFIED BY bflstick2;
    DROP ROLE acct;
    CREATE ROLE acct;
    GRANT acct TO scott;
  2. Connect as user joe and then create a simple table.

    CONNECT joe
    Enter password: bflstick2
    CREATE TABLE finance (empno NUMBER);
    GRANT SELECT ON finance TO acct;
  3. Connect as user SCOTT.

    Enter password: password
  4. As user SCOTT, try creating the following procedure:

        n NUMBER;

    The procedure fails because the security check that verifies that you have the SELECT privilege on table 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.

  5. Now, still as user scott, try creating the following procedure:

       n NUMBER;
       EXECUTE IMMEDIATE 'select empno from' INTO n;
        --other calls to SYS.DBMS_SQL

    In contrast, the DBMS_SQL package, which uses dynamic SQL, is not subject to the restriction in the procedure that you tried to create in Step 4. When you use this package, the security checks are performed when the procedure executes, and not when it is compiled. Therefore, this PL/SQL block is successful.

5.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 includes the following topics:

5.6.1 Protecting Database Objects in a Unique Schema

You can think of most schemas as user names: the accounts that enable users to connect to a database and access the database objects. However, a unique schema does not allow connections to the database, but is used to contain a related set of objects. Schemas of this sort are created as typical 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 a unique schema if you want to use the CREATE SCHEMA statement to create multiple tables and views in a single transaction.

For example, a given schema might own the schema objects for a specific application. 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.

5.6.2 Protecting Database Objects in a Shared Schema

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 to only access the payroll objects. To address this issue, Oracle Advanced Security provides the enterprise users, which are 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, you 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.

For more information about managing enterprise users, see Oracle Database Enterprise User Security Administrator's Guide.

5.7 Managing Object Privileges in an Application

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 includes the following topics:

5.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 5-2 summarizes the object privileges available for each type of object.

Table 5-2 How Privileges Relate to Schema Objects

Object Privilege Applies to Table? Applies to View? Applies to Sequence? Applies to Procedure?Foot 1 

















YesFoot 2 










YesFootref 2






YesFoot 3 








Footnote 1 Standalone 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

See also "Auditing Schema Objects" for detailed information about how schema objects can be audited.

5.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 ensure 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 5-3 lists the SQL statements permitted by the object privileges shown in Table 5-2.

Table 5-3 SQL Statements Permitted by Database Object Privileges

Object Privilege SQL Statements Permitted


ALTER object (table or sequence)

CREATE TRIGGER ON object (tables only)


DELETE FROM object (table, view, or synonym)


EXECUTE object (procedure or function)

References to public package variables


CREATE INDEX ON object (table, view, or synonym)


INSERT INTO object (table, view, or synonym)


CREATE or ALTER TABLE statement defining a FOREIGN KEY integrity constraint on object (tables only)


SELECT...FROM object (table, view, synonym, or snapshot)

SQL statements using a sequence

See "About Privileges and Roles" for a discussion of object privileges. See also "Auditing SQL Statements" for detailed information about how SQL statements can be audited.

5.8 Parameters for Enhanced Security of Database Communication

Database administrators can manage security for their applications by following the procedures in this section.

5.8.1 Reporting Bad Packets Received on the Database from Protocol Errors

Networking communication utilities such as Oracle Call Interface (OCI) or Two-Task Common (TTC) can generate a large disk file containing the stack trace and heap dump when the server receives a bad packet, out-of-sequence packet, or a private or an unused remote procedure call. Typically, this disk file can grow quite large. An intruder can potentially cripple a system by repeatedly sending bad packets to the server, which can result in disk flooding and denial of service. An unauthenticated client can also mount this type of attack.

You can prevent these attacks by setting the SEC_PROTOCOL_ERROR_TRACE_ACTION initialization parameter to one of the following values:

  • None: Configures the server to ignore the bad packets and does not generate any trace files or log messages. Use this setting if the server availability is overwhelmingly more important than knowing that bad packets are being received.

    For example:

  • Trace (default setting): Creates the trace files, but it is useful for debugging purposes, for example, when a network client is sending bad packets as a result of a bug.

    For example:

  • Log: Writes a short, one-line message to the server trace file. This choice balances some level of auditing with system availability.

    For example:

  • Alert: Writes a short, one-line error message to the server trace file and alert log.

    For example:


5.8.2 Terminating or Resuming Server Execution After Receiving a Bad Packet

After Oracle Database detects a client or server protocol error, it needs to continue execution. However, this could subject the server to further bad packets, which could lead to disk flooding or denial-of-service attacks.

You can control the further execution of a server process when it is receiving bad packets from a potentially malicious client by setting the SEC_PROTOCOL_ERROR_FURTHER_ACTION initialization parameter to one of the following values:

  • Continue (default setting): Continues the server execution. However, be aware that the server may be subject to further attacks.

    For example:

  • Delay,m: Delays the client m seconds before the server can accept the next request from the same client connection. This setting prevents malicious clients from excessively using server resources while legitimate clients experience a degradation in performance but can continue to function.

    For example:

  • Drop,n: Forcefully terminates the client connection after n bad packets. This setting enables the server to protect itself at the expense of the client, for example, loss of a transaction. However, the client can still reconnect, and attempt the same operation again.

    For example:


5.8.3 Configuring the Maximum Number of Authentication Attempts

With Oracle Database, a server process is first started, and then the client authenticates with this server process. An intruder could start a server process first, and then issue an unlimited number of authenticated requests with different user names and passwords in an attempt to gain access to the database.

You can limit the number of failed login attempts for application connections by setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter to restrict the number of authentication attempts on a connection. After the specified number of authentication attempts fail, the database process drops the connection. By default, SEC_MAX_FAILED_LOGIN_ATTEMPTS is set to 10.

Remember that the SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter is designed to prevent potential intruders from attacking your applications; it does not apply to valid users. The sqlnet.ora INBOUND_CONNECT_TIMEOUT parameter and the FAILED_LOGIN_ATTEMPTS initialization parameter also restrict failed logins, but the difference is that these two parameters only apply to valid user accounts.

For example, to limit the maximum attempts to 5, set SEC_MAX_FAILED_LOGIN_ATTEMPTS as follows in the initsid.ora initialization parameter file:


5.8.4 Controlling the Display of the Database Version Banner

Detailed product version information should not be accessible before a client connection (including an Oracle Call Interface client) is authenticated. An intruder could use the database version to find information about security vulnerabilities that may be present in the database software.

You can restrict the display of the database version banner to unauthenticated clients by setting the SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter in the initsid.ora initialization parameter file to either YES or NO. By default, SEC_RETURN_SERVER_RELEASE_BANNER is set to NO.

For example, if you set it to YES, the Oracle Database displays the full correct database version:

Oracle Database 11g Enterprise Edition Release - Production

In the future, if you install Oracle Database, for example, it will display the following banner:

Oracle Database 11g Enterprise Edition Release - Production

However, if in that same release, you set it to NO, then Oracle Database restricts the banner to display the following fixed text starting with Release 11.1:

Oracle Database 11g Release - Production

5.8.5 Configuring Banners for Unauthorized Access and Auditing User Actions

You should create and configure banners to warn users against unauthorized access and possible auditing of user actions. The notices are available to the client application when it logs into the database.

To configure these banners to display, set the following sqlnet.ora parameters on the database server side to point to a text file that contains the banner information:


    SEC_USER_UNAUTHORIZED_ACCESS_BANNER = /opt/Oracle/11g/dbs/unauthaccess.txt

    SEC_USER_AUDIT_ACTION_BANNER = /opt/Oracle/11g/dbs/auditactions.txt

By default, these parameters are not set.

After you set the these parameters, the Oracle Call Interface application needs to the use the appropriate OCI APIs to retrieve these banners and present them to the end user.