Secure Application Role

http://www.oracle.com/technology/obe/obe10gdb/security/approles/approles.htm

1
connect system/oracle
drop role ots_role;
create role ots_role IDENTIFIED USING sec_roles;

2
connect oe/oe
grant select on oe.orders to ots_role;
grant select on oe.customers to ots_role;

connect system/oracle
grant ots_role to "KAREN.PARTNERS@OSRD.COM";
alter user "KAREN.PARTNERS@OSRD.COM" default role none;

grant ots_role to "LOUISE.DORAN@OSRD.COM";
alter user "LOUISE.DORAN@OSRD.COM" default role none;

3
connect system/oracleCREATE OR REPLACE procedure sec_roles authid current_user
as 
v_user          varchar2(50); 
v_manager_id    number :=1;  
begin   
v_user := (sys_context ('userenv', 'session_user'));   
select manager_id into v_manager_id from hr.employees where email=v_user;    
if v_manager_id = 100     then     
dbms_session.set_role('ots_role');     
else null;    
end if;   
exception   
when no_data_found then 
v_manager_id:=0;
end sec_roles;
/

4
connect system/oracle
GRANT EXECUTE ON sec_roles to "KAREN.PARTNERS@OSRD.COM";
GRANT EXECUTE ON sec_roles to "LOUISE.DORAN@OSRD.COM";

5
connect "KAREN.PARTNERS@OSRD.COM"/welcome1;
execute system.sec_roles;
select sales_rep_id, order_total from oe.orders order by order_total desc;

6
connect "LOUISE.DORAN@OSRD.COM"/welcome1;
execute system.sec_roles;
select sales_rep_id, order_total from oe.orders order by order_total desc;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License