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