Creating table in schema A with user B and granting permissions to user C
942364Jun 7 2012 — edited Jun 8 2012Hello, I've got an amusing problem -- we have a large table that requires a batch process to do millions of updates, and it needs to finish faster. This takes several hours as an update, but a create table as select does the same thing in about a minute. Yay! Then we just drop the old table and rename the new table to the old, and rebuild all the index, and grant select/insert/update/delete permissions to another user that needs access... and there's the problem, Oracle says insufficient privs.
We have schema A that owns the table, userid B that does all the application batch stuff, and userid C that does inserts for a middleware process. None of these are actual users, of course. We want user B to do the CTAS, rename, index rebuild, and permission re-granting. But user B apparently cannot grant permissions on objects in schema A to user C, even though B has created the table and has the DBA role (not ideal I know)!
What's really crazy is that there is one way user B can grant the permissions to user C, which is to:
grant select any table to USER C;
grant insert any table to USER C;
grant update any table to USER C;
grant delete any table to USER C;
It seems really perverse that user B can create tables in schema A, and even grant C "any table" privs, but can't grant privs specifically on this object in schema A, even with DBA privs. I must be missing something... right?