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 E
ADD_POLICY Procedure
This procedure adds a fine-grained access control policy to a table, view, or synonym.
The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.
See Also:
Operational Notes A COMMIT
is also performed at the end of the operation.
DBMS_RLS.ADD_POLICY ( object_schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2, function_schema IN VARCHAR2 NULL, policy_function IN VARCHAR2, statement_types IN VARCHAR2 NULL, update_check IN BOOLEAN FALSE, enable IN BOOLEAN TRUE, static_policy IN BOOLEAN FALSE, policy_type IN BINARY_INTEGER NULL, long_predicate IN BOOLEAN FALSE, sec_relevant_cols IN VARCHAR2, sec_relevant_cols_opt IN BINARY_INTEGER NULL);
Table 89-3 ADD_POLICY Procedure Parameters
Parameter | Description |
---|---|
Schema containing the table, view, or synonym. If no object_schema is | |
Name of table, view, or synonym to which the policy is added. | |
Name of policy to be added. It must be unique for the same table or view. | |
Schema of the policy function (current default schema, if | |
Name of a function which generates a predicate for the policy. If the function is defined within a package, then the name of the package must be present. | |
Statement types to which the policy applies. It can be any combination of | |
Optional argument for | |
Indicates if the policy is enabled when it is added. The default is | |
The default is | |
Default is | |
Default is | |
Enables column-level Virtual Private Database (VPD), which enforces security policies when a column containing sensitive information is referenced in a query. Applies to tables and views, but not to synonyms. Specify a list of comma- or space-separated valid column names of the policy-protected object. The policy is enforced only if a specified column is referenced (or, for an abstract datatype column, its attributes are referenced) in the user SQL statement or its underlying view definition. Default is all the user-defined columns for the object. | |
Use with |
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. |
策略类型
正确的使用策略类型能提高VPD的效率,通过缓存策略函数的输出,使得随后的查询语句,不再执行策略函数,
DBMS_RLS.ADD_POLICY存储过程的策略类型参数可以设置5种策略类型中的一种。
Ø 静态类型:缓存策略函数的返回值,可以被个别对象重复利用,所以策略函数的返回值必须静态。
Ø 共享静态类型,同静态类型一样,但是生成的谓语能够被应用与多个对象。
Ø 敏感上下文类型:当策略基于本地应用上下文的时候使用,策略函数的结果被缓存和重复使用,只有当应用
上下文改变的时候策略函数才会再一次执行。
Ø 共享敏感上下文:同敏感上下文类型一样,但是生成的谓语能够应用与多个对象。
动态类型:策略函数在每此执行SQL语句的时候都被执行。-
SYS
is free of any security policy. -
If no object_schema is specified, the current log-on user schema is assumed.
-
The policy functions which generate dynamic predicates are called by the server. Following is the interface for the function:
FUNCTION policy_function (object_schema IN VARCHAR2, object_name VARCHAR2) RETURN VARCHAR2 --- object_schema is the schema owning the table of view. --- object_name is the name of table, view, or synonym to which the policy applies.
-
The policy functions must have the purity level of
WNDS
(write no database state).See Also:
The Oracle Database Application Developer's Guide - Fundamentals has more details about theRESTRICT_REFERENCES
pragma. -
Dynamic predicates generated out of different policies for the same object have the combined effect of a conjunction (
AND
ed) of all the predicates. -
The security check and object lookup are performed against the owner of the policy function for objects in the subqueries of the dynamic predicates.
-
If the function returns a zero length predicate, then it is interpreted as no restriction being applied to the current user for the policy.
-
When a table alias is required (for example, parent object is a type table) in the predicate, the name of the table or view itself must be used as the name of the alias. The server constructs the transient view as something like
"select c1, c2, ... from tab tab where <predicate>"
-
Validity of the function is checked at runtime for ease of installation and other dependency issues during import and export.
-
Column-level VPD column masking behavior (specified with
sec_relevant_cols_opt => dbms_rls.ALL_ROWS
) is fundamentally different from all other VPD policies, which return only a subset of rows. Instead the column masking behavior returns all rows specified by the user's query, but the sensitive column values display asNULL
. The restrictions for this option are as follows:-
Only applies to
SELECT
statements -
Unlike regular VPD predicates, the masking condition that is generated by the policy function must be a simple boolean expression.
-
If your application performs calculations, or does not expect
NULL
values, then you should use the default behavior of column-level VPD, which is specified with thesec_relevant_cols
parameter. -
If you use
UPDATE AS SELECT
with this option, then only the values in the columns you are allowed to see will be updated. -
This option may prevent some rows from displaying. For example:
select * from employees where salary = 10
This query may not return rows if the
salary
column returns aNULL
value because the column masking option has been set.
-
As the first of two examples, the following creates a policy that applies to the hr.employee
table. This is a column-level VPD policy that will be enforced only if aSELECT
or an INDEX
statement refers to the salary
, birthdate
, or SSN
columns of the table explicitly, or implicitly through a view. It is also a CONTEXT_SENSITIVE
policy, so the server will invoke the policy function hr.hrfun
at parse time. During execution, it will only invoke the function if there has been any session private context change since the last use of the statement cursor. The predicate generated by the policy function must not exceed 4000 bytes, the default length limit, since the long_predicate
parameter is omitted from the call.
BEGIN dbms_rls.add_policy(object_schema => 'hr',
object_name => 'employee', policy_name => 'hr_policy', function_schema =>'hr', policy_function => 'hrfun', statement_types =>'select,index', policy_type => dbms_rls.CONTEXT_SENSITIVE, sec_relevant_cols=>'salary,birthdate,ssn');
END; /
As the second example, the following command creates another policy that applies to the same object for hosting, so users can access only data based on their subscriber ID. Since it is defined as a SHARED_STATIC
policy type, the server will first try to find the predicate in the SGA cache. The server will only invoke the policy function, subfun
, if that search fails.
BEGIN dbms_rls.add_policy(object_schema => 'hr',
object_name => 'employee', policy_name => 'hosting_policy', function_schema =>'hr', policy_function => 'subfun', policy_type => dbms_rls.SHARED_STATIC);
END; /