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.
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.
A workaround might be resetting that user's password to some other value (and then, once you're done with what you are doing, restoring it back to its original value).
Here's an example: connected as a privileged user, I'm going to create a test user. (You already have it, but don't know its password, right?)
SQL> create user lf identified by lf; User created. SQL> grant create session to lf; Grant succeeded.
In order to find hashed password value, query
sys.user$ (prior to 11g database version, it was visible in
dba_users as well):
SQL> select name, password from sys.user$ where name = 'LF'; NAME PASSWORD ------------------------------ ------------------------------ LF 340F8BE3D2015FE1
lf's password to something else, so that I could connect and run those scripts.
SQL> alter user lf identified by friday; User altered. SQL> connect lf/friday@orcl Connected.
OK then - run your scripts now, connected as
lf. Once you're done, connect as a privileged user and restore
lf's password using the hashed value you should have remembered:
SQL> connect mydba/mypwd@orcl Connected. SQL> alter user lf identified by values '340F8BE3D2015FE1'; User altered.
lf can now connect as he used to:
SQL> connect lf/lf@orcl Connected. SQL>
Come on!! You are an administrator. If you are logged on using SYSDBA user then You can directly access any object of any schema using
No need to get into the SCHEMA for it.
SQL> conn tejash/oracle Connected. SQL> create table tab1(col1 number); Table created. SQL> INSERT INTO TAB1 VALUES(1); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM TAB1; COL1 ---------- 1 SQL> conn / as sysdba Connected. SQL> SQL> SELECT * FROM TEJASH.TAB1; COL1 ---------- 1 SQL> UPDATE TEJASH.TAB1 SET COL1 = 2; 1 row updated. SQL> COMMIT; Commit complete. SQL> SELECT * FROM TEJASH.TAB1; COL1 ---------- 2 SQL> conn tejash/oracle Connected. SQL> SELECT * FROM TEJASH.TAB1; COL1 ---------- 2 SQL>