Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Creating table in schema A with user B and granting permissions to user C

942364Jun 7 2012 — edited Jun 8 2012
Hello, 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?
This post has been answered by JustinCave on Jun 8 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 6 2012
Added on Jun 7 2012
16 comments
1,928 views