Authid

What is the need of 'authid' keyword in pl/sql.

'Authid' is a keyword in pl/sql which is used with proceuders (create procedure), packages (create package), functions (create function) and types (create type).
A stored procedure runs eiether with rights of the caller (authid current_user) or with the rights of owner (authid definer)
The default value for 'authid' is 'definer'. It immediatly follows the 'create procedure' keyword.

The generic syntax as below

SQL> ed
Wrote file afiedt.buf

1 create or replace procedure sp_authid_currentuser
2 --[authid current_user|define]
3 as
4 v_value varchar2(100);
5 begin
6 select a into v_value from t;
7 dbms_output.put_line(v_value);
8* end;
9 /

Let say there are two users (DASARIS, CISBATCH) and a table (T) which is created in both the schemas.

[From DASARIS]
SQL> create table t(a varchar2(100));

Table created.

SQL> insert into t values('from srikar');

1 row created.

SQL> commit;

Commit complete.


[From CISBATCH]
SQL> create table t (a varchar2(100));

Table created.

SQL> insert into t values('from cisbatch');

1 row created.

SQL> commit;

Commit complete.

First, we would create a procedure in 'DASARIS' schema which simply displays the content of the table 'T' with 'authid' as 'definer'

SQL> ed
Wrote file afiedt.buf

1 create procedure sp_authid
2 as --Here we dint mention anything so default is 'authid definer'
3 v_value varchar2(100);
4 begin
5 select a into v_value from t;
6 dbms_output.put_line(v_value);
7* end;
SQL> /


Here is what we get when we run the procedure from 'DASARIS' schema

SQL> begin
2 sp_authid;
3 end;
4 /
from srikar

PL/SQL procedure successfully completed.

no surprise !

Lets try to run the same procedure from 'CISBATCH' schema
SQL> ed
Wrote file afiedt.buf

1 begin
2 dasaris.sp_authid;
3* end;
SQL> /
dasaris.sp_authid;
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00904: insufficient privilege to access object DASARIS.SP_AUTHID
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

mmmm, we need to grant privileges to 'CISBATCH'


[From DASARIS]
SQL> grant execute on sp_authid to cisbatch;

Grant succeeded.

lets try one more time

[From CISBATCH]
SQL> ed
Wrote file afiedt.buf

1 begin
2 dasaris.sp_authid;
3* end;
4 /
from srikar

PL/SQL procedure successfully completed.

Here we got the output as 'from srikar' thouh the table 'T' in schema 'CISBATCH' is holding 'from cisbatch', we got such result as the procedure ran with the rights of the definer i.e. 'DASARIS'

thats all about 'authid definer'


Lets now go to 'authid current_user'

the general syntax for 'authid current_user' is
SQL> ed
Wrote file afiedt.buf

1 create or replace procedure sp_authid_currentuser
2 authid current_user
3 as
4 v_value varchar2(100);
5 begin
6 select a into v_value from t;
7 dbms_output.put_line(v_value);
8* end;
9 /


lets run this procedure from two schemas,

[From DASARIS]
SQL> begin sp_authid_currentuser; end;
2 /
from srikar

PL/SQL procedure successfully completed.

SQL> grant execute on sp_authid_currentuser to cisbatch;

Grant succeeded.

[From CISBATCH]
SQL> begin dasaris.sp_authid_currentuser; end;
2 /
from cisbatch

PL/SQL procedure successfully completed.

so, the procedure run from 'CISBATCH' schema gives us the rusult from its own table, it just like running procedure which is in the same schema.

Lets try to drop the table 'T' in 'CISBATCH' schema and run both the procedures from 'CISBATCH' schema,

[From CISBATCH]
SQL> drop table t;

Table dropped.

SQL> begin
2 dasaris.sp_authid_currentuser;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "DASARIS.SP_AUTHID_CURRENTUSER", line 6
ORA-06512: at line 2

SQL>
SQL> begin
2 dasaris.sp_authid;
3 end;
4 /
from srikar

PL/SQL procedure successfully completed.


The second procedure is run successfully though the table is dropped, because the procedure is ran with the rights of the owner in which the table is available