SQL> desc dba_tab_privs
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE				   NOT NULL VARCHAR2(30)
 OWNER					   NOT NULL VARCHAR2(30)
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 GRANTOR				   NOT NULL VARCHAR2(30)
 PRIVILEGE				   NOT NULL VARCHAR2(40)
 GRANTABLE					    VARCHAR2(3)
 HIERARCHY					    VARCHAR2(3)

SQL> ---- create user aa
SQL> drop user aa cascade
  2  /

User dropped.

SQL> CREATE USER aa IDENTIFIED BY aa
  2  DEFAULT TABLESPACE "USERS"
  3  /

User created.

SQL> GRANT connect, resource TO aa
  2  /

Grant succeeded.

SQL> 
SQL> ---- create user bb
SQL> drop user bb cascade
  2  /

User dropped.

SQL> CREATE USER bb IDENTIFIED BY bb
  2  DEFAULT TABLESPACE "USERS"
  3  /

User created.

SQL> 
SQL> GRANT connect, resource TO bb
  2  /

Grant succeeded.

SQL> 
SQL> 
SQL> ---- create user cc
SQL> drop user cc cascade
  2  /

User dropped.

SQL> CREATE USER cc IDENTIFIED BY cc
  2  DEFAULT TABLESPACE "USERS"
  3  /

User created.

SQL> 
SQL> GRANT connect, resource TO cc
  2  /

Grant succeeded.

SQL> 
SQL> ---- create table_aa with aa
SQL> conn aa/aa
Connected.
SQL> create table table_aa
  2  (
  3  	dummy	varchar2(1000)
  4  )
  5  /

Table created.

SQL> 
SQL> 
SQL> ---- grant select to cc with grant option
SQL> grant select on table_aa to cc with grant option
  2  /

Grant succeeded.

SQL> 
SQL> 
SQL> ---- grant select to bb through cc
SQL> conn cc/cc
Connected.
SQL> grant select on aa.table_aa to bb
  2  /

Grant succeeded.

SQL> 
SQL> 
SQL> ---- revoke select from bb using system
SQL> conn system/a
Connected.
SQL> revoke select on aa.table_aa from bb
  2  /
revoke select on aa.table_aa from bb
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant 

select 'revoke ' ||privilege || ' on ' 
|| grantor || '.' || table_name 
|| ' from ' || grantee
from dba_tab_privs

select 'revoke ' ||privilege || ' on ' 
|| owner || '.' || table_name 
|| ' from ' || grantee
from dba_tab_privs

select 'revoke ' ||privilege || ' on ' 
|| owner || '.' || table_name 
|| ' from ' || grantee
from dba_tab_privs
where grantor = user

CREATE OR REPLACE
  PROCEDURE EXECUTE_AS_USER(
                            p_username VARCHAR2,
                            p_stmt     VARCHAR2
                           )
    IS
        v_cur     NUMBER;
        v_user_id NUMBER;
    BEGIN
        SELECT  MAX(user_id)
          INTO  v_user_id
          FROM  DBA_USERS
          WHERE username = UPPER(p_username);
        IF v_user_id IS NULL
          THEN
            RAISE_APPLICATION_ERROR(-20900,'User ' || p_username || ' does not exist.');
        END IF;
        v_cur := DBMS_SQL.OPEN_CURSOR;
        DBMS_SYS_SQL.PARSE_AS_USER(v_cur,p_stmt,DBMS_SQL.NATIVE,v_user_id);
        DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

SQL> show user
USER is "SCOTT"
SQL> grant select on emp to u1 with grant option
  2  /

Grant succeeded.

SQL> connect u1/u1
Connected.
SQL> grant select on scott.emp to u2
  2  /

Grant succeeded.

SQL> connect u2/u2
Connected.
SQL> select count(*) from scott.emp
  2  /

   COUNT(*)
-----------
         14

SQL> connect scott as sysdba
Enter password: *****
Connected.
SQL> show user
USER is "SYS"
SQL> revoke select on scott.emp from u2
  2  /
revoke select on scott.emp from u2
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant


SQL> CREATE OR REPLACE
  2    PROCEDURE EXECUTE_AS_USER(
  3                              p_username VARCHAR2,
  4                              p_stmt     VARCHAR2
  5                             )
  6      IS
  7          v_cur     NUMBER;
  8          v_user_id NUMBER;
  9      BEGIN
 10          SELECT  MAX(user_id)
 11            INTO  v_user_id
 12            FROM  DBA_USERS
 13            WHERE username = UPPER(p_username);
 14          IF v_user_id IS NULL
 15            THEN
 16              RAISE_APPLICATION_ERROR(-20900,'User ' || p_username || ' does not exist.');
 17          END IF;
 18          v_cur := DBMS_SQL.OPEN_CURSOR;
 19          DBMS_SYS_SQL.PARSE_AS_USER(v_cur,p_stmt,DBMS_SQL.NATIVE,v_user_id);
 20          DBMS_SQL.CLOSE_CURSOR(v_cur);
 21  END;
 22  /

Procedure created.

SQL> EXEC EXECUTE_AS_USER('u1','revoke select on scott.emp from u2');

PL/SQL procedure successfully completed.

SQL> connect u2/u2
Connected.
SQL> select count(*) from scott.emp
  2  /
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>