Accessing a schema without knowing the password

Most of the times that you may need to logon to a database user / schema owner, to do so emergency maintenance,,  but you don’t know the password.

There is an alternative where you can use alter session set current schema “Schema-Name” ;

Other than this you can use the below process, where you record the current password encryption, change the password, logon and do your maintenance.

Let’s try an example here
      

Create an account:

SQL> conn / as sysdba

Connected.

 

SQL>  create user nikhil identified by mypass1 ;

User created.

SQL> grant connect , resource to nikhil ;

Grant succeeded.

 

Now if I want to change the password, then I should know the ‘Old Password’

SQL> conn nikhil/mypass1 ;
Connected.


SQL> password 

Changing password for NIKHIL

Old password:

Oops..!

In such cases you can use the user$ view under sys user which will give us the encrypted password so that we will preserve the old password

Let’s see..

SQL> conn / as sysdba

Connected.



SQL> select name,'alter user '||name||' identified by values '''||spare4||';'||password||''';' command from sys.user$ where name = 'NIKHIL';



NAME

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

COMMAND

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

NIKHIL

alter user NIKHIL identified by values 'S:2549CDA4335FCEF7814FD9832AD653A937AAE8

7105AB962A873A59A576E8;FD135DE4875002BA';

Now I will set a temporary password for the account to perform the activity.. Later will set the old password using this encrypted values.

SQL> alter user nikhil identified by demopassword ;

User altered.


SQL> conn nikhil/demopassword ;

Connected.

Now connect as sysdba,  and revert the password using the encrypted values

SQL> conn / as sysdba

Connected.



SQL> alter user NIKHIL identified by values 'S:2549CDA4335FCEF7814FD9832AD653A937AAE87105AB962A873A59A576E8;FD135DE4875002BA';

User altered.



SQL> conn nikhil/mypass1 ;

Connected.

SQL>

Alternatively, we can use the DBMS_METADATA package to get the encryption;

SQL> set long 10000

select dbms_metadata.get_ddl('USER','NIKHIL') command from dual;SQL>



COMMAND

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



   CREATE USER "NIKHIL" IDENTIFIED BY VALUES 'S:2549CDA4335FCEF7814FD9832AD653A9

37AAE87105AB962A873A59A576E8;FD135DE4875002BA'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP"