title: PL/SQL Loop to Revoke Privileges
body: <p>I need to create a procedure that does the following: </p>
  
  <ol>
  <li>Revoke system privileges granted directly to the user. (Table: dba_sys_privs) </li>
  <li>Revoke object privileges granted directly to the user. (Table: dba_tab_privs)</li>
  <li>It revokes roles granted directly to the user. (Table: dba_role_privs)</li>
  </ol>
  
  <p>It has to loop through the tables and remove all of them for a user. So far I have #1 and #3 working. But I need to add #2, and I can't seem to figure out how to do it. This is my code for #2, and I keep getting an error:</p>
  
  <p><code></p>
  
  <pre><code>//REVOKING OBJECT PRIVILEGES
  CREATE or REPLACE PROCEDURE deactivate_user 
      (p_username IN VARCHAR2) AS
  l_username VARCHAR2(30) := UPPER(p_username);
  BEGIN
      FOR rec IN (SELECT * FROM dba_tab_privs WHERE GRANTEE = p_username)
      LOOP
          EXECUTE IMMEDIATE 'REVOKE ALL PRIVILEGES ON TABLE '||rec.owner||'.'||rec.table_name||' FROM '||rec.grantee; 
      END LOOP;
  END;
  /
  </code></pre>
  
  <p></code></p>
  
  <p>When I try to execute the code: SQL> exec deactivate_user('BLAKE'), I get this error:
  ERROR: at line 1:
  ORA-06550: table or view does not exist
  ORA-06512: at "SYS.DEACTIVATE_USER", line 7</p>
  
  <p>Your help is greatly appreciated!</p>