Posted By

thescorpion on 11/14/12

Tagged

sql
Oracle

Versions (?)

Last Edited at 11/14/12 03:55am

Statistics

Viewed 700 times

Favorited by 0 user(s)

Related snippets

Grant y Revoke sobre objetos - dinamico

/ Published in: SQL

Uso de Grant y Revoke de objetos masivos de distintos user

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

-----Grant
BEGIN
   FOR R IN (SELECT owner, object_name ,object_type FROM All_Objects WHERE OWNER = 'OTELO' AND object_type IN ('VIEW','TABLE')) LOOP
      BEGIN
        EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.object_name||' to AUDITOR';
      Exception 
         WHEN Others THEN
           DBMS_OUTPUT.put_line(Sqlerrm);  
      END ;
   END LOOP;
END;
-----revoke
 
BEGIN
   FOR R IN (SELECT owner, object_name ,object_type FROM All_Objects WHERE OWNER = 'APEX_030200' AND object_type IN ('VIEW','TABLE')) LOOP
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE All ON '||R.owner||'.'||R.object_name||' FROM AUDITOR';
      Exception 
         WHEN Others THEN
           DBMS_OUTPUT.put_line(Sqlerrm);  
      END ;
   END LOOP;
END;