Posted By

kamilch on 10/01/10


Tagged

Oracle


Versions (?)

MISSING GRANTS


 / Published in: PL/SQL
 

  1. SELECT 'grant select on ' || :src_schema || '.' || object_name || ' to ' || :target_schema || ';' AS grant_cmd FROM (
  2. SELECT tabs.table_name AS object_name FROM all_all_tables tabs WHERE tabs.owner = :src_schema AND NOT EXISTS (
  3. SELECT * FROM all_tab_privs privs WHERE privs.table_schema = :src_schema AND privs.table_name = tabs.table_name AND privs.grantee = :target_schema
  4. )
  5. SELECT seqs.sequence_name AS object_name FROM all_sequences seqs WHERE seqs.sequence_owner = :src_schema AND NOT EXISTS (
  6. SELECT * FROM all_tab_privs privs WHERE privs.table_schema = :src_schema AND privs.table_name = seqs.sequence_name AND privs.grantee = :target_schema
  7. )
  8. ORDER BY object_name
  9. )
  10. ;

Report this snippet  

You need to login to post a comment.