Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Concepts - Policy Usage

Oracle Tips by Burleson Consulting

Policy Usage

Policy usage is controlled internally by the Oracle system and adheres to the following usage guidelines:

1. SYS user is not restricted by any security policy.

2. The policy functions which generate dynamic predicates are called by the server. The following is the required structure for the function:

FUNCTION policy_function (object_schema IN VARCHAR2, object_name VARCHAR2)
             RETURN VARCHAR2

Where:

       object_schema is the schema owning the table of view.
       object_name is the name of table of view that the policy will apply.

3. The maximum length of the predicate that the policy function can return is 2,000 bytes.

4. The policy functions must have the purity level of WNDS (write no database state).

5. Dynamic predicates generated out of different policies for the same object have the combined effect of a conjunction (ANDed) of all the predicates.

6. The security check and object lookup are performed against the owner of the policy function for objects in the subqueries of the dynamic predicates.

7. If the function returns a zero length predicate, then it is interpreted as no restriction being applied to the current user for the  policy.

8. When table alias is required (e.g., parent object is a type table) in the predicate, the name of the table or view itself must be  used as the name of the alias. The server constructs the transient view as something like "select c1, c2, ... from tab where <predicate>".

9. The checking of the validity of the function is done at runtime for ease of installation and other dependency issues import/export.

DBMS_RLS Package

The entire concept of row level security is based on the use of policies stored in the database. The only way to store policies in the database is to use the DBMS_RLS package.

The DBMS_RLS procedures cause current DML transactions, if any, to commit before the operation. However, the procedures do not cause a commit first if they are inside a DDL event trigger. With DDL transactions, the DBMS_RLS procedures are part of the DDL transaction.

For example, you may create a trigger for CREATE TABLE. Inside the trigger, you may add a column through ALTER TABLE, and you can add a policy through DBMS_RLS. All these operations are in the same transaction as CREATE TABLE, even though each one is a DDL statement. The CREATE TABLE succeeds only if the trigger is completed successfully.

The DBMS_RLS package has the procedures shown in Table 1.

Table 1     Procedure in DBMS_RLS Package

Procedure

Purpose

ADD_POLICY

Creates a fine-grained access control policy to a table or view. 

DROP_POLICY

Drops a fine-grained access control policy from a table or view. 

REFRESH_POLICY

Causes all the cached statements associated with the policy to be re-parsed. 

ENABLE_POLICY

Enables or disables a fine-grained access control policy.

The syntax for calling the DBMS_RLS procedures are shown in the next sections.

Syntax for the ADD_POLICY Procedure:

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);

Where:

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?

Syntax for the DROP_POLICY Procedure:

DBMS_RLS.DROP_POLICY (
   object_schema IN VARCHAR2 := NULL,
   object_name   IN VARCHAR2,
   policy_name   IN VARCHAR2);

Where:

object_schema - Schema containing the table or view (logon user if NULL). 
object_name - Name of table or view. 
policy_name - Name of policy to be dropped from the table or view.

Syntax for the REFRESH_POLICY Procedure:

DBMS_RLS.REFRESH_POLICY (
   object_schema IN VARCHAR2 := NULL,
   object_name   IN VARCHAR2 := NULL,
   policy_name   IN VARCHAR2 := NULL);

Where:

object_schema - Schema containing the table or view. 
object_name - Name of table or view that the policy is associated with. 
policy_name - Name of policy to be refreshed.

Syntax for the ENABLE_POLICY Procedure:

DBMS_RLS.ENABLE_POLICY (
   object_schema IN VARCHAR2 := NULL,
   object_name   IN VARCHAR2,
   policy_name   IN VARCHAR2,
   enable        IN BOOLEAN);

Where:

object_schema - Schema containing the table or view (logon user if NULL). 
object_name - Name of table or view that the policy is associated with. 
policy_name - Name of policy to be enabled or disabled. 
Enable - TRUE to enable the policy, FALSE to disable the policy. 

Through the use of the above procedures DBAs and developers can easily manage policies.

Summary

Oracle has given DBAs and developers a powerful new tool to manage row level security. This new tool is a combination of contexts, triggers and packages and a new package named DBMS_RLS through which security policies are implemented.

Through the proper use of policies, contexts, packages and database level triggers row level security can be easily integrated in Oracle8i applications.

(The above lesson was excerpted from the book: "Oracle8i Administration and Management", Michael R. Ault, John Wiley and Sons Publishers).

 

 


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.

 


 

 
??
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.