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....?

4 comments:

  1. Hello Richard,

    I think there's a confusion about authid current_user. The roles are always enabled if you use authid current_user. It doesn't matter if you use dynamic SQL or not:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e11822/adobjdes.htm#CHDEBEDJ

    Using dynamic SQL just lets you pass the privilege checking on compiling. Login as BOB, create the procedure without dynamic SQL, and then grant execution to DAVE. You'll see that DAVE will be able to execute it:

    "create or replace procedure demoproc authid current_user IS
    v_col number;
    begin
    select col1 into v_col from bob.demotab;
    end;
    /

    grant execute on demoproc to dave;

    conn dave/dave

    exec bob.demoproc;"

    If you revoke the role called "bread" from DAVE, he will get an error when he tries to execute the procedure.

    ReplyDelete
  2. Hi Gokhan,
    Thanks for that - indeed you are correct but i think its clearer to demo that with a 3rd user.

    The 'owner' of the code always needs explicit rights but the executor can run it using the role even if its not dynamic sql.

    so if dave owns the procedure which is based on a table owned by bob - i.e..the very first example i showed (which he has explicit rights for) the procedure will compile

    If it's with authid current user and i create a new user gokhan and only grant gokhan the bread role it will work as you said even though its not an execute immediate.

    I think its more understandable with the 3 user example.

    In our case it's quite an obscure bit of code which is doing various ddl operations - the authid we are actually using is actually the owner of the stored procedure - but is the only way to get the code to run....

    Cheers,
    Rich

    ReplyDelete