Do you pass the AUTHID test.....?

Now I'm not sure whether i already knew this and had just forgotten or whether i had genuinely missed this little 'feature' but it's useful to know this exists even if it's just so you don't look stupid when someone shows it to you.....

For you plsql people out there (i know some of you survived the onslaught of java and dotnet) you'll be familiar with the AUTHID clause within compiled plsql units - can't remember exactly when it came it but it's been there for a while, and i thought i knew what it did. Well i did know what it did but what i'd overlooked forgotten is a subtle difference in the way roles are treated when dynamic SQL is executed from within a stored program unit.

Lets do a quick example to demo what i mean

we do some initial setup of 2 basic users and give privilege on one of the schemas objects to a role.

SQL> create user bob identified by bob;
SQL> grant create session,create table, create procedure to bob;
SQL> create user dave identified by dave;
SQL> grant create session,create table, create procedure to dave;
SQL> create role bread;
SQL> grant unlimited tablespace to bob;
SQL> grant unlimited tablespace to dave;
SQL>  create table bob.demotab(col1 number);
SQL> grant select,insert on bob.demotab to bread;
SQL> grant bread to dave;
SQL> insert into bob.demotab values (1);
SQL> commit;

So after that, dave has permissions to select and insert on a table called demotab owned by bob via the bread role (see what i did there....)

Now as I'm sure we all know if you create some stored plsql it won't compile - as demonstrated here

SQL> create or replace procedure demoproc IS
  2  v_col number;
  3  begin
  4  select col1 into v_col from bob.demotab;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE DEMOPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/33     PL/SQL: ORA-00942: table or view does not exist

even though i can select from it normally in sqlplus

SQL> select * from bob.demotab;

      COL1
----------
         1

The reason is the role is disabled when the code is compiled so the rights to the table are lost.

If we change the code slightly to use AUTHID CURRENT_USER this happens

SQL> create or replace procedure demoproc authid current_user IS
v_col number;
begin
select col1 into v_col from bob.demotab;
end;
/
  2    3    4    5    6
Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE DEMOPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/33     PL/SQL: ORA-00942: table or view does not exist

i.e the exact same thing

What is interesting however is if we change the code to be dynamic SQL rather than static - so here is an example of that with the default definers rights

SQL> create or replace procedure demoproc IS
v_sql varchar2(4000);
begin
v_sql := 'insert into bob.demotab values (99)';
execute immediate v_sql;
end;
/  2    3    4    5    6    7

Procedure created.

So the code compiles as the semantic checks aren't done at compile time now, but when we execute the code......

SQL> exec demoproc;
BEGIN demoproc; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "DAVE.DEMOPROC", line 5
ORA-06512: at line 1

It fails as the role is still disabled at runtime

However - if we perform the same thing with AUTHID CURRENT_USER

SQL> create or replace procedure demoproc authid current_user IS
v_sql varchar2(4000);
begin
v_sql := 'insert into bob.demotab values (99)';
execute immediate v_sql;
end;
/  2    3    4    5    6    7

Procedure created.

SQL>  exec demoproc;

PL/SQL procedure successfully completed.

SQL> select * from bob.demotab;

      COL1
----------
         1
        99

And it works! So roles are enabled from dynamic sql within authid current_user blocks.

Be honest did you know that....?