dbms_rls
Overview
The functionality to support fine-grained access control is based on dynamic predicates, where security rules are not embedded in views, but are acquired at the statement parse time, when the base table or view is referenced in a DML statement.
A dynamic predicate for a table, view, or synonym is generated by a PL/SQL function, which is associated with a security policy through a PL/SQL interface. For example:
DBMS_RLS.ADD_POLICY ( 'hr', 'employees', 'emp_policy', 'hr', 'emp_sec', 'select');
Whenever the EMPLOYEES
table, under the HR schema, is referenced in a query or subquery (SELECT
), the server calls the EMP_SEC
function (under the HR
schema). This function returns a predicate specific to the current user for the EMP_POLICY
policy. The policy function may generate the predicates based on the session environment variables available during the function call. These variables usually appear in the form of application contexts. The policy can specify any combination of security-relevant columns and of these statement types: INDEX
, SELECT
, INSERT, UPDATE
, or DELETE
.
The server then produces a transient view with the text:
SELECT * FROM hr.employees WHERE P1
Here, P1
(for example, where SAL
> 10000, or even a subquery) is the predicate returned from the EMP_SEC
function. The server treats the EMPLOYEES
table as a view and does the view expansion just like the ordinary view, except that the view text is taken from the transient view instead of the data dictionary.
If the predicate contains subqueries, then the owner (definer) of the policy function is used to resolve objects within the subqueries and checks security for those objects. In other words, users who have access privilege to the policy-protected objects do not need to know anything about the policy. They do not need to be granted object privileges for any underlying security policy. Furthermore, the users do not require EXECUTE
privilege on the policy function, because the server makes the call with the function definer's right.
Note:
The transient view can preserve the updatability of the parent object because it is derived from a single table or view with predicate only; that is, noJOIN
,
ORDER
BY
,
GROUP
BY
, and so on.
DBMS_RLS
also provides the interface to drop or enable security policies. For example, you can drop or enable the EMP_POLICY
with the following PL/SQL statements:
DBMS_RLS.DROP_POLICY('hr', 'employees', 'emp_policy'); DBMS_RLS.ENABLE_POLICY('hr', 'employees', 'emp_policy', FALSE);
policy type
Table 89-4 DBMS_RLS.ADD_POLICY Policy Types
Policy Type | Description |
---|---|
Predicate is assumed to be the same regardless of the runtime environment. Static policy functions are executed once and then cached in SGA. Statements accessing the same object do not reexecute the policy function. However, each execution of the same cursor could produce a different row set even for the same predicate because the predicate may filter the data differently based on attributes such as | |
Same as | |
Server re-evaluates the policy function at statement execution 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 context during client switches. Note that the server does not cache the value returned by the function for this policy type; it always executes the policy function on statement parsing. Applies to only one object. | |
Same as | |
The default policy type. Server assumes the predicate may be affected by any system or session environment at any time, and so always reexecutes the policy function on each statement parsing or execution. Applies to only one object. |
245.
You want to enforce a company's business policy on several objects by using a single policy function. Which two types of policies can be assigned to the policy_type argument in the dbms_rls.add_policy procedure to achieve the above objective? (Choose two.)
A. DBMS_RLS.STATIC
B. DBMS_RLS.DYNAMIC
C. DBMS_RLS.SHARED_STATIC
D. DBMS_RLS.CONTEXT_SENSITIVE
E. DBMS_RLS.SHARED_CONTEXT_SENSITIVE
Answer: C D