Fine Grained Access Control

Fine Grained Access Control (FGAC)

Fine Grained Access Control (FGAC)

FGAC is a mechanism for deploying a transparent security policy on tables, which Oracle will use to "rewrite" the submitted query and add your security policy predicate information. It is a great mechanism for implementing security and not having to rewrite / modify any applications written against the database.

Note, this feature is available from 8.1.5 and onwards.

Application Contexts

One of the major needs for Oracle developers is the need for maintaining state. Prior to 8i, this could only be done by the use of PL/SQL packages (so called package (or session) variables), or relational tables. The use of PL/SQL packages for holding transient data (global values etc.) is great when solely in a PL/SQL context (or when directly referencing them in a cursor, in which case they promote the use of bind variables), i.e.

SQL> CREATE OR REPLACE PACKAGE test_pack
  2  AS
  3    g_test   VARCHAR2(10);
  4
  5    FUNCTION get_g_test RETURN VARCHAR2;
  6  END test_pack;
  7  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY test_pack
  2  AS
  3    FUNCTION get_g_test RETURN VARCHAR2
  4    IS
  5    BEGIN
  6      RETURN g_test;
  7    END get_g_test;
  8  END test_pack;
  9  /

Package body created.

SQL> BEGIN
  2    test_pack.g_test := 'ABC';
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT test_pack.get_g_test
  2  FROM dual;

GET_G_TEST
--------------------------------------------------------------------------------
ABC

1 row selected.

*IMPORTANT*
Package variables cannot be directly referenced in SQL (which is the reason for the FUNCTION declaration in the package to get the value). In PL/SQL, the variable can be referenced directly, i.e.

SQL> SELECT test_pack.g_test
  2  FROM dual;
SELECT test_pack.g_test
       *
ERROR at line 1:
ORA-06553: PLS-221: 'G_TEST' is not a procedure or is undefined

SQL> DECLARE
  2    l_test   VARCHAR2(10);
  3  BEGIN
  4    SELECT test_pack.g_test
  5    INTO l_test
  6    FROM dual;
  7    dbms_output.put_line(l_test);
  8  END;
  9  /
ABC

PL/SQL procedure successfully completed.

In situations where a purely SQL approach is required, it can be desirable to eliminate the context switch between SQL and PL/SQL, and therefore if there was a mechanism for storing transient data purely in a SQL context, this would be ideal. Application Contexts fulfil this requirement :

SQL> CREATE OR REPLACE PROCEDURE p_context ( p_username IN VARCHAR2 DEFAULT NULL )
  2  AS
  3  BEGIN
  4    dbms_session.set_context('TEST_CONTEXT', 'USER_NAME', p_username);
  5  END p_context;
  6  /

Procedure created.

SQL> CREATE OR REPLACE CONTEXT test_context USING p_context
  2  /

Context created.

Because the use of contexts is part of Fine Grained Access Control (FGAC), the value of a context can only be set using a procedure (this can, and SHOULD, be a package procedure). The value of a variable within a context (in this case, the USER_NAME "variable" within the TEST_CONTEXT context) is obtained by using the SYS_CONTEXT built-in. Typically, therefore, the application (or PL/SQL construct) will set the value of the context variable, using in this case pucd_context, and then issue the SQL statement with the reference to sys_context, i.e.

SQL> BEGIN
  2    p_context('ABC');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT sys_context('TEST_CONTEXT', 'USER_NAME')
  2  FROM dual;

SYS_CONTEXT('TEST_CONTEXT','USER_NAME')
--------------------------------------------------------------------------------
ABC

1 row selected.

SYS_CONTEXT can also be used to obtain session information from the built-in context, USERENV, i.e.

SQL> SELECT
  2    SYS_CONTEXT('USERENV','TERMINAL') terminal,
  3    SYS_CONTEXT('USERENV','LANGUAGE') language,
  4    SYS_CONTEXT('USERENV','SESSIONID') sessionid,
  5    SYS_CONTEXT('USERENV','INSTANCE') instance,
  6    SYS_CONTEXT('USERENV','ENTRYID') entryid,
  7    SYS_CONTEXT('USERENV','ISDBA') isdba,
  8    SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
  9    SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
 10    SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
 11    SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
 12    SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
 13    SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
 14    SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
 15    SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
 16    SYS_CONTEXT('USERENV','SESSION_USER') session_user,
 17    SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
 18    SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
 19    SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
 20    SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
 21    SYS_CONTEXT('USERENV','DB_NAME') db_name,
 22    SYS_CONTEXT('USERENV','HOST') host,
 23    SYS_CONTEXT('USERENV','OS_USER') os_user,
 24    SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
 25    SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
 26    SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
 27    SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
 28    SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
 29    SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
 30    SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data,
 31    SYS_CONTEXT('USERENV','CURRENT_SQL') current_sql,
 32    SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') client_identifier,
 33    SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') global_context_memory
 34  FROM dual
 35  /

Global Application Contexts

Global Application Contexts (GACs) are designed to be used in similar situations for normal contexts, but where the connection (and hence the session) is not maintained. The application maintains a record of it's own session id and sets it at every connection. This is great for web applications. Typically, the "session id" will be encrypted (using DBMS_OBFUSCATION_TOOLKIT) and then stored as a cookie in the browser. Note, the information stored in a GAC are stored in the SGA.

They are defined in the same manner as "normal" contexts, but with the extra ACCESSED GLOBALLY clause, i.e.

SQL> CREATE OR REPLACE CONTEXT app_ctx USING my_pkg
  2  ACCESSED GLOBALLY
  3  /

Context created.

SQL> CREATE OR REPLACE PACKAGE my_pkg
  2  AS
  3    PROCEDURE set_session_id( p_session_id IN NUMBER );
  4
  5    PROCEDURE set_ctx( p_name IN VARCHAR2,
  6                       p_value  IN VARCHAR2 );
  7
  8    PROCEDURE close_session( p_session_id IN NUMBER );
  9  END my_pkg;
 10  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY my_pkg
  2  AS
  3    g_session_id NUMBER;
  4
  5    PROCEDURE set_session_id( p_session_id IN NUMBER )
  6    IS
  7    BEGIN
  8      g_session_id := p_session_id;
  9      dbms_session.set_identifier( p_session_id );
 10    END;
 11
 12    PROCEDURE set_ctx( p_name IN VARCHAR2,
 13                       p_value IN VARCHAR2 )
 14    AS
 15    BEGIN
 16      dbms_session.set_context( 'app_ctx', p_name, p_value, USER, g_session_id );
 17    END;
 18
 19    PROCEDURE close_session( p_session_id IN NUMBER )
 20    IS
 21    BEGIN
 22      dbms_session.clear_context('app_ctx', p_session_id);
 23    END;
 24  END my_pkg;
 25  /

Package body created.

So, first thing we do is set our "session id".

SQL> exec my_pkg.set_session_id( 1234 );

PL/SQL procedure successfully completed.

Now, set our context variable (in this case Var1) to the value of "Val1"

SQL> exec my_pkg.set_ctx( 'Var1', 'Val1' );

PL/SQL procedure successfully completed.

SQL> SELECT SYS_CONTEXT( 'app_ctx', 'var1' ) var1
  2  FROM dual
  3  /

VAR1
-------------------------------------------------------------------------------------------
Val1

1 row selected.

Now, we'll disconnect and reconnect to check the value state is maintained.

SQL> disconnect
SQL> connect ...
Enter password: *******
Connected.

Again, set the "session id" and select the value back.

SQL> exec my_pkg.set_session_id( 1234 );

PL/SQL procedure successfully completed.

SQL> SELECT SYS_CONTEXT( 'app_ctx', 'var1' ) var1
  2  FROM dual
  3  /

VAR1
-------------------------------------------------------------------------------------------
Val1

1 row selected.

In an environment where "session ids" are reused, it may be prudent to clear down the values in the GACs. This can be done by a DBMS_JOB which loops through all the distinct client_identifier values in GLOBAL_CONTEXT and runs the close_session procedure in my_pkg (i.e. dbms_session.set/clear_identifier), or maybe a seperate table of client_identifiers and an expire_time column with a DBMS_JOB running expiring all contexts which need it (i.e. a "timeout" mechanism).

*IMPORTANT*
At 9.2.0.3.0, there is a bug with dbms_session.clear_identifier NOT removing the context, i.e.

SQL> exec my_pkg.set_session_id(10);

PL/SQL procedure successfully completed.

SQL> exec my_pkg.set_ctx('XYZ', 123);

PL/SQL procedure successfully completed.

/* Prove the context value is there */

SQL> SELECT namespace, attribute, value
  2  FROM v$globalcontext
  3  /

NAMESPACE                      ATTRIBUTE                      VALUE
------------------------------ ------------------------------ ------------------------------
APP_CTX                        XYZ                            123

Okay, now we'll try and close the session (note, close_session here does a set_identifier / clear_identifier)

SQL> exec my_pkg.close_session(10);

PL/SQL procedure successfully completed.

But, unfortunately, this doesn't clear the values from the SGA, i.e. the context is still there

SQL> SELECT namespace, attribute, value
  2  FROM v$globalcontext
  3  /

NAMESPACE                      ATTRIBUTE                      VALUE
------------------------------ ------------------------------ ------------------------------
APP_CTX                        XYZ                            123

This is the reason why my_pkg.close_session SHOULD do a clear_context, rather than a set/clear_identifier, as it does in the above example.

Table Policies

Table policies allow the system administrator to define transparent rules for row-level security on tables to particular users. What it means is that the developer does not need to be concerned with what data a user can see, policies can be setup to modify any queries issued by the system appending any business security rules. This is a boon for system administrators who can modify the security policy for a particular table without modifying the application.

A function needs to be created which returns a valid component of the WHERE clause which will be appended to any SELECT from the table (or NULL for no security), i.e.

