1

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.

3

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.

0

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

Now, reset 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.

Fine; lf can now connect as he used to:

SQL> connect lf/lf@orcl
Connected.
SQL>
0

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 SCHEMA_NAME.OBJECT_NAME.

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>

Cheers!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.