DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: granting execute to functions, packages, etc through roles

  1. #1
    Join Date
    Oct 2012
    Location
    Virginia
    Posts
    2

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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    no, you cannot access stored procedures via roles

  3. #3
    Join Date
    Oct 2012
    Location
    Virginia
    Posts
    2
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by davey23uk View Post
    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.

  5. #5
    Join Date
    Nov 2001
    Posts
    335
    As far as I can tell you can grant execute privilege to a role, am I missing something ?
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Quote Originally Posted by gandolf989 View Post
    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

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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. ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width