15 Implementing Application Context and Fine-Grained Access Control

15.2.1 Task 1: Create a PL/SQL Package that Sets the Secure Context for Your Application

Begin by creating a PL/SQL package with functions that set the secure context for your application. This section presents the syntax and behavior of the SYS_CONTEXT SQL function, followed by an example for creating the PL/SQL package.

SYS_CONTEXT ('namespace', 'attribute', [length])

CREATE OR REPLACE PACKAGE App_security_context IS
   PROCEDURE Set_empno;
END;

CREATE OR REPLACE PACKAGE BODY App_security_context IS
   PROCEDURE Set_empno
   IS
   Emp_id NUMBER;
   BEGIN
    SELECT Empno INTO Emp_id FROM Emp
       WHERE Ename = SYS_CONTEXT('USERENV',
                                 'SESSION_USER');
    DBMS_SESSION.SET_CONTEXT('app_context', 'empno', Emp_id);
   END;
END;

EMPLOYEE_NAME = SYS_CONTEXT ('USERENV', 'SESSION_USER')

CREATE FUNCTION proc1 AS RETURN NUMBER;
BEGIN
     IF SYS_CONTEXT ('hr', 'id') = 5
     THEN RETURN 1; ELSE RETURN 2;
     END
END;

SELECT * FROM EMP WHERE proc1( ) = 1;

15.2.2 Task 2: Create a Unique Secure Context and Associate It with the PL/SQL Package

To perform this task, use the CREATE CONTEXT statement. Each context must have a unique attribute and belong to a namespace. That is, context names must be unique within the database, not just within a schema. Contexts are always owned by the SYS schema.

For example:

CREATE CONTEXT order_entry USING App_security_context;

Here, order_entry is the context namespace and App_security_context is the trusted package that can set attributes in the context namespace.

After you have created the context, you can set or reset the context attributes by using the DBMS_SESSION.SET_CONTEXT package. The values of the attributes you set remain either until you reset them or until the user ends the session.

You can only set the context attributes inside the trusted procedure you named in the CREATE CONTEXT statement. This prevents a malicious user from changing context attributes without proper attribute validation.

Alternatively, you can use the Oracle Policy Manager graphical user interface (GUI) to create a context and associate it with a PL/SQL package. Oracle Policy Manager, accessed from Oracle Enterprise Manager, enables you to apply policies to database objects and create application contexts. It also can be used to create and manage Oracle Label Security policies.

15.2.3 Task 3: Set the Secure Context Before the User Retrieves Data

Always use an event trigger on login to pull session information into the context. This sets the security-limiting attributes of the user for the database to evaluate, and thus enables it to make the appropriate security decisions.

Other considerations come into play if you have a changing set of books, or if positions change constantly. In these cases, the new attribute values may not be picked up right away, and you must force a cursor reparse to pick them up.

15.2.4 Task 4: Use the Secure Context in a VPD Policy Function

Now that you have set up the context and the PL/SQL package, your VPD policy functions can use the application context to make policy decisions based on different context values.

15.3.1 Example 1: Implementing the Policy

This example uses secure application context to implement a policy, in which customers can see only their own orders, and builds the application using the following steps:

• Step 1: Create a PL/SQL Package To Set the Secure Context for the Application

• Step 2: Create a Secure Application Context

• Step 3: Access the Secure Application Context Inside the Package

• Step 4: Create the New Security Policy

The procedure in this example assumes a one-to-one relationship between users and customers. It finds the user customer number (Cust_num) and caches the customer number in the application context. You can later refer to the cust_num attribute of the order entry context (oe_ctx) inside the security policy function.

Note that you could use a login trigger to set the initial context.

CREATE TABLE apps.customers (cust_no NUMBER(4), cust_name VARCHAR2(20));
CREATE TABLE scott.orders_tab (order_no NUMBER(4));

CREATE OR REPLACE PACKAGE apps.oe_ctx AS
   PROCEDURE set_cust_num;
END;

CREATE OR REPLACE PACKAGE BODY apps.oe_ctx AS
   PROCEDURE set_cust_num IS
     custnum NUMBER;
   BEGIN
      SELECT cust_no INTO custnum FROM customers WHERE cust_name =
         SYS_CONTEXT('USERENV', 'SESSION_USER');
     /* SET cust_num attribute in 'order_entry' context */
         DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum);
         DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum);
   END set_cust_num;
END;

CREATE CONTEXT Order_entry USING apps.oe_ctx;

CREATE OR REPLACE PACKAGE Oe_security AS 
FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2) 
RETURN VARCHAR2; 
END;

