Impersonating Of Any Oracle User?

Yes, it is possible! It's like sudo command in UNIX or “Run As” in Windows (but without prompting for password). Starting with 8i version, Oracle has a special undocumented package called DBMS_SYS_SQL which provides the functionality needed for impersonating users. It is internally used in databases replication or within the XDB platform. Of course, execute right against this package should be granted with extreme care, as it can be easily exploited by hackers. Furthermore, as an additional precaution it is advisable to check from time to time to whom execute privilege for this package was granted.

So, lets try it!

SQL> connect sys as sysdba
Enter password:
Connected.

SQL> grant execute on dbms_sys_sql to alek;

Grant succeeded.

SQL> connect alek
Enter password:
Connected.
SQL> declare
  2  l_uid number;
  3  l_sqltext varchar2(100) := 'grant dba to super_user identified by xxx';
  4  l_myint integer;
  5
  6  begin
  7  select user_id into l_uid from all_users where username like 'SYS';
  8
  9  l_myint:=sys.dbms_sys_sql.open_cursor();
 10  sys.dbms_sys_sql.parse_as_user(l_myint, l_sqltext, dbms_sql.native, l_uid);

 11
 12  sys.dbms_sys_sql.close_cursor(l_myint);
 13  end ;
 14  /

PL/SQL procedure successfully completed.

SQL> connect super_user/xxx
Connected.

Brrr!!! Nice but scary!