CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlplsql-package
Gopal00005
Gopal00005

Reputation: 2161

Grant/revoke execute permission to procedure from package

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.

Upvotes: 0

Views: 13276

Answers (3)

Heiner
Heiner

Reputation: 100

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

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

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;
/

Upvotes: 1

Gopal00005
Gopal00005

Reputation: 2161

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;

Upvotes: 3

Related Questions