granting execute to functions, packages, etc through roles

Printable View

10-04-2012, 03:26 PM
worthinb

granting execute to functions, packages, etc through roles

Is it possible for a DBA to grant execute to a user via user defined roles for functions and packages that the DBA created and owns?

Also, can a DBA that has been granted execute on an Oracle restricted package (such as utl_file or dbms_random) be able to create a role, grant execute to one of the packages mentioned to that role and then grant that role to another user. Would that user then be able to use utl_file or dbms_random?

Of course, it is noted that sys has revoked execute from public for those two packages (utl_file and dbms_random).
10-09-2012, 03:42 AM
davey23uk

no, you cannot access stored procedures via roles
10-09-2012, 09:58 AM
worthinb

Davey23uk,

Thank you for your quick response. I agree with you because I tried it in the past and it didn't work. However, our developers are requesting it be done.

Is there any documentation that you know of that states this? If so, it would be greatly appreciated. I could give it to them so that they would stop requesting it and coding to it.

Take care,
worthinb
10-09-2012, 01:17 PM
gandolf989

Quote:

Originally Posted by davey23uk

no, you cannot access stored procedures via roles

Are you sure about that? I know you need an explicit grant to access an object from within a
stored procedure, however to access a stored procedure from the sql prompt I would think
that a role would work as well as a direct grant.
10-09-2012, 01:40 PM
BV1963

As far as I can tell you can grant execute privilege to a role, am I missing something ?
10-09-2012, 02:34 PM
davey23uk

Quote:

Originally Posted by gandolf989

Are you sure about that? I know you need an explicit grant to access an object from within a
stored procedure, however to access a stored procedure from the sql prompt I would think
that a role would work as well as a direct grant.

indeed, I mis-read that, need direct access to the table.

You can grant access to a stored procedure to a role - need to make sure you have the privs on any underlying objects as well if that's appropriate
10-09-2012, 03:06 PM
gandolf989

Of course you only "need to make sure you have the privs on any underlying objects" if you are using invoker rights as opposed to using definer rights. ;-)