#

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

> TIP: 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 access -- hence 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 Oracle8i capability, described in Chapter 6, New Trigger Features in Oracle8i). 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;
>     /

> WARNING: In Oracle8i 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.

7.2 DBMS_TRACE: Providing a PL/SQL Trace Facility |  | 7.4 UTL_COLL: Using Collection Locators

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.