Oracle for Beginner: Fine Grained Access (FGA)

SQL> conn / as sydba
SQL> alter system set Event="28131 trace name context forever" scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> conn / as sysdba
SQL> show parameter event;
SQL> create user test identified by oracle;
SQL> grant connect, resource, create any context, administer database trigger to test;
SQL> grant execute on dbms_rls to test;
SQL> conn test/oracle
SQL> create table manager(dep_id number(3) primary key, nama_manajer varchar(30));
SQL> create table karyawan(dep_id number(3), nama_kary varchar(30), primary key(dep_id, nama_kary) );
SQL> insert into manager values (10, 'BUDSUS');
SQL> insert into manager values (20, 'HR');
SQL> commit;
SQL> insert into karyawan values (10, 'ANI');
SQL> insert into karyawan values (10, 'WATI');
SQL> insert into karyawan values (20, 'ANTOK');
SQL> commit;
SQL> create table orders(id number(4) primary key, dep_id number(3), tgl date, karyawan varchar(30) default user);
SQL> insert into orders values (1, 10, '01-AUG-07', 'ANI');
SQL> insert into orders values (2, 10, '02-AUG-07', 'ANI');
SQL> insert into orders values (3, 10, '02-AUG-07', 'WATI');
SQL> insert into orders values (4, 20, '02-AUG-07', 'ANTOK');
SQL>

Here is the procedure that will be called when a user login to the database:

create or replace procedure set_testapp_role(
p_user varchar2 default sys_context('userenv', 'session_user')) is

--parameter berisi username,
--jika tidak diberikan, username akan diambil dari sys_context()
--yang mengembalikan user session saat ini

--v_ctx menyimpan nama application context.
v_ctx varchar2(16) := 'testapp_ctx';

v_is_manajer number;
v_dep_id number;
v_is_kary number;

begin
--set variabel application context "username" dengan user yang diberikan
dbms_session.set_context(v_ctx, 'username', p_user);

select count(*) into v_is_manajer from test.manajer where nama_manajer=p_user;
select count(*) into v_is_kary from test.karyawan where nama_kary=p_user;

--jika user adalah TEST,
-- maka beri role APP_OWNER.

if (p_user=sys_context('userenv','current_schema')) then
dbms_session.set_context(v_ctx,'rolename','APP_OWNER');
elsif (v_is_manajer=1) then
--jika user manajer, beri role APP_ADMIN dan ambil
--dep_id

select dep_id into v_dep_id from manajer where nama_manajer=p_user;

dbms_session.set_context(v_ctx,'rolename','APP_ADMIN');
dbms_session.set_context(v_ctx,'depid',v_dep_id);

elsif (v_is_kary=1) then
--jika user karyawan, beri role APP_USER
dbms_session.set_context(v_ctx,'rolename','APP_USER');
else
--jika user tidak berwewenang
dbms_session.set_context(v_ctx,'rolename','NOT_AUTHORIZED');
end if;
end;
/

We have to create a context to save some global variables :

create or replace context testapp_ctx using set_testapp_role;
/

We will call set_testapp_role procedure for each time a user login to database. To make that thing can run, we have to create a trigger that will be run for each time a login happened:

create or replace trigger test_logon_trigger after logon on database
begin
set_testapp_role;
end;
/

Here is a function that will return a condition string for a user session. It's mean, when a user is login to database and want to access ORDERS table, the system will make a restriction for each query from the active user:

create or replace function testapp_security_function (
p_schema varchar2, p_object varchar2) return varchar2 is
begin
if (sys_context('testapp_ctx','rolename')='APP_OWNER') then
--tidak ada kondisi yang diberkan
return '';

elsif (sys_context('testapp_ctx','rolename')='APP_ADMIN') then
--kondisi query dibatasi untuk data-data departemen dimana
--user sekarang sebagai manajernya (APP_ADMIN)
return 'dep_id=sys_context(''testapp_ctx'',''depid'')';

elsif ( sys_context('testapp_ctx','rolename')='APP_USER') then
--untuk role APP_USER
return 'karyawan=sys_context(''testapp_ctx'',''username'') and '||
'dep_id = (select dep_id from test.karyawan ' ||
'where nama_kary=sys_context(''testapp_ctx'',''username''))';

else
--untuk NOT_AUTHORIZED
return '1=2';
end if;
end;
/

Then, we add a new security policy to implement FGA :

declare
begin
DBMS_RLS.ADD_POLICY (
object_schema => 'TEST',
object_name => 'ORDERS', policy_name => 'TESTAPP_POLICY',
function_schema => 'TEST',
policy_function => 'TESTAPP_SECURITY_FUNCTION',
statement_types => 'SELECT,UPDATE,INSERT,DELETE',
update_check => TRUE,
enable => TRUE,
static_policy => FALSE);
end;
/

Let's try our FGA example:

SQL> conn / as sysdba
SQL> create user budsus identified by oracle;
SQL> grant connect to budsus;
SQL> create user ani identified by oracle;
SQL> grant connect to ani;
SQL> conn test/oracle
SQL> grant select, update, delete, insert on orders to budsus;
SQL> grant select, update, delete, insert on orders to ani;

SQL> conn ani/oracle
SQL> select * from test.orders;

ID DEP_ID TGL KARYAWAN
---------- ---------- --------- ------------------------------
1 10 01-AUG-07 ANI
2 10 02-AUG-07 ANI

SQL> insert into test.orders values (10,20,'03-AUG-07','ANI');
insert into test.orders values (10,20,'03-AUG-07','ANI')
*
ERROR at line 1:
ORA-28115: policy with check option violation
SQL> insert into test.orders values (10,10,'03-AUG-07','ANI');

1 row created.

SQL> conn budsus/oracle
Connected.
SQL> select * from test.orders;

ID DEP_ID TGL KARYAWAN
---------- ---------- --------- ------------------------------
1 10 01-AUG-07 ANI
2 10 02-AUG-07 ANI
3 10 02-AUG-07 WATI
10 10 03-AUG-07 ANI