AUTHID CURRENT_USER

If USERA creates a function or procedure and allows USERB to run it, USERB does so with USERA’s permissions. However, if USERA adds the AUTHID CURRENT_USER clause to the code, USERB runs it with its own permissions. You can see what I mean in the example below, which I tested in an Oracle 11.1 database:

 

I created a user called USERB and allowed it to login to the database:

 

SQL> create user userb

  2  identified by userb

  3  /

 

User created.

 

SQL> grant create session to userb

  2  /

 

Grant succeeded.

 

SQL>

 

I created a user called USERA and allowed it to connect to the database and create tables and compiled code:

 

SQL> create user usera

  2  identified by usera

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant create session,

  2  create table,

  3  create procedure to usera

  4  /

 

Grant succeeded.

 

SQL>

 

USERA connected to the database and created a table called CARS with 2 rows:

 

SQL> conn usera/usera

Connected.

SQL> create table cars as

  2  select 'AUSTIN' manufacturer from dual

  3  /

 

Table created.

 

SQL> insert into cars values('MORRIS')

  2  /

 

1 row created.

 

SQL> select count(*) from cars

  2  /

 

  COUNT(*)

----------

         2

 

SQL>

 

USERA then created a function to accept a table name parameter, count the number of rows in that table and return the value to the caller. USERA then allowed USERB to execute this function:

 

SQL> create function count_rows

  2  (tab in varchar2) return pls_integer

  3  is

  4  row_count pls_integer;

  5  begin

  6  execute immediate 'select count(*) from '||tab

  7  into row_count;

  8  return row_count;

  9  end count_rows;

10  /

 

Function created.

 

SQL> grant execute on count_rows to userb

  2  /

 

Grant succeeded.

 

SQL>

 

USERA created a copy of this function with the AUTHID CURRENT_USER clause:

 

SQL> create function count_rows_acu

  2  (tab in varchar2) return pls_integer

  3  authid current_user

  4  is

  5  row_count pls_integer;

  6  begin

  7  execute immediate 'select count(*) from '||tab

  8  into row_count;

  9  return row_count;

10  end count_rows_acu;

11  /

 

Function created.

 

SQL> grant execute on count_rows_acu to userb

  2  /

 

Grant succeeded.

 

SQL>

 

USERB then logged in and tried to look at USERA’s CARS table but failed, as you would expect:

 

SQL> conn userb/userb

Connected.

SQL> select count(*) from usera.cars

  2  /

select count(*) from usera.cars

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL>

 

USERB then ran USERA’s first function to count the rows in the CARS table. This did not have the AUTHID CURRENT_USER clause so it ran with USERA’s permissions and there was no problem:

 

SQL> select usera.count_rows('cars') from dual

  2  /

 

USERA.COUNT_ROWS('CARS')

------------------------

                       2

 

SQL>

 

USERB then ran USERA’s second function to count the rows in the CARS table. This had the AUTHID CURRENT_USER clause so it ran with USERB’s permissions and failed:

 

SQL> select usera.count_rows_acu('cars') from dual

  2  /

select usera.count_rows_acu('cars') from dual

       *

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "USERA.COUNT_ROWS_ACU", line 7

 

SQL>