A simple example of "Secure Application Role"
During the invesigation of the implementation of "Secure Application User" in our system, I noticed it turned out to be implemented using "Secure Application Role".
So I'd like to have a simple test ---
(1) Create two users --
SQL> create user test_a identified by a; User created. SQL> create user test_b identified by a; User created. SQL> grant connect, resource to test_a, test_b; Grant succeeded. SQL> grant create role to test_a; Grant succeeded.
(2) Create one table and two roles in test_a
SQL> conn test_a/a@orcl Connected. SQL> create role role_ro identified using test_a.pack_authentication; Role created. SQL> create role role_rw identified using test_a.pack_authentication; Role created. SQL> create table test(id int); Table created. SQL> grant select on test to role_ro; Grant succeeded. SQL> grant select, update, delete on test to role_rw; Grant succeeded. SQL> SQL> create or replace package pack_authentication authid current_user as 2 procedure grant_roles(p_token IN varchar2); 3 end pack_authentication; 4 / Package created. SQL> SQL> SQL> SQL> create or replace package body pack_authentication as 2 procedure grant_roles(p_token IN varchar2) 3 as 4 begin 5 if p_token = 'B' then 6 dbms_session.set_role('ROLE_RO'); 7 elsif p_token = 'A' then 8 dbms_session.set_role('ROLE_RW'); 9 else 10 execute immediate 'set role none'; 11 end if; 12 end; 13 end pack_authentication; 14 / Package body created. SQL> SQL> grant execute on pack_authentication to test_b; Grant succeeded.
(3) Test in test_b
SQL> conn test_b/a@orcl Connected. SQL> select * from test_a.test; select * from test_a.test * ERROR at line 1: ORA-00942: table or view does not exist SQL> exec test_a.pack_authentication.grant_roles('B'); PL/SQL procedure successfully completed. SQL> select * from session_roles; ROLE ------------------------------ ROLE_RO SQL> select * from test; select * from test * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from test_a.test; no rows selected SQL> delete from test_a.test; delete from test_a.test * ERROR at line 1: ORA-01031: insufficient privileges SQL> exec test_a.pack_authentication.grant_roles('A'); PL/SQL procedure successfully completed. SQL> delete from test_a.test; 0 rows deleted. SQL> rollback; Rollback complete. SQL> exec test_a.pack_authentication.grant_roles('C'); PL/SQL procedure successfully completed. SQL> select * from session_roles; no rows selected SQL> select * from test_a.test; select * from test_a.test * ERROR at line 1: ORA-00942: table or view does not exist
We can use "alter session set current_schema" to get rid of schema prefix used in test_b...
SQL> alter session set current_schema=test_a; Session altered. SQL> select * from test; select * from test * ERROR at line 1: ORA-00942: table or view does not exist SQL> exec test_a.pack_authentication.grant_roles('B'); PL/SQL procedure successfully completed. SQL> select * from test; no rows selected SQL> delete from test; delete from test * ERROR at line 1: ORA-01031: insufficient privileges SQL> exec test_a.pack_authentication.grant_roles('A'); PL/SQL procedure successfully completed. SQL> select * from session_roles; ROLE ------------------------------ ROLE_RW SQL> select * from test; no rows selected SQL> delete from test; 0 rows deleted. SQL> rollback; Rollback complete. SQL> exec test_a.pack_authentication.grant_roles('C'); PL/SQL procedure successfully completed. SQL> select * from test; select * from test * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from test_a.test; select * from test_a.test * ERROR at line 1: ORA-00942: table or view does not exist SQL>
Note:
The package used for secure application role should be created using invoker's right (authid current_user)
"
http://docs.oracle.com/cd/B14117_01/network.101/b10773/apdvntro.htm
The PL/SQL package DBMS_SESSION.SET_ROLE is functionally equivalent to the SET ROLE statement in SQL.
Roles are not supported in definer's rights procedures, so the DBMS_SESSION.SET_ROLE command cannot be called from them.
However, the DBMS_SESSION.SET_ROLE command can be called from the following:
Anonymous PL/SQL blocks
Invoker's rights stored procedures (except those invoked from within definer's rights procedures)
"
--------------------------------------
Regards,
FangwenYu