Sunday, July 29, 2007

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

2 comments:

dghnfgj said...

Joy in warcraft leveling living comes wow lvl from having wow lvl fine emotions,wow power level trusting them,power leveling giving them power leveling the freedom of wrath of the lich king power leveling a bird in the open.wlk power leveling Joy in living can age of conan gold never be assumed as a pose,or put on from guildwars gold the outside as a mask. People who have this joy don not need maple story mesos to talk about it; they radiate it. wow gold They just live out their joy and let wow power leveling it splash its sunlight and glow into other lives as naturally as bird sings.

Viagra Online said...

Exactly the page that I was looking for as a Oracle beginner that I am. I will try your advices in order to achieve big performance with my equipment. Viagra Generic Viagra