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.
========================================
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;
