7.3 DBMS_RLS: Implementing Fine-Grained Access Control

Team-Fly

---

 

	Oracle PL/SQL Programming Guide to Oracle 8 i Features By Steven Feuerstein
	Table of Contents
	Chapter 7.  New and Enhanced Built-in Packages in Oracle8i

---

7.3 DBMS_RLS: Implementing Fine-Grained Access Control

Fine-grained access control (FGAC) is a new feature in Oracle8 i that allows you to implement security policies with functions and then associate those security policies with tables or views. The database server enforces those policies automatically, no matter how the data is accessed. For lots more information on FGAC, see Chapter 8. I'll focus here on the new built-in package, DBMS_RLS.

7.3.1 Installing DBMS_RLS

The DBMS_RLS package should have been installed automatically with the rest of the built-in packages. If you are not able to execute the procedures in the package, you can install the package yourself. To do this, connect to the SYS schema and run the following files in the order specified:

\Oracle\Ora81\Rdbms\Admin\dbmsrlsa.sql

\Oracle\Ora81\Rdbms\Admin\prvtrlsa.plb

The directory shown here is the default for a Windows NT installation. Your Oracle 8.1 home directory may be different, but these files will always be found in the Rdbms\Admin subdirectory under the Oracle 8.1 home directory.

7.3.2 DBMS_RLS Programs

The DBMS_RLS package offers a set of procedures to administer your security policies. Fine-grained access control usually affects the rows a user can accesshence the name of the package, the Row-Level Security (RLS) administrative interface. Using this package, you can add, drop, enable, disable, and refresh the policies you create. Table 7.6 lists the programs in this package.

Table 7.6. DBMS_RLS Programs

Program	Description
ADD_POLICY procedure	Creates or registers a fine-grained access control policy for a table or view
DROP_POLICY procedure	Drops a fine-grained access control policy from a table or view
ENABLE_POLICY procedure	Enables or disables a fine-grained access control policy
REFRESH_POLICY procedure	Causes all the cached statements associated with the policy to be reparsed

7.3.3 Committing with DBMS_RLS

Each of the DBMS_RLS procedures causes the current transaction to commit before carrying out the specified operation. The procedures will also issue a commit at the end of their operations.

This commit processing does not occur if the DBMS_RLS action takes place within a DDL event trigger. In this case, the DBMS_RLS action becomes a part of the DDL transaction. You might, for example, place a trigger on the CREATE TABLE user event (another new Oracle8 i capability, described in Chapter 6). This trigger can then call DBMS_RLS.ADD_POLICY to add a policy on that table.

7.3.4 ADD_POLICY: Adding a Policy

Use the DBMS_RLS.ADD_POLICY procedure to add a policy for use in the FGAC architecture. Here is the header for this program:

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

The parameters for this procedure are listed in Table 7.7.

Table 7.7. DBMS_RLS.ADD_POLICY Parameters

Parameter	Description
object_schema	Schema containing the table or view. The default is the currently connected schema (that returned by USER).
object_name	Name of the table or view to which the policy is added.
policy_name	Name of the policy to be added. It must be unique for the same table or view. If not, you will get this error: ORA-28101: policy already exists
function_schema	Schema of the function that is used to implement the policy. The default is the currently connected schema (that returned by USER).
policy_function	Name of the function that generates a predicate for the policy. If the function is defined within a package, then you must specify the function in the form package.function , as in the following example: 'personnel_rules.by_department'
statement_types	Statement types to which the policy will apply. Those types can be any combination of SELECT, INSERT, UPDATE, and DELETE. The default is to apply to all of these types. This is a comma-delimited list. If you provide a list with the wrong structure, you will receive one of these compile-time errors: ORA-00911: invalid character ORA-28106: input value for argument #6 is not valid
update_check	Optional argument for INSERT or UPDATE statement types. The default is FALSE. Setting update_check to TRUE causes the server to check the policy against the value after the insert or update has been performed.
enable	Indicates if the policy is enabled when it is added. The default is TRUE. If you specify FALSE, then you must also call DBMS_RLS.ENABLE_POLICY after you have added the policy.

The following rules apply when adding a policy:

• The policy function that generates a dynamic predicate is called by the Oracle server. Your function must conform to the following header:

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

  Where object_schema is the schema owning the table or view and object_name is the table or view to which the policy applies. Your function does not necessarily have to use those arguments, but they must be included in the parameter list.

• The maximum length of the predicate that the policy function can return is 2000 bytes.

