granting execute to functions, packages, etc through roles

[worthinb]

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).

[davey23uk]

no, you cannot access stored procedures via roles

[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

[gandolf989]

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.

[BV1963]

As far as I can tell you can grant execute privilege to a role, am I missing something ?

[davey23uk]

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

[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. ;-)