Skip to Main Content

Oracle Database Discussions

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!

grant create and drop objects on schema to a role/user

ji liSep 24 2014 — edited Sep 26 2014

Generic question for Oracle RDBMS:

As far as I know, there is no way for a schema owner to grant privileges such as create objects, or drop objects of that schema to a user or role.

I know many people have always asked if this is possible, and I keep hoping Oracle will provide this ability, but as far as I know, it is still not possible, even in 12.1.0.2.

Does anyone have any thoughts on this?

I considered to have developers create packages to create tables, views, packages, etc. and then grant execute on them to user/roles, but it would be an enormous amount of work.

Has anyone come up with an easier way to do this?  Or know of a way that this can be done?

Obviously, I can not grant create any table, index, package, view, etc., or drop any table, view, etc, and we sure can't grant DBA role.

I thought the "become user" was a solution, but it apparently falls short of being able to do these kinds of things.

In our case, apparently, we have users who log in with the schema owner's account and create objects, modify objects, etc.  (Modify objects is not a problem because we can just grant all on object to someone.)   The problem is that we need to stop people from logging in with shared group accounts (or logging in as the schema owner) to create objects, drop objects, etc.).  We need to have them all log in as their own userID, and only be able to create and drop objects in select schemas.

This post has been answered by unknown-951199 on Sep 25 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 24 2014
Added on Sep 24 2014
21 comments
15,764 views