• The policy function must have a purity level of WNDS ( writes no database state), so that the function can be called within a SQL statement.

• If a SQL statement causes the generation of more than one dynamic predicate for the same object, these predicates are combined with an AND operator. In other words, all dynamic predicates must be satisfied.

• The definer rights model is used to resolve any references in the policy function. Any object lookups required are performed against the owner of the policy function, not the owner of the table or view on which the policy is based.

• If your function returns a NULL predicate, then the predicate is ignored. In other words, no filtering of rows takes place for the current user.

• In some cases, usually involving object types, Oracle requires an alias for the table name. In these cases, the name of the table or view itself must be used as the name of the alias.

• The policy function is not checked until runtime. The program you specify in the call to DBMS_RLS.ADD_POLICY does not need to exist or be compilable when the policy is added.

  Here is an example of adding a policy:

 BEGIN    DBMS_RLS.ADD_POLICY (       'SCOTT',       'patient',       'patient_privacy',       'SCOTT',       'nhc_pkg.person_predicate',       'SELECT,UPDATE,DELETE'); END; / 

7.3.5 ENABLE_POLICY: Enabling or Disabling a Policy

You can enable or disable a policy with the DBMS_RLS.ENABLE_POLICY procedure:

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

The parameters for this procedure are listed in Table 7.8.

Table 7.8. DBMS_RLS.ENABLE_POLICY Parameters

Parameter	Description
object_schema	Schema containing the table or view. The default is the currently connected schema (that returned by USER).
object_name	Name of the table or view for which the policy is enabled or disabled.
policy_name	Name of the policy to be enabled or disabled. It must be unique for the same table or view. If not, you will get this error: ORA-28101: policy already exists
enable	TRUE to enable the policy, FALSE to disable the policy.

7.3.6 DROP_POLICY: Dropping a Policy

The DBMS_RLS package also provides the interface to drop security policies with the DBMS_RLS.DROP_POLICY procedure:

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

Parameters have essentially the same meanings as those shown in Table 7.7.

The following procedure uses the DBMS_RLS package's DROP_POLICY procedure to drop all policies for a specific schema and database object:

 /* Filename on companion disk: droppol.sp */ CREATE OR REPLACE PROCEDURE drop_policy (    objname IN VARCHAR2,    polname IN VARCHAR2 := '%',    objschema IN VARCHAR2 := NULL)    AUTHID CURRENT_USER IS BEGIN    FOR rec IN (       SELECT object_owner,               object_name,               policy_name         FROM ALL_POLICIES        WHERE object_owner LIKE NVL (objschema, USER)          AND object_name LIKE objname          AND policy_name LIKE polname)    LOOP       DBMS_RLS.DROP_POLICY (          rec.object_owner, rec.object_name, rec.policy_name);    END LOOP; END; / 

In Oracle8 i Release 8.1.5, the behavior of the DBMS_RLS.DROP_POLICY procedure was erratic inside droppol.sp Sometimes it worked, but often it raised an exception along these lines:

 ORA-28106: input value for argument #2 is not valid 

If you pass hardcoded string literals to DBMS_RLS_DROP_POLICY, the procedure doesn't seem to have any difficulties.

7.3.7 REFRESH_POLICY: Refreshing a Policy

The DBMS_RLS.REFRESH_POLICY procedure causes all the cached SQL statements associated with the policy to be reparsed. This guarantees that the latest change to this policy will have an immediate effect after the procedure is executed. This procedure is needed because parsed SQL statements are cached in the System Global Area to improve performance. The header is as follows :

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

Parameters have essentially the same meanings as those shown in Table 7.7.

Every time you change the set of policies associated with a table or view, you should issue a refresh for that object. To ensure that this happens, you might consider building an encapsulation around DBMS_RLS so that a call to your ADD_POLICY procedure would automatically add the policy and then refresh as well. Your encapsulated add would then look like this:

 /* Filename on companion disk: my_rls.pkg */ CREATE OR REPLACE PACKAGE BODY my_rls IS    ...    PROCEDURE 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);    IS    BEGIN       DBMS_RLS.ADD_POLICY (          object_schema   ,          object_name     ,          policy_name     ,          function_schema ,          policy_function ,          statement_types ,          update_check    ,          enable);       IF enable        THEN          DBMS_RLS.REFRESH_POLICY (             object_schema,             object_name  ,             policy_name);       END IF;    END; END; / 

See Chapter 8 to explore in much more detail the features supported by DBMS_RLS.

---

Team-Fly

Top