AUTHID DEFINER V/S 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'