Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle DBMS_RLS Package
Oracle Tips by Burleson Consulting

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

     


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.