Row-Level Security with Virtual Private Database - Creating the Policy Function
(Page 3 of 10 )
All RLS policy functions are passed two parameters by the database when they are invoked. The first is the name of the schema that owns the object for which the RLS policy is being invoked. The second is the name of the object for which the RLS policy is being invoked. These two parameters are helpful because a single policy function (PL/SQL program) may be applied to multiple objects in multiple schemas. The parameters can then be used to determine specifically which object the policy is being invoked.
A good practice is to create your policy functions with the parameter values defaulted to null. This will allows you to test the function directly:
sec_mgr@KNOX10g> -- Create policy function.
sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION dept_only (
2 p_schema IN VARCHAR2 DEFAULT NULL,
3 p_object IN VARCHAR2 DEFAULT NULL)
4 RETURN VARCHAR2
5 AS
6 BEGIN
7 RETURN 'deptno = sys_context
(''people_ctx'',''deptno'')';
8 END;
9 /
Function created.
Notice that this policy function is created in the security manager’s schema. This is important because access to the policy function should be guarded.
NOTE
Your users should not have execute privileges on the policy function, nor should they be able to alter or drop the function.
This last requirement generally implies the function is separated from the data schema to which it will be applied. In some cases, you can see the predicate string that will be used by displaying the return value of the function:
sec_mgr@KNOX10g> -- Test policy function. sec_mgr@KNOX10g> col predicate format a50
sec_mgr@KNOX10g> SELECT dept_only predicate FROM DUAL;
PREDICATE
-------------------------------------------
deptno = sys_context('people_ctx','deptno')
This policy function is quite simple; in fact, it could just as easily have been implemented in a view. You are using VPD instead of views because this policy is only relevant to inserts and updates.
Applying the Insert/Update Policy The security requirement implemented by the preceding code is supposed to be in effect on all inserts and updates. After running the following statement, the database will call the DEPT_ONLY function in the SEC_MGR schema whenever someone inserts or updates the SCOTT.PEOPLE table:
sec_mgr@KNOX10g> -- apply RLS policy to table
sec_mgr@KNOX10g> BEGIN
2 DBMS_RLS.add_policy
3 (object_schema => 'SCOTT',
4 object_name => 'PEOPLE',
5 policy_name => EOPLE_IU',
6 function_schema => 'SEC_MGR',
7 policy_function => 'Dept_Only',
8 statement_types => 'INSERT,
UPDATE',
9 update_check => TRUE);
10 END;
11 /
PL/SQL procedure successfully completed.
The combination of object schema, object name, and policy name has to be unique. A good practice for the policy name is to choose something that describes the intended use of the policy. PEOPLE_IU is my way of indicating a policy on the PEOPLE table for insert and update operations. Likewise, choosing a function name that is descriptive about what the function results are is also good practice. The DEPT_ONLY implies the function restricts records at the department level. As a result of executing the ADD_POLICY procedure, the following SQL
update people
set ename = '<NEW_VALUE>'
will be effectively transformed into
update people
set ename = '<NEW_VALUE>'
where deptno = sys_context('people_ctx','deptno')
Figure 11-1 depicts how this occurs.
FIGURE 11-1. Queries are modified transparently by
the RLS policy.
Testing VPD Protected Insert and Updates A quick test validates that the policy is working. For the test, simply update a record within your department (Department 20 for SCOTT), and update a record outside your department. The following query returns a record from each so you can properly issue your update statements:
sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> SELECT username, deptno
2 FROM people
3 WHERE username < 'C';
USERNAME DEPTNO
---------- ----------
ALLEN 30
BLAKE 30
ADAMS 20
Testing the updates should show that updates to records in Department 20 will be successful. Updates to records outside Department 20 should not succeed:
scott@KNOX10g> -- Update to department 20 user
scott@KNOX10g> -- This should succeed. scott@KNOX10g> UPDATE people
2 SET username = 'GRIZZLY'
3 WHERE username = 'ADAMS';
1 row updated.
scott@KNOX10g> -- Update to department 30 user
scott@KNOX10g> -- This should not update anything.
scott@KNOX10g> UPDATE people
2 SET username = 'BOZO'
3 WHERE username = 'BLAKE';
0 rows updated.
Note that zero errors are thrown for the second update. The modified SQL simply excludes all possible records. The update statement was effectively augmented and resolved to the following:
UPDATE people
SET ename = 'Bozo'
WHERE ename = 'BLAKE'
AND deptno = SYS_CONTEXT ('people_ctx', 'deptno')
The SYS_CONTEXT function will resolve to the number 20 for the user SCOTT. Because there is no BLAKE record with a deptno = 20, zero records are updated.
To test the inserts, try to insert a new record for Department 20, then insert a record outside Department 20:
scott@KNOX10g> -- This insert should work as deptno = 20
scott@KNOX10g> INSERT INTO people
2 (username, job, salary, deptno)
3 VALUES ('KNOX', 'Clerk', '3000', 20);
1 row created.
scott@KNOX10g> -- This insert should not work
scott@KNOX10g> INSERT INTO people
2 (username, job, salary, deptno)
3 VALUES ('ELLISON', 'CEO','90000', 30); INSERT INTO people
*
ERROR at line 1:
ORA-28115: policy with check option violation
Note the insert operation fails with an error. This operation fails because you set the UPDATE_CHECK=TRUE in the ADD_POLICY procedure call. The default value for UPDATE_ CHECK is FALSE. If you had not specified TRUE, the insert would have succeeded. This behavior is consistent with that of a view with a check option.
The Delete RLS Policy Example The delete policy says that the user can only delete their record. This function can be implemented as follows:
sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION user_only (
2 p_schema IN VARCHAR2 DEFAULT NULL,
3 p_object IN VARCHAR2 DEFAULT NULL)
4 RETURN VARCHAR2
5 AS
6 BEGIN
7 RETURN 'username = sys_context
(''userenv'',''session_user'')';
8 END;
9 /
Function created.
To apply the delete policy, specify DELETE statements in the ADD_POLICY procedure and provide the USER_ONLY function for the POLICY_FUNCTION:
sec_mgr@KNOX10g> -- apply delete policy to table
sec_mgr@KNOX10g> BEGIN
2 DBMS_RLS.add_policy
3 (object_schema => 'SCOTT',
4 object_name => 'PEOPLE',
5 policy_name => People_Del',
6 function_schema => 'SEC_MGR',
7 policy_function => 'user_only',
8 statement_types => 'DELETE');
9 END;
10 /
PL/SQL procedure successfully completed.
Testing the delete policy, you’ll notice that a delete statement that attempts to delete all records actually results in only one actual deletion—the user’s very own record:
scott@KNOX10g> DELETE FROM people;
1 row deleted.
scott@KNOX10g> SELECT * FROM people
2 WHERE username = 'SCOTT';
no rows selected
Again, the database has transparently augmented the SQL statement. The policy function restricts delete operations to the user’s record. Therefore, the most users can ever delete is their record.
Next: The RLS Layer of Security >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article was excerpted from chapter 11 of Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300). Check it out at your favorite bookstore. Buy this book now.
|
|
