How to give the privilege of giving privileges in Oracle

Question

I want to create an application about one user select other users and define there privileges, but this user don't be a Administrator or "dba". A sentence as: GRANT GRANT TO BIG_USER; The "Big User" have many privileges, another users the same or less. Thanks

Answer 1

If you want to grant all grant that have been given to a user to some other users at first you should get that user grants then for each user write a script to give that grant. the following tables show the grant for each user

USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS tables

write a query to get privileges and then give them to other users

Answer 2

Oracle's permission system allows a bit of granularity regarding what you can allow this user to grant.

For objects (tables, etc) you have to either be a dba, the object owner, or have permissions granted with the grant option. So the following should work:

GRANT ALL PRIVILEGES ON mytable TO WITH GRANT OPTION;

You would have to repeat this on every table the user needs to be able to manage permissions on.

This answer is assuming you are looking at permissions on objects (tables etc) rather than system privileges.