Skip Headers
Oracle® Database 2 Day + Security Guide
12c Release 1 (12.1)

E17609-18
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
PDF · Mobi · ePub

6 Restricting Access with Oracle Virtual Private Database

This chapter contains:

See Also:

Oracle Database Security Guide for detailed information about how Oracle Virtual Private Database works

About Oracle Virtual Private Database

Oracle Virtual Private Database (VPD) provides row-level security at the database table or view level. You can extend it to provide column-level security as well. Essentially, Virtual Private Database inserts an additional WHERE clause to any SQL statement that is used on any table or view to which a Virtual Private Database security policy has been applied. (A security policy is a function that allows or prevents access to data.) The WHERE clause allows only users whose identity passes the security policy, and hence, have access to the data that you want to protect.

In a multitenant environment, each Virtual Private Database policy applies only to the current pluggable database (PDB).

An Oracle Virtual Private Database policy has the following components, which are typically created in the schema of the security administrator:

  • A PL/SQL function to append the dynamic WHERE clause to SQL statements that affect the Virtual Private Database tables. For example, a PL/SQL function translates the following SELECT statement:

    SELECT * FROM ORDERS;
    

    to the following:

    SELECT * FROM ORDERS
      WHERE SALES_REP_ID = 159;
    

    In this example, the user can only view orders by Sales Representative 159. The PL/SQL function used to generate this WHERE clause is as follows:

     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    
    CREATE OR REPLACE FUNCTION auth_orders( 
     schema_var IN VARCHAR2, 
     table_var  IN VARCHAR2 
    ) 
    RETURN VARCHAR2
    IS  
     return_val VARCHAR2 (400); 
    BEGIN 
     return_val := 'SALES_REP_ID = 159';
     RETURN return_val; 
    END auth_orders; 
    /
    

    In this example:

    • Lines 2–3: Create parameters to store the schema name, OE, and table name, ORDERS. (The second parameter, table_var, for the table, can also be used for views and synonyms.) Always create these two parameters in this order: create the parameter for the schema first, followed by the parameter for the table, view, or synonym object. Note that the function itself does not specify the OE schema or its ORDERS table. The Virtual Private Database policy you create uses these parameters to specify the OE.ORDERS table.

    • Line 5: Returns the string that will be used for the WHERE predicate clause.

    • Lines 6–10: Encompass the creation of the WHERE SALES_REP_ID = 159 predicate.

    You can design the WHERE clause to filter the user information based on the session information of that user, such as the user ID. To do so, you create an application context. Application contexts can be used to authenticate both database and nondatabase users. An application context is a name-value pair. For example:

    SELECT * FROM oe.orders 
     WHERE sales_rep_id = SYS_CONTEXT('userenv','session_user'); 
    

    In this example, the WHERE clause uses the SYS_CONTEXT PL/SQL function to retrieve the user session ID (session_user) designated by the userenv context. See Oracle Database Security Guide for detailed information about application contexts.

  • A way to attach the policy the package. Use the DBMS_RLS.ADD_POLICY function to attach the policy to the package. Before you can use the DBMS_RLS PL/SQL package, you must be granted EXECUTE privileges on it. User SYS owns the DBMS_RLS package.

The advantages of enforcing row-level security at the database level rather than at the application program level are enormous. Because the security policy is implemented in the database itself, where the data to be protected is, this data is less likely to be vulnerable to attacks by different data access methods. This layer of security is present and enforced no matter how users (or intruders) try to access the data it protects. The maintenance overhead is low because you maintain the policy in one place, the database, rather than having to maintain it in the applications that connect to this database. The policies that you create provide a great deal of flexibility because you can write them for specific DML operations.

Tutorial: Limiting Access to Data Based on the Querying User

The ORDERS table in the Order Entry database, OE, contains the following information:

Name                                   Null?    Type
-------------------------------------- -------- ---------------------------------
ORDER_ID                               NOTNULL  NUMBER(12)
ORDER_DATE                             NOTNULL  TIMESTAMP(6) WITH LOCAL TIME ZONE
ORDER_MODE                                      VARCHAR2(8)
CUSTOMER_ID                            NOTNULL  NUMBER(6)
ORDER_STATUS                                    NUMBER(2)
ORDER_TOTAL                                     NUMBER(8,2)
SALES_REP_ID                                    NUMBER(6)
PROMOTION_ID                                    NUMBER(6)

Suppose you want to limit access to this table based on the person who is querying the table. For example, a sales representative should only see the orders that he or she have created, but other employees should not. In this tutorial, you create a sales representative user account and an account for a finance manager. Then, you create an Oracle Virtual Private Database policy that will limit the data access to these users based on their roles.

The Virtual Private Database policy that you will create is associated with a PL/SQL function. Because VPD policies are controlled by PL/SQL functions or procedures, you can design the policy to restrict access in many different ways. For this tutorial, the function you create will restrict access by the employees based on to whom they report. The function will restrict the customer access based on the customer's ID.

You may want to store VPD policies in a database account separate from the database administrator and from application accounts. In this tutorial, you will use the sec_admin account, which was created in "Tutorial: Creating a Secure Application Role", to create the VPD policy. This provides better security by separating the VPD policy from the applications tables.

To restrict access based on the sensitivity of row data, you can use Oracle Label Security (OLS). OLS lets you categorize data into different levels of security, with each level determining who can access the data in that row. This way, the data access restriction is focused on the data itself, rather than on user privileges. See Chapter 8, "Enforcing Row-Level Security with Oracle Label Security" for more information.

In this tutorial:

Step 1: Create User Accounts for This Tutorial

You are ready to create accounts for the employees who must access the OE.ORDERS table.

To create the employee user accounts:  

  1. In Enterprise Manager, access the Database home page for your target database as user SYS with the SYSDBA administrative privilege.

    See Oracle Database 2 Day DBA for more information.

  2. From the Administration menu, select Security, then Users.

  3. In the Users Page, click Create.

  4. In the Create User page, enter the following information:

    • Name: LDORAN (to create the user account Louise Doran)

    • Profile: DEFAULT

    • Authentication: Password

    • Enter Password and Confirm Password: Enter a password that meets the requirements in "Requirements for Creating Passwords".

    • Default Tablespace: EXAMPLE

    • Temporary Tablespace: TEMP

    • Status: Unlocked

  5. Select the Object Privileges tab.

  6. From the Select Object Type list, select Table, and then click Add.

  7. In the Add Table Object Privileges page, in the Select Table Objects field, enter the following text:

    OE.ORDERS
    

    Do not include spaces in this text.

  8. In the Available Privileges list, select SELECT, and then click Move to move it to the Selected Privileges list. Click OK.

    The Create User page appears, with SELECT privileges for OE.ORDERS listed.

  9. Click OK.

    The Users page appears, with user ldoran is listed in the User Name column.

  10. Select the selection button for user LDORAN, and from the Actions list, select Create Like. Then, click Go.

  11. In the Create User page, enter the following information:

    • Name: LPOPP (to create the user account for Finance Manager Luis Popp.)

    • Enter Password and Confirm Password: Enter a password that meets the requirements in "Requirements for Creating Passwords".

  12. Click OK.

Both employee accounts have been created, and they have identical privileges. If you check the privileges for user LPOPP, you will see that they are identical to those of user LDORAN's. At this stage, if either of these users performs a SELECT statement on the OE.ORDERS table, he or she will be able to see all of its data.

Step 2: If Necessary, Create the Security Administrator Account

In "Tutorial: Creating a Secure Application Role", you created a security administrator account called sec_admin for that tutorial. You can use that account for this tutorial. If you have not yet created this account, follow the steps in "Step 2: Create a Security Administrator Account" to create sec_admin.

Step 3: Update the Security Administrator Account

The sec_admin account user must have privileges to use the DBMS_RLS packages. User SYS owns this package, so you must log on as SYS to grant these package privileges to sec_admin. The user sec_admin also must have SELECT privileges on the CUSTOMERS table in the OE schema and the EMPLOYEES table in the HR schema.

To grant sec_admin privileges to use the DBMS_RLS package: 

  1. In Enterprise Manager, access the Database home page and ensure that you are logged in as user SYS with the SYSDBA role selected.

    See Oracle Database 2 Day DBA for more information.

  2. From the Administration menu, select Security, then Users.

  3. In the Users Page, select the SEC_ADMIN user, and in the View User page, click Edit.

  4. In the Edit User page, click Object Privileges.

  5. From the Select Object Type list, select Package, and then click Add.

  6. In the Add Package Object Privileges page, under Select Package Objects, enter SYS.DBMS_RLS so that sec_admin will have access to the DBMS_RLS package.

  7. Under Available Privileges, select EXECUTE, and then click Move to move it to the Selected Privileges list.

  8. Click OK.

  9. In the Edit User page, from the Select Object Type list, select Table, and then click Add.

  10. In the Add Table Object Privileges page, in the Select Table Objects field, enter HR.EMPLOYEES so that sec_admin will have access to the HR.EMPLOYEES table.

  11. Under Available Privileges, select SELECT, and then click Move to move it to the Selected Privileges list.

  12. Click OK.

    The Edit User page appears. It shows that user sec_admin has object privileges for the HR.EMPLOYEES table and DBMS_RLS PL/SQL package. Ensure that you do not select the grant option for either of these objects.

  13. Click Apply.

    All the changes you have made, in this case, the addition of the two object privileges, are applied to the sec_admin user account.

Step 4: Create the F_POLICY_ORDERS Policy Function

The f_policy_orders policy is a PL/SQL function that defines the policy used to filter users who query the ORDERS table. To filter the users, the policy function uses the SYS_CONTEXT PL/SQL function to retrieve session information about users who are logging in to the database.

To create the application context and its package: 

  1. Select Logout to log out of the database instance.

  2. In the Confirmation dialog box, select Logout of (Database Instance) and then select the Display login page after logout check box. Then click Logout.

  3. Log in as user sec_admin using the NORMAL role.

  4. From the Schema menu, select Programs, then Functions.

  5. In the Database Login page, log in as user sec_admin with the NORMAL role selected.

  6. From the Schema menu, select Programs, and then Functions.

  7. In the Functions page, ensure that the Object Type menu is set to Function, and then click Create.

  8. In the Create Function page, enter the following information:

    • Name: F_POLICY_ORDERS

    • Schema: SEC_ADMIN

    • Source: Delete the empty function code that has been provided, and then enter the following code (but not the line numbers on the left side of the code) to create a function that checks whether the user who has logged on is a sales representative. You can copy and paste this text by positioning the cursor at the start of (schema in varchar2) in the first line.

      The f_policy_orders function uses the SYS_CONTEXT PL/SQL function to get the session information of the user. It then compares this information with the job ID of that user in the HR.EMPLOYEES table, for which sec_admin has SELECT privileges.

       
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      
      (schema in varchar2,
      tab in varchar2)
      return varchar2 
      as 
       v_job_id   varchar2(20);
       v_user     varchar2(100);
       predicate  varchar2(400);
       
      begin
       v_job_id  := null;
       v_user    := null;
       predicate := '1=2';
      
      v_user := lower(sys_context('userenv','session_user'));
      
       select lower(job_id) into v_job_id from hr.employees
         where lower(email) = v_user;
       
       if  v_job_id='sa_rep' then
          predicate := '1=1';
       else 
          null; 
       end if;
      
       return predicate;
      
       exception 
        when no_data_found then 
         null;
      end;
      

      In this specification:

      • Lines 1–2: Define parameters for the schema (schema) and table (tab) that must be protected. Notice that the function does not mention the OE.ORDERS table. The ACCESSCONTROL_ORDERS policy that you create in Step 5: Create the ACCESSCONTROL_ORDERS Virtual Private Database Policy uses these parameters to specify the OE schema and ORDERS table. Ensure that you create the schema parameter first, followed by the tab parameter.

      • Line 3: Returns the string that will be used for the WHERE predicate clause. Always use VARCHAR2 as the data type for this return value.

      • Lines 4–7: Define variables to store the job ID, user name of the user who has logged on, and predicate values.

      • Lines 9–25: Encompass the creation of the WHERE predicate, starting the with the BEGIN clause at Line 9.

      • Lines 10–12: Sets the v_job_id and v_user variables to null, and the predicate variable to 1=2, that is, to a false value. At this stage, no WHERE predicate can be generated until these variables pass the tests starting with Line 16.

      • Line 14: Uses the SYS_CONTEXT function to retrieve the session information of the user and write it to the v_user variable.

      • Lines 16–23: Checks if the user is a sales representative by comparing the job ID with the user who has logged on. If the job ID of the user who has logged on is sa_rep (sales representative), then the predicate variable is set to 1=1. In other words, the user, by being a sales representative, has passed the test.

      • Line 25: Returns the WHERE predicate, which translates to WHERE role_of_user_logging_on IS "sa_rep". Oracle Database appends this WHERE predicate onto any SELECT statement that users LDORAN and LPOPP issue on the OE.ORDERS table.

      • Lines 27–29: Provide an EXCEPTION clause for cases where a user without the correct privileges has logged on.

  9. Click OK.

Step 5: Create the ACCESSCONTROL_ORDERS Virtual Private Database Policy

Now that you have created the Virtual Private Database policy function, you can create the Virtual Private Database policy, accesscontrol_orders, and then attach it to the ORDERS table. To increase performance, add the CONTEXT_SENSITIVE parameter to the policy, so that Oracle Database only executes the f_policy_orders function when the content of the application context changes, in this case, when a new user logs on. Oracle Database only activates the policy when a user performs a SQL SELECT statement on the ORDERS table. Hence, the user cannot run the INSERT, UPDATE, and DELETE statements, because the policy does not allow him or her to do so.

