Rem Copyright (c) 1998, 2008, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem dbmsrlsa.sql - Row Level Security Adminstrative interface
Rem
Rem DESCRIPTION
Rem dbms_rls package for row level security adminstrative interface
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem ajadams 11/10/08 - add _with_commit to supplemental_log_data pragma
Rem clei 10/22/07 - new DBMS_XDS API for XDS enhancements
Rem pknaggs 08/31/07 - DSD schema: aclids to aclFiles or aclDirectory.
Rem preilly 03/26/07 - Pragma dbms_xds to not replicate in Logical
Rem Standby
Rem clei 01/08/07 - remove DV_INTERNAL
Rem fjlee 04/27/06 - XbranchMerge ayalaman_dv_overlay_5112125_0418
Rem from st_rdbms_10.2
Rem clei 03/15/06 - remove grant to XDB
Rem clei 02/11/06 - add dbms_xdsutl
Rem clei 12/19/05 - add dbms_xds
Rem cchui 04/02/06 - XbranchMerge cchui_skip_function_call from
Rem st_rdbms_10.2dv
Rem cchui 03/28/06 - add new type for Data Vault
Rem clei 10/13/03 - ALL_COLUMNS -> ALL_ROWS
Rem clei 08/13/03 - add security relevant column option
Rem clei 05/28/02 - policy types, sec relevant cols, and predicate sz
Rem gviswana 05/24/01 - CREATE OR REPLACE SYNONYM
Rem clei 04/12/01 - support static policy
Rem dmwong 08/16/00 - rename UI to grouped_policies.
Rem dmwong 02/09/00 - add groups for refresh and enable
Rem dmwong 01/25/00 - add group extension
Rem clei 03/16/98 -
Rem clei 02/24/98 - Created
Rem
CREATE OR REPLACE PACKAGE dbms_rls AS
STATIC CONSTANT BINARY_INTEGER := 1;
SHARED_STATIC CONSTANT BINARY_INTEGER := 2;
CONTEXT_SENSITIVE CONSTANT BINARY_INTEGER := 3;
SHARED_CONTEXT_SENSITIVE CONSTANT BINARY_INTEGER := 4;
DYNAMIC CONSTANT BINARY_INTEGER := 5;
XDS1 CONSTANT BINARY_INTEGER := 6;
XDS2 CONSTANT BINARY_INTEGER := 7;
XDS3 CONSTANT BINARY_INTEGER := 8;
-- security relevant columns options, default is null
ALL_ROWS CONSTANT BINARY_INTEGER := 1;
-- Support log based replication of RLS (proj 17779)
PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO_WITH_COMMIT);
-- ------------------------------------------------------------------------
-- add_policy - add a row level security policy to a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be added
-- function_schema - schema of the policy function, current user if NULL
-- policy_function - function to generate predicates for this policy
-- statement_types - statement type that the policy apply, default is any
-- update_check - policy checked against updated or inserted value?
-- enable - policy is enabled?
-- static_policy - policy is static (predicate is always the same)?
-- policy_type - policy type - overwrite static_policy if non-null
-- long_predicate - max predicate length 4000 bytes (default) or 32K
-- sec_relevant_cols - list of security relevant columns
-- sec_relevant_cols_opt - security relevant column option
PROCEDURE 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 BOOLEAN := FALSE,
sec_relevant_cols IN VARCHAR2 := NULL,
sec_relevant_cols_opt IN BINARY_INTEGER := NULL);
-- drop_policy - drop a row level security policy from a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be dropped
PROCEDURE drop_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2);
-- refresh_policy - invalidate all cursors associated with the policy
-- if no argument provides, all cursors with
-- policies involved will be invalidated
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be refreshed
PROCEDURE refresh_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2 := NULL);
-- enable_policy - enable or disable a security policy for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be enabled or disabled
-- enable - TRUE to enable the policy, FALSE to disable the policy
PROCEDURE enable_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
enable IN BOOLEAN := TRUE );
-- create_policy_group - create a policy group for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_group - name of policy to be created
PROCEDURE create_policy_group(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_group IN VARCHAR2);
-- ------------------------------------------------------------------------
-- add_grouped_policy - add a row level security policy to a policy group
-- for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_group - name of policy group to be added
-- policy_name - name of policy to be added
-- function_schema - schema of the policy function, current user if NULL
-- policy_function - function to generate predicates for this policy
-- statement_types - statement type that the policy apply, default is any
-- update_check - policy checked against updated or inserted value?
-- enable - policy is enabled?
-- static_policy - policy is static (predicate is always the same)?
-- policy_type - policy type - overwrite static_policy if non-null
-- long_predicate - max predicate length 4000 bytes (default) or 32K
-- sec_relevant_cols - list of security relevant columns
-- sec_relevant_cols_opt - security relevant columns option
PROCEDURE 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 BOOLEAN := FALSE,
sec_relevant_cols IN VARCHAR2 := NULL,
sec_relevant_cols_opt IN BINARY_INTEGER := NULL);
-- ------------------------------------------------------------------------
-- add_policy_context - add a driving context to a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- namespace - namespace of driving context
-- attribute - attribute of driving context
PROCEDURE add_policy_context(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
namespace IN VARCHAR2,
attribute IN VARCHAR2);
-- delete_policy_group - drop a policy group for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_group - name of policy to be dropped
PROCEDURE delete_policy_group(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_group IN VARCHAR2);
-- drop_grouped_policy - drop a row level security policy from a policy
-- group of a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_group - name of policy to be dropped
-- policy_name - name of policy to be dropped
PROCEDURE drop_grouped_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_group IN VARCHAR2 := 'SYS_DEFAULT',
policy_name IN VARCHAR2);
-- ------------------------------------------------------------------------
-- drop_policy_context - drop a driving context from a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- namespace - namespace of driving context
-- attribute - attribute of driving context
PROCEDURE drop_policy_context(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
namespace IN VARCHAR2,
attribute IN VARCHAR2);
-- refresh_grouped_policy - invalidate all cursors associated with the policy
-- if no argument provides, all cursors with
-- policies involved will be invalidated
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_group - name of group of the policy to be refreshed
-- policy_name - name of policy to be refreshed
PROCEDURE refresh_grouped_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
group_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2 := NULL);
-- enable_grouped_policy - enable or disable a policy for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be enabled or disabled
-- enable - TRUE to enable the policy, FALSE to disable the policy
PROCEDURE enable_grouped_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
group_name IN VARCHAR2,
policy_name IN VARCHAR2,
enable IN BOOLEAN := TRUE);
-- disable_grouped_policy - enable or disable a policy for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be enabled or disabled
-- enable - TRUE to enable the policy, FALSE to disable the policy
PROCEDURE disable_grouped_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
group_name IN VARCHAR2,
policy_name IN VARCHAR2);
END dbms_rls;
/
CREATE OR REPLACE PUBLIC SYNONYM dbms_rls FOR sys.dbms_rls
/
--
-- Grant execute right to EXECUTE_CATALOG_ROLE
--
GRANT EXECUTE ON sys.dbms_rls TO execute_catalog_role
/
CREATE OR REPLACE PACKAGE dbms_xds AS
ENABLE_DYNAMIC_IS CONSTANT BINARY_INTEGER := 1;
ENABLE_ACLOID_COLUNM CONSTANT BINARY_INTEGER := 2;
ENABLE_STATIC_IS CONSTANT BINARY_INTEGER := 3;
-- Disable log based replication for this package
PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED_WITH_COMMIT);
-- enable_xds - Enables XDS for a table
--
-- INPUT PARAMETERS
-- object_schema - schema owning the object, current user if NULL
-- object_name - name of object
-- enable_option - enable option
-- ENABLE_DYNAMIC_IS: enable XDS with dynamic instance
-- set support only.
-- ENABLE_ACLOID_COLUNM: enable XDS with dynamic instance
-- set support and SYS_ACLOID column avaliable
-- for static ACLID storage.
-- ENABLE_STATIC_IS: enable XDS with dynamic and static
-- instance set support.
-- NULL (default): re-enable with the current option or
-- ENABLE_DYNAMIC_IS if it is enabled the first time.
-- dsd_path - DSD path, default is null (no change on designated
-- or the link to the designated location)
PROCEDURE enable_xds(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
enable_option IN BINARY_INTEGER := NULL,
dsd_path IN VARCHAR2 := NULL);
----------------------------------------------------------------------------
-- disable_xds - disable XDS for a table
--
-- INPUT PARAMETERS
-- object_schema - schema owning the object, current user if NULL
-- object_name - name of object
PROCEDURE disable_xds(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2);
----------------------------------------------------------------------------
-- drop_xds - drop XDS policy from a table
--
-- INPUT PARAMETERS
-- object_schema - schema owning the object, current user if NULL
-- object_name - name of object
PROCEDURE drop_xds(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2);
----------------------------------------------------------------------------
-- refresh_dsd - refresh XDS document cache for a table/view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the object, current user if NULL
-- object_name - name of object
PROCEDURE REFRESH_DSD(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2);
----------------------------------------------------------------------------
-- refresh_dsd - refresh XDS document cache identified by its DSD path
--
-- INPUT PARAMETERS
-- dsd_path - DSD resource path
PROCEDURE REFRESH_DSD(dsd_path IN VARCHAR2);
END dbms_xds;
/
CREATE OR REPLACE PUBLIC SYNONYM dbms_xds FOR sys.dbms_xds
/
--
-- Grant execute right to EXECUTE_CATALOG_ROLE
--
GRANT EXECUTE ON sys.dbms_xds TO execute_catalog_role
/
----------------------------------------------------------------------------
-- This is an internal API to invalidate entries in the DataSecurity
-- document kgl cache.
-- We need this before c api is available for XML event handling.
-- Don't publish this package because we will remove it soon!
CREATE OR REPLACE PACKAGE dbms_xdsutl AS
-- invalidate_dsd_cache
--
-- Given the object_id of a DataSecurity document, invalidate
-- the DSD kgl cache entry associated with this object_id.
--
-- INPUT PARAMETERS
-- object_id - XMLRef of DataSecurity doc to invalidate
PROCEDURE invalidate_dsd_cache(object_id IN VARCHAR2);
-- invalidate_dsd_cache_by_aclid
--
-- Given hex string containing the ACLID of an ACL, invalidate all
-- of the DSD kgl cache entries associated with this ACL.
--
-- INPUT PARAMETERS
-- aclid - Hex string of ACLID
PROCEDURE invalidate_dsd_cache_by_aclid(aclid IN VARCHAR2);
END dbms_xdsutl;
/
CREATE OR REPLACE PUBLIC SYNONYM dbms_xdsutl FOR sys.dbms_xdsutl
/