SQL> CREATE OR REPLACE FUNCTION f_policy( p_schema IN VARCHAR2,
  2                                       p_object IN VARCHAR2 )
  3    RETURN VARCHAR2
  4  AS
  5  BEGIN
  6    IF user = 'X' THEN
  7      RETURN NULL;
  8    ELSE
  9      RETURN 'username_column = user';
 10    END IF;
 11  END f_policy;
 12  /

Function created.

Once the function is created, the function can be applied to a particular policy and assigned to an object (such as a table).

BEGIN
  dbms_rls.add_policy
    ( object_schema => 'schema',
      object_name => 'table_name_or_view_name',
      policy_name => 'policy_name',
      function_schema => 'schema',
      policy_function => 'f_policy',
      statement_types => 'select' );
END;

Note, the same function (assuming the RETURNed predicate component is valid) can be applied to many tables. See very important issue below regarding table-driven predicates.

In this situation, if anyone other than user X issues a

SELECT * FROM table_name_or_view_name

then the query becomes

SELECT * FROM ( SELECT * FROM table_name_or_view_name WHERE username_column = user )

*IMPORTANT*
The function must have two parameters, p_schema and p_object.

*VERY IMPORTANT*
You HAVE to be aware of PL/SQL cursor caching issues when dealing with table policies. Basically, if a table policy can return different predicates within a given session, then these should be avoided at all cost.

PL/SQL will cache cursors for us, but this caching occurs at the parse phase of the query, effectively the first time that the DBMS_RLS policy has been applied. Subsequent calls can return the same predicate as before (even though the policy function would return a different predicate) because of this caching.

The way around this is to ensure that your policy function always returns the same predicate format within a given session. A good way of doing this is to employ application contexts, setting them in the policy function, and referencing SYS_CONTEXT(..) within the predicate, instead of the value itself. This ensures that DBMS_RLS works in a predictable way, while still taking advantage of PL/SQL cursor caching.

The above policy is fine from this, since the value of user, in the IF statement, will not change for the lifetime of the session. Imagine though a policy which does something like this :

CREATE OR REPLACE FUNCTION f_policy( p_schema IN VARCHAR2,
                                     p_object IN VARCHAR2 )
  RETURN VARCHAR2
AS
  l_role  users.role%TYPE;
BEGIN
  SELECT role
    INTO l_role
    FROM users
   WHERE username = user;

  IF l_role = 'MANAGER' THEN
    RETURN NULL;
  ELSE
    RETURN 'username_column = user';
  END IF;
END f_policy;

On the face of it, very similar to the above policy, but what happens if the users role changes mid-session? In this case, the predicate is no longer constant per session, and we could fall foul of PL/SQL cursor caching issues. We could always log out, of course, this only applies per session, but this is not always ideal, but of course does depend on the business / application.

There are a few solutions to this :

Use DBMS_RLS.REFRESH_POLICY when data in the tables change. This causes flushing of the cached cursors for that policy.
At 8.1.7 and above, any changes to application contexts results in session cursor flushing

But, the BEST solution is to code the policy such that the predicate format does NOT change (in this situation, embedding the role derivation SQL in the predicate would seem prudent).

*IMPORTANT*
If the same table is referenced multiple times within a given SQL statement, i.e.

SQL> SELECT *
  2  FROM   t a, t b
  3  WHERE  a.a = b.a;

Then, any policy on table "t" gets applied n times (i.e. n - number of times table is referenced).

How to "bypass" FGAC

Using SYS or any user connected "as sysdba" will effectively bypass RLS.

In 9i, you can grant the EXTREMELY powerful privilege EXEMPT ACCESS POLICY to any DBA, giving the same bypass ability.

An interesting "use" when EXPorting

EXPorting a table with a security policy defined on it, gives you the warning :

EXP-00079: Data in table "..." is protected. Conventional path may only be exporting partial table.

which leads to an interesting useful "use" of this feature. Say you wanted to export a schema with lots of tables, however, there was one really big audit trail table you didn't care to get the data for. Export doesn't help you much here, there is no "everything BUT this table" option.

So, just use FGAC to say:

BEGIN
   IF ( user = 'export user' )
   THEN
     RETURN 1=0;
   ELSE
     RETURN NULL;
   END IF;
END;

and export. No data for that table would be exported for the 'export user'!

Monitoring FGAC components

Monitoring Application Contexts

The following views can be used to monitor both global and session contexts :

SESSION_CONTEXT	Shows the context / name / value of defined context entries.
GLOBAL_CONTEXT	Similar to SESSION_CONTEXT, but for global contexts, with the inclusion of user / namespace
V$CONTEXT	Identical to SESSION_CONTEXT
V$GLOBALCONTEXT	Identical to GLOBAL_CONTEXT
[ALL \| DBA]_CONTEXT	Provides a list of all contexts, associated packages, session or global, and owner

Monitoring Table Policies

The following views can be used to monitor table policies :

[USER | ALL | DBA]_POLICIES

Shows the policy name / packages / functions / owners etc. of defined policies.