Tuesday, October 27, 2015

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>

No comments: