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 databasebegin 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 isbegin 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 :
declarebeginDBMS_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
