Need Advice We got the schema refresh of schema IT_PROJ_TRKG_DATA request from the customer. customer need as below :
All related GRANTS should be backed up BEFORE dropping the schemas and reapplied after loading them on ALL schemas where IT_PROJ_TRKG_DATA is either the grantee or the grantor.
Please advice me how to find all the details required and how to apply those to all the schemas IT_PROJ_TRKG_DATA is either the grantee or the grantor
you can type expdp help=y
this will list all the parameters for export data pump.
when you export using expdp and you specify the parameter SCHEMAS='ZZZ' it will export all database objects under schema ZZZ including the grants.
only synonyms will be skipped in the export.
hope this helps.
When you export schema, for example:
expdp system/*** directory=data_pump_dir dumpfile=dump.dmp logfile=dump.log schemas=your_schema consistent=y
you have in dump all system privilege and roles granted your_schema
but not have directly granted object privileges, for example grant select on other_schema.table_name to your_schema
you can save by this scripts:
select 'grant ' || lower(privilege) || ' on ' || lower(owner) || '.' || lower(table_name) || ' to ' || lower(grantee) || case grantable when 'YES' then ' with grant option;' else ';' end from dba_tab_privs where grantee = upper('&user_name');