Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 7.2 DBMS_TRACE: Providing a PL/SQL Trace FacilityChapter 7
New and Enhanced Built-in Packages in Oracle8i
Next: 7.4 UTL_COLL: Using Collection Locators
 

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:

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.


Previous: 7.2 DBMS_TRACE: Providing a PL/SQL Trace FacilityOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 7.4 UTL_COLL: Using Collection Locators
7.2 DBMS_TRACE: Providing a PL/SQL Trace FacilityBook Index7.4 UTL_COLL: Using Collection Locators

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference