On a database project with the multiple team members doing the development work, it becomes harder to keep track of all the grants given. Before promoting your changes to the higher environments like UAT, TEST or production, you can use the following script to extract the grants on all the database objects in a schema.
SQL> select 'grant ' || privilege || ' on ' || table_name ||
2 ' to ' || grantee ||
3 case when grantable = 'YES' then ' with grant option;'
4 else ';'
5 end as my_grant
6 from user_tab_privs_made
7 order by table_name;
MY_GRANT
-----------------------------------------------------------------------------
grant ALTER on BONUS to SYSTEM;
grant SELECT on DEPT to SCOTT;
grant INSERT on EMP to SCOTT;
grant SELECT on EMP to SCOTT;
grant UPDATE on EMP to SCOTT;
grant REFERENCES on EMP to SCOTT;
grant ON COMMIT REFRESH on EMP to SCOTT;
grant QUERY REWRITE on EMP to SCOTT;
grant DEBUG on EMP to SCOTT;
grant FLASHBACK on EMP to SCOTT;
grant INDEX on EMP to SCOTT;
grant ALTER on EMP to SCOTT;
grant DELETE on EMP to SCOTT;
grant SELECT on EMP to SYSTEM with grant option;
grant EXECUTE on P to HR;
15 rows selected.
Please notice the "with grant option" in the above query result. Also, notice that the procedure grants are also included there. In fact, all the grants will be included here.
The dbms_metadata is not a good idea to use for the grants. The above approach always works!
If you want to get the object grants for more than one schema, use the following version of the query.
SQL> select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
2 ' to ' || grantee ||
3 case when grantable = 'YES' then ' with grant option;'
4 else ';'
5 end as my_grant
6 from dba_tab_privs
7 where owner in ('SCOTT','HR')
8 order by owner,table_name;
MY_GRANT
---------------------------------------------------------------------------------------
grant SELECT on SCOTT.BONUS to HR;
grant UPDATE on SCOTT.BONUS to HR;
grant INSERT on SCOTT.BONUS to HR;
grant ON COMMIT REFRESH on SCOTT.DEPT to PUBLIC;
grant REFERENCES on SCOTT.DEPT to PUBLIC;
grant UPDATE on SCOTT.DEPT to PUBLIC;
grant SELECT on SCOTT.DEPT to PUBLIC;
grant INSERT on SCOTT.DEPT to PUBLIC;
grant INDEX on SCOTT.DEPT to PUBLIC;
grant DELETE on SCOTT.DEPT to PUBLIC;
grant ALTER on SCOTT.DEPT to PUBLIC;
grant QUERY REWRITE on SCOTT.DEPT to PUBLIC;
grant DEBUG on SCOTT.DEPT to PUBLIC;
grant FLASHBACK on SCOTT.DEPT to PUBLIC;
grant UPDATE on SCOTT.DEPT to HR;
grant INSERT on SCOTT.DEPT to HR;
grant SELECT on SCOTT.DEPT to HR;
grant SELECT on SCOTT.LINEITEM to HR;
grant UPDATE on SCOTT.LINEITEM to HR;
grant INSERT on SCOTT.LINEITEM to HR;
grant SELECT on SCOTT.ORDERS to HR;
grant UPDATE on SCOTT.ORDERS to HR;
grant INSERT on SCOTT.ORDERS to HR;
grant UPDATE on SCOTT.PROJ to HR;
grant SELECT on SCOTT.PROJ to HR;
grant INSERT on SCOTT.PROJ to HR;
grant SELECT on SCOTT.SALGRADE to HR;
grant UPDATE on SCOTT.SALGRADE to HR;
grant INSERT on SCOTT.SALGRADE to HR;
29 rows selected.
Please note that we are using "dba_tab_privs" view now and also prefixing the object name with the owner.
Please let me know your feedback!
Thanks
If you are interested in the book "Oracle SQL Scipting". here is the link.
http://www.amazon.com/Oracle-SQL-Scripting-ebook/dp/B00E2T1DVA/ref=sr_1_1?ie=UTF8&qid=1374436123&sr=8-1&keywords=oracle+sql+scripting
