Introduction
In this article, I’ll demonstrate simple implementation of
Fine Grained Access Control (FGAC) in Oracle 11g.
FGAC is based on dynamically (at execution time) modified
SQL statements (primarily DMLs). FGAC feature allows us to define a function
(user defined PL/SQL) that returns a predicate as a string.
When this function is applied as policy to table (or views /
synonyms), Oracle at execution time will append (as AND) to the query issued by
users.
When a query is issued on a table on which FGAC policy is
applied (and enabled)…
·
If the predicate evaluates to TRUE (at run time)
the data will be returned
·
If the predicate evaluates to FALSE the data
will NOT be returned
HOWEVER
·
If the function returns NULL, no predicate will
be applied and the result of the query will be returned.
I’ll use an example, outlined below, to demonstrate this
feature.
On account of simplicity, I have used a very simple
function. The predicate returned from the function can be much more complex.Sample Requirement
1. There is a table with following definition and data.
- a. Table T is owned by user HR
- b. Table T has 10 rows, with c1 value from 1 through 10.
-
a. User A access see only rows which have EVEN c1 value.
- b. User B access see only rows which have ODD c1 value.
- c. All other users (including C) can access ALL data in the table.
Required Setup
The following scripts can be used for setup.Environment:
SQL>
select * from v$version where rownum < 4;
BANNER
--------------------------------------------------------------------------------
Oracle
Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL
Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
SQL>
show user;
USER is
"HR"
SQL>
Create & load table:
SQL> drop
table T;
drop table
T
*
ERROR at
line 1:
ORA-00942:
table or view does not exist
SQL>
create table hr.T (c1 int, c2 varchar2(20));
Table
created.
SQL>
insert into T select level c, 'AAAA' c2 from dual connect by level < 11;
10 rows
created.
SQL>
commit;
Commit
complete.
Create users as SYSDBA:
SQL>
connect / as sysdba;
Connected.
SQL>
create user a identified by a;
User
created.
SQL>
create user b identified by b;
User
created.
SQL>
create user c identified by c;
User
created.
SQL>
grant create session to a;
Grant
succeeded.
SQL>
grant create session to b;
Grant
succeeded.
SQL>
grant create session to c;
Grant
succeeded.
SQL>
Grant SELECT privilege on T to users (as HR):
SQL>
connect hr/hr;
Connected.
SQL>
grant select on T to a;
Grant
succeeded.
SQL>
grant select on T to b;
Grant
succeeded.
SQL>
grant select on T to c;
Grant
succeeded.
SQL>
With this setup completed, all three users can access all
the rows in table T.
SQL>
connect a/a
Connected.
SQL>
select count(*) from hr.t;
COUNT(*)
----------
10
SQL>
connect b/b;
Connected.
SQL>
select count(*) from hr.t;
COUNT(*)
----------
10
SQL>
connect c/c;
Connected.
SQL>
select count(*) from hr.t;
COUNT(*)
----------
10
SQL>
SQL>
CREATE OR REPLACE FUNCTION hr.t_policy
(oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
case
when user = 'A' then
RETURN ' mod(hr.T.c1,2) = 1 ';
when user = 'B' then
RETURN ' mod(hr.T.c1,2) = 0 ';
else
RETURN null;
end case;
END;
/
Function
created.
SQL>
Apply policy to table T:
Note: HR user did
not have right privilege to execute DBMS_RLS package, so we got error PLS-00201. Granting this privilege as shown may not
be a great idea.
SQL>exec
DBMS_RLS.ADD_POLICY
(object_schema=>'HR',
object_name=>'T',policy_name=>'tp',
function_schema=>'HR',
policy_function=>'T_POLICY');
BEGIN
DBMS_RLS.ADD_POLICY
(object_schema=>'HR',
object_name=>'T',policy_name=>'tp',
function_schema=>'HR',
policy_function=>'T_POLICY');
END;
*
ERROR at
line 1:
ORA-06550:
line 1, column 7:
PLS-00201:
identifier 'DBMS_RLS' must be declared
ORA-06550:
line 1, column 7:
PL/SQL:
Statement ignored
SQL>
connect / as sysdba;
Connected.
SQL>
grant execute on dbms_rls to hr;
Grant
succeeded.
SQL>
exec DBMS_RLS.ADD_POLICY
(object_schema=>'HR',
object_name=>'T',
policy_name=>'tp',
function_schema=>'HR',
policy_function=>'T_POLICY');
PL/SQL
procedure successfully completed.
SQL>
Verify the policy:
SQL>
select object_owner, policy_name, function from all_policies;
OBJECT_OWNER POLICY_NAME FUNCTION
------------------------------
------------------------- ----------------
HR TP T_POLICY
SQL>
Verify the
Solution
The following section show the result as per the
requirement.
SQL>
connect a/a;
Connected.
SQL> /
C1 C2
----------
--------------------
1 AAAA
3 AAAA
5 AAAA
7 AAAA
9 AAAA
SQL>
connect b/b;
Connected.
SQL> /
C1 C2
----------
--------------------
1 AAAA
3 AAAA
5 AAAA
7 AAAA
9 AAAA
SQL>
connect c/c;
Connected.
SQL> /
C1 C2
----------
--------------------
1 AAAA
2 AAAA
3 AAAA
4 AAAA
5 AAAA
6 AAAA
7 AAAA
8 AAAA
9 AAAA
10 AAAA
10 rows
selected.
SQL>
SQL>
connect hr/hr;
Connected.
SQL>exec
dbms_rls.drop_policy(
object_schema=>'HR',
object_name=>'T',
policy_name=>'tp');
PL/SQL
procedure successfully completed.
SQL>
With RLS policy not in effect now, all users can see all the rows from table T.
Row Level Security (RLS) can be enforced in production
database with minimal changes to application code.
Till next time Bye!
Cleanup of DEMO
objects
SQL>
connect / as sysdba;
Connected.
SQL>
drop user a cascade;
User
dropped.
SQL>
drop user b cascade;
User
dropped.
SQL>
drop user c cascade;
User
dropped.
SQL>
drop table hr.t;
Table
dropped.
SQL>
drop function hr.t_policy;
Function
dropped.
SQL>
References
FGAC Documentation
http://docs.oracle.com/cd/B12037_01/network.101/b10773/apdvcntx.htm#1007183
No comments:
Post a Comment