Oracle DBMS_RLS Demos

Oracle DBMS_RLS Version 11.2.0.3

General Information

Note

Note: The functionality supporting FGA is based on dynamic predicates acquired at statement parse time, when the base table or view is referenced in a DML statement.

AUTHID

DEFINER

Constants

Name	Data Type	Value
General
STATIC	BINARY_INTEGER	1
SHARED_STATIC	BINARY_INTEGER	2
CONTEXT_SENSITIVE	BINARY_INTEGER	3
SHARED_CONTEXT_SENSITIVE	BINARY_INTEGER	4
DYNAMIC	BINARY_INTEGER	5
XDS1	BINARY_INTEGER	6
XDS2	BINARY_INTEGER	7
XDS3	BINARY_INTEGER	8
Security Relevant Column Operations
ALL_ROWS	BINARY_INTEGER	ALL_ROWS

Default Policy Group

SYS_DEFAULT

Dependencies

ALL_POLICIES	DBA_SEC_RELEVANT_COLS	RLS$
ALL_POLICY_CONTEXTS	DBMS_RLS_LIB	USER_CONTEXTS
CONTEXT$	DBMS_XDBZ0	USER_POLICIES
DBA_CONTEXTS	GV$VPD_POLICY	USER_POLICY_CONTEXTS
DBA_POLICIES	LTADM	V$VPD_POLICY
DBA_POLICY_CONTEXTS	LTUTIL	WK_ADM
DBA_POLICY_GROUPS

First Available

8.1.5

Related System Privileges

create any context
create policy group
drop any context
exempt access policy (not subject to SYS_DEFAULT policies)

GRANT create any context TO uwclass;
GRANT create any policy TO uwclass;
GRANT drop any context TO uwclass;
GRANT execute ON dbms_rls TO uwclass;

Security Model

Owned by SYS EXECUTE is granted to EXECUTE_CATALOG_ROLE, WMSYS, XDB, and APEX_030200

Source

{ORACLE_HOME}/rdbms/admin/dbmsrlsa.sql

Subprograms

ADD_GROUPED_POLICY	DISABLE_GROUPED_POLICY	ENABLE_POLICY
ADD_POLICY	DROP_GROUPED_POLICY	REFRESH_GROUPED_POLICY
ADD_POLICY_CONTEXT	DROP_POLICY	REFRESH_POLICY
CREATE_POLICY_GROUP	DROP_POLICY_CONTEXT	Related Queries
DELETE_POLICY_GROUP	ENABLE_GROUPED_POLICY	Sample Function

ADD_GROUPED_POLICY

Add a row level security policy to a policy group for a table or view

dbms_rls.add_grouped_policy(
object_schema         IN VARCHAR2       := NULL,
object_name           IN VARCHAR2,
policy_group          IN VARCHAR2       := 'SYS_DEFAULT',
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       := NULL,
sec_relevant_cols_opt IN BINARY_INTEGER := NULL);

See FGAC Demo: Link at page bottom

ADD_POLICY

Add a row level security policy to a table or view

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       := NULL,
sec_relevant_cols_opt IN BINARY_INTEGER := NULL);

See FGAC Demo: Link at page bottom

ADD_POLICY_CONTEXT

Add a driving context to a table or view

dbms_rls.add_policy_context(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
namespace     IN VARCHAR2,
attribute     IN VARCHAR2);

TBD

CREATE_POLICY_GROUP

Create a policy group for a table or view

dbms_rls.create_policy_group(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_group IN VARCHAR2);

See FGAC Demo: Link at page bottom

DELETE_POLICY_GROUP

Delete a policy group for a table or view

dbms_rls.delete_policy_group(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_group IN VARCHAR2);

TBD

DISABLE_GROUPED_POLICY

Enable or disable a policy for a table or view

dbms_rls.disable_grouped_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
group_name    IN VARCHAR2,
policy_name   IN VARCHAR2);

See FGAC Demo: Link at page bottom

DROP_GROUPED_POLICY

Drop a row level security policy from a policy group of a table or view

dbms_rls.drop_grouped_policy(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_group IN VARCHAR2 := 'SYS_DEFAULT',
policy_name IN VARCHAR2);

See FGAC Demo: Link at page bottom

DROP_POLICY

Drop a row level security policy from a table or view

dbms_rls.drop_policy(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2);

See FGAC Demo: Link at page bottom

DROP_POLICY_CONTEXT

Drop a driving context from a table or view

dbms_rls.drop_policy_context(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
namespace     IN VARCHAR2,
attribute     IN VARCHAR2);

TBD

ENABLE_GROUPED_POLICY

Enable or disable a policy for a table or view

dbms_rls.enable_grouped_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
group_name    IN VARCHAR2,
policy_name   IN VARCHAR2,
enable        IN BOOLEAN := TRUE);

See FGAC Demo: Link at page bottom

ENABLE_POLICY

Enable or disable a security policy for a table or view

dbms_rls.enable_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2,
enable        IN BOOLEAN := TRUE);

See FGAC Demo: Link at page bottom

REFRESH_GROUPED_POLICY

Invalidate all cursors associated with the policy if no argument provides, all cursors with policies involved will be invalidated

dbms_rls.refresh_grouped_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2 := NULL,
group_name    IN VARCHAR2 := NULL,
policy_name   IN VARCHAR2 := NULL);

TBD

REFRESH_POLICY

Invalidate all cursors associated with the policy. If no argument provides, all cursors with policies involved will be invalidated

dbms_rls.refresh_policy(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2 := NULL);

TBD

Related Queries

Find all objects with policies and the functions enforcing them

SELECT object_owner, object_name, package_name, policy_name
FROM dba_policies
ORDER BY 1,2;

SELECT pfname, obj#, pfschema
FROM sys.rls$
WHERE enable_flag = 1
ORDER BY 1;

Examine VPD policies in memory

SELECT DISTINCT object_owner, object_name, predicate
FROM sys.v$vpd_policy
WHERE predicate IS NOT NULL
ORDER BY 1,2;

Sample Function

This function limits data access by adding a qualifying predicate.

If the user executing the SQL is UWCLASS nothing is appended: Otherwise no rows will be returned because 1 will never equal 0.

CREATE OR REPLACE FUNCTION vpd_sec(p_owner IN VARCHAR2, p_name IN VARCHAR2) AUTHID DEFINER RETURN VARCHAR2 IS
BEGIN
IF sys_context('userenv', 'session_user') IN ('UWCLASS') THEN
RETURN NULL;
ELSE
RETURN '1=0';
END IF;
END vpd_sec;
/