0

I've couple of stored procedures(Let's say PROC_1 and PROC_2) inside one package(i.e. PROC_PKG) which is under one of schema/user (i.e. A).

I need to grant execute permission to another user(i.e. B).

So I've already tried with following commands:

grant execute on PROC_1 TO B;

grant execute on A.PROC_1 TO B;

grant execute on PROC_PKG.PROC_1 TO B;

grant execute on A.PROC_PKG.PROC_1 TO B;

I've already looked into this and this answers but they didn't helped me.

PS: I want to allow user B to access only PROC_1 that means user B should not be able to access PROC_2 from the same package.
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
Gopal00005
  • 2,061
  • 4
  • 35
  • 54
  • 2
    package is a unit you can grant privileges on package and not on each procedure in a package . for that case you should create stored procedure – hotfix Jun 20 '19 at 12:45
  • 2
    Can't be done. You could possibly let PROC_2 decide whether the calling user is allowed and throw an exception if not; but not through direct privilege grants. One possibility might be through roles and checking if the user has a specific role enabled, but that seems a long way from where you are now. – Alex Poole Jun 20 '19 at 13:05
  • Got it and added the same in the answer for further reference. thanks a lot guys for hint. – Gopal00005 Jun 20 '19 at 13:11

3 Answers3

3

We can not grant execute privilege to individual stored procedure or function from package. So either we can grant execute privilege to every stored procedure/functions from package or none of them from package.

In-fact this is one of the advantage of Package: You can grant roles on the package, instead of granting roles on each object in the package.

Following query will grant execute privilege to user B from user A(That will allow user B to execute every stored procedure/functions from that package).

GRANT EXECUTE ON PROC_PKG TO B;

Gopal00005
  • 2,061
  • 4
  • 35
  • 54
1

As pointed out by Alex Poole one workaround with ROLES would be like this:

CREATE ROLE EXECUTE_PROC_1 NOT IDENTIFIED;

GRANT EXECUTE_PROC_1 TO B;
ALTER USER B DEFAULT ROLE ALL;

CREATE OR REPLACE PACKAGE BODY MY_PACKAGE AS
   PROCEDURE PROC_1 IS
   BEGIN
       IF NOT DBMS_SESSION.IS_ROLE_ENABLED('EXECUTE_PROC_1') THEN
           RAISE_APPLICATION_ERROR(-20001, 'Not permitted');
       END IF;
       -- Do your stuff
   END;
END MY_PACKAGE;
/
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
1

You can create a separate "wrapper" procedure which executes only A.PROC_PKG.PROC_1 and then grant execute on that separately to B.

Heiner
  • 90
  • 7