擁有sysdba許可權使用者,在資料庫中和密碼檔案中的密碼關係

super_sky發表於2013-12-25
擁有sysdba許可權使用者,在資料庫中和密碼檔案中的密碼關係

測試系統:oracle11g 11.2.0.3

環境配置:
在sqlnet.ora中,將
SQLNET.AUTHENTICATION_SERVICES= (NTS)修改為NONE
修改後為
SQLNET.AUTHENTICATION_SERVICES= (NONE),目的是取消系統登入驗證

結論:
無論是修改orapwd檔案還是通過alter user 來修改sys使用者的密碼,密碼是一起來變化的。

連線測試
C:\Users\thinkpad>sqlplus sys/oracle@db as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 25 16:00:31 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

1 修改orapwd的密碼
[oracle@r11g dbs]$ rm -f orapwdb
[oracle@r11g dbs]$ orapwd file=orapwdb password=sysoracle 

遠端連線:
C:\Users\thinkpad>sqlplus sys/oracle@db as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 25 16:04:13 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:

本地連線:
[oracle@r11g dbs]$ sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 25 16:16:18 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: ^C
[oracle@r11g dbs]$ 
[oracle@r11g dbs]$ sqlplus sys/sysoracle as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 25 16:16:35 2013
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, OLAP, Data Mining and Real Application Testing options
SQL> 

2 使用alter user sys identified by xxx 來修改sys使用者密碼
SQL> alter user sys identified by mssoracle;
User altered.

連線測試
C:\Users\thinkpad>sqlplus sys/oracle@db as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 25 16:11:02 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
C:\Users\thinkpad>
C:\Users\thinkpad>sqlplus sys/mssoracle@db as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 25 16:11:12 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

本地
[oracle@r11g dbs]$ sqlplus sys/sysoracle as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 25 16:18:45 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: ^C
[oracle@r11g dbs]$ 
[oracle@r11g dbs]$ sqlplus sys/mssoracle as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 25 16:18:56 2013
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, OLAP, Data Mining and Real Application Testing options
SQL> 

3 檢視密碼檔案
[oracle@r11g dbs]$ strings orapwdb
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A

SQL> alter user sys identified by moracle;
User altered.

[oracle@r11g dbs]$ strings orapwdb
]\[Z
ORACLE Remote Password file
INTERNAL
E568563E8635750E
5D7748DDCA524F13

通過這個可以看到使用alter user 來修改sys的密碼,密碼檔案中的內容也修改了。

4 刪除密碼檔案
[oracle@r11g dbs]$ rm -f orapwdb
[oracle@r11g dbs]$ 
[oracle@r11g dbs]$ ls
hc_db.dat  initdb.ora  init.ora  lkDB  spfiledb.ora

遠端將無法登入
C:\Users\thinkpad>sqlplus sys/oracle@db as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 25 16:45:52 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:

參考文件:
asktom:sys password change and orapwd file
hat is a bit dated but still true. 
If you create the password file with a password different than the sys password, that'll be the password you use to connect as sysdba over a network (the password in the password file is used for sysdba connections). The sys password to just connect as sys (something you wouldn't really ever have a need to do EVER) can be different. 
But when you alter the SYS password, they sync that up with the password file, as demonstrated. 
Since the password file is separate and distinct from the database - it does not attempt to update the database when you update the password file. 

asktom:Problem to connect as SYSDBA

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/11590946/viewspace-1064362/,如需轉載,請註明出處,否則將追究法律責任。

相關文章