Posted By

thescorpion on 11/14/12


Tagged

sql Oracle DBA privilegios


Versions (?)

Grant y Revoke sobre objetos - dinamico


 / Published in: SQL
 

Uso de Grant y Revoke de objetos masivos de distintos user

  1. -----Grant
  2. BEGIN
  3. FOR R IN (SELECT owner, object_name ,object_type FROM All_Objects WHERE OWNER = 'OTELO' AND object_type IN ('VIEW','TABLE')) LOOP
  4. Begin
  5. EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.object_name||' to AUDITOR';
  6. Exception
  7. When Others Then
  8. DBMS_OUTPUT.put_line(Sqlerrm);
  9. End ;
  10. END LOOP;
  11. END;
  12. -----revoke
  13.  
  14. BEGIN
  15. FOR R IN (SELECT owner, object_name ,object_type FROM All_Objects WHERE OWNER = 'APEX_030200' AND object_type IN ('VIEW','TABLE')) LOOP
  16. Begin
  17. EXECUTE IMMEDIATE 'REVOKE All ON '||R.owner||'.'||R.object_name||' FROM AUDITOR';
  18. Exception
  19. When Others Then
  20. DBMS_OUTPUT.put_line(Sqlerrm);
  21. End ;
  22. END LOOP;
  23. END;

Report this snippet  

You need to login to post a comment.