RLS In-Depth

The preceding example can help you get started, but there is much more to RLS than this. The security policies can be much more complex, and the policy functions typically return dramatically different strings based on user authorizations.

There are some similarities between RLS and some of the view examples you read about in Chapter 10. Row-level security is enforced by a PL/SQL function, and the role of the PL/SQL function is the same as it was when used in the view examples. The difference between RLS and views is how the PL/SQL is invoked.

The policy function’s job is to return a string (varchar2) that will serve as a predicate or a where clause to the original query. In effect, the original query is modified, the predicate string is attached, and the query is executed. For example, a simple query select * from EMP might be augmented by an RLS policy function that returns the predicate ename = USER. The effective SQL that is then executed will be select * from EMP where ename = USER. You’ll see a diagram of this process later in Figure 11-1.

image from book
Figure 11-1: Queries are modified transparently by the RLS policy.

The PL/SQL functions are registered to tables, views, or synonyms by invoking the DBMS_RLS.ADD_POLICY procedure. The DBMS_RLS package is not granted to everyone; administrators will require direct execute privileges on the package. The ADD_POLICY procedure requires, at minimum, the name of the object to which the policy will be applied, a name for the policy, and the name of a PL/SQL function that will implement the security policy.

The policies can be applied to SELECT, INSERT, UPDATE, DELETE, and INDEX statements. The index affects CREATE INDEX and ALTER INDEX DDL commands. Whenever a user directly or indirectly accesses a protected table, view, or synonym, the RLS engine is transparently invoked, the PL/SQL function registered will execute, and the SQL statement will be modified and executed.

Benefits

RLS is very flexible and very granular. By default, the policy applies to all DML statements. The ADD_POLICY procedure accepts a STATEMENT_TYPES parameter that allows the administrator to specify which DML operations the policy is to apply. This granularity also allows the database to apply separate policies based on the DML type. For example, the database can easily support a policy to allow all records for SELECT statements; an INSERT, UPDATE policy to restrict records to a user’s department on insert and update operations; and a DELETE policy that restricts DELETE operations to only the user’s record.

Multiple policies also can be applied to the same object: the database logically ANDs the policies together. That is, if there is one policy that returns ename = USER and another policy (on the same object for the same DML) that returns sal > 2000, the database will automatically add both policies, effectively generating where ename = USER and sal > 2000.

The security from VPD derives from the fact that the predicates are used to restrict records returned by the original query, regardless of how the query was issued or who issued the query. This record filtering provides consistent row-level security that is guaranteed to work irrespective of the applications interacting with the data. The entire process is transparent to the application originally issuing the query. One of the strongest arguments for VPD is that the security is tightly fixed to the data it protects—it’s consistent, centrally managed, and it can’t be bypassed.

To understand why this is desirable, look at an alternative security model in which the application implements the record filtering. A particular challenge arises when the same data is required by multiple applications. In this case, the security about the data has to be replicated to all the applications. Varying programming languages, COTS applications, and design models often make this an arduous job at best.

The database’s ability to support security for the data at both an object level and within the object (intra-object) are crucial for ensuring consistent and constant security. Programming languages are born and die within a few years. Applications change in functionality, design, and use even more frequently than that. A well-defined database schema will invariably outlive both. Therefore, a proper security model at the database is paramount to ensuring overall data security. By using features like VPD, the database implements the security policies and thus any application using the data will have the security policies automatically applied.

Setup

You now have the background to understand why VPD is a good tool, now you will see more examples of how to use it. In the next example, you’ll solve the challenge posed in the “Chapter 10: a user is allowed to see all records; to insert and update records only within their department; and to delete only their individual record. You could build this with three views, but your application code would have to switch between views based on the type of operation it wishes to perform, and you would have to write Instead-of triggers. VPD will allow you to enforce two policies. Thus, any currently written SQL doesn’t have to be altered as the security policy is created, altered, or deleted.

Setting the Application Context

For this example, the RLS policy will be applied to the PEOPLE table. To enforce the security just described, two policies will be needed: the first will manage the insert and update operations restricting records to the user’s department; the second will manage delete operations to ensure the user can only delete their own records.

Restricting insert and updates to the user’s department requires knowing what department the user belongs to. To make this as efficient as possible, the user’s department number will be stored in an application context. While this is being done for illustrative purposes, application contexts are not required to implement RLS. A lookup table is created to support the population of the context values. The application context setup will involve three steps: creating an application context for the department number, building the namespace manager program to populate the context with the appropriate value, and invoking the namespace manager automatically when the user logs on.

The following code sample for setting up the application context is similar to an example given in Chapter 9. More details about why the code is written as it is are given in that chapter. The code is shown here again as a convenience.

The security manager will set the context values. As such, select privileges are required on the PEOPLE table. The security manager then creates the lookup table.

scott@KNOX10g> -- Recreate the people table to include all rows from EMP   scott@KNOX10g> DROP TABLE people; Table dropped. scott@KNOX10g> CREATE TABLE people     2  AS SELECT ename username, job, sal salary, deptno     3    FROM emp; Table created. scott@KNOX10g> GRANT SELECT ON people TO sec_mgr; Grant succeeded. scott@KNOX10g> CONN sec_mgr/oracle10g  Connected. sec_mgr@KNOX10g> -- Create table to populate application context values   sec_mgr@KNOX10g> CREATE TABLE lookup_dept     2  AS SELECT username, deptno FROM scott.people; Table created.

The namespace manager program will set the context based on the user’s department number as stored in the LOOKUP_DEPT table:

sec_mgr@KNOX10g> -- Create namespace for application context   sec_mgr@KNOX10g> CREATE CONTEXT people_ctx USING sec_mgr.people_ctx_mgr; Context created. sec_mgr@KNOX10g> -- Create namespace manager program for modifying context. sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE people_ctx_mgr     2  AS      3    PROCEDURE set_deptno;   4    PROCEDURE clear_deptno;   5  END;   6  /     Package created. sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY people_ctx_mgr     2  AS     3  -----------------------------------------     4    PROCEDURE set_deptno     5    AS      6      l_deptno  NUMBER;   7    BEGIN     8      SELECT deptno     9        INTO l_deptno   10        FROM lookup_dept    11       WHERE username =  12               SYS_CONTEXT ('userenv',  13                            'session_user');  14      DBMS_SESSION.set_context    15                         (namespace    => 'people_ctx',  16                          ATTRIBUTE    => 'deptno',  17                          VALUE        => l_deptno);  18    END set_deptno;  19  -----------------------------------------    20    PROCEDURE clear_deptno    21    AS    22    BEGIN    23      DBMS_SESSION.clear_context   24                        (namespace    => 'people_ctx',  25                         ATTRIBUTE    => 'deptno');  26    END clear_deptno;  27  -----------------------------------------    28  END people_ctx_mgr;  29  /    Package body created. sec_mgr@KNOX10g> -- do NOT have to grant execute on namespace manager

To populate the context value automatically, a logon trigger will be used:

sec_mgr@KNOX10g> -- do NOT have to grant execute on namespace manager   sec_mgr@KNOX10g> CREATE OR REPLACE TRIGGER set_user_deptno     2    AFTER LOGON ON DATABASE     3  BEGIN     4    sec_mgr.people_ctx_mgr.set_deptno;   5  EXCEPTION     6    WHEN NO_DATA_FOUND     7    THEN     8      -- If user is not in table,    9      -- a no_data_found is raised    10      -- If exception is not handled, then users not in table    11      -- will be unable to log on    12      NULL;  13  END;  14  /     Trigger created. 

Test the context by logging in as the SCOTT user:

sec_mgr@KNOX10g> CONN scott/tiger   Connected. scott@KNOX10g> COL deptno format a8   scott@KNOX10g> SELECT SYS_CONTEXT ('people_ctx',   2                      'deptno') deptno     3    FROM DUAL; DEPTNO   --------   20

Creating the Policy Function

All RLS policy functions are passed two parameters by the database when they are invoked. The first is the name of the schema that owns the object for which the RLS policy is being invoked. The second is the name of the object for which the RLS policy is being invoked. These two parameters are helpful because a single policy function (PL/SQL program) may be applied to multiple objects in multiple schemas. The parameters can then be used to determine specifically which object the policy is being invoked.

A good practice is to create your policy functions with the parameter values defaulted to null. This will allows you to test the function directly:

sec_mgr@KNOX10g> -- Create policy function. sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION dept_only (   2    p_schema  IN  VARCHAR2 DEFAULT NULL,   3    p_object  IN  VARCHAR2 DEFAULT NULL)   4    RETURN VARCHAR2   5  AS   6  BEGIN   7    RETURN 'deptno = sys_context(''people_ctx'',''deptno'')';   8  END;   9  / Function created.

Notice that this policy function is created in the security manager’s schema. This is important because access to the policy function should be guarded.

Note 

Your users should not have execute privileges on the policy function, nor should they be able to alter or drop the function.

This last requirement generally implies the function is separated from the data schema to which it will be applied. In some cases, you can see the predicate string that will be used by displaying the return value of the function:

sec_mgr@KNOX10g> -- Test policy function. sec_mgr@KNOX10g> col predicate format a50 sec_mgr@KNOX10g> SELECT dept_only predicate FROM DUAL; PREDICATE   --------------------------------------------------   deptno = sys_context('people_ctx','deptno')

This policy function is quite simple; in fact, it could just as easily have been implemented in a view. You are using VPD instead of views because this policy is only relevant to inserts and updates.

Applying the Insert/Update Policy

The security requirement implemented by the preceding code is supposed to be in effect on all inserts and updates. After running the following statement, the database will call the DEPT_ONLY function in the SEC_MGR schema whenever someone inserts or updates the SCOTT.PEOPLE table:

sec_mgr@KNOX10g> -- apply RLS policy to table sec_mgr@KNOX10g> BEGIN   2    DBMS_RLS.add_policy   3               (object_schema      => 'SCOTT',   4                object_name        => 'PEOPLE',   5                policy_name        => 'PEOPLE_IU',   6                function_schema    => 'SEC_MGR',   7                policy_function    => 'Dept_Only',   8                statement_types    => 'INSERT,UPDATE',   9                update_check       => TRUE);  10  END;  11  / PL/SQL procedure successfully completed.

The combination of object schema, object name, and policy name has to be unique. A good practice for the policy name is to choose something that describes the intended use of the policy. PEOPLE_IU is my way of indicating a policy on the PEOPLE table for insert and update operations. Likewise, choosing a function name that is descriptive about what the function results are is also good practice. The DEPT_ONLY implies the function restricts records at the department level.

As a result of executing the ADD_POLICY procedure, the following SQL

update people   set ename = '<NEW_VALUE>'

will be effectively transformed into

update people   set ename = '<NEW_VALUE>'   where deptno = sys_context('people_ctx','deptno')

Figure 11-1 depicts how this occurs.

Testing VPD Protected Insert and Updates

A quick test validates that the policy is working. For the test, simply update a record within your department (Department 20 for SCOTT), and update a record outside your department. The following query returns a record from each so you can properly issue your update statements:

sec_mgr@KNOX10g> CONN scott/tiger Connected. scott@KNOX10g> SELECT username, deptno   2    FROM people   3   WHERE username < 'C'; USERNAME       DEPTNO ---------- ---------- ALLEN              30 BLAKE              30 ADAMS              20

Testing the updates should show that updates to records in Department 20 will be successful. Updates to records outside Department 20 should not succeed:

scott@KNOX10g> -- Update to department 20 user scott@KNOX10g> -- This should succeed. scott@KNOX10g> UPDATE people   2     SET username = 'GRIZZLY'   3   WHERE username = 'ADAMS'; 1 row updated. scott@KNOX10g> -- Update to department 30 user scott@KNOX10g> -- This should not update anything. scott@KNOX10g> UPDATE people   2     SET username = 'BOZO'   3   WHERE username = 'BLAKE'; 0 rows updated.

Note that zero errors are thrown for the second update. The modified SQL simply excludes all possible records. The update statement was effectively augmented and resolved to the following:

UPDATE people    SET ename  = 'Bozo'  WHERE ename  = 'BLAKE'    AND deptno = SYS_CONTEXT ('people_ctx', 'deptno')

The SYS_CONTEXT function will resolve to the number 20 for the user SCOTT. Because there is no BLAKE record with a deptno = 20, zero records are updated.

To test the inserts, try to insert a new record for Department 20, then insert a record outside Department 20:

scott@KNOX10g> -- This insert should work as deptno = 20 scott@KNOX10g> INSERT INTO people   2              (username, job, salary, deptno)   3       VALUES ('KNOX', 'Clerk', '3000', 20); 1 row created. scott@KNOX10g> -- This insert should not work scott@KNOX10g> INSERT INTO people   2              (username, job, salary, deptno)   3       VALUES ('ELLISON', 'CEO','90000', 30); INSERT INTO people             * ERROR at line 1: ORA-28115: policy with check option violation

Note the insert operation fails with an error. This operation fails because you set the UPDATE_CHECK=TRUE in the ADD_POLICY procedure call. The default value for UPDATE_CHECK is FALSE. If you had not specified TRUE, the insert would have succeeded. This behavior is consistent with that of a view with a check option.

The Delete RLS Policy Example

The delete policy says that the user can only delete their record. This function can be implemented as follows:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION user_only (   2    p_schema  IN  VARCHAR2 DEFAULT NULL,   3    p_object  IN  VARCHAR2 DEFAULT NULL)   4    RETURN VARCHAR2   5  AS   6  BEGIN   7    RETURN 'username = sys_context(''userenv'',''session_user'')';   8  END;   9  / Function created.

To apply the delete policy, specify DELETE statements in the ADD_POLICY procedure and provide the USER_ONLY function for the POLICY_FUNCTION:

sec_mgr@KNOX10g> -- apply delete policy to table sec_mgr@KNOX10g> BEGIN   2    DBMS_RLS.add_policy   3               (object_schema      => 'SCOTT',   4                object_name        => 'PEOPLE',   5                policy_name        => 'People_Del',   6                function_schema    => 'SEC_MGR',   7                policy_function    => 'user_only',   8                statement_types    => 'DELETE');   9  END;  10  / PL/SQL procedure successfully completed.

Testing the delete policy, you’ll notice that a delete statement that attempts to delete all records actually results in only one actual deletion—the user’s very own record:

scott@KNOX10g> DELETE FROM people; 1 row deleted. scott@KNOX10g> SELECT * FROM people   2   WHERE username = 'SCOTT'; no rows selected

Again, the database has transparently augmented the SQL statement. The policy function restricts delete operations to the user’s record. Therefore, the most users can ever delete is their record.

The RLS Layer of Security

If you want to prevent users from accessing all records (including their own), first use the object privileges to enforce this to ensure the user doesn’t have the privileges on the table.

To strengthen the security, you can also use RLS. RLS provides an additional layer of security and will even prevent someone with the DBA role or an ANY system privilege—SELECT ANY TABLE—from accessing your protected table(s). Note this doesn’t imply the table is protected from a DBA with malicious intentions. The DBA can alter the policy function, drop the table, disable the RLS policy, and so on. The shear fact of being a DBA doesn’t override the RLS capability. In the previous example, a DBA who can read records from your table still can’t delete any records because the delete policy restricts access to the records matching USERNAME with the schema name:

scott@KNOX10g> CONN system/manager Connected. system@KNOX10g> -- user has SELECT ANY TABLE so he can see all records   system@KNOX10g> SELECT COUNT(*) FROM scott.people;   COUNT(*) ----------           14     system@KNOX10g> -- user cannot delete records as RLS prevents this   system@KNOX10g> DELETE FROM scott.people; 0 rows deleted.

Returning Zero Records

The most effective way to prevent records from being accessed within an RLS policy function is to return a string that consists of something that can never happen. An easy example is the string “1=0”. Be aware—returning a null or a zero length string has the opposite effect, and all rows are returned.

Caution 

Returning null from the policy function allows all records to be accessed.

You might find it useful to create a function that eliminates all records. You can then use it anytime you want to quickly lock down a table:

sec_mgr@KNOX10g> -- Create a function to be used with RLS   sec_mgr@KNOX10g> -- that will always eliminate all records. sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION no_records (   2    p_schema  IN  VARCHAR2 DEFAULT NULL,   3    p_object  IN  VARCHAR2 DEFAULT NULL)   4    RETURN VARCHAR2     5  AS     6  BEGIN     7    RETURN '1=0';   8  END;   9  /    Function created. 

There are several ways to create a READ ONLY table or view; here you can see another. Adding the NO_RECORDS function to an RLS policy for inserts, updates, and deletes effectively makes the table READ ONLY.

scott@KNOX10g> CREATE TABLE people_ro AS SELECT * FROM emp; Table created. scott@KNOX10g> conn sec_mgr/oracle10g  Connected. sec_mgr@KNOX10g> BEGIN    2    DBMS_RLS.add_policy     3        (object_schema      => 'SCOTT',   4         object_name        => 'PEOPLE_RO',   5         policy_name        => 'PEOPLE_RO_IUD',   6         function_schema    => 'SEC_MGR',   7         policy_function    => 'No_Records',   8         statement_types    => 'INSERT,UPDATE,DELETE',   9         update_check       => TRUE);  10  END;  11  /     PL/SQL procedure successfully completed.

As indicated by the tests here, the RLS policy helps to ensure the table maintains its READ ONLY status:

sec_mgr@KNOX10G> scott@KNOX10g> -- User can read all records   scott@KNOX10g> SELECT COUNT (*) FROM people_ro;   COUNT(*) ----------           14     scott@KNOX10g> -- Cannot update any records   scott@KNOX10g> UPDATE people_ro     2     SET ename = NULL; 0 rows updated. scott@KNOX10g> -- Cannot delete records   scott@KNOX10g> DELETE FROM people_ro; 0 rows deleted. scott@KNOX10g> -- Cannot insert new records   scott@KNOX10g> INSERT INTO people_ro (ename) VALUES ('KNOX'); INSERT INTO people_ro (ename) VALUES ('KNOX')             * ERROR at line 1: ORA-28115: policy with check option violation

RLS Exemption

While the RLS provides wonderful security, it can be problematic when doing database administration tasks such as performing data backups. As you have seen, even the DBAs and the data owner cannot bypass the RLS policy. If you perform an export as the data owner or another administrator while an RLS policy was enabled, you may very well end up with a dataless backup file.

For this reason (and a few others), there is an EXEMPT ACCESS POLICY privilege. This privilege allows the grantee to be exempted from all RLS functions. An administrator who has to perform data backups can use this privilege to ensure backup ability for all the data in the tables. The following example illustrates how this privilege is granted and the affect it has on enabled RLS policies:

system@KNOX10g> CONN system/manager Connected. system@KNOX10g> -- Show system is affected by RLS policy. system@KNOX10g> -- No records should be deleted. system@KNOX10g> DELETE FROM scott.people_ro; 0 rows deleted. system@KNOX10g> -- grant privilege to bypass RLS policies system@KNOX10g> GRANT EXEMPT ACCESS POLICY TO SYSTEM; Grant succeeded. system@KNOX10g> -- Show system is no longer affected by RLS policy. system@KNOX10g> -- All records should be deleted. system@KNOX10g> DELETE FROM scott.people_ro; 14 rows deleted. system@KNOX10g> ROLLBACK ;

EXEMPT ACCESS POLICY is a very powerful privilege. The privilege isn’t specific to a schema or policy; it applies to all schemas and all policies. Care should be given to ensure that the privilege is well guarded. By default, users with SYSDBA privileges are exempt from RLS policies. You can determine who has been granted the EXEMPT ACCESS POLICY privilege by checking system privileges, as shown here:

sec_mgr@KNOX10g> -- Show users that are exempt from RLS policies   sec_mgr@KNOX10g> SELECT grantee   2    FROM dba_sys_privs   3   WHERE PRIVILEGE = 'EXEMPT ACCESS POLICY'; GRANTEE   ------------------------------   SYSTEM 

Note 

The EXEMPT ACCESS POLICY system privilege allows privileged users to bypass RLS policies. This may be necessary for database backup and recovery, but it can also be a security risk. Guard this privilege tightly.

Audit Exempt Access Policy

You can’t prevent privileged users from abusing their privileges, you can only catch them doing it. Auditing is an effective way to ensure the RLS exemption privilege is not being abused. The following shows how to enable auditing for this privilege. Once enabled, you should test to ensure that everything is being audited as you think. For this to occur, the system privilege has to be exercised. This too is shown:

sec_mgr@KNOX10g> -- Audit the exempt policy system privilege sec_mgr@KNOX10g> AUDIT EXEMPT ACCESS POLICY BY ACCESS; Audit succeeded. sec_mgr@KNOX10g> -- Test audit by exercising the system privilege sec_mgr@KNOX10g> CONN system/manager Connected. system@KNOX10g> DELETE FROM scott.people_ro; 14 rows deleted. system@KNOX10g> -- Rollback will not erase the audit record system@KNOX10g> ROLLBACK ; Rollback complete. system@KNOX10g> CONN sec_mgr/oracle10g Connected. sec_mgr@KNOX10g> -- show audited operation sec_mgr@KNOX10g> BEGIN   2    FOR rec IN (SELECT *   3                  FROM dba_audit_trail)   4    LOOP   5      DBMS_OUTPUT.put_line ('-------------------------');   6      DBMS_OUTPUT.put_line ('Who:   ' || rec.username);   7      DBMS_OUTPUT.put_line (   'What:  '   8                            || rec.action_name   9                            || ' on '  10                            || rec.owner  11                            || '.'  12                            || rec.obj_name);  13      DBMS_OUTPUT.put_line (   'When:  '  14                            || TO_CHAR  15                                    (rec.TIMESTAMP,  16                                     'MM/DD HH24:MI'));  17      DBMS_OUTPUT.put_line ('How:   "' || rec.sql_text || '"');  18      DBMS_OUTPUT.put_line ('Using: ' || rec.priv_used);  19    END LOOP;  20  END;  21  / ------------------------- Who:   SYSTEM What:  DELETE on SCOTT.PEOPLE_RO When:  04/04 14:22 How:   "DELETE FROM scott.people_ro" Using: DELETE ANY TABLE ------------------------- Who:   SYSTEM What:  DELETE on SCOTT.PEOPLE_RO When:  04/04 14:22 How:   "DELETE FROM scott.people_ro" Using: EXEMPT ACCESS POLICY PL/SQL procedure successfully completed.

The audit trail shows two records because the SYSTEM user exercised two system privileges when the delete statement was issued. The first privilege, DELETE ANY TABLE, allowed access to the table. The second privilege, EXEMPT ACCESS POLICY, allowed access within the table.

Debugging RLS Policies

When an RLS policy fails, the users can no longer access the protected objects. I have found that the chances of this happening are in direct proportion to the complexity of the policy function. When this does happen, having a sound method for troubleshooting the problem is critical to minimizing the down time associated with the error.

There are generally two reasons for a policy error. First, the policy function is invalid and will not recompile or execute. For example, an error will occur if the policy function queries a table that no longer exists. A policy error will also occur if the policy function doesn’t exist. This is usually because the function has been dropped or the function has been incorrectly registered in the ADD_POLICY procedure.

The second reason for policy error occurs when the policy function returns a string that, when added to the original SQL, produces an invalid SQL statement. There are many possible reasons why the function can fail. The first step to debugging is to ensure that the function is working.

Broken Policy Functions

Errors caused by an invalid policy function can easily make the VPD transparency disappear. To show this, the following example creates a simple table with a simple policy function. The policy function is dependent on the table.

scott@KNOX10g> -- Create a dependency. This table will scott@KNOX10g> -- be called by the RLS policy function. scott@KNOX10g> CREATE TABLE t AS SELECT * FROM DUAL; Table created. scott@KNOX10g> -- Create policy function. Function scott@KNOX10g> -- is dependent on table T. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (   2    p_schema  IN  VARCHAR2 DEFAULT NULL,   3    p_object  IN  VARCHAR2 DEFAULT NULL)   4    RETURN VARCHAR2   5  AS   6    l_total_recs  NUMBER;   7  BEGIN   8    SELECT COUNT (*)   9      INTO l_total_recs  10      FROM t;  11    RETURN '1 <= ' || l_total_recs;  12  END;  13  / Function created.

The point here is to create and then break a dependency. The policy function is dependent on the table T. Add the policy to your table and check to see that no errors occur on access:

scott@KNOX10g> CONN sec_mgr/oracle10g  Connected. sec_mgr@KNOX10g> -- Add RLS policy to EMP table; sec_mgr@KNOX10g> BEGIN     2    DBMS_RLS.add_policy     3                (object_schema      => 'SCOTT',   4                 object_name        => 'EMP',   5                 policy_name        => 'debug',   6                 function_schema    => 'SCOTT',   7                 policy_function    => 'pred_function');   8  END;   9  /     PL/SQL procedure successfully completed. sec_mgr@KNOX10g> CONN scott/tiger  Connected. scott@KNOX10g> -- Everything initially works fine   scott@KNOX10g> SELECT COUNT(*) FROM emp;   COUNT(*) ----------           14 

Dropping the table T will invalidate the policy function. The first indication that something is wrong may come when you try to query the EMP table:

scott@KNOX10g> -- This drop breaks the policy function   scott@KNOX10g> DROP TABLE t; Table dropped. scott@KNOX10g> -- Policy function is invalid and will not recompile; scott@KNOX10g> SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM emp                       * ERROR at line 1: ORA-28110: policy function or package SCOTT.PRED_FUNCTION has error

Recovering from this is very easy with Oracle Database 10g. You can simply use the Flashback Drop to restore the table T. Once this is done, access to the EMP table is also restored because the policy function will be able to successfully execute:

scott@KNOX10g> -- Recover table   scott@KNOX10g> FLASHBACK TABLE t TO BEFORE DROP; Flashback complete. scott@KNOX10g> SELECT COUNT(*) FROM emp;   COUNT(*) ----------           14

Handling Policy Function Exceptions

When the query on EMP was executed after the T table was dropped and before it was recovered, the database threw an error indicating precisely why the query failed. This is very helpful, but it may not be desirable because two potentially sensitive things were revealed. First, it indicated that there is an RLS policy on the table; second, it gave the name of the policy function guarding the table.

You may want to consider suppressing the policy function exceptions to prevent this information from being displayed to the users. The best approach to this requires the use of dynamic SQL that hides the function’s database object dependencies. The function still has to return a value. Returning null will allow the user access to all the records. Failing secure means that you should return zero records if an exception is thrown. This example shows how to fail secure:

scott@KNOX10g> -- User dynamic SQL and exception handling scott@KNOX10g> -- to mask policy function errors scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (   2    p_schema  IN  VARCHAR2 DEFAULT NULL,   3    p_object  IN  VARCHAR2 DEFAULT NULL)   4    RETURN VARCHAR2   5  AS   6    l_total_recs  NUMBER;   7  BEGIN   8    -- Dynamic SQL hides the dependency on table T   9    EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM t'  10                 INTO l_total_recs;  11    RETURN '1 <= ' || l_total_recs;  12  EXCEPTION  13    WHEN OTHERS  14    THEN  15      -- Fail Secure: remove all rows  16      RETURN '1=0';  17  END;  18  / Function created. scott@KNOX10g> SELECT COUNT(*) FROM emp;   COUNT(*) ----------         14 scott@KNOX10g> -- This drop breaks the policy function scott@KNOX10g> DROP TABLE t; Table dropped. scott@KNOX10g> -- Policy fails secure. No records are displayed or scott@KNOX10g> -- exception messages given to user. scott@KNOX10g> SELECT COUNT(*) FROM emp;   COUNT(*) ----------          0

The downside to this approach is that it will be more difficult to debug the policy yourself. The solution to this is to comment out or remove the exception handling code, but you should only do this while debugging.

Invalid SQL

A second possible reason a policy may fail is because the SQL returned by the policy function creates an invalid SQL statement. Recall the policy’s return value is added to the original SQL. The database will try to parse and execute this final SQL statement. If the SQL is invalid, the policy will throw an error.

Unfortunately, the error message does not indicate how the SQL is malformed. (It’s not even clear what the problem is.) For complex policies, or objects that have multiple policies, this can create some frustration.

For this example, the policy function has been modified to support a policy that restricts DML to just the user’s record. Unfortunately, the SQL column NAME should really be ENAME. Because the return value is a string, this error does not manifest itself until runtime:

scott@KNOX10g> -- Create policy function that returns scott@KNOX10g> -- records just for the user. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (   2    p_schema  IN  VARCHAR2 DEFAULT NULL,   3    p_object  IN  VARCHAR2 DEFAULT NULL)   4    RETURN VARCHAR2   5  AS   6  BEGIN   7    -- Restricting records by mapping ENAME   8    -- to authenticated database user's name   9    RETURN 'name = user';  10  END;  11  / Function created. scott@KNOX10g> SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM emp                      * ERROR at line 1: ORA-28113: policy predicate has error

An ORA-28113 is the indication that the SQL is bad. It’s impossible for the database to know why this is true. Your policy function could have derived the predicate string in an infinite number of ways. The only thing you know is that the resulting SQL is invalid. To debug it, first try to inspect the value returned by the policy function:

scott@KNOX10g> SELECT pred_function "Policy Predicate" FROM DUAL; Policy Predicate -------------------- name = user

Note you can query the DBA_POLICIES view (as shown later in the “Viewing the Original SQL and Predicate” section) to see the PREDICATE but only if the policy doesn’t throw an error.

At this point, your best alternative may be to access a trace file. To do this easily, create a simple view (based on another Tom Kyte contribution from asktom.oracle.com) that can determine the trace filename the database will be using:

sec_mgr@KNOX10g> -- Create view to display session's current trace file   sec_mgr@KNOX10g> CREATE OR REPLACE VIEW get_trace_filename     2  AS     3    SELECT    c.VALUE     4           || decode(instr(c.value,'\'),0,'/','\')   5           || INSTANCE   6           || '_ora_'   7           || LTRIM (TO_CHAR (a.spid, 'fm99999'))   8           || '.trc' filename     9      FROM v$process a,  10           v$session b,  11           v$parameter c,  12           v$thread c    13     WHERE a.addr = b.paddr   14       AND b.audsid = USERENV ('sessionid')  15       AND c.NAME = 'user_dump_dest'; View created. sec_mgr@KNOX10g> GRANT SELECT ON get_trace_filename TO PUBLIC; Grant succeeded. sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM get_trace_filename     2                    FOR get_trace_filename; Synonym created.

Connect as a user and reproduce the query that caused the policy error. This is done here as SCOTT:

scott@KNOX10g> -- Enable tracing   scott@KNOX10g> ALTER SESSION SET EVENTS     2    '10730 trace name context forever, level 12'; Session altered. scott@KNOX10g> -- Reproduce error. scott@KNOX10g> -- Issue query; note only selecting ENAME   scott@KNOX10g> SELECT ename FROM emp; SELECT ename FROM emp                    * ERROR at line 1: ORA-28113: policy predicate has error       scott@KNOX10g> -- view trace file   scott@KNOX10g> COL filename format a50   scott@KNOX10g> SELECT * FROM get_trace_filename; FILENAME   --------------------------------------------------   C:\ORACLE\ADMIN\KNOX10G\UDUMP\knox10g_ora_3740.trc     scott@KNOX10g> -- fix the policy function   scott@KNOX10g> -- Create policy function that returns scott@KNOX10g> -- records just for the user. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (   2    p_schema  IN  VARCHAR2 DEFAULT NULL,   3    p_object  IN  VARCHAR2 DEFAULT NULL)   4    RETURN VARCHAR2      5  AS     6  BEGIN     7    -- Restricting records by mapping ENAME     8    -- to authenticated database user's name      9    RETURN 'ename = user';  10  END;  11  /     Function created. scott@KNOX10g> -- Re-query to verify policy works   scott@KNOX10g> SELECT ename FROM emp; ENAME   ----------   SCOTT

Viewing the contents of the trace file with the name returned from GET_TRACE_FILENAME provides helpful information. The relevant information from the trace file just generated from the previous session is displayed here:

-------------------------------------------------------------   Logon user     : SCOTT   Table/View     : SCOTT.EMP   Policy name    : DEBUG   Policy function: SCOTT.PRED_FUNCTION   RLS view : SELECT  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM    SCOTT"."EMP"  "EMP" WHERE (name = user)  *** 2004-04-05 13:32:02.874   -------------------------------------------------------------   Error information for ORA-28113: Logon user     : SCOTT   Table/View     : SCOTT.EMP   Policy name    : DEBUG   Policy function: SCOTT.PRED_FUNCTION   RLS predicate  : name = user ORA-00904: "NAME": invalid identifier -------------------------------------------------------------   Logon user     : SCOTT   Table/View     : SCOTT.EMP   Policy name    : DEBUG   Policy function: SCOTT.PRED_FUNCTION   RLS view : SELECT  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM    SCOTT"."EMP"  "EMP" WHERE (ename = user) 

This file is very useful, especially the section that displays information on the ORA-28113 error. The RLS predicate record (bolded) pinpoints the erroneous SQL.

Be careful, there is a very misleading fact printed, too.

Caution 

The RLS view is not the actual SQL the database executes.

Many incorrectly believe it is. This should be obvious because the original SQL statement asked only for the ENAME column; the RLS view would have you believe all columns were selected.

Viewing the Original SQL and Predicate

Another view you may find helpful in inspecting your VPD implementations is the V$VPD_POLICY view. This will tell you what policies have been successfully applied to your SQL statements. The PREDICATE column will actually show the SQL string that was appended to the query. You can join this record with V$SQL, which has the original SQL statement as follows:

sec_mgr@KNOX10g> -- Nothing in pool - a fresh database sec_mgr@KNOX10g> SELECT * FROM v$vpd_policy; no rows selected sec_mgr@KNOX10g> CONN scott/tiger Connected. scott@KNOX10g> -- this will seed the v$vpd_policy view scott@KNOX10g> SELECT COUNT (*) FROM emp;   COUNT(*) ----------          1 scott@KNOX10g> CONN sec_mgr/oracle10g Connected. sec_mgr@KNOX10g> -- show VPD Policy results sec_mgr@KNOX10g> COL object_owner format a12 sec_mgr@KNOX10g> COL object_name format a12 sec_mgr@KNOX10g> COL policy format a6 sec_mgr@KNOX10g> COL sql_fulltext format a26 sec_mgr@KNOX10g> COL predicate format a12 sec_mgr@KNOX10g> SELECT object_owner,   2         object_name,   3         policy,   4         sql_fulltext,   5         predicate   6    FROM v$vpd_policy p, v$sql s   7   WHERE p.sql_id = s.sql_id   8     AND predicate IS NOT NULL; OBJECT_OWNER OBJECT_NAME  POLICY SQL_FULLTEXT               PREDICATE ------------ ------------ ------ -------------------------- ------------ SCOTT        EMP          DEBUG  SELECT COUNT (*) FROM emp  ename = user 

This is a good way of inspecting the policies, but there is one important caveat: no records are recorded if the policy throws an error. For policy errors, you will have to use tracing, and even then the real SQL is not given.

Null Application Context Values and Recursive Lookups

There are two areas that consistently trap newcomers to VPD. The problem occurs when an RLS policy function references an application context that is null. The reason the application context is null is because the namespace manager is trying to set the context on the table to which the RLS policy is being applied.

This example begins by illustrating the working context. Recall the user’s department number is set by a logon trigger. The trigger calls the namespace manager, which sets the value by querying the LOOKUP_DEPT table:

sec_mgr@KNOX10g> CONN scott/tiger Connected. scott@KNOX10g> -- Context value is set on logon scott@KNOX10g> COL deptno format a6 scott@KNOX10g> SELECT SYS_CONTEXT ('people_ctx',   2                      'deptno') deptno   3    FROM DUAL; DEPTNO ------ 20 scott@KNOX10g> CONN sec_mgr/oracle10g Connected. sec_mgr@KNOX10g> -- Current view on table sec_mgr@KNOX10g> SELECT * FROM lookup_dept   2   WHERE username = 'SCOTT'; USERNAME       DEPTNO ---------- ---------- SCOTT              20

Normally, the lookup table would not exist. It was created specifically to bypass this problem. Nevertheless, the mistake comes next when an RLS policy is added to the LOOKUP_DEPT table. To help with this example, an update policy will be added to the EMP table using the same policy function. Most often the context will be set from the same table the RLS policy will be enforced from which, as you will see, can’t be done:

sec_mgr@KNOX10g> -- Add Dept level policy to lookup table. sec_mgr@KNOX10g> -- Policy references people_ctx context. sec_mgr@KNOX10g> BEGIN     2    DBMS_RLS.add_policy     3                 (object_schema      => 'SEC_MGR',   4                  object_name        => 'lookup_dept',   5                  policy_name        => 'lookup_dept_sel',   6                  policy_function    => 'dept_only',   7                  statement_types    => 'SELECT');   8  END;   9  /     PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- Add Dept level policy to EMP table. sec_mgr@KNOX10g> -- Policy references people_ctx context. sec_mgr@KNOX10g> BEGIN     2    DBMS_RLS.add_policy     3                 (object_schema      => 'SCOTT',   4                  object_name        => 'EMP',   5                  policy_name        => 'EMP_UPD',   6                  policy_function    => 'dept_only',   7                  statement_types    => 'UPDATE');   8  END;   9  /     PL/SQL procedure successfully completed.

Querying from the lookup table shows the expected results. If you are the security administrator, you may feel nothing is wrong:

sec_mgr@KNOX10g> -- rows are no longer visible    sec_mgr@KNOX10g> -- since SEC_MGR has not context   sec_mgr@KNOX10g> SELECT * FROM lookup_dept; no rows selected

Connecting as the SCOTT user, you can see the application context wasn’t set. The ramifications of this are that any RLS policies that are based on this context will not behave properly:

sec_mgr@KNOX10g> CONN scott/tiger   Connected. scott@KNOX10g> -- Context no longer exists   scott@KNOX10g> COL deptno format A6   scott@KNOX10g> SELECT SYS_CONTEXT ('people_ctx',   2                      'deptno') deptno     3    FROM DUAL; DEPTNO   ------       scott@KNOX10g> -- Any RLS policies that used the context value will not    cott@KNOX10g> -- behave correctly. scott@KNOX10g> -- This update would normally succeed for all dept 20 records   scott@KNOX10g> UPDATE emp     2     SET ename = ename; 0 rows updated. 

Recall the policy function being added restricts the user’s records to the department in which they work. The department number is stored in the context that is populated from the same table being restricted by the RLS policy. This recursive condition prevents the application context from being populated.

The solution to this is to create a different object to be used for populating the application context. The object can be a table or a view. A view is the best because there is no data synchronization that needs to occur. If you create a view, the application context lookup should occur on the table, and the RLS policy should be applied to the view. If you did this the other way, it wouldn’t work because the RLS on the base table would still be in effect when the table was accessed by way of the view.

Partitioned Fine-Grained Access Control

Oracle introduced partitioned fine-grained access control in Oracle9i Database, which allows you to group together multiple policies and then enable and disable them easily. This is a useful concept when multiple policies exist on the same objects but have conflicting return values.

Recall that Oracle logically ANDs all RLS policies. That is, if there are two policies, A and B, for the same object, then the resulting SQL will be the output of A and B. If policy A returns “deptno = 20” and policy B returns “deptno = 10”, then the resulting query will be “deptno = 20 and deptno = 10”. Because the deptno is either 10 or 20, this query results in zero rows.

Partitioned fine-grained access control allows you to define which RLS policies you want to enable for the user session. This is done by setting the policy name into an application context. The database will then reference this policy name by looking at the value stored in the user’s application context and applying that RLS policy.

Normally, I would include an example that illustrates some nuance of the technology. Fortunately, the Oracle Database Security Guide 10g has an excellent example of partitioned fine-grained access control. Including a different example here would be redundant.



Effective Oracle Database 10g Security by Design
Effective Oracle Database 10g Security by Design
ISBN: 0072231300
EAN: 2147483647
Year: 2003
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net