You can use Oracle Virtual Private Database to restrict access to data based on a dynamic WHERE clause that is added to the SQL statements that users enter.
Topics:
Oracle Database Security Guide for detailed information about how Oracle Virtual Private Database works
Oracle Virtual Private Database (VPD) enables you to dynamically add a WHERE clause in any SQL statement that a user executes. The WHERE clause filters the data the user is allowed to access, based on the identity of a user.
This feature restricts row and column level data access by creating a policy that enforces a WHERE clause for all SQL statements that query the database. The WHERE clause allows only users whose identity passes the security policy, and hence, have access to the data that you want to protect. You create and manage the VPD policy at the database table or view level, which means that you do not modify the applications that access the database.
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:
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:
schema_var and table_var: 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.
RETURN VARCHAR2: Returns the string that will be used for the WHERE predicate clause.
IS ... RETURN return_val: Encompasses 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.
Oracle Database Security Guide for detailed information about how Oracle Virtual Private Database works
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.
You can use Oracle Database limit access to data based on who the querying user is. For example, a sales representative should only see the orders that he or she have created, but other employees should not.
Topics:
In this tutorial, you will use the ORDERS table in the Order Entry database, OE.
This table 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)
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 Enforcing Row-Level Security with Oracle Label Security for more information.
You are ready to create accounts for the employees who must access the OE.ORDERS table.
To create the employee user accounts:
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.
From the Administration menu, select Security, then Users.
In the Users Page, click Create.
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
Select the Object Privileges tab.
From the Select Object Type list, select Table, and then click Add.
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.
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.
Click OK.
The Users page appears, with user ldoran is listed in the User Name column.
Select the selection button for user LDORAN, and from the Actions list, select Create Like. Then, click Go.
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".
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.
The sec_admin security administrator account enables you to perform the tasks a security administrator can perform.
In "Tutorial: Creating a Secure Application Role", you created the sec_admin for that tutorial. You can use that account for this tutorial.
If you have not yet created this account, then follow the steps in "Step 2: Create a Security Administrator Account" to create sec_admin.
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:
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.
From the Administration menu, select Security, then Users.
In the Users Page, select the SEC_ADMIN user, and in the View User page, click Edit.
In the Edit User page, click Object Privileges.
From the Select Object Type list, select Package, and then click Add.
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.
Under Available Privileges, select EXECUTE, and then click Move to move it to the Selected Privileges list.
Click OK.
In the Edit User page, from the Select Object Type list, select Table, and then click Add.
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.
Under Available Privileges, select SELECT, and then click Move to move it to the Selected Privileges list.
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.
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.
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:
Select Logout to log out of the database instance.
In the Confirmation dialog box, select Logout of (Database Instance) and then select the Display login page after logout check box. Then click Logout.
Log in as user sec_admin using the NORMAL role.
From the Schema menu, select Programs, then Functions.
In the Database Login page, log in as user sec_admin with the NORMAL role selected.
From the Schema menu, select Programs, and then Functions.
In the Functions page, ensure that the Object Type menu is set to Function, and then click Create.
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.
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.
(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:
(schema in varchar2, tab in varchar2): Defines 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.
return varchar2: Returns the string that will be used for the WHERE predicate clause. Always use VARCHAR2 as the data type for this return value.
as ... predicate: Defines variables to store the job ID, user name of the user who has logged on, and predicate values.
begin ... return predicate: Encompasses the creation of the WHERE predicate, starting the with the BEGIN clause for the v_job_id and v_user settings.
v_job_id varchar2(20) and v_user varchar2(100): 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 select lower(job_id) into v_job_id.
v_user := lower(sys_context...: Uses the SYS_CONTEXT function to retrieve the session information of the user and write it to the v_user variable.
select lower(job_id) into v_job_id...end if: 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.
return predicate: 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.
exception ... null: Provide an EXCEPTION clause for cases where a user without the correct privileges has logged on.
Click OK.
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:
From the Administration menu, select Security, then Virtual Private Database Policies.
In the Virtual Private Database Policies page, click Create.
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.
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.
Under Enforcement, select the SELECT option and deselect the remaining options that already may be selected.
Do not select any options under Security Relevant Columns.
Click OK.
The Virtual Private Database Policies page appears, with the ACCESSCONTROL_ORDERS policy listed in the list of policies.
Do not log out of Enterprise Manager.
After you create the accesscontrol_orders policy, you can test it by logging on as each user and attempting to select data from the ORDERS table.
To test the ACCESSCONTROL_ORDERS policy:
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.
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
Connect as Finance Manager Luis Popp.
CONNECT lpopp
Enter password: password
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
Exit SQL*Plus:
EXIT
You can remove the components that you created for this tutorial if you no longer need them.
Topics:
You can use Enterprise Manager to remove the data structures that user sec_admin created.
To remove the data structures created by sec_admin:
In Enterprise Manager, ensure that you are logged in as user sec_admin.
From the Administration menu, select Security, then Virtual Private Database Policies.
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.
Select ACCESSCONTROL_ORDERS, and then click Delete.
In the Confirmation page, click Yes.
From the Schema menu, select Programs, then Functions.
If the F_POLICY_ORDERS function is not listed, then use the Search field to search for it.
Select the selection button for the F_POLICY_ORDERS function and then click Delete.
In the Confirmation window, click OK.
You can use Enterprise Manager to remove the user accounts.
To remove the user accounts:
From Enterprise Manager, select Logout to log out of the database instance.
Log in as user SYSTEM with the NORMAL role selected.
In the Database home page, from the Administration menu, select Security, then Users.
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.
You can use Enterprise Manager to revoke the EXECUTE privilege on the DBMS_RLS package from user sec_admin.
To revoke the EXECUTE privilege on the DBMS_RLS package from user sec_admin:
From Enterprise Manager, select Logout to log out of the database instance.
Log in as the SYS administrative user with the SYSDBA role selected.
In the Users page, select user SEC_ADMIN and then click Edit.
Select the Object Privileges tab.
From the list of object privileges, select the listing for the SELECT privilege for the HR.EMPLOYEES table.
Click Delete.
From the list of object privileges, select the listing for the EXECUTE privilege for the DBMS_RLS package.
Click Delete.
Click Apply.
Exit Enterprise Manager.