Oracle – execute DML query as different user

This sounds like the usual problem with stored procedures: when they are compiled by developer A in his schema, the compiler only considers the privileges DIRECTLY granted to A - as if the roles were all stripped off before compiling. This is often addressed by writing your procedure as AUTHID CURRENT_USER, but if the procedure calls another procedure granted through a role, it can fail.

Generally it seems anything you want to compile into a PL/SQL object has to be directly granted to you. I've written a number of scripts to turn role-granted privileges into direct GRANT statements to the procedure owner so that it would compile.

Note this does not apply to anonymous blocks - they compile and run just fine with the benefit of your current roles' privileges.

So I think the scheduler is another case where you have to code as if the roles weren't there. It's a pain and I hate it, but that's how it is.

User_B will be the owner of the objects and user_A is the user of those objects, which is a good separation and guarantees that the objects can not be changed by the application. You would not want user_A to be able to change any structure.

Normally you want an application manager to be able to connect as user_B to make the changes needed for a new version or something like that. If this is the case, you want your admins to have the possibility to connect through their personal accounts to user_B to do the changes. Since user_B is the owner of the objects, user_B also has the rights to use them.

So the solution is to use an application manager user to do the ddl using the proxy user. That is way better than giving dba privileges or ANY privileges to an application manager.