AUTHID DEFINER V/S AUTHID CURRENT_USER

There are lot of times we get error in Oracle Apps while trying to execute the API's at development time, due to the AUTHID DEFINER and AUTHID CURRENT_USER. This article gives you good understanding about the AUTHID DEFINER and AUTHID CURRENT_USER.

A stored procedure runs either with the rights of the caller (AUTHID CURRENT_USER) or with the rights of the procedure's owner (AUTHID DEFINER). This behaviour is specified with the AUTHID clause. This authid clause immediatly follows the create procedure, create function, create package or create type statement. It can be ommited, in which case the default authid definer is taken.


AUTHID DEFINER and AUTHID CURRENT_USER
-----------------------------------------------------------
AUTHID DEFINER:-
--------------------

Example:-
---------
The following are done in APPS scheme.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ AUTHID DEFINER
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to 

Example:-
---------

grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema SCOTT try to run the same query.

begin
xyz.XYZ_1;
end;

It have inserted new record in the 'A' table. Note there is no synonym for the table A in SCOTT schema.

Running this program from anywhere, it is as good as running from APPS schema in case of AUTHID DEFINER.

10.2) CURRENT_USER:-
----------------------

Example:-
-----------

The following are done in the APPS schema.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ AUTHID CURRENT_USER
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to 

Example:-
-----------

grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema (scott) try to run the same query.

begin
xyz.XYZ_1;
end;

Got the error message table or view doesn't exist for the A table.

Create view for the a table and run the same program again.

create synonym 'A' for table 'A'

begin
xyz.XYZ_1;
end;

select * from a;

Now there is no error. It is inserting the record with no issue.

WITH NO AUTHID DEFINER and AUTHID CURRENT_USER :-
-----------------------------------------------------------------------


Example:-
---------

The following are done in the APPS schema.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to 

Example:-
---------

grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema SCOTT try to run the same query.


begin
xyz.XYZ_1;
end;

It is working in same way as it have done for the AUTHID DEFINER.

Q) Is it possible to know from the select statement if it is INVOKER(CURRENT_USER) or DEFINER

A) Yes, It is possible to get this information from the select statement. Use

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = "Your Package Name"
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

Example:-
-----------

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = 'ASO_APR_WF_INT'
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS';

References:
http://alloracletech.blogspot.com/2008/07/authid-definer-vs-authid-currentuser.html