Oracle DBMS_RLS
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Fine Grained Access Control achieves security by modifying predicates, in the optimizer such that policies are enforced.
Note The functionality supporting FGAC 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
OLS BINARY_INTEGER 9
Security Column Options
ALL_ROWS BINARY_INTEGER 1
Type of refresh on static acl mv
XDS_ON_COMMIT_MV BINARY_INTEGER 0
XDS_ON_DEMAND_MV BINARY_INTEGER 1
XDS_SCHEDULED_MV BINARY_INTEGER 2
Type of static acl mv
XDS_SYSTEM_GENERATED_MV BINARY_INTEGER 0
XDS_USER_SPECIFIED_MV BINARY_INTEGER 1
Alter options for a row level security policy
ADD_ATTRIBUTE_ASSOCIATION BINARY_INTEGER 1
REMOVE_ATTRIBUTE_ASSOCIATION BINARY_INTEGER 2
Default Policy Group SYS_DEFAULT
Dependencies
ALL_POLICIES DBA_SEC_RELEVANT_COLS RLS$
ALL_POLICY_CONTEXTS DBMS_GSM_CLOUDADMIN USER_CONTEXTS
CONTEXT$ DBMS_RLS_LIB USER_POLICIES
DBA_CONTEXTS DBMS_XDBZ0 USER_POLICY_CONTEXTS
DBA_POLICIES DBMS_XDS V$VPD_POLICY
DBA_POLICY_CONTEXTS GV$VPD_POLICY WK_ADM
DBA_POLICY_GROUPS LTUTIL  
Documented Yes
First Available 8.1.5
Pragma -- Support log based replication of RLS
PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO_WITH_COMMIT);
Related System Privileges
CREATE ANY CONTEXT DROP ANY CONTEXT EXEMPT ACCESS POLICY
CREATE POLICY GROUP    

exempt access policy means "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 DVSYS, DV_OWNER, EXECUTE_CATALOG_ROLE, GSMADMIN_INTERNAL, LBACSYS, WMSYS, and XDB
Source {ORACLE_HOME}/rdbms/admin/dbmsrlsa.sql
Subprograms
 
ADD_GROUPED_POLICY (new 12.1 parameters)
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,
namespace             IN VARCHAR2       := NULL,
attribute             IN VARCHAR2       := 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,
namespace             IN VARCHAR2       := NULL,
attribute             IN VARCHAR2       := 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
 
ALTER_GROUPED_POLICY (new 12.1)
Alter a row level security policy of a policy group dbms_rls.alter_grouped_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_group  IN VARCHAR2 := 'SYS_DEFAULT',
policy_name   IN VARCHAR2,
alter_option  IN BINARY_INTEGER := NULL,
namespace     IN VARCHAR2,
attribute     IN VARCHAR2);
TBD
 
ALTER_POLICY (new 12.1)
Alter a row level security policy dbms_rls.alter_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2,
alter_option  IN BINARY_INTEGER := NULL,
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;
/

Related Topics
Fine Grained Access Control Demo
Object Privileges
Packages
Roles
Security
System Privileges
SYS_CONTEXT
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved