1 Reply Latest reply: Feb 5, 2014 1:58 AM by trajon RSS

retain the roles and privileges of the schema before deleting

928199 Newbie
Currently Being Moderated

Hi Experts,

 

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

 

Thanks

 

Sam

  • 1. Re: retain the roles and privileges of the schema before deleting
    Emad Al-Mousa Journeyer
    Currently Being Moderated

    Hi,

     

    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.

  • 2. Re: retain the roles and privileges of the schema before deleting
    trajon Newbie
    Currently Being Moderated

    Hi

    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');
    
    

     

    Good luck...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points