Grant y Revoke sobre objetos - dinamico


/ Published in: SQL
Save to your folder(s)

Uso de Grant y Revoke de objetos masivos de distintos user


Copy this code and paste it in your HTML
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.