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

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

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


       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




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


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


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


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:

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


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:

   object_schema IN VARCHAR2 := NULL,
   object_name   IN VARCHAR2,
   policy_name   IN VARCHAR2);


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:

   object_schema IN VARCHAR2 := NULL,
   object_name   IN VARCHAR2 := NULL,
   policy_name   IN VARCHAR2 := NULL);


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:

   object_schema IN VARCHAR2 := NULL,
   object_name   IN VARCHAR2,
   policy_name   IN VARCHAR2,
   enable        IN BOOLEAN);


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.


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 at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational