Do something in somebody else's schema without knowing or changing his password. This can be used, for instance, to view a private database link's password, or to change or alter another user's job. The same technique can be used to cause security breach. Even granting execute any procedure privilege alone (without creating any procedure) could cause problems. -------------------------------------------------------------------------------- The easist solution since 9i: alter user yong grant connect through dbauser; --select * from proxy_users; conn dbauser[yong]/dbauserpassword -- Do work here as yong conn dbauser alter user yong revoke connect through dbauser; -------------------------------------------------------------------------------- View password of a private db link (before 10g): In schema YONG: SQL> select password from user_db_links where db_link = 'L'; PASSWORD ------------------------------ YONG In schema SYSTEM (or anybody that can create any procedure and execute any procedure): SQL> conn system/manager Connected. SQL> create procedure yong.dblinkpasswd 2 as 3 passwd varchar2(30); 4 begin 5 select password into passwd from user_db_links where db_link = 'L'; 6 dbms_output.put_line(passwd); 7 end; 8 / Procedure created. SQL> set serverout on SQL> exec yong.dblinkpasswd YONG PL/SQL procedure successfully completed. Before 10gR2, the password is also stored in sys.link$ and SYS can see it. Don't grant select any dictionary to anybody prior to 10gR1. -------------------------------------------------------------------------------- Drop a user's job: SQL> sho user USER is "SYS" SQL> grant create job to yong; Grant succeeded. SQL> conn yong/yong Connected. SQL> exec dbms_scheduler.create_job('TESTJOB', 'P') PL/SQL procedure successfully completed. SQL> select * from v$transaction; no rows selected <-- Different from dbms_job, dbms_scheduler auto commits after you create a job. SQL> conn / as sysdba Connected. Session altered. SQL> create or replace procedure yong.dropjob 2 as 3 begin 4 execute immediate('begin dbms_scheduler.drop_job(''TESTJOB''); end;'); 5 end; 6 / Procedure created. SQL> select job_name from dba_scheduler_jobs where owner = 'YONG'; JOB_NAME ------------------------------ TESTJOB SQL> exec yong.dropjob PL/SQL procedure successfully completed. SQL> select job_name from dba_scheduler_jobs where owner = 'YONG'; no rows selected -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Note1: If you don't like the above approach, you can always temporarily and quickly change the user's password. col password new_value oldpwd select password from dba_users where username = 'APP'; alter user app identified by app; conn app/app create or replace view v as select * from apptable; conn system/manager alter user app identified by values '&oldpwd'; Comment: If app is being used constantly and new connections could come in at any moment, this approach is unacceptable. -------------------------------------------------------------------------------- Note2: In 8i, if a user's view definition has a query with an inline view, you need to alter session set current_schema in your DBA account or use the above techniques. In schema U: create view v as select * from (select a from ut); Login as a DBA: SQL> create or replace view u.v as select * from (select a from ut); create or replace view u.v as select * from (select a from ut) * ERROR at line 1: ORA-00942: table or view does not exist SQL> alter session set current_schema = u; Session altered. SQL> create or replace view u.v as select * from (select a from ut); View created. You could also simply prefix the user's table with his username if you don't mind this change. This problem is gone in 9i.