Logged on as sysdba, how to run dml script as another user if you do not have the passwrod

oracle

logged on as sysdba to an oracle database, how to run dml script as another user (Joebloggs) if you do not have the password. The scripts will run dml that updates objects in (jobbloggs) schema.

Best Answer

If you really need to log in as that user, use a proxy user:

SQL> conn / as sysdba
Connected.
SQL> select count(*) from dba_users where username = 'UNKNOWN_PASSWORD';

  COUNT(*)
----------
         1

SQL> conn UNKNOWN_PASSWORD/UNKNOWN_PASSWORD
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> create user proxy_user identified by proxy_password;

User created.

SQL> alter user UNKNOWN_PASSWORD grant connect through proxy_user;

User altered.

SQL> conn proxy_user[UNKNOWN_PASSWORD]/proxy_password
Connected.
SQL> show user
USER is "UNKNOWN_PASSWORD"
SQL>

Otherwise, you can run DML on objects in other schemas.

Related Question