Finding grants from data dictionary

Unless you make an full export with grants you won’t get all grants when doing a export/import with Oracle. So if you don’t have a list of all grants across those schemas you imported, you will have a huge job of compiling and establishing which grants are missing.

The solution to this is to find those grants through the data dictionary on the source system. For instance, if you want to find all grants made by the PORTAL schema, the query would be like this:


SET HEADING OFF
SET PAGES 999
SPOOL grants.sql
SELECT 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO '||grantee||DECODE(grantable,'YES',' WITH GRANT OPTION;',';')
FROM DBA_TAB_PRIVS
WHERE grantor = 'PORTAL';
SPOOL OFF
EXIT


Now you have everything you need in the grants.sql and this script can now be run on the target system.