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

开发者_StackOverflowI 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?

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)

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

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

A possible solution might be following:

1. You create a package with AUTHID CURRENT_USER option;
2. You grant select, insert, etc. to the objects that reside in the DEFINER schema that you want to use;
3. 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.