Insufficient privileges error

I have been using dynamic sql to insert into a table owned by another user. The reason for the dynamic sql is because the database can be either integrated with the objects owned by the other user or not. In a lot of cases if the objects weren't in existence then if I had not of had the insert statement in a string there would have been a compile error.
I was logged on as OWNERA (the dynamic sql inserts to a table owned by OWNERB).
I had previously connected as OWNERB and granted insert to A_USER which is a role of which OWNERA is one. Yet I got the insufficient privileges error. I got round it by explicitly granting the insert to OWNERA. The processes involved then worked (and only then) for all users with A_USER role inserting to OWNERB's table in the dynamic sql held within a procedure of OWNERA's.

I was just wondering if anyone could offer an explanation for this. The only thing I could think of was that for some reason when using dynamic sql the user has to explicitly be granted the privileges and that when other users with A_USER role do this, Oracle thinks (for some bizarre reason) that they are OWNERA as it is through OWNERA's object that it is called.

Is it a bug in Oracle ?

Sorry about the waffly explanation,

Regards,

Joe Mellors Received on Thu Feb 07 2002 - 17:20:46 CET