Skip to Main Content
  • Questions
  • How can i grant execute to a procedure inside a package?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Simone.

Asked: June 28, 2017 - 9:33 am UTC

Last updated: June 30, 2017 - 4:04 am UTC

Version: Oracle Database 11g Express Edition

Viewed 50K+ times! This question is

You Asked

Hello everyone.

I'm trying to do this.

I have a package where are declared 2 procedure

CREATE OR REPLACE PACKAGE PACK_CT AS

PROCEDURE A;
PROCEDURE B;

END PACK_CT;
/


And I have two users, let's call it User1 and User2.

What I want is this:
Can I do
GRANT EXECUTE PACK_CT.A TO User1; GRANT EXECUTE PACK_CT.B TO User2;
?

and Connor said...

It doesn't work that way - you perform the grants at the *package* level.

If you need to workaround that, simply create some wrapper procedures, ie, *no-one* gets the grant on PACK_CT and then you do:

procedure PROC_A_FOR_USER_1 is
begin
  pack_ct.a;
end;

procedure PROC_B_FOR_USER_2 is
begin
  pack_ct.b;
end;



and then issue grants on them accordingly.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What if I get the role of the user inside the procedure?

Simone, June 29, 2017 - 8:52 am UTC

Hello everyone. Thank you for answering my question.

I also thinked about this.

Having always the two users, User1 and User2, and both users have a role, for example:

CREATE ROLE ROLE_A;
GRANT ROLE_A TO User1;
CREATE ROLE ROLE_B;
GRANT ROLE_B TO User2;


When the user call the procedure PACK_CT.A or PACK_CT.B, it is possible to read the role of the user inside the procedure?
I want to this because if the user has the role "ROLE_A" ok you can execute the procedure, if you the user has the role "ROLE_B", no you can't execute.
Connor McDonald
June 30, 2017 - 4:04 am UTC

Roles are never enabled during the execution of a procedure except in the special case of Invokers Rights which is a new feature in Oracle8i, release 8.1.
This fact is documented application developers guide:


Privileges Required to Create Procedures and Functions
To create a stand-alone procedure or function, or package specification or
body, you must meet the following prerequisites:

• You must have the CREATE PROCEDURE system privilege to create a
procedure or package in your schema, or the CREATE ANY
PROCEDURE system privilege to create a procedure or package in
another user’s schema.

Attention: To create without errors, that is, to compile the procedure
or package successfully, requires the following additional privileges:
The owner of the procedure or package must have been explicitly
granted the necessary object privileges for all objects referenced within
the body of the code; the owner cannot have obtained required
privileges through roles.

If the privileges of a procedure’s or package’s owner change, the procedure
must be reauthenticated before it is executed. If a necessary privilege to a
referenced object is revoked from the owner of the procedure (or package), the
procedure cannot be executed.

</quote>
Try this:


SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure (unless you are using Invokers rights in Oracle8i. See the PLSQL documentation for more information on this feature and make sure you understand the ramifications). To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library