Using dynamic SQL - priv related

[kerrieoconnor]

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

[davey23uk]

whats the error

[kerrieoconnor]

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.

[davey23uk]

you need permissions granted directly through you, not through a role

[ebrian]

You may want to consider INVOKERS RIGHTS.

[kerrieoconnor]

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