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 ]]]]]]]])
|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.
In Advanced Server, the policy function can be written in any language supported by Advanced Server such as SQL, PL/pgSQL and SPL.
The database objects currently supported by Advanced Server Virtual Private Database are tables. Policies cannot be applied to views or synonyms.
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
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
, 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.
function is often used with DBMS
. The signature is:
is a VARCHAR2
; the only accepted value is USERENV
. Any other value will return NULL
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:
procedure creates a new policy by associating a policy function with a database object.
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.
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
Advanced Server accepts INDEX
as a statement type, but it is ignored. Policies are not applied to index operations in Advanced Server.
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
command throws an exception and no rows are inserted or modified by the INSERT
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.
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
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
The setting of this parameter is ignored by Advanced Server. Advanced Server always assumes a dynamic policy.
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
constant of value 1), then the columns listed in sec_relevant_cols
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
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
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
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
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
, or DELETE
SQL command is given referencing the vpemp
An unqualified UPDATE
command (no WHERE
clause) is issued by the salesmgr
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
, does not match the session user, which is salesmgr
, and hence, fails the policy.
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
If the query references the sal
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:
procedure deletes an existing policy. The policy function and database object associated with the policy are not deleted by the DROP_POLICY
procedure enables or disables an existing policy on the specified database object.
When set to TRUE
, the policy is enabled. When set to FALSE
, the policy is disabled.