7.11 DBMS_RLS

Table of Contents Previous Next


7 Built-In Packages : 7.11 DBMS_RLS

The DBMS_RLS package enables the implementation of Virtual Private Database on certain Advanced Server database objects.
ADD_POLICY(object_schema, object_name, policy_name, function_schema, policy_function [, statement_types [, update_check [, enable [, static_policy [, policy_type [, long_predicate [, sec_relevant_cols [, sec_relevant_cols_opt ]]]]]]]])
DROP_POLICY(object_schema, object_name, policy_name)
ENABLE_POLICY(object_schema, object_name, policy_name, enable)
Advanced Server's implementation of DBMS_RLS is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
Virtual Private Database is a type of fine-grained access control using security policies. Fine-grained access control in Virtual Private Database means that access to data can be controlled down to specific rows as defined by the security policy.
The rules that encode a security policy are defined in a policy function, which is an SPL function with certain input parameters and return value. The security policy is the named association of the policy function to a particular database object, typically a table.
Note: In Advanced Server, the policy function can be written in any language supported by Advanced Server such as SQL, PL/pgSQL and SPL.
Note: The database objects currently supported by Advanced Server Virtual Private Database are tables. Policies cannot be applied to views or synonyms.
Provides a fine-grained level of security. Database object level privileges given by the GRANT command determine access privileges to the entire instance of a database object, while Virtual Private Database provides access control for the individual rows of a database object instance.
Note: The only way security policies can be circumvented is if the EXEMPT ACCESS POLICY system privilege has been granted to a user. The EXEMPT ACCESS POLICY privilege should be granted with extreme care as a user with this privilege is exempted from all policies in the database. See the GRANT command in Section 3.3.55 or the ALTER ROLE command in Section 3.3.3 for additional information.
The DBMS_RLS package provides procedures to create policies, remove policies, enable policies, and disable policies.
Create a policy function. The function must have two input parameters of type VARCHAR2. The first input parameter is for the schema containing the database object to which the policy is to apply and the second input parameter is for the name of that database object. The function must have a VARCHAR2 return type. The function must return a string in the form of a WHERE clause predicate. This predicate is dynamically appended as an AND condition to the SQL command that acts upon the database object. Thus, rows that do not satisfy the policy function predicate are filtered out from the SQL command result set.
Use the ADD_POLICY procedure to define a new policy, which is the association of a policy function with a database object. With the ADD_POLICY procedure, you can also specify the types of SQL commands (INSERT, UPDATE, DELETE, or SELECT) to which the policy is to apply, whether or not to enable the policy at the time of its creation, and if the policy should apply to newly inserted rows or the modified image of updated rows.
Use the ENABLE_POLICY procedure to disable or enable an existing policy.
Use the DROP_POLICY procedure to remove an existing policy. The DROP_POLICY procedure does not drop the policy function or the associated database object.
Once policies are created, they can be viewed in the catalog views, compatible with Oracle databases: ALL_POLICIES (see Section 10.11), DBA_POLICIES (see Section 10.35), or USER_POLICIES (see Section 10.62).
The SYS_CONTEXT function is often used with DBMS_RLS. The signature is:
SYS_CONTEXT(namespace, attribute)
namespace is a VARCHAR2; the only accepted value is USERENV. Any other value will return NULL.
attribute is a VARCHAR2. attribute may be:
Note: The examples used to illustrate the DBMS_RLS package are based on a modified copy of the sample emp table provided with Advanced Server along with a role named salesmgr that is granted all privileges on the table. You can create the modified copy of the emp table named vpemp and the salesmgr role as shown by the following:
7.11.1 ADD_POLICY
The ADD_POLICY procedure creates a new policy by associating a policy function with a database object.
ADD_POLICY(object_schema VARCHAR2, object_name VARCHAR2,
policy_name VARCHAR2, function_schema VARCHAR2,
policy_function VARCHAR2
[, statement_types VARCHAR2
[, update_check BOOLEAN
[, enable BOOLEAN
[, static_policy BOOLEAN
[, policy_type INTEGER
[, long_predicate BOOLEAN
[, sec_relevant_cols VARCHAR2
[, sec_relevant_cols_opt INTEGER ]]]]]]]])
Name assigned to the policy. The combination of database object (identified by object_schema and object_name) and policy name must be unique within the database.
Note: The policy function may belong to a package in which case function_schema must contain the name of the schema in which the package is defined.
Note: The policy function may belong to a package in which case policy_function must also contain the package name in dot notation (that is, package_name.function_name).
Comma-separated list of SQL commands to which the policy applies. Valid SQL commands are INSERT, UPDATE, DELETE, and SELECT. The default is INSERT,UPDATE,DELETE,SELECT.
Note: Advanced Server accepts INDEX as a statement type, but it is ignored. Policies are not applied to index operations in Advanced Server.
Applies to INSERT and UPDATE SQL commands only.
When set to TRUE, the policy is applied to newly inserted rows and to the modified image of updated rows. If any of the new or modified rows do not qualify according to the policy function predicate, then the INSERT or UPDATE command throws an exception and no rows are inserted or modified by the INSERT or UPDATE command.
When set to FALSE, the policy is not applied to newly inserted rows or the modified image of updated rows. Thus, a newly inserted row may not appear in the result set of a subsequent SQL command that invokes the same policy. Similarly, rows which qualified according to the policy prior to an UPDATE command may not appear in the result set of a subsequent SQL command that invokes the same policy.
When set to TRUE, the policy is enabled and applied to the SQL commands given by the statement_types parameter. When set to FALSE the policy is disabled and not applied to any SQL commands. The policy can be enabled using the ENABLE_POLICY procedure. The default is TRUE.
In Oracle, when set to TRUE, the policy is static, which means the policy function is evaluated once per database object the first time it is invoked by a policy on that database object. The resulting policy function predicate string is saved in memory and reused for all invocations of that policy on that database object while the database server instance is running.
When set to FALSE, the policy is dynamic, which means the policy function is re-evaluated and the policy function predicate string regenerated for all invocations of the policy.
Note: In Oracle 10g, the policy_type parameter was introduced, which is intended to replace the static_policy parameter. In Oracle, if the policy_type parameter is not set to its default value of NULL, the policy_type parameter setting overrides the static_policy setting.
Note: The setting of static_policy is ignored by Advanced Server. Advanced Server implements only the dynamic policy, regardless of the setting of the static_policy parameter.
Note: The setting of this parameter is ignored by Advanced Server. Advanced Server always assumes a dynamic policy.
In Oracle, allows predicates up to 32K bytes if set to TRUE, otherwise predicates are limited to 4000 bytes. The default is FALSE.
Note: The setting of this parameter is ignored by Advanced Server. An Advanced Server policy function can return a predicate of unlimited length for all practical purposes.
Comma-separated list of columns of object_name. Provides column-level Virtual Private Database for the listed columns. The policy is enforced if any of the listed columns are referenced in a SQL command of a type listed in statement_types. The policy is not enforced if no such columns are referenced.
The default is NULL, which has the same effect as if all of the database object’s columns were included in sec_relevant_cols.
In Oracle, if sec_relevant_cols_opt is set to DBMS_RLS.ALL_ROWS (INTEGER constant of value 1), then the columns listed in sec_relevant_cols return NULL on all rows where the applied policy predicate is false. (If sec_relevant_cols_opt is not set to DBMS_RLS.ALL_ROWS, these rows would not be returned at all in the result set.) The default is NULL.
Note: Advanced Server does not support the DBMS_RLS.ALL_ROWS functionality. Advanced Server throws an error if sec_relevant_cols_opt is set to DBMS_RLS.ALL_ROWS (INTEGER value of 1).
This function generates the predicate authid = SYS_CONTEXT('USERENV', 'SESSION_USER'), which is added to the WHERE clause of any SQL command of the type specified in the ADD_POLICY procedure.
Note: This example uses the SYS_CONTEXT function to return the login user name. In Oracle the SYS_CONTEXT function is used to return attributes of an application context. The first parameter of the SYS_CONTEXT function is the name of an application context while the second parameter is the name of an attribute set within the application context. USERENV is a special built-in namespace that describes the current session. Advanced Server does not support application contexts, but only this specific usage of the SYS_CONTEXT function.
The following anonymous block calls the ADD_POLICY procedure to create a policy named secure_update to be applied to the vpemp table using function verify_session_user whenever an INSERT, UPDATE, or DELETE SQL command is given referencing the vpemp table.
After successful creation of the policy, a terminal session is started by user salesmgr. The following query shows the content of the vpemp table:
An unqualified UPDATE command (no WHERE clause) is issued by the salesmgr user:
Instead of updating all rows in the table, the policy restricts the effect of the update to only those rows where the authid column contains the value salesmgr as specified by the policy function predicate authid = SYS_CONTEXT('USERENV', 'SESSION_USER').
The following query shows that the comm column has been changed only for those rows where authid contains salesmgr. All other rows are unchanged.
Furthermore, since the update_check parameter was set to TRUE in the ADD_POLICY procedure, the following INSERT command throws an exception since the value given for the authid column, researchmgr, does not match the session user, which is salesmgr, and hence, fails the policy.
If update_check was set to FALSE, the preceding INSERT command would have succeeded.
The following example illustrates the use of the sec_relevant_cols parameter to apply a policy only when certain columns are referenced in the SQL command. The following policy function is used for this example, which selects rows where the employee salary is less than 2000.
The policy is created so that it is enforced only if a SELECT command includes columns sal or comm:
If a query does not reference columns sal or comm, then the policy is not applied. The following query returns all 14 rows of table vpemp:
If the query references the sal or comm columns, then the policy is applied to the query eliminating any rows where sal is greater than or equal to 2000 as shown by the following:
The DROP_POLICY procedure deletes an existing policy. The policy function and database object associated with the policy are not deleted by the DROP_POLICY procedure.
DROP_POLICY(object_schema VARCHAR2, object_name VARCHAR2,
policy_name VARCHAR2)
The following example deletes policy secure_update on table public.vpemp:
The ENABLE_POLICY procedure enables or disables an existing policy on the specified database object.
ENABLE_POLICY(object_schema VARCHAR2, object_name VARCHAR2,
policy_name VARCHAR2, enable BOOLEAN)
When set to TRUE, the policy is enabled. When set to FALSE, the policy is disabled.
The following example disables policy secure_update on table public.vpemp:

7 Built-In Packages : 7.11 DBMS_RLS

Table of Contents Previous Next