Section A.3. DBMS_RLS


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.

ADD_POLICY

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


DROP_POLICY

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


ENABLE_POLICY

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


REFRESH_POLICY

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.

DBA_POLICIES

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





Oracle PL(s)SQL For DBAs
Oracle PL(s)SQL For DBAs
ISBN: N/A
EAN: N/A
Year: 2005
Pages: 122

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net