CREATE OR REPLACE PACKAGE BODY Oe_security AS

/* limits select statements based on customer number: */
FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2
IS
    D_predicate VARCHAR2 (2000);
    BEGIN
     D_predicate := 'cust_no = SYS_CONTEXT(''order_entry'', ''cust_num'')';
     RETURN D_predicate;    
    END Custnum_sec;
END Oe_security;

CONNECT sys/xIcf1T9u AS sysdba;
CREATE USER secusr IDENTIFIED BY secusr;

BEGIN
DBMS_RLS.ADD_POLICY ('scott', 'orders_tab', 'oe_policy', 'secusr',
                     'oe_security.custnum_sec', 'select');
END;

SELECT * FROM Orders_tab;

SELECT * FROM Orders_tab 
   WHERE Custno = SYS_CONTEXT('order_entry','cust_num'); 

15.3.2 Example 2: Controlling User Access with an Application

This example uses secure application context to control user access with a Human Resources application. Each task that you need to perform is described more fully in the sections that follow:

• Step 1: Create a PL/SQL Package to Set the Secure Context

• Step 2: Create the Secure Context and Associate It with the Package

• Step 3: Create the Initialization Script for the Application

In this example, assume that the application context for the Human Resources application is assigned to the HR_CTX namespace.

DROP USER apps CASCADE;
CREATE USER apps IDENTIFIED BY welcome1;

CREATE OR REPLACE PACKAGE apps.hr_sec_ctx IS 
   PROCEDURE set_resp_id (respid NUMBER);
   PROCEDURE set_org_id (orgid NUMBER);
  /* PROCEDURE validate_respid (respid NUMBER); */
  /* PROCEDURE validate_org_id (orgid NUMBER); */
END hr_sec_ctx;

CREATE OR REPLACE PACKAGE BODY apps.hr_sec_ctx IS
/* function to set responsibility id */
PROCEDURE set_resp_id (respid NUMBER) IS
BEGIN

/* validate respid based on primitive and other context */
/*    validate_respid (respid); */
/* set resp_id attribute under namespace 'hr_ctx'*/

    DBMS_SESSION.SET_CONTEXT('hr_ctx', 'resp_id', respid);
END set_resp_id;

/* function to set organization id */
PROCEDURE set_org_id (orgid NUMBER) IS
BEGIN

/* validate organization ID */
/*    validate_org_id(orgid); /*
/* set org_id attribute under namespace 'hr_ctx' */

    DBMS_SESSION.SET_CONTEXT('hr_ctx', 'org_id', orgid);
END set_org_id;

/* more functions to set other attributes for the HR application */
END hr_sec_ctx;

CREATE CONTEXT Hr_ctx USING apps.hr_sec_ctx;

APPS.HR_SEC_CTX.SET_RESP_ID(1);
APPS.HR_SEC_CTX.SET_ORG_ID(101);

CREATE VIEW Hr_organization_secv AS 
   SELECT * FROM hr_organization_unit 
      WHERE Organization_id = SYS_CONTEXT('hr_ctx','org_id');

CREATE TABLE hr_organization_unit (organization_id NUMBER);

15.3.3 Example 3: Event Triggers, Secure Application Context, Fine-Grained Access Control, and Encapsulation of Privileges

This example illustrates use of the following security features in Oracle Database:

• Event triggers

• Application context (session-based)

• Fine-grained access control

• Encapsulation of privileges in stored procedures

In this example, we associate a security policy with the table called DIRECTORY, which has the following columns:

CREATE TABLE Payroll(
   Srate  NUMBER, 
   Orate  NUMBER, 
   Acctno NUMBER, 
   Empno  NUMBER, 
   Name   VARCHAR2(20));
CREATE TABLE Directory_u(
   Empno NUMBER, 
   Mgrno NUMBER, 
   Rank  NUMBER);
CREATE SEQUENCE Empno_seq;
CREATE SEQUENCE Rank_seq;

The security policy associated with this table has two elements:

• All users can find the MGRID for a specific EMPNO. To implement this, we create a definer's right package in the human resources schema (HR) to perform SELECT on the table.

• Managers can update only those positions in the corporate hierarchy that are their direct subordinates. To do this, they must use only the designated application. You can implement this as follows:

  • Define fine-grained access control policies on the table based on EMPNO and application context.

  • Set EMPNO by using a login trigger.

  • Set the application context by using the designated package for processing the updates (event triggers and application context).

    Note:

    In this example, we grant UPDATE privileges on the table to PUBLIC, because fine-grained access control prevents an unauthorized user from wrongly modifying a given row.

The following code implements this example as described:

CONNECT system/yJdg2U1v AS sysdba
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE,CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE ANY TRIGGER TO HR IDENTIFIED BY HR;
CONNECT hr/hr;
CREATE TABLE Directory (Empno   NUMBER(4) NOT NULL,
                        Mgrno   NUMBER(4) NOT NULL,
                        Rank    NUMBER(7,2) NOT NULL);

CREATE TABLE Payroll (Empno  NUMBER(4) NOT NULL,
                      Name   VARCHAR(30) NOT NULL );

/* seed the tables with a couple of managers: */
INSERT INTO Directory VALUES (1, 1, 1.0);
INSERT INTO Payroll VALUES (1, 'KING');
INSERT INTO Directory VALUES (2, 1, 5);
INSERT INTO Payroll VALUES (2, 'CLARK');

/* Create the sequence number for EMPNO: */
CREATE SEQUENCE Empno_seq START WITH 5;

/* Create the sequence number for RANK:  */
CREATE SEQUENCE Rank_seq START WITH 100;

CREATE OR REPLACE CONTEXT Hr_app USING Hr.Hr0_pck;
CREATE OR REPLACE CONTEXT Hr_sec USING Hr.Hr1_pck;

CREATE OR REPLACE PACKAGE Hr0_pck IS
PROCEDURE adjustrankby1(Empno NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY Hr0_pck IS
/* raise the rank of the empno by 1:  */
PROCEDURE Adjustrankby1(Empno NUMBER)
IS
   Stmt   VARCHAR2(100);
   BEGIN
   
   /*Set context to indicate application state */
   DBMS_SESSION.SET_CONTEXT('hr_app','adjstate',1);
   /* Now we can issue DML statement:  */
   Stmt := 'UPDATE Directory d SET Rank = Rank + 1 WHERE d.Empno = ' 
   || Empno;
   EXECUTE IMMEDIATE STMT;

/* Re-set application state: */
   DBMS_SESSION.SET_CONTEXT('hr_app','adjstate',0);
   END;
END;

CREATE OR REPLACE PACKAGE hr1_pck IS PROCEDURE setid;
END;
/
/* Based on userid, find EMPNO, and set it in application context */

CREATE or REPLACE PACKAGE BODY Hr1_pck IS
PROCEDURE setid
  IS
  id NUMBER;
  BEGIN
    SELECT Empno INTO id FROM Payroll WHERE Name = 
      SYS_CONTEXT('userenv','session_user') ;
    DBMS_SESSION.SET_CONTEXT('hr_sec','empno',id);
    DBMS_SESSION.SET_CONTEXT('hr_sec','appid',id);
  EXCEPTION
    /* For purposes of demonstration insert into payroll table 
    /  so that user can continue on and run example. */
    WHEN NO_DATA_FOUND THEN
      INSERT INTO Payroll (Empno, Name) 
         VALUES (Empno_seq.NEXTVAL, SYS_CONTEXT('userenv','session_user'));
      INSERT INTO Directory (Empno, Mgrno, Rank)
         VALUES (Empno_seq.CURRVAL, 2, Rank_seq.NEXTVAL);
      SELECT Empno INTO id FROM Payroll WHERE Name =
        sys_context('userenv','session_user') ;
      DBMS_SESSION.SET_CONTEXT('hr_sec','empno',id);
      DBMS_SESSION.SET_CONTEXT('hr_sec','appid',id);
    WHEN OTHERS THEN
      NULL;
    /* If this is to be fired by using a "logon" trigger,
    /  you need to handle exceptions if you want the user to continue
    /  logging into the database. */ 
  END;
END;

GRANT EXECUTE ON Hr1_pck TO public;

CONNECT system/yJdg2U1v AS sysdba 

CREATE OR REPLACE TRIGGER Databasetrigger

AFTER LOGON
ON DATABASE
BEGIN
   hr.Hr1_pck.Setid;
END;

/* Creates the package for finding the MGRID for a particular EMPNO 
using definer's right (encapsulated privileges). Note that users are 
granted EXECUTE privileges only on this package, and not on the table 
(DIRECTORY) it is querying. */

CONNECT hr/hr

CREATE OR REPLACE PACKAGE hr2_pck IS
   FUNCTION Findmgr(Empno NUMBER) RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE BODY hr2_pck IS
   /* insert a new employee record: */
   FUNCTION findmgr(empno number) RETURN NUMBER IS
   Mgrid NUMBER;
   BEGIN
      SELECT mgrno INTO mgrid FROM directory WHERE mgrid = empno;
   RETURN mgrid;
   END;
END;

CREATE OR REPLACE FUNCTION secure_updates(ns varchar2,na varchar2) 
  RETURN VARCHAR2 IS 
     Results VARCHAR2(100);
    BEGIN
       /* Only allow updates when designated application has set the session 
       state to indicate we are inside it. */
       IF (sys_context('hr_app','adjstate') = 1)
          THEN results := 'mgrno = SYS_CONTEXT("hr_sec","empno")';
       ELSE results := '1=2';
      END IF;
      RETURN Results;
   END;

/* Attaches fine-grained access policy to all update operations on 
hr.directory */

CONNECT system/yJdg2U1v AS sysdba;
BEGIN
   DBMS_RLS.ADD_POLICY('hr','directory','secure_update','hr',
                       'secure_updates','update',TRUE,TRUE);
END;

15.4.1 Obtaining Default Values from Users

Sometimes it is desirable to obtain default values from users. Initially, these default values may serve as hints or preferences, and then after validation become trusted contexts. Similarly, it may be more convenient for clients to initialize some default values, and then rely on a login event trigger or applications to validate the values.

For job queues, the job submission routine records the context being set at the time the job is submitted, and restores it when executing the batched job. To maintain the integrity of the context, job queues cannot bypass the designated PL/SQL package to set the context. Rather, externally initialized application context accepts initialization of context values from the job queue process.

Automatic propagation of context to a remote session may create security problems. Developers or administrators can effectively handle this type of context that takes default values from resources other than the designated PL/SQL procedure by using login triggers to reset the context when users log in.

15.4.2 Obtaining Values from Other External Resources

In addition to using the designated trusted package, externally initialized secure application contexts can also accept initialization of attributes and values through external resources. Examples include an OCI interface, a job queue process, or a database link.

Externally initialized secure application context provides:

• For remote sessions, automatic propagation of context values that are in the externally initialized secure context namespace

• For job queues, restoration of context values that are in the externally initialized secure context namespace

• For OCI interfaces, a mechanism to initialize context values that are in the externally initialized secure context namespace

Although this type of namespace can be initialized by any client program using OCI, there are login event triggers that can verify the values. It is up to the application to interpret and trust the values of the attributes.

Middle-tier servers can actually initialize secure context values on behalf of database users. Context attributes are propagated for the remote session at initialization time, and the remote database accepts the values if the namespace is externally initialized.

15.5.1 Using Secure Application Context with LDAP

Secure session-based application context initialized globally uses the Lightweight Directory Access Protocol (LDAP), a standard, extensible, and efficient directory access protocol. The LDAP directory stores a list of users to which this application is assigned. An Oracle database server uses the directory service, typically Oracle Internet Directory, for authentication and authorization of enterprise users.

The orclDBApplicationContext LDAP object (a subclass of groupOfUniqueNames) stores the application context values in the directory. The location of the application context object is described in Figure 15-1, which is based on the Human Resources example.

An internal C function is required to retrieve the orclDBApplicationContext value, which returns a list of application context values to the RDBMS.

Figure 15-1 Location of Application Context in LDAP Directory Information Tree

Description of the illustration adfns001.gif

15.5.2 How Globally Initialized Secure Application Context Works

The administrator configures Enterprise User Security, a feature of Oracle Advanced Security. Then, the administrator sets up the secure application context values for the user in the database and the directory.

When a global user (enterprise user) connects to the database, the Oracle Advanced Security Enterprise User Security feature performs authentication to verify the identity of the user connecting to the database. After authentication, the global user roles and application context are retrieved from the directory. When the user logs on to the database, the global roles and initial application context are already set up.

15.5.3 Example: Initializing Secure Application Context Globally

The initial application context for a user, such as department name and title, can be set up and stored in the LDAP directory. The values are retrieved during user login so that the context is set properly. In addition, any information related to the user is retrieved and stored in the SYS_USER_DEFAULTS application context namespace. The following example shows how this is done.

1. Create a secure application context in the database.

   CREATE CONTEXT HR USING hrapps.hr_manage_pkg INITIALIZED GLOBALLY;
   
   

2. Create and add new entries in the LDAP directory.

   An example of the entries added to the LDAP directory follows. These entries create an attribute named Title with attribute value Manager for the application (namespace) HR, and assign user names user1 and user2.

   dn: cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Products,cn=OracleC
   ontext,ou=Americas,o=oracle,c=US
   changetype: add
   cn: OracleDBAppContext
   objectclass: top
   objectclass: orclContainer
   
   dn: cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Products,cn=O
   racleContext,ou=Americas,o=oracle,c=US
   changetype: add
   cn: HR
   objectclass: top
   objectclass: orclContainer
   
   dn: cn=Title,cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Prod
   ucts,cn=OracleContext,ou=Americas,o=oracle,c=US
   changetype: add
   cn: Title
   objectclass: top
   objectclass: orclContainer
   
   dn: cn=Manager,cn=Title,cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Products,cn=OracleContext,ou=Americas,o=oracle,c=US
   cn: Manager
   objectclass: top
   objectclass: groupofuniquenames
   objectclass: orclDBApplicationContext
   uniquemember: CN=user1,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US
   uniquemember: CN=user2,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US
   

3. If an LDAP inetOrgPerson object entry exists for the user, then the connection will also retrieve all the attributes from inetOrgPerson and assign them to the namespace SYS_LDAP_USER_DEFAULT. The following is an example of an inetOrgPerson entry:

   dn: cn=user1,ou=Americas,O=oracle,L=redwoodshores,ST=CA,C=US
   changetype: add
   objectClass: top
   objectClass: person
   objectClass: organizationalPerson
   objectClass: inetOrgPerson
   cn: user1
   sn: One
   givenName: User
   initials: UO
   title: manager, product development
   uid: uone
   mail: uone@us.oracle.com
   telephoneNumber: +1 650 123 4567
   employeeNumber: 00001
   employeeType: full time
   

4. Connect to the database.

   When user1 connects to a database that belongs to the myDomain domain, user1 will have his Title set to Manager. Any information related to user1 will be retrieved from the LDAP directory. The value can be obtained using the following syntax:

   SYS_CONTEXT('namespace','attribute name') 
   
   

   For example:

   DECLARE 
   tmpstr1 VARCHAR2(30);
   tmpstr2 VARCHAR2(30);
   BEGIN
   tmpstr1 = SYS_CONTEXT('HR','TITLE);
   tmpstr2 = SYS_CONTEXT('SYS_LDAP_USER_DEFAULT','telephoneNumber');
   DBMS_OUTPUT.PUT_LINE('Title is ' || tmpstr1);
   DBMS_OUTPUT.PUT_LINE('Telephone Number is ' || tmpstr2);
   END;
   
   

   The output of this example is:

   Title is Manager
   Telephone Number is +1 650 123 4567
   

15.6.1 Setting a Value in CLIENTCONTEXT

For OCI, use a command of the following form:

• err = OCIAppCtxSet((void *) session_handle,(dvoid *)"CLIENTCONTEXT",(ub4) 13,                   (dvoid *)attribute_name, length_of_attribute_name 
                     (dvoid *)attribute_value, length_of_attribute_value, errhp,
                     OCI_DEFAULT);
  

  where session_handle represents the OCISessionHandle, attribute_name could be, for example, "responsibility", with a length of 14, and attribute_value could be, for example, "manager", with a length of 7.

For JDBC, use a command of the following form:

• public void setApplicationContext(String CLIENTCONTEXT, string attribute, string value) throws SQLException; 
  
  

  where attribute represents the attribute whose value needs to be set, and value is the value to be assigned to that attribute.

15.6.2 Clearing a Particular Setting in CLIENTCONTEXT

For OCI, set the value to NULL or to an empty string by using one of the following command forms:

• (void) OCIAppCtxSet((void *) session_handle, (dvoid *)"CLIENTCONTEXT", 13,
                     (dvoid *)attribute_name, length_of_attribute_name, 
                     (dvoid *)0, 0,errhp,
                     OCI_DEFAULT);
  

  or

• (void) OCIAppCtxSet((void *) session_handle, (dvoid *)"CLIENTCONTEXT", 13
                     (dvoid *)attribute_name, length_of_attribute_name, 
                     (dvoid *)"", 0,errhp,
                     OCI_DEFAULT);
  
  

For JDBC, use the following command:

• public void setApplicationContext(String CLIENTCONTEXT, string attribute, string value) throws SQLException; 
  
  

  where attribute represents the attribute whose value needs to be cleared and value is either 0 or the null string "".

15.6.3 Clearing all Settings in CLIENTCONTEXT

For OCI, use a command of the following form:

• err = OCIAppCtxClearAll((void *) session_handle, (dvoid *)"CLIENTCONTEXT", 13,
                          errhp,                        OCI_DEFAULT);
  

For JDBC, use a command of the following form:

• public void clearAllApplicationContext(String CLIENTCONTEXT) throws SQLException;
  

15.7.1 Using the DBMS_SESSION Interface to Manage Application Context in Client Sessions

The DBMS_SESSION interface for managing application context has a client identifier for each application context. In this way, application context can be managed globally, yet each client sees only his or her assigned application context. The following interfaces in DBMS_SESSION enable the administrator to manage application context in client sessions:

• SET_CONTEXT

• CLEAR_CONTEXT

• CLEAR_ALL_CONTEXT (can also be used with session-based application context)

• SET_IDENTIFIER

• CLEAR_IDENTIFIER

The middle-tier application server can use SET_CONTEXT to set application context for a specific client ID. Then, when assigning a database connection to process the client request, the application server needs to issue a SET_IDENTIFIER to denote the ID of the application session. From then on, every time the client invokes SYS_CONTEXT, only the context that was associated with the set identifier is returned.

15.7.2 Examples: Global Application Context

This section provides two examples that use global application context.

• Example 1: Global Application Context Process

• Example 2: Global Application Context for Lightweight Users

DBMS_SESSION.SET_CONTEXT( 'hr', 'id', 'scott', NULL , 12345 );
DBMS_SESSION.SET_CONTEXT( 'hr', 'dept', 'sales', NULL , 12345 );

SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')

15.9.1 The Default Policy Group: SYS_DEFAULT

In the Oracle Policy Manager tree structure, the Fine-Grained Access Control Policies folder contains the Policy Groups folder. The Policy Groups folder contains an icon for each policy group, as well as an icon for the SYS_DEFAULT policy group.

By default, all policies belong to the SYS_DEFAULT policy group. Policies defined in this group for a particular table, view, or synonym will always be executed along with the policy group specified by the driving context. The SYS_DEFAULT policy group may or may not contain policies. If you attempt to drop the SYS_DEFAULT policy group, then an error will be raised.

If, to the SYS_DEFAULT policy group, you add policies associated with two or more objects, then each such object will have a separate SYS_DEFAULT policy group associated with it. For example, the EMP table in the SCOTT schema has one SYS_DEFAULT policy group, and the DEPT table in the SCOTT schema has a different SYS_DEFAULT policy group associated with it. These are displayed in the tree structure as follows:

SYS_DEFAULT
  - policy1 (SCOTT/EMP)
  - policy3 (SCOTT/EMP)
SYS_DEFAULT
  - policy2 (SCOTT/DEPT)

15.9.2 New Policy Groups

When adding the policy to a table, view, or synonym, you can use the DBMS_RLS.ADD_GROUPED_POLICY interface to specify the group to which the policy belongs. To specify which policies will be effective, you add a driving context using the DBMS_RLS.ADD_POLICY_CONTEXT interface. If the driving context returns an unknown policy group, then an error is returned.

If the driving context is not defined, then all policies are executed. Likewise, if the driving context is NULL, then policies from all policy groups are enforced. In this way, an application accessing the data cannot bypass the security setup module (which sets up application context) to avoid any applicable policies.

You can apply multiple driving contexts to the same table, view, or synonym, and each of them will be processed individually. In this way, you can configure multiple active sets of policies to be enforced.

Consider, for example, a hosting company that hosts Benefits and Financial applications, which share some database objects. Both applications are striped for hosting using a SUBSCRIBER policy in the SYS_DEFAULT policy group. Data access is partitioned first by subscriber ID, then by whether the user is accessing the Benefits or Financial applications (determined by a driving context). Suppose that Company A, which uses the hosting services, wants to apply a custom policy which relates only to its own data access. You could add an additional driving context (such as COMPANY A SPECIAL) to ensure that the additional, special policy group is applied for data access for Company A only. You would not apply this under the SUBSCRIBER policy, because the policy relates only to Company A, and it is more efficient to segregate the basic hosting policy from other policies.

15.9.3 How to Implement Policy Groups

To create policy groups, the administrator must do two things:

• Set up a driving context to identify the effective policy group.

• Add policies to policy groups as required.

The following example shows how to perform these tasks.

DROP USER finance CASCADE;
CREATE USER finance IDENTIFIED BY welcome2;
GRANT RESOURCE TO apps;
DROP TABLE apps.benefit;
CREATE TABLE apps.benefit (c NUMBER);

CREATE CONTEXT appsctx USING apps.apps_security_init;

CREATE OR REPLACE PACKAGE apps.apps_security_init IS
PROCEDURE setctx (policy_group VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY apps.apps_security_init AS
PROCEDURE setctx ( policy_group varchar2 ) IS
BEGIN

REM  Do some checking to determine the current application. 
REM  You can check the proxy if using the proxy authentication feature.
REM  Then set the context to indicate the current application.
.
.
.
DBMS_SESSION.SET_CONTEXT('APPSCTX','ACTIVE_APPS', policy_group);
END;
END;

BEGIN
DBMS_RLS.ADD_POLICY_CONTEXT('apps','benefit','APPSCTX','ACTIVE_APPS');
END;

CREATE OR REPLACE FUNCTION by_company (sch varchar2, tab varchar2)
RETURN VARCHAR2 AS
BEGIN
  RETURN 'COMPANY = SYS_CONTEXT(''ID'',''MY_COMPANY'')';
END;

BEGIN
DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','SYS_DEFAULT',
'security_by_company',
'apps','by_company');
END;

CREATE OR REPLACE FUNCTION hr.security_policy 
RETURN VARCHAR2
AS
BEGIN
                 RETURN 'SYS_CONTEXT(''ID'',''TITLE'') = ''MANAGER'' ';
END;

BEGIN
DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','HR');
DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','HR',
'hr_security','hr','security_policy');
END;

CREATE OR REPLACE FUNCTION finance.security_policy 
RETURN VARCHAR2
AS
BEGIN
                 RETURN ('SYS_CONTEXT(''ID'',''DEPT'') = ''FINANCE'' ');
END;

BEGIN
DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','FINANCE');
DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','FINANCE',
'finance_security','finance', 'security_policy');
END;

