Oracle DBMS_RLS (Row Level Security)
Version 23c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Row Level Security is also known as Fine Grained Access Control (FGAC) and Virtual Private Database (VPD).

Row Level Security achieves its objective by modifying predicates, in the optimizer such that policies are enforced.
Note The functionality supporting RLS/FGAC/VPD is based on dynamic predicates acquired at statement parse time, when the base table or view is referenced in a DML statement.
AUTHID CURRENT_USER
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 DBMS_GSM_CLOUDADMIN LTUTIL
ALL_POLICY_CONTEXTS DBMS_RLS_INT RLS$
DBA_POLICIES DBMS_RLS_LIB USER_CONTEXTS
DBA_POLICY_CONTEXTS DBMS_XDBZ0 USER_POLICIES
DBA_POLICY_GROUPS DBMS_XDS_INT USER_POLICY_CONTEXTS
DBA_SEC_RELEVANT_COLS GV$VPD_POLICY V_$DIAG_VPDB_PROBLEM
DBMS_ASSERT IS_VPD_ENABLED V$VPD_POLICY
Documented Yes: Packages and Types Reference
First Available 8.1.5
Pragma -- Support log based replication of RLS
PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO);
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 DELETE_POLICY_GROUP ENABLE_POLICY
ADD_POLICY DISABLE_GROUPED_POLICY REFRESH_GROUPED_POLICY
ADD_POLICY_CONTEXT DROP_GROUPED_POLICY REFRESH_POLICY
ALTER_GROUPED_POLICY DROP_POLICY Related Queries
ALTER_POLICY DROP_POLICY_CONTEXT Sample Function
CREATE_POLICY_GROUP ENABLE_GROUPED_POLICY  
 
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,
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);

-- statement types: DELETE, INDEX, INSERT, SELECT, UPDATE
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
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
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
Built-in Functions
Built-in Packages
Database Security
DBMS_RLS_INT
Fine Grained Access Control Demo
Object Privileges
Roles
System Privileges
SYS_CONTEXT
What's New In 21c
What's New In 23c
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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx