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>