execute apps.security_init.setctx('HR');

15.9.4 Validating the Application Used to Connect to the Database

The package implementing the driving context must correctly validate the application that is being used to connect to the database. Although the database always checks the call stack to ensure that the package implementing the driving context sets context attributes, inadequate validation can still occur within the package.

For example, in applications where database users or enterprise users are known to the database, the user needs the EXECUTE privilege on the package that sets the driving context. Consider a user who knows that:

• The BENEFITS application allows more liberal access than the HR application

• The setctx procedure (which sets the correct policy group within the driving context) does not perform any validation to determine which application is actually connecting. That is, the procedure does not check either the IP address of the incoming connection (for a three-tier system) or the proxy_user attribute of the user session.

Such a user could pass to the driving context package an argument setting the context to the more liberal BENEFITS policy group, and then access the HR application instead. Because the setctx does no further validation of the application, this user bypasses the normally more restrictive HR security policy.

By contrast, if you implement proxy authentication with VPD, then you can determine the identity of the middle tier (and the application) that is actually connecting to the database on behalf of a user. In this way, the correct policy will be applied for each application to mediate data access.

For example, a developer using the proxy authentication feature could determine that the application (the middle tier) connecting to the database is HRAPPSERVER. The package that implements the driving context can thus verify whether the proxy_user in the user session is HRAPPSERVER. If so, then it can set the driving context to use the HR policy group. If proxy_user is not HRAPPSERVER, then it can disallow access.

