Oracle Trick: Login without knowing another user password.

========================================
General
========================================
Suppose you need to login into another user account, without knowing his password, and without altering his password?

Step 1 - Get current password mask.
SELECT password FROM DBA_USERS WHERE username = 'ABC';
--Suppose the password is 'aswdededed'

Step 2 - Temporary change user password.
ALTER USER ABC IDENTIFIED BY ABC123;

Now it is possible to login with ABC/ABC123

Step 3 - Revert back to original password.
ALTER USER ABC IDENTIFIED BY VALUES 'aswdededed';


========================================
Run PL/SQL code for another user - DBMS_JOB
========================================
In this example, run DBMS_JOB for another user.

When attempting to run a job that belongs to another user, even as privileged user, one would get this error message:
ORA-23421: job number 123 is not a job in the job queue

To overcome this, need to use DBMS_SYS_SQL Package.
DBMS_SYS_SQL Package allows user A to run SQL statements as user B.

DBMS_SYS_SQL is owned by SYS, and first must be granted.
sqlplus / as sysdba
GRANT EXECUTE ON SYS.DBMS_SYS_SQL TO admin_user;

Now, log as admin_user, and use DBMS_SYS_SQL, in a PL/SQL block.

In this example, all jobs of a certain type are executed under all users.

DECLARE
 v_uid     NUMBER;
 v_result  INTEGER;

 v_sql_template VARCHAR2(1000) := 'BEGIN DBMS_JOB.BROKEN(my_job_id, FALSE, SYSDATE+1/1440); COMMIT; END;  ';

 v_sqltext      VARCHAR2(1000);
 v_myint   INTEGER;

 CURSOR broken_jobs_cur IS SELECT schema_user, job FROM DBA_JOBS WHERE what = 'SUPPORT_UTIL_PKG.SUPPORT_COUNTERS_PRC;' AND broken = 'Y';

 BEGIN

   FOR broken_jobs_rec IN broken_jobs_cur loop

     SELECT user_id INTO v_uid FROM ALL_USERS WHERE username = broken_jobs_rec.schema_user;     

     v_sqltext := REPLACE (v_sql_template,'my_job_id',broken_jobs_rec.job);
     
     v_myint := SYS.DBMS_SYS_SQL.OPEN_CURSOR();

     SYS.DBMS_SYS_SQL.PARSE_AS_USER(v_myint, v_sqltext, DBMS_SQL.NATIVE, v_uid);

     v_result := SYS.DBMS_SYS_SQL.EXECUTE(v_myint);

     SYS.DBMS_SYS_SQL.CLOSE_CURSOR(v_myint);     

     COMMIT;
  END LOOP;   
 END ;
/

========================================
Example compiling Invalid Objects from same user.
========================================

CREATE USER DBA_USER IDENTIFIED BY DBA_PASS DEFAULT TABLESPACE IGT_TABLE;

GRANT DBA TO DBA_USER;

--Because PL/SQL require explicit grants:

GRANT SELECT ON DBA_USERS TO DBA_USER;

GRANT SELECT ON  DBA_OBJECTS TO DBA_USER;

GRANT EXECUTE ON SYS.DBMS_SYS_SQL TO DBA_USER;

CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL IS

   --=======================================  

  C_EXP_GEN_APP_ERR      CONSTANT NUMBER := -20100;

   --=======================================

   PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS 

     PRAGMA AUTONOMOUS_TRANSACTION;

     v_msg_text    VARCHAR2(1000);

   BEGIN

     INSERT INTO SGA_W_LOG(module_name,  msg_text, msg_date)

       VALUES ( p_module_name, p_msg_text, SYSDATE);

     COMMIT;

   EXCEPTION

     WHEN OTHERS THEN 

       v_msg_text := SQLERRM;

      RAISE_APPLICATION_ERROR(C_EXP_GEN_APP_ERR, v_msg_text);       

   END;    

  ---------------------------------------------------

  PROCEDURE EXECUTE_SQL (p_owner       IN DBA_OBJECTS.owner%TYPE,

                         p_sql_text    IN VARCHAR2) IS

    v_module_name        SGA_W_LOG.module_name%TYPE;

    v_msg_text           SGA_W_LOG.msg_text%TYPE;    

    v_uid     NUMBER;

    v_result  INTEGER;

    v_myint   INTEGER;    

  BEGIN

     v_module_name := 'EXECUTE_SQL';

     SELECT user_id INTO v_uid FROM DBA_USERS WHERE username = p_owner;     

     v_myint := SYS.DBMS_SYS_SQL.OPEN_CURSOR();

     SYS.DBMS_SYS_SQL.PARSE_AS_USER(v_myint, p_sql_text, DBMS_SQL.NATIVE, v_uid);

     v_result := SYS.DBMS_SYS_SQL.EXECUTE(v_myint);

     SYS.DBMS_SYS_SQL.CLOSE_CURSOR(v_myint);     

     COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      v_msg_text := 'Error Running Command: '|| p_sql_text||' : '|| SUBSTR(SQLERRM,1,800);

      write_sga_w_log(v_module_name,v_msg_text);    

 END EXECUTE_SQL;

  ---------------------------------------------------

  PROCEDURE COMPILE_PACKAGE_BODIES IS

    CURSOR get_invalid_package_cur IS

    SELECT owner, 

           'ALTER PACKAGE  '||owner||'.'||object_name||' COMPILE BODY' AS sql_command

      FROM DBA_OBJECTS

     WHERE DBA_OBJECTS.status = 'INVALID'

       AND DBA_OBJECTS.object_type = 'PACKAGE BODY';

       

    v_module_name        SGA_W_LOG.module_name%TYPE;

    v_msg_text           SGA_W_LOG.msg_text%TYPE;    

  BEGIN

   v_module_name := 'COMPILE_PACKAGE_BODIES';

   

   FOR get_invalid_package_rec IN get_invalid_package_cur LOOP

     BEGIN

       EXECUTE_SQL(get_invalid_package_rec.owner, get_invalid_package_rec.sql_command);

--       EXECUTE IMMEDIATE get_invalid_package_rec.sql_command;

     EXCEPTION

       WHEN OTHERS THEN         

         v_msg_text := 'Error Running Command: '|| get_invalid_package_rec.sql_command||' : '|| SUBSTR(SQLERRM,1,800);

         write_sga_w_log(v_module_name,v_msg_text);    

       END;  

   END LOOP;    

  EXCEPTION

    WHEN OTHERS THEN  

         v_msg_text := 'Unexpected Error: '|| SUBSTR(SQLERRM,1,800);

         write_sga_w_log(v_module_name,v_msg_text);    

  END COMPILE_PACKAGE_BODIES;    

  ---------------------------------------------------

  PROCEDURE COMPILE_INVALID_OBJECTS IS

  BEGIN

    COMPILE_PACKAGE_BODIES;

  EXCEPTION

    WHEN OTHERS THEN  

      NULL;

  END COMPILE_INVALID_OBJECTS;    

  ---------------------------------------------------

END ADMIN_UTIL;