Fine Grained Access Control
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. |