The relationship between the Oracle password file and the user's password

Today, a colleague received a task is to modify the database sys and the system user's password, the boss in the assignment when the task also brought with a sentence do not forget to synchronize the password file, so think of a few days ago wrote a blog about the Oracle password file, then just wrote how to create a password file, How to remotely use SYSDBA to log in to the database, but does not mention that the password file in the user's password is related to the user's password in the database.

Here's a test, the platform I'm using is 11.2.0.4

First modify the SYS password

[Email protected]>alter user sys identified by ZHAOXU; User altered.--because the password in the Dba_user view in 11g is null, select user$ View [email protected]>select password from user$ where Name= ' SYS '; PASSWORD------------------------------------------------------------------------------------------c53b64bc84353309--recommended REM Ote_login_passwordfile required for exclusive[email protected]>show parameter remote_login_passwordfilename TYPE VALUE-- -------------------------------------------------------------------------------------------------Remote_login_ Passwordfile string EXCLUSIVE

Create a password file and test Telnet

#创建一个与SYS用户密码相同的密码文件 [[email protected] dbs]$ cd  $ORACLE _home/dbs[[email protected]  dbs]$ orapwd file=orapworcl entries=3 password=zhaoxu[[email protected]  Dbs]$ ls -l orapworcl -rw-r----- 1 oracle oinstall 1536 Dec  26 21:32 orapworcl# Test Connection Successful [c:\~]$ sqlplus sys/[email protected]/orcl as  sysdbasql*plus: release 12.1.0.1.0 production on  Monday  12 Month  26 21:34:22  2016Copyright  (c)  1982, 2013, oracle.  all rights reserved. Connect to:  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -  64bit productionwith the partitioning, olap, data mining and real  application testing optionssql> select password from user$ where  name= ' SYS '; PASSWORD------------------------------------------------------------the hash value of the c53b64bc84353309# password does not change # Create a password file that differs from the SYS password [email  protected] dbs]$ orapwd file=orapworcl entries=3 password=luoxi force=y[[ Email protected] dbs]$ ls -l orapworcl -rw-r----- 1 oracle  oinstall 1536 dec 26 21:36 orapworcl# test Connection, use the password of the original password file login is unsuccessful, use the new password file password login success, However, the password hash value of the SYS user is still unchanged [c:\~]$ sqlplus sys/[email protected]/orcl as sysdbasql*plus:  Release 12.1.0.1.0 Production on  Monday  12 Month  26 21:36:20  2016copyright  (c)  1982, 2013, oracle.  all rights reserved. error:ora-01017: invalid username/password; logon denied[c:\~]$ sqlplus sys/[ Email protected]/orcl as sysdbasql*plus: release 12.1.0.1.0 production on   Monday  12 Month  26 21:36:30 2016Copyright (c)  1982, 2013, oracle.  all rights reserved. Connect to:  Oracle Database  11g enterprise edition release 11.2.0.4.0 - 64bit productionwith  the partitioning, olap, data mining and real application testing  optionssql> select password from user$ where name= ' SYS '; PASSWORD------------------------------------------------------------c53b64bc84353309

Test directly modify the SYS user password to see what happens to the password file

#修改SYS密码成功, the password hash value is different from the previous [email protected]>alter user sys identified by oracle; User altered. [Email protected]>select password from user$ where name= ' SYS '; PASSWORD------------------------------------------------------------------------------------------8a8f025737a9097a #查看密码文件, the password file is also updated when the SYS password is modified [[email protected] dbs]$ ls -l orapworcl -rw-r-----  1 oracle oinstall 1536 dec 26 21:39 orapworcl# Test Telnet, You cannot log in to the database using the Luoxi password, but the modified sys password can log in to the database [c:\~]$ sqlplus sys/[email protected]/orcl as  sysdbasql*plus: release 12.1.0.1.0 production on  Monday  12 Month  26 21:41:50  2016Copyright  (c)  1982, 2013, oracle.  all rights reserved. error:ora-01017: invalid username/password; logon denied[c:\~]$ sqlplus sys/[ Email protected]/orcl as sysdbasql*plus: release 12.1.0.1.0 production on  Monday  12 Month  26 21:41:58  2016Copyright  (c)  1982, 2013, oracle.  all rights reserved. Connect to:  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -  64bit productionwith the partitioning, olap, data mining and real  Application Testing optionsSQL>

Summary: When you modify the sys password, the password in the password file is also modified to be consistent; if it is a manually created password file, the password in the password file can be the same as the SYS password and will not affect remote logins. However, it is recommended that the full sys password is consistent with the password file in order to avoid the need for emergency remote login password is not correct, resulting in maintenance problems.

There are also related documents on the query MOS:Password or SYS doesn ' t Match the Hash Value in user$ (document ID 2139304.1)

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1886333

The relationship between the Oracle password file and the user's password