Grant needed for table in dynamic SQL in a package?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Grant needed for table in dynamic SQL in a package?

  1. #1
    Join Date
    Nov 2000
    Posts
    6

    Question

    Does one need to grant SELECT on a table referenced in dynamic SQL in a stored proc. in a package? I'm using a standard "definers' rights" type of package in Oracle 8.1.6. I dynamically build a SELECT statement an execute it with an

    OPEN cursor FOR sql_string;

    sql_string is my dynamically-built SQL command. I'm returning this to ADO in a VB 6 app., using an OLE-DB connect string that specifies the MS OLE-DB provider for Oracle. I grant EXECUTE on the package to scott, connect with scott, and execute the stored proc. in the package. I get an error saying table or view not found. If I grant SELECT on the table to scott, he can execute it without errors. I've also tested this using a role, where I grant EXECUTE on the package to the role, and grant the role to scott - same error! My conclusion is that tables in dynamic SQL must be granted explicitly to all users/ roles using them.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Yes.
    You need to give SELECT privilege directly to user or role.

  3. #3
    Join Date
    Jul 2000
    Posts
    296
    The 'problem' is the package, not dynamic SQL.
    Roles are disabled/invisible in stored procedures/packages. You cannot use privileges granted to roles in packages, only privileges granted directly to the user.

  4. #4
    Join Date
    Nov 2000
    Posts
    6

    Smile

    I've succeeded in giving scott the right priviledges but with roles, and not by granting permissions directly to scott. I gave EXECUTE permission on the packages, and stand-alone stored procs. and functions to scott's role. I noticed however, that scott still didn't have access to certain tables, which turned out to be those that were used with REF CURSORs to SELECT. After giving SELECT permission to scott's role, on those particular tables, it works. I would have preferred though, not to have to give SELECT persmission to anyone on my tables.

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