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)
"