In this case, when the following query is executed

SELECT * FROM APPS.BENEFIT;

Oracle Database picks up policies from the default policy group (SYS_DEFAULT) and active namespace HR. The query is internally rewritten as follows:

SELECT * FROM APPS.BENEFIT WHERE COMPANY = SYS_CONTEXT('ID','MY_COMPANY') and SYS_CONTEXT('ID','TITLE') = 'MANAGER';

15.10.1 DBMS_RLS.ADD_POLICY Procedure Policy Types

The execution of policy functions can consume a significant amount of system resources. If you can minimize the number of times that policy functions must run, then you can optimize your database server performance. To avoid unnecessary policy function execution, you can choose from five different policy types, which enable you to precisely specify how and how often a policy predicate should change. You can enable these different types of policies, which are listed in Table 15-3, by setting the policy_type parameter of the DBMS_RLS.ADD POLICY procedure.

Static and context sensitive policies enable you to optimize server performance, because they do not run the policy function each time protected database objects are accessed. However, Oracle recommends that before you enable policies as either static or context-sensitive, you first test them as DYNAMIC policy types, which run every time. Testing policy functions as DYNAMIC policies first enables you to observe how the policy function affects each query, because nothing is cached. This ensures that the functions work properly before you enable them as static or context-sensitive policy types to optimize performance.

Dynamic policies are the system default. If you do not specify a policy type with the DBMS_RLS.ADD_POLICY procedure, then by default your policy will be dynamic. You can specifically configure a policy to be dynamic by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to DYNAMIC. Refer to Example 15-1 for the syntax.

DBMS_RLS.ADD_POLICY (

.
.
.
policy_type => dbms_rls.POLICY_TYPE);

15.10.2 Optimizing Performance by Enabling Static and Context Sensitive Policies

In previous releases, policies were dynamic, which means that the database runs the policy function for each query or DML statement. In addition to dynamic policies, the current release of Oracle Database provides static and context-sensitive policies. These policy types provide a means to improve server performance, because they do not always rerun policy functions for each DML statement and can be shared across multiple database objects.

WHERE dept=SYS_CONTEXT ('HR_APP','deptno')

15.10.3 Adding Policies for Column-Level VPD

Column-level VPD, which can be applied to a table or a view, enables you to enforce security when a security-relevant column is referenced in a query, resulting in row-level security. Column-level VPD cannot be applied to a synonym.

It can be configured to produce two distinct behaviors as follows:

• Default Behavior

• Column-masking Behavior

The following example shows a VPD policy in which sales department users should not see the salaries of people outside their own department (department number 30). The relevant columns for such a policy are SAL and COMM. First, the VPD policy function is created and then added by using the DBMS_RLS PL/SQL package as shown in Example 15-2.

*/Create a policy function which does not expose salaries of employees outside the sales department (department 30)/*

CREATE OR REPLACE FUNCTION pf1 (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2 (200);
BEGIN
   con := 'deptno=30';
   RETURN (con);
END pf1;

BEGIN
   DBMS_RLS.ADD_POLICY (object_schema=>'scott', object_name=>'emp',
                        policy_name=>'sp', function_schema=>'pol_admin',
                        policy_function=>'pf1',
                        sec_relevant_cols=>'sal,comm');
END;

SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
WHERE d.deptno = e.deptno;

ENAME          DNAME          JOB                   SAL          COMM
-------------- -------------- ------------ ------------ -------------
ALLEN          SALES          SALESMAN             1600           300
WARD           SALES          SALESMAN             1250           500
MARTIN         SALES          SALESMAN             1250          1400
BLAKE          SALES          MANAGER              2850
TURNER         SALES          SALESMAN             1500             0
JAMES          SALES          CLERK                 950

*/add the ALL_ROWS policy/*
BEGIN
     DBMS_RLS.ADD_POLICY(object_schema=>'scott', object_name=>'emp',
                         policy_name=>'sp', function_schema=>'pol_admin',
                         policy_function=>'pf1',
                         sec_relevant_cols=>'sal,comm',
                         sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
END;

ENAME          DNAME          JOB                   SAL          COMM
-------------- -------------- ------------ ------------ -------------
SMITH          RESEARCH       CLERK
ALLEN          SALES          SALESMAN             1600           300
WARD           SALES          SALESMAN             1250           500
JONES          RESEARCH       MANAGER
MARTIN         SALES          SALESMAN             1250          1400
BLAKE          SALES          MANAGER              2850
CLARK          ACCOUNTING     MANAGER
SCOTT          RESEARCH       ANALYST
KING           ACCOUNTING     PRESIDENT
TURNER         SALES          SALESMAN             1500             0
ADAMS          RESEARCH       CLERK
JAMES          SALES          CLERK                 950
FORD           RESEARCH       ANALYST
MILLER         ACCOUNTING     CLERK

15.10.4 Enforcing VPD Policies on Specific SQL Statement Types

VPD policies can be enforced for SELECT, INSERT, UPDATE, INDEX, and DELETE statements. Specify any combination of these statement types with the DBMS_RLS.ADD_POLICY procedure statement_types parameter as follows:

DBMS_RLS.ADD_POLICY (

.
.
.

statement_types=>'SELECT,INDEX');

15.12.1 SYS User Exempted from VPD Policies

The database user SYS is always exempt from VPD or Oracle Label Security policy enforcement, regardless of the export mode, application, or utility that is used to extract data from the database. However, SYSDBA actions can be audited.

15.12.2 EXEMPT ACCESS POLICY System Privilege

The system privilege EXEMPT ACCESS POLICY allows a user to be exempted from all fine-grained access control policies on any SELECT or DML operation (INSERT, UPDATE, and DELETE). This provides ease of use for administrative activities such as installation and import and export of the database through a non-SYS schema.

Also, regardless of the utility or application that is being used, if a user is granted the EXEMPT ACCESS POLICY privilege, then the user is exempt from VPD and Oracle Label Security policy enforcement. That is, the user will not have any VPD or Oracle Label Security policies applied to their data access.

Because EXEMPT ACCESS POLICY negates the effect of fine-grained access control, this privilege should only be granted to users who have legitimate reasons for bypassing fine-grained access control enforcement. This privilege should not be granted WITH ADMIN OPTION, so that users cannot pass on the EXEMPT ACCESS POLICY privilege to other users, and thus propagate the ability to bypass fine-grained access control.