欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

A simple example of "Secure Application Role"

程序员文章站 2022-04-01 16:11:47
...

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/[email protected]
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/[email protected]
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)

"

 

 

转载于:https://www.cnblogs.com/fangwenyu/archive/2012/05/16/2504635.html