Oracle PL/SQL Programming: Guide to Oracle8i Features

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

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

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

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

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

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.