7.3 DBMS_RLS: Implementing Fine-Grained Access ControlFine-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_RLSThe 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:
7.3.2 DBMS_RLS ProgramsThe 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.
7.3.3 Committing with DBMS_RLSEach 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, 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 PolicyUse 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 .
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 PolicyYou 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 .
7.3.6 DROP_POLICY: Dropping a PolicyThe 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 PolicyThe 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. Copyright (c) 2000 O'Reilly & Associates. All rights reserved. | ||||||||||||||||||||||||||||||||||||||||
|