Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If you have DBA privileges, you can connect with that user without knowing a user’s password, then you can set the old password. In this way, the user will not understand that someone else is connected to his / her account. You do not need to connect as sysdba for this operation.
First of all, we will detect the user’s password in an encrypted way. The area that we will use in the future will be the information in the PASSWORD column.
1 2 3 4 5 | SQL> SELECT name, password, spare4 FROM sys.user$ WHERE name='SCOTT'; NAME PASSWORD SPARE4 ------------------------------ ------------------------------ SCOTT F9CEE3031292BF07 S:5B3EBB444A10F303D08B160993C2AFF33C7184122F86EF258AD4CC2467A1 |
Let’s change the user’s password with a password we want.
1 2 3 4 5 6 7 8 9 10 | SQL> alter user SCOTT identified by "Welcome1"; User altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options You have new mail in /var/spool/mail/oracle |
Now we can connect to the database with the user we change the password and we can perform the operation we want.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [oracle@orcldb ~]$ sqlplus SCOTT/Welcome1@ORCL SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 28 16:31:12 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create table deneme(id number,name varchar(30)); Table created. SQL> insert into deneme values (1,'Ahmet'); 1 row created. SQL> commit; Commit complete. SQL> show user USER is "SCOTT" SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options |
We re-set the old password with the following script. You should write the the old password’s spare value instead of S:5B3EBB444A10F303D08B160993C2AFF33C7184122F86EF258AD4CC2467A1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [oracle@orcldb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 28 16:32:54 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> alter user SCOTT identified by values 'S:5B3EBB444A10F303D08B160993C2AFF33C7184122F86EF258AD4CC2467A1'; User altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options |
The user will not be able to connect with the old password when we do this.
