Can only one procedure within a PL/SQL package be run with AUTHID CURRENT_USER?

Question

I have a PL/SQL package that does not specify an AUTHID (effectively making it AUTHID DEFINER). However, there is precisely one procedure within this package that needs to run as AUTHID CURRENT_USER. Is this possible, or must I create a separate, top-level procedure?

score 4 · Accepted Answer · answered Sep 29 '11 at 14:41

4

Pretty sure a new package would be needed since the AUTHID can only be specified at the PACKAGE level (to the best of my knowledge anyway)

answered Sep 29 '11 at 14:41

cagcowboy

30,012
11
69
93

Do you know of any documentation that confirms this? – Adam Paynter Sep 30 '11 at 08:47
1

@Adam, Your best bet is to test it on your DEV system. Then you'll know for sure for your DB version etc. – Ollie Sep 30 '11 at 12:09
Write authid current_user at package **specification** CREATE PACKAGE package_name **AUTHID CURRENT_USER** IS – Shahidul Islam Molla Nov 09 '17 at 04:16

score 2 · Answer 2 · edited May 23 '17 at 12:31

2

Though this linked question is slightly off-topic, the answer supplied by JulesLt explains that you can't specify AUTHID in a package at a level below the package level:

Executing an Oracle Stored Proc as Another User

edited May 23 '17 at 12:31

Community

1
1

answered Sep 29 '11 at 21:15

Ollie

17,058
7
48
59

score 0 · Answer 3 · answered Oct 09 '12 at 06:26

0

Oracle does not allow the authid clause on a subprogram in a package or type. You will get the following error:

Error: PLS-00157: AUTHID only allowed on schema-level programs

answered Oct 09 '12 at 06:26

AsemRadhwi

887
11
21

score 0 · Answer 4 · answered Mar 16 '16 at 15:10

A possible solution might be following:

You create a package with AUTHID CURRENT_USER option;
You grant select, insert, etc. to the objects that reside in the DEFINER schema that you want to use;
You use fully qualified names of the DEFINER objects.

Here is an example:

CREATE PACKAGE pkg1 AUTHID CURRENT_USER
IS
  procedure insert_current_user;
  procedure insert_definer;
END pkg1;
/
CREATE OR REPLACE PACKAGE BODY pkg1
IS
  procedure insert_current_user
  is
  begin
    insert into table1 values(1);
  end insert_current_user;

  procedure insert_definer
  is
  begin
    insert into DEFINER.table1 values(1);
  end insert_definer;
END pkg1;
/

DEFINER is the owner of the table.

As an improvement, you can create a synonym for the DEFINER.table1 table and then use the synonyms in the package.

Can only one procedure within a PL/SQL package be run with AUTHID CURRENT_USER?

4 Answers4