Using dynamic SQL - priv related
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Using dynamic SQL - priv related

Hybrid View

  1. #1
    Join Date
    Aug 2006
    Posts
    5

    Using dynamic SQL - priv related

    Hi

    I execute the following

    execute immediate 'insert into DVD.TMP_DVD_PRICE select * from ' || l_table;

    within a package.

    It worked fine in devt (I was connecting as the schema owner) but in UAT it is failing on connect with a different account.

    The new account has permissions on the package the command is in. Do I need to grant insert rights to this account also? Or is there any other reason that it might be failing?

    many thanks

    Kerrie

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    whats the error

  3. #3
    Join Date
    Aug 2006
    Posts
    5
    I'm getting back a generic error msg - I will contact the DBA team about the exact error. As this may take some time if anyone knows whether executing SQL dynamically changes the way permissions should be applied (i.e. that the privs to the package are not enough for the objects referenced in the dynamic SQL to be recognisable) I would be very grateful.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you need permissions granted directly through you, not through a role

  5. #5
    Join Date
    Apr 2006
    Posts
    377
    You may want to consider INVOKERS RIGHTS.

  6. #6
    Join Date
    Aug 2006
    Posts
    5
    Hi

    I granted 'insert' permission on DVD.TMP_DVD_PRICE to the role used by the connecting user. It works now without error. Many thanks

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