A.3. DBMS_RLS This package contains all of the programs used to implement row-level security by adding, dropping, enabling, disabling, and refreshing policies. Because some program parameters are new or different in Oracle Database 10g, the tables below indicate the differences between the Oracle9i Database and Oracle Database 10g releases.
This procedure adds an RLS policy on a table. | | | Oracle Database version |
---|
Parameter name | Datatype | Description | 9i | 10g |
---|
object_schema | VARCHAR2 | Owner of the table on which the RLS policy is placed. Default is the current user. | Yes | Yes | object_name | VARCHAR2 | Name of the table on which the RLS policy is placed. | Yes | Yes | policy_name | VARCHAR2 | Name of the RLS policy being created. | Yes | Yes | function_schema | VARCHAR2 | Owner of the policy function. This function produces the predicate that is applied to the query to restrict rows. Default is the current user. | Yes | Yes | policy_function | VARCHAR2 | Name of the policy function. | Yes | Yes | statement_types | VARCHAR2 | Types of statements to which this policy is appliedSELECT, INSERT, UPDATE, and/or DELETE. Default is all. In Oracle Database 10g Release 2, a new type, INDEX, is also available. | Yes | Yes | update_check | BOOLEAN | Possible valuesTRUE or FALSE. If set to TRUE, the policy makes sure that the user sees the rows even after the change. Default is FALSE. | Yes | Yes | enable | BOOLEAN | Possible valuesTRUE or FALSE. Indicates whether the policy is enabled. | Yes | Yes | static_policy | BOOLEAN | Included if the policy is static. | Yes | Yes | policy_type | BINARY_INTEGER | Dynamism of the policy; STATIC, SHARED_STATIC, CONTEXT_SENSITIVE, SHARED_ CONTEXT_SENSITIVE, or DYNAMIC. Prefix with DBMS_RLS, as in POLICY_TYPE=> DBMS_RLS. STATIC. Default is DYNAMIC. | No | Yes | long_predicate | BOOLEAN | If the length of the predicate returned by the policy function is more than 4,000 bytes, you must set this parameter to TRUE; that allows the policy function to return predicates up to 32,000 bytes long. Default is FALSE. | No | Yes | sec_relevant_cols | VARCHAR2 | Specifies the list of columns whose selection causes the RLS policy to be applied; otherwise, the RLS policy is not applied to the query. | No | Yes | sec_relevant_cols_opt | VARCHAR2 | If there are specific columns whose selection triggers use of the RLS policy, then there is a choice: when the user selects the sensitive columns, should the row be displayed with the values of the columns shown as NULL, or should the row not be displayed at all? Setting this parameter to ALL_ROWS chooses the former behavior. Prefix with DBMS_RLS, as in SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS. Default is NULL, which indicates that the rows containing these values should not be displayed. | No | Yes |
This procedure drops an existing RLS policy on a table. | | | Oracle Database version |
---|
Parameter name | Datatype | Description | 9i | 10g |
---|
object_schema | VARCHAR2 | Owner of the table on which the RLS policy is placed. Default is the current user. | Yes | Yes | object_name | VARCHAR2 | Name of the table on which the RLS policy is placed. | Yes | Yes | policy_name | VARCHAR2 | Name of the RLS policy to be dropped. | Yes | Yes |
This procedure enables or disables an RLS policy on a table. | | | Oracle Database version |
---|
Parameter name | Datatype | Description | 9i | 10g |
---|
object_schema | VARCHAR2 | Owner of the table on which the RLS policy is placed. Default is the current user. | Yes | Yes | object_name | VARCHAR2 | Name of the table on which the RLS policy is placed. | Yes | Yes | policy_name | VARCHAR2 | Name of the RLS policy to be enabled or disabled. | Yes | Yes | enable | BOOLEAN | TRUE means enable this policy; FALSE means disable this policy. | Yes | Yes |
This procedure refreshes the predicate on an RLS policy. When a policy is defined as being anything other than DYNAMIC, the policy predicate may not execute. The predicate cached in memory will be used until the expiring condition specified for that predicate occurs. When you want to refresh the policy, simply call the REFRESH_POLICY procedure . It re-executes the policy function and refreshes the cached predicate. | | | Oracle Database version |
---|
Parameter name | Datatype | Description | 9i | 10g |
---|
object_schema | VARCHAR2 | Owner of the table on which the RLS policy is placed. Default is the current user. | Yes | Yes | object_name | VARCHAR2 | Name of the table on which the RLS policy is placed. | Yes | Yes | policy_name | VARCHAR2 | Name of the RLS policy to be refreshed. | Yes | Yes |
A.3.1. RLS Data Dictionary Views This section summarizes the columns in the data dictionary views that are relevant to row-level security.
This view shows all the RLS policies on the database, whether they are enabled or not. | | Oracle Database version |
---|
Column name | Description | 9i | 10g |
---|
OBJECT_OWNER | Owner of the table on which the policy is defined. | Yes | Yes | OBJECT_NAME | Name of the table on which the policy is defined. | Yes | Yes | POLICY_GROUP | If this is part of a group, the name of the policy group. | Yes | Yes | POLICY_NAME | Name of the policy. | Yes | Yes | PF_OWNER | Owner of the policy function, which creates and returns the predicate. | Yes | Yes | PACKAGE | If the policy function is a packaged one, this is the name of the package. | Yes | Yes | FUNCTION | Name of the policy function. | Yes | Yes | SEL | Indicates that this is a policy for SELECT statements on this table. | Yes | Yes | INS | Indicates that this is a policy for INSERT statements on this table. | Yes | Yes | UPD | Indicates that this is a policy for UPDATE statements on this table. | Yes | Yes | DEL | Indicates that this is a policy for DELETE statements on this table. | Yes | Yes | IDX | Indicates that this is a policy for CREATE INDEX statements on this table (only for Oracle Database 10g Release 2). | No | Yes | CHK_OPTION | Indicates whether the update check option was enabled when the policy was created. | Yes | Yes | ENABLE | Indicates whether the policy is enabled. | Yes | Yes | STATIC_POLICY | Indicates whether this is a static policy. | Yes | Yes | POLICY_TYPE | Dynamism of the policy (e.g., STATIC). See ADD_POLICY for a complete list. | No | Yes | LONG_PREDICATE | Indicates whether this policy function returns a predicate longer than 4,000 bytes. | No | Yes |
|