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.
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.
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
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
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
will be effectively transformed into
Figure 11-1 depicts how this occurs.
FIGURE 11-1. Queries are modified transparently by
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
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
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:
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
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 (
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
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;
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.
blog comments powered by Disqus