role privileges, plsql and invokers rights

Some observations on role privileges, plsql and invokers rights
===============================================================
Oracle 9.2.0.4

As tuser1, create table t as select * from dba_data_files; to test privileges accessing this table.

sys@orcl92> create user tuser2 identified by tuser2 default tablespace users temporary tablespace temp;

User created.

sys@orcl92> grant connect, resource to tuser2;

Grant succeeded.

sys@orcl92> @conn tuser2/tuser2
Connected.

tuser2@orcl92> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

tuser2@orcl92> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TUSER2                         CONNECT                        NO  YES NO
TUSER2                         RESOURCE                       NO  YES NO

tuser2 cannot select from tuser1.t:

tuser2@orcl92> select count(*) from tuser1.t;
select count(*) from tuser1.t
                            *
ERROR at line 1:
ORA-00942: table or view does not exist


Now create a package-protected secure application role as tuser1:

tuser1@orcl92> !cat sar-cr.sql
@conn tuser1/tuser1

drop role sar1;

create role sar1 identified using tuser1.saradmin;

grant select on tuser1.t to sar1;

create or replace package saradmin
authid current_user
as
procedure letmein;
end;
/

create or replace package body saradmin
as
procedure letmein
is
  begin
    dbms_session.set_role('sar1');
  end;
end;
/

tuser1@orcl92> @sar-cr
Connected.

Role dropped.


Role created.


Grant succeeded.


Package created.


Package body created.


Now grant the role sar1 to tuser2:
tuser1@orcl92> grant sar1 to tuser2;

Grant succeeded.

####  At this stage, tuser2 gets the role as a default role, meaning it is automatically set upon login.  So logging off and loging in again gives the privilege WITHOUT EXECUTING THE PACKAGE:

tuser2@orcl92> select count(*) from tuser1.t;
select count(*) from tuser1.t
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

tuser2@orcl92> @conn tuser1/tuser1
Connected.

tuser1@orcl92> select count(*) from tuser1.t;

  COUNT(*)
----------
        11

... which defeats the purpose of a security procedure that can check the user's credentials. 

To get around this, just disable the role after it is granted:

sys@orcl92> alter user tuser2 default role all except sar1;

User altered.

If tuser2 IS STILL LOGGED IN, THE PRIVILEGE IS STILL ENABLED:

tuser2@orcl92> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TUSER2                         CONNECT                        NO  YES NO
TUSER2                         RESOURCE                       NO  YES NO
TUSER2                         SAR1                           NO  NO  NO  <- NOT DEFAULT ANYMORE

tuser2@orcl92> select count(*) from tuser1.t;

  COUNT(*)
----------
        11

That's because default roles are enabled/disabled UPON LOGIN:

tuser2@orcl92> @conn tuser2/tuser2
Connected.

tuser2@orcl92> select count(*) from tuser1.t;
select count(*) from tuser1.t
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

=> So remember to disable a protected role after granting it.  Note that " ... default role all except ..." may enable OTHER roles that the tuser2 is not supposed to have as default.


tuser1@orcl92> grant execute on saradmin to tuser2;

Grant succeeded.

tuser2@orcl92> execute tuser1.saradmin.letmein;

PL/SQL procedure successfully completed.

(Without the execute grant on saradmin, tuser2 cannot run letmein)

Finally, the table is accessible from sqlplus:
tuser2@orcl92> select count(*) from tuser1.t;

  COUNT(*)
----------
        11


But from PLSQL, the table is NOT accessible:
tuser2@orcl92> create or replace procedure sar1test
  2  is
  3  x int;
  4  begin
  5    select count(*) into x from tuser1.t;
  6    --execute immediate 'select count(*) from dual';
  7  end;
  8
  9  /

Warning: Procedure created with compilation errors.

tuser2@orcl92> show error
Errors for PROCEDURE SAR1TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/38     PL/SQL: ORA-00942: table or view does not exist


But this works:
tuser2@orcl92> ed
Wrote file afiedt.buf

  1  create or replace procedure sar1test
  2  is
  3  x int;
  4  begin
  5    --select count(*) into x from tuser1.t;
  6    execute immediate 'select count(*) from tuser1.t';
  7* end;
  8  /

Procedure created.

... but it does not run ...

tuser2@orcl92> exec sar1test
BEGIN sar1test; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "TUSER2.SAR1TEST", line 6
ORA-06512: at line 1



Using AUTHID CURRENT_USER does help:

  1  create or replace procedure sar1test
  2  authid current_user
  3  is
  4  x int;
  5  begin
  6    execute immediate 'select count(*) from tuser1.t';
  7* end;

tuser2@orcl92> /

Procedure created.

tuser2@orcl92> exec sar1test;

PL/SQL procedure successfully completed.

However, using AUTHID CURRENT_USER does not help if the execute immediate is replaced by select ... into:

  1  create or replace procedure sar1test
  2  authid current_user
  3  is
  4  x int;
  5  begin
  6    select count(*) into x from tuser1.t;
  7    --execute immediate 'select count(*) from tuser1.t';
  8* end;
tuser2@orcl92> /

Warning: Procedure created with compilation errors.

tuser2@orcl92> show errors
Errors for PROCEDURE SAR1TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
6/38     PL/SQL: ORA-00942: table or view does not exist

So here we are back to the ORA-942.




We know that "Within an invoker-rights routine, roles are enabled ..."

What does this mean?

It does NOT mean that, using authid current_user, tuser2 can run it's own procedure and reference tuser1's table using a privilege granted through a role (except for the execute immediate case).  Trying this, tuser2 would not even be able to compile the procedure, as shown above.

What it DOES mean is that tuser2 can run a procedure owned by tuser1 with privileges from a role granted by tuser1 to tuser2:


tuser1@orcl92>

  1  create or replace procedure sar1test
  2  authid current_user
  3  is
  4  x int;
  5  begin
  6    select count(*) into x from tuser1.t;
  7    --execute immediate 'select count(*) from tuser1.t';
  8    dbms_output.put_line(x);
  9* end;
tuser1@orcl92> /

Procedure created.


tuser2@orcl92> exec tuser1.sar1test
11

PL/SQL procedure successfully completed.


However, this means tuser2 is running a procedure that is selecting from a table in the procedures' schema (tuser1).  So then, definers' rights will do just as well!  Just remove authid current_user and tuser2 can execute tuser1.sar1test successfully.

The real use of authid current_user would be when the stored procedure references a table in the invokers' schema, for which no explicit grant is required because both code and table are in the same (invoking) schema.  In this way, a "code-base" in tuser1 can be built up, which during run-time, references objects (e.g. tables) in the invokers' schema.  In addition, role-based privileges can also be used to reference objects in the definers' schema as shown above.

So,
temporarily grant direct privilege for tuser2 to create a duplicate of table t:

tuser1@orcl92> grant select on t to tuser2;

Grant succeeded.

tuser2@orcl92> create table t as select * from tuser1.t where rownum<5;

Table created.

tuser1@orcl92> revoke select on t from tuser2;

Revoke succeeded.



Redefine the procedure with invokers' rights:

tuser1@orcl92>

  1  create or replace procedure sar1test
  2  authid current_user
  3  is
  4  x int;
  5  y int;
  6  begin
  7    select count(*) into x from t;  -- unqualified, so becomes t in invokers' schema.
  8    select count(*) into y from tuser1.t;
  9    dbms_output.put_line('num lines in invoker''s schema: '||x);
 10    dbms_output.put_line('num lines in  tuser1''s schema: '||y);
 11* end;

tuser1@orcl92> /

Procedure created.

Connect as tuser2, set role and run the proc:

tuser2@orcl92> exec tuser1.saradmin.letmein

PL/SQL procedure successfully completed.

tuser2@orcl92>  select * from session_roles;

ROLE
------------------------------
SAR1

tuser2@orcl92> exec tuser1.sar1test;
num lines in invoker's schema: 4  <-- from invokers' schema tuser2.
num lines in  tuser1's schema: 11 <-- from fixed schema tuser1.

PL/SQL procedure successfully completed.

Just to be sure, verify that removing authid means the unqualified schema becomes tuser2:

tuser1@orcl92> ed
Wrote file afiedt.buf

  1  create or replace procedure sar1test
  2  -- authid current_user
  3  is
  4  x int;
  5  y int;
  6  begin
  7    select count(*) into x from t;
  8    select count(*) into y from tuser1.t;
  9    dbms_output.put_line('num lines in invoker''s schema: '||x);
 10    dbms_output.put_line('num lines in  tuser1''s schema: '||y);
 11* end;
tuser1@orcl92> /

Procedure created.

tuser2@orcl92> exec tuser1.sar1test;
num lines in invoker's schema: 11
num lines in  tuser1's schema: 11

PL/SQL procedure successfully completed.


To summarize, this shows the use of invokers' rights in a stored procedure to access 2 schemas using role privileges, instead of directly granted privileges.