| |
 |
|
Oracle dbms_rls
Oracle Database Tips by Donald Burleson |
Using
Oracle dbms_rls
A VPD security model
uses the Oracle dbms_rls package (RLS stands for row-level
security) to implement the security policies and application
contexts. This requires a policy that is defined to control.
access to tables and rows. Below is an example for the Oracle
dbms_rls policy:
Here are examples of invocations of the
dbms_rls.add policy:
DBMS_RLS.ADD_POLICY (
'pubs',
'book',
'access_policy',
'pubs',
'book_access_policy',
'select'
);
BEGIN
DBMS_RLS.ADD_POLICY (object_schema
=> 'scott',
object_name
=> 'emp',
policy_name
=> 'sp_job',
function_schema => 'scott',
policy_function => 'pf_job',
policy_type
=> DBMS_RLS.STATIC,
sec_relevant_cols => 'sal,comm',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/
CALL DBMS_RLS.ADD_POLICY
('BART', 'condition_sets', 'condition_sets_policy', 'BART', 'exp_security.client_id_security',
'SELECT');
Oracle
Virtual Private Database
object_schema => 'CLAIM_SCHEMA',
object_name => 'CLAIMS',
policy_name => 'CLAIM_SELECT_POLICY',
function_schema => 'SECUSER',
policy_function => 'SELECT_AUTH_CLAIMS',
statement_types => 'SELECT'
);
dbms_rls.add_policy (
object_schema => 'CLAIM_SCHEMA',
object_name => 'CLAIMS',
policy_name => 'CLAIM_INSERT_POLICY',
function_schema => 'SECUSER',
policy_function => 'INSERT_AUTH_CLAIMS',
statement_types => 'INSERT',
update_check => TRUE
);
dbms_rls.add_policy (
object_schema => 'CLAIM_SCHEMA',
object_name => 'CLAIMS',
policy_name => 'CLAIM_UPDATE_POLICY',
function_schema => 'SECUSER',
policy_function => 'UPDATE_AUTH_CLAIMS',
statement_types => 'UPDATE',
update_check => TRUE
);
dbms_rls.add_policy (
object_schema => 'CLAIM_SCHEMA',
object_name => 'CLAIMS',
policy_name => 'CLAIM_VALUE_POLICY',
function_schema => 'SECUSER',
policy_function => 'CHECK_CLAIM_VALUE_POLICY',
statement_types => 'UPDATE',
update_check => TRUE
Oracle
DBMS_RLS Package
Advanced Oracle Utilities: The Definitive Reference by
Rampant TechPress is written by the top Oracle
database experts (Bert Scalzo, Donald
Burleson, and Steve Callan). The following is an excerpt
from the book.
DBMS_RLS
The
dbms_rls
package provides a collection of procedures for
administering row level security (RLS), also
commonly referred to as fine grain access
control (FGAC). Essentially, RLS or FGAC appends
dynamic predicates, such as WHERE clause
restrictions, to DML statements at parse time.
This security is completely transparent to both
the end user as well as the application
developer.
Note
that the dbms_rls package itself is
fairly straightforward, but the example of its
usage is slightly more complex and depends
heavily on application code containing proper
calls to dbms_session for setting
context. Therefore, it is advisable to review
dbms_session later in this chapter.
Plus, dbms_rls is available only with
Oracle
Enterprise
Edition.
Dbms_rls is most often used to create a
Virtual Private Database (VPD, which allows
multiple users to access a single schema while
preventing them from seeing each others' data.
Think of it as schema segregation or
partitioning. A single ACCOUNTING schema,
tables, views and indexes can be created to hold
multiple companies data, and yet still guarantee
data privacy, meaning that no company can see
any other companies' data. This can also be done
at the application level for different classes
of users. The process involves the following
steps:
Create or setup the database environment
(schema, tables, views and such)
-
Create one or more
application contexts
-
Create a login trigger for application users
-
Create security policies to define dynamic
predicates
-
Apply, or associate, security policies with
the tables
Next to
be examined are the procedures that dbms_rls
offers, followed by a fairly simple example of
implementing a VPD within the pre-canned "SCOTT"
schema.
There are a few enumerated
constants that one must know to use this
package:
-- Policy Types
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;
Add_grouped_policy is a procedure that adds
a policy to the specified table, view, or
synonym and associates that policy with the
specified policy group. If no schema is defined,
then the current session's schema is assumed.
The statement type can be any combination of
INDEX, SELECT, INSERT, UPDATE, or DELETE.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLICY_GROUP
|
VARCHAR2
|
IN
|
SYS_DEFAULT
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
|
|
FUNCTION_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
POLICY_FUNCTION
|
VARCHAR2
|
IN
|
|
|
STATEMENT_TYPES
|
VARCHAR2
|
IN
|
NULL
|
|
UPDATE_CHECK
|
BOOLEAN
|
IN
|
FALSE
|
|
ENABLED
|
BOOLEAN
|
IN
|
TRUE
|
|
STATIC_POLICY
|
BOOLEAN
|
IN
|
FALSE
|
|
POLICY_TYPE
|
BINARY_INTEGER
|
IN
|
NULL
|
|
LONG_PREDICATE
|
BOOLEAN
|
IN
|
FALSE
|
|
SEC_RELEVANT_COLS
|
VARCHAR2
|
IN
|
NULL
|
|
SEC_RELEVANT_COLS_OPTS
|
BINARY_INTEGER
|
IN
|
NULL
|
Table 6.7:
Add_grouped_policy
Statement Types
Add_policy is a procedure that simply
attaches a policy to the specified table, view,
or synonym. If no schema is defined, then the
current session's schema is assumed. The
statement type can be any combination of INDEX,
SELECT, INSERT, UPDATE, or DELETE.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
|
|
FUNCTION_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
POLICY_FUNCTION
|
VARCHAR2
|
IN
|
|
|
STATEMENT_TYPES
|
VARCHAR2
|
IN
|
NULL
|
|
UPDATE_CHECK
|
BOOLEAN
|
IN
|
FALSE
|
|
ENABLED
|
BOOLEAN
|
IN
|
TRUE
|
|
STATIC_POLICY
|
BOOLEAN
|
IN
|
FALSE
|
|
POLICY_TYPE
|
BINARY_INTEGER
|
IN
|
NULL
|
|
LONG_PREDICATE
|
BOOLEAN
|
IN
|
FALSE
|
|
SEC_RELEVANT_COLS
|
VARCHAR2
|
IN
|
NULL
|
|
SEC_RELEVANT_COLS_OPTS
|
BINARY_INTEGER
|
IN
|
NULL
|
Table 6.8:
Add_policy
Statement Types
Add_policy_context is a procedure that
defines the application context that drives the
enforcement of policies such as the context that
determines which application is running. If no
schema is defined, then the current session's
schema is assumed.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
NAMESPACE
|
VARCHAR2
|
IN
|
|
|
ATTRIBUTE
|
VARCHAR2
|
IN
|
|
Table 6.9:
Add_policy_context
Statement Types
Create_policy_group
is a procedure that creates a policy group. That
group must be unique for each table or view.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLCIY_GROUP
|
VARCHAR2
|
IN
|
|
Table 6.10:
Create_policy_group
Statement Types
Delete_policy_group
is a procedure that drops a policy group. Note
that no policy can be in that policy group..
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLCIY_GROUP
|
VARCHAR2
|
IN
|
|
Table 6.11:
Delete_policy_group
Statement Types
Disable_grouped_policy is a procedure that
simply drops a policy associated with a policy
group.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLCIY_GROUP
|
VARCHAR2
|
IN
|
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
|
Table 6.12:
Disable_grouped_policy
Statement Types
|
|
|
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
|
|
|