To create the ACCESSCONTROL_ORDERS Virtual Private Database policy:  

  1. From the Administration menu, select Security, then Virtual Private Database Policies.

  2. In the Virtual Private Database Policies page, click Create.

  3. In the Create Policy page, under General, enter the following:

    • Policy Name: ACCESSCONTROL_ORDERS

    • Object Name: OE.ORDERS

    • Policy Type: Select CONTEXT_SENSITIVE.

      This type reevaluates the policy function at statement run-time if it detects context changes since the last use of the cursor. For session pooling, where multiple clients share a database session, the middle tier must reset the context during client switches. Note that Oracle Database does not cache the value that the function returns for this policy type; it always runs the policy function during statement parsing. The CONTEXT_SENSITIVE policy type applies to only one object.

      To enable the Policy Type, select the Enabled box.

  4. Under Policy Function, enter the following:

    • Policy Function: Enter the name of the function that generates a predicate for the policy, in this case, SEC_ADMIN.F_POLICY_ORDERS.

    • Long Predicate: Do not select this box.

      Typically, you select this box to return a predicate with a length of up to 32K bytes. By not selecting this box, Oracle Database limits the predicate to 4000 bytes.

  5. Under Enforcement, select the SELECT option and deselect the remaining options that already may be selected.

  6. Do not select any options under Security Relevant Columns.

  7. Click OK.

    The Virtual Private Database Policies page appears, with the ACCESSCONTROL_ORDERS policy listed in the list of policies.

  8. Do not log out of Enterprise Manager.

Step 6: Test the ACCESSCONTROL_ORDERS Virtual Private Database Policy

At this stage, you are ready to test the accesscontrol_orders policy by logging on as each user and attempting to select data from the ORDERS table.

To test the ACCESSCONTROL_ORDERS policy:  

  1. Start SQL*Plus.

    From a command prompt, enter the following command to start SQL*Plus, and log in as Sales Representative Louise Doran, whose user name is ldoran:

    sqlplus ldoran
    Enter password: password
    

    SQL*Plus starts, connects to the default database, and then displays a prompt.

    For detailed information about starting SQL*Plus, see Oracle Database 2 Day DBA.

  2. Enter the following SELECT statement:

    SELECT COUNT(*) FROM OE.ORDERS;
    

    The following results should appear for Louise. As you can see, Louise is able to access all the orders in the OE.ORDERS table.

    COUNT(*)
    --------
         105
    
  3. Connect as Finance Manager Luis Popp.

    CONNECT lpopp
    Enter password: password
    
  4. Enter the following SELECT statement:

    SELECT COUNT(*) FROM OE.ORDERS;
    

    The following result should appear, because Mr. Popp, who is not a sales representative, does not have access to the data in the OE.ORDERS table. Because Mr. Popp does not have access, Oracle Database only allows him access to 0 rows.

    COUNT(*)
    --------
           0
    
  5. Exit SQL*Plus:

    EXIT
    

Step 7: Optionally, Remove the Components for This Tutorial

After completing this tutorial, you can remove the data structures that you used if you no longer need them.

To remove the data structures created by sec_admin: 

  1. In Enterprise Manager, ensure that you are logged in as user sec_admin.

  2. From the Administration menu, select Security, then Virtual Private Database Policies.

  3. In the Virtual Private Database Policies page, under Search, enter the following information, and then click Go:

    • Schema Name: OE

    • Object Name: ORDERS

    • Policy Name: %

    The policy you created, ACCESSCONTROL_ORDERS, is listed.

  4. Select ACCESSCONTROL_ORDERS, and then click Delete.

  5. In the Confirmation page, click Yes.

  6. From the Schema menu, select Programs, then Functions.

  7. If the F_POLICY_ORDERS function is not listed, then use the Search field to search for it.

  8. Select the selection button for the F_POLICY_ORDERS function and then click Delete.

  9. In the Confirmation window, click OK.

To remove the user accounts: 

  1. From Enterprise Manager, select Logout to log out of the database instance.

  2. Log in as user SYSTEM with the NORMAL role selected.

  3. In the Database home page, from the Administration menu, select Security, then Users.

  4. In the Users page, select each of the following users, and then click Delete to remove them:

    • LDORAN

    • LPOPP

    Do not remove sec_admin because you will need this account for later tutorials in this guide.

To revoke the EXECUTE privilege on the DBMS_RLS package from user sec_admin: 

  1. From Enterprise Manager, select Logout to log out of the database instance.

  2. Log in as the SYS administrative user with the SYSDBA role selected.

  3. In the Users page, select user SEC_ADMIN and then click Edit.

  4. Select the Object Privileges tab.

  5. From the list of object privileges, select the listing for the SELECT privilege for the HR.EMPLOYEES table.

  6. Click Delete.

  7. From the list of object privileges, select the listing for the EXECUTE privilege for the DBMS_RLS package.

  8. Click Delete.

  9. Click Apply.

  10. Exit Enterprise Manager.