Oracle 密码文件
為什么80%的碼農(nóng)都做不了架構(gòu)師？>>> ??
--==============================
-- Oracle 密碼文件
--==============================
/*
一、密碼文件
??? 作用：主要進(jìn)行DBA權(quán)限的身份認(rèn)證
??? DBA用戶：具有sysdba，sysoper權(quán)限的用戶被稱為dba用戶。默認(rèn)情況下sysdba角色中存在sys用戶，sysoper角色中存在system用戶
???
二、Oracle的兩種認(rèn)證方式；
??? 1.使用與操作系統(tǒng)集成的身份驗(yàn)證
??? 2.使用Oracle數(shù)據(jù)庫(kù)的密碼文件進(jìn)行身份認(rèn)證
?
三、密碼文件的位置
??? Linux下的存放位置：$ORACLE_HOME/dbs/orapw$ORACLE_SID
????????????????? 即：ORACLE_HOME/dbs/orapw<sid>
??? Windows下的存放位置:$ORACLE_HOME/database/PW%ORACLE_SID%.ora
?
??? 密碼文件查找的順序
??? --->orapw<sid>--->orapw--->Failure
?
??? 兩種認(rèn)證方式：類型與SQL server中的windows認(rèn)證和SQL server認(rèn)證
??? 決定在兩個(gè)參數(shù)中
??? 1.remote_login_passwordfile = none | exclusive |shared? 位于$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora參數(shù)文件中
??? ?? none : 不使用密碼文件認(rèn)證
??? ?? exclusive :要密碼文件認(rèn)證，自己獨(dú)占使用(默認(rèn)值)
??? ?? shared ：要密碼文件認(rèn)證，不同實(shí)例dba用戶可以共享密碼文件
??? ??
??? 2. $ORACLE_HOME/network/admin/sqlnet.ora?
??? ?? SQLNET.AUTHENTICATION_SERVICES = none | all | ntf(windows)
??? ?? none : 表示關(guān)閉操作系統(tǒng)認(rèn)證，只能密碼認(rèn)證
??? ?? all : 用于linux或unix平臺(tái)，關(guān)閉本機(jī)密碼文件認(rèn)證，采用操作系統(tǒng)認(rèn)證，但遠(yuǎn)程<異機(jī)>可以使用密碼文件認(rèn)證
??? ?? nts : 用于windows平臺(tái)
??? ??
??? 不同的組合
??? ? 1?????????? 2
??? none????????? none????? sys用戶無(wú)論是本機(jī)還是遠(yuǎn)程均不可用
??
??? 判斷當(dāng)前使用的是操作系統(tǒng)認(rèn)證還是密碼認(rèn)證
???
四、演示：
??? 1.在sqlnet.ora 中追加SQLNET.AUTHENTICATION_SERVICES = none? */
??? [oracle@robinson ~]$ sqlplus / as sysdba /*登陸失敗*/
?
??? SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 10:41:28 2010
?
??? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
??? ERROR:
??? ORA-01031: insufficient privileges
??? Enter user-name:
??? --------------------------------------------------------------------------------
??? [oracle@robinson ~]$ sqlplus sys/redhat as sysdba /*使用密碼文件認(rèn)證，登陸成功*/
?
??? SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 10:42:35 2010
?
??? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
?
??? Connected to:
??? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
??? With the Partitioning, OLAP and Data Mining options
?
??? SQL>
??? --=================================================================================
???
??? 2.將SQLNET.AUTHENTICATION_SERVICES的值改為all
?
??? [oracle@robinson admin]$ sqlplus / as sysdba /*采用本機(jī)認(rèn)證可以登陸*/
?
??? SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 10:46:55 2010
?
??? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
?
??? Connected to:
??? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
??? With the Partitioning, OLAP and Data Mining options
?
??? SQL>
??? --------------------------------------------------------------------------------------
??? [oracle@robinson admin]$ sqlplus sys/redhat@orcl as sysdba /*使用密碼文件登陸認(rèn)證失敗*/
?
??? SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 10:48:35 2010
?
??? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
??? ERROR:
??? ORA-12641: Authentication service failed to initialize
?
??? Enter user-name:
?
??? --注：此時(shí)可以使用遠(yuǎn)程登陸。?
?
??? --使用#符號(hào)將新增的SQLNET.AUTHENTICATION_SERVICES行注釋掉恢復(fù)到缺省值
?
??? /*
五、密碼文件的建立：orapwd? */
??? [oracle@robinson ~]$ orapwd
??? Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>
?
??? ? where
?????? file - name of password file (mand),? /*密碼文件的名字orapw<sid>*/
?????? password - password for SYS (mand),?? /*sys用戶的密碼*/
?????? entries - maximum number of distinct DBA and? /*可以有多少個(gè)sysdba,sysoper權(quán)限用戶放到密碼文件中去，去掉重復(fù)記錄*/
????????????????????????????????????????????? ? /*注意entries中存放的個(gè)數(shù)但不是實(shí)際個(gè)數(shù)，這個(gè)是二進(jìn)制數(shù)據(jù)*/??
?????? force - whether to overwrite existing file (opt), /*10g新增的參數(shù)，默認(rèn)值為n ,y表示允許覆蓋*/
??? OPERs (opt),
??? ? There are no spaces around the equal-to (=) character.
??? ?
??? --修改密碼：
??? [oracle@robinson ~]$ cd $ORACLE_HOME/dbs
??? [oracle@robinson dbs]$ ll orapworcl
??? -rw-r----- 1 oracle oinstall 1536 Apr? 7 15:50 orapworcl
??? [oracle@robinson dbs]$ orapwd file=orapworcl password=oracle force=y
??? [oracle@robinson dbs]$ sqlplus sys/oracle@orcl as sysdba
?
??? SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 11:34:09 2010
?
??? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
?
??? Connected to:
??? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
??? With the Partitioning, OLAP and Data Mining options
?
??? SQL>??
??? --將密碼改回原來(lái)的密碼
??? [oracle@robinson dbs]$ orapwd file=orapworcl password=redhat
?
??? OPW-00005: File with same name exists - please delete or rename
??? [oracle@robinson dbs]$ orapwd file=orapworcl password=redhat force=y
??? [oracle@robinson dbs]$ rm orapworcl? /*刪除密碼文件*/
??? [oracle@robinson dbs]$ orapwd file=orapworcl password=redhat /*重建密碼文件*/
?
??? --演示將entries 改為，然后將多個(gè)用戶設(shè)置為sysdba或sysoper
??? [oracle@robinson dbs]$ orapwd file=orapworcl password=redhat entries=1
??? [oracle@robinson dbs]$ strings orapworcl
??? ]/[Z
??? ORACLE Remote Password file
??? INTERNAL
??? F7AC0C5E9C3C37AB
??? E100B964899CDDDF
?
??? --創(chuàng)建PL/SQL 增加個(gè)新用戶
??? SQL> begin
??? ? 2? for i in 1..20 loop
??? ? 3? execute immediate 'create user u'||i||' identified by u'||i||'';
??? ? 4? end loop;
??? ? 5? end;
??? ? 6? /
??? --將新用戶賦予sysdba角色
??? PL/SQL procedure successfully completed.
?
??? SQL> begin
??? ? 2? for i in 1..20 loop
??? ? 3? execute immediate 'grant sysdba to u'||i||'';
??? ? 4? end loop;
??? ? 5? end;
??? ? 6? /
??? begin? /*得到和密碼文件相關(guān)的錯(cuò)誤提示*/
??? *
??? ERROR at line 1:
??? ORA-01996: GRANT failed: password file '' is full
??? ORA-06512: at line 3
?
??? --再次查看orapworcl發(fā)現(xiàn)多出了行，即當(dāng)設(shè)置為的時(shí)候多出了個(gè)用戶。原因是該密碼文件是二進(jìn)制文件，按矩陣計(jì)算可存放多少
??? [oracle@robinson dbs]$ strings orapworcl
??? ]/[Z
??? ORACLE Remote Password file
??? INTERNAL
??? F7AC0C5E9C3C37AB
??? E100B964899CDDDF
??? 3E81B724A296E296
??? 668509DF9DD36B43
??? 9CE6AF1E3F609FFC
??? 7E19965085C9ED47
?
?
??? --注意不要輕易刪掉密碼文件，這樣會(huì)將其他賬戶的信息也刪除
?
??? /*
六、導(dǎo)致密碼文件內(nèi)容修改的幾種方式：
??? 1.使用orapwd建立，修改密碼文件，不建議使用
??? 2.使用alter user sys identified by <>
??? 3.使用grant sysdba to <> 或grant sysoper to <> 或revoke sysdba |sysoper from <>
?
七、查看密碼文件內(nèi)容 */
?
??? [oracle@robinson dbs]$ strings orapworcl
??? ]/[Z
??? ORACLE Remote Password file
??? INTERNAL
??? F7AC0C5E9C3C37AB
??? E100B964899CDDDF
?
??? --當(dāng)sys密碼不記得可以使用OS系統(tǒng)身份認(rèn)證登陸到sqlplus,再使用alter user修改密碼
??? SQL> alter user sys identified by oracle;
?
??? User altered
??? --再次查看密碼文件與上一次對(duì)比，已經(jīng)發(fā)生變化
??? SQL> ho strings orapworcl
??? ]/[Z
??? ORACLE Remote Password file
??? INTERNAL
??? AB27B53EDC5FEF41
??? 8A8F025737A9097A
?
?
??? --通過(guò)授予權(quán)限來(lái)修改密碼，密碼文件中多出了scott的信息
??? SQL> grant sysdba to scott;
?
??? Grant succeeded.
?
??? SQL> ho strings orapworcl
??? ]/[Z
??? ORACLE Remote Password file
??? INTERNAL
??? AB27B53EDC5FEF41
??? 8A8F025737A9097A
??? SCOTT
??? F894844C34402B67
?
?
??? --注意此處中登陸后，顯示的賬戶信息還是sys,而不是scott,但此時(shí)的scott已經(jīng)具備了sys權(quán)限
??? [oracle@robinson dbs]$ sqlplus scott/tiger@orcl as sysdba
?
??? SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 11:56:09 2010
?
??? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
?
??? Connected to:
??? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
??? With the Partitioning, OLAP and Data Mining options
?
??? SQL> show user
??? USER is "SYS"
?
??? /*
八、sysdba 與sysoper的區(qū)別*/
??? SQL> select * from system_privilege_map where name like '%SYS%';
?
??? ?PRIVILEGE NAME?????????????????????????????????????? PROPERTY
??? ---------- ---------------------------------------- ----------
?????????? -3 ALTER SYSTEM?????????????????????????????????????
?????????? -4 AUDIT SYSTEM?????????????????????????????????????
?????? ?? -83 SYSDBA???????????????????????????????????????????
?????? ?? -84 SYSOPER??????????????????????????????????????????
?????? ??
??? --下面的鏈接是兩者不同的權(quán)限說(shuō)明? ??
??? http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#sthref137
??? The manner in which you are authorized to use these privileges depends upon the method of authentication that you use.
?
??? When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema,
??? not with the schema that is generally associated with your username.
??? For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
??? --兩者的schema不同
??? SQL> show user
??? USER is "SYS"
??? SQL> conn / as sysoper
??? Connected.
??? SQL> show user
??? USER is "PUBLIC"
?
??? --查看密碼文件視圖，可以得到哪些用戶為sysdba,哪些用戶為sysoper
??? SQL> select * from v$pwfile_users;
?
??? USERNAME?????????????????????? SYSDB SYSOP
??? ------------------------------ ----- -----
??? SYS??????????????????????????? TRUE? TRUE
??? SCOTT????????????????????????? TRUE? FALSE
??? USER1????????????????????????? FALSE TRUE
??? --下面演示了使用不同的角色來(lái)登陸
??? SQL> conn scott/tiger@orcl as sysdba
??? Connected.
??? SQL> conn scott/tiger@orcl as sysoper? /*scott的sysop列為false*/
??? ERROR:
??? ORA-01031: insufficient privileges
?
?
??? Warning: You are no longer connected to ORACLE.
??? SQL> conn user1/user1@orcl as sysdba
??? ERROR:
??? ORA-01031: insufficient privileges
?
?
??? SQL> conn user1/user1 as sysoper
??? Connected.
??? /*
九、更多 */
Oracle 數(shù)據(jù)庫(kù)實(shí)例啟動(dòng)關(guān)閉過(guò)程
?
Oracle 10g SGA 的自動(dòng)化管理
?
使用OEM,SQL*Plus,iSQL*Plus 管理Oracle實(shí)例
?
Oracle實(shí)例和Oracle數(shù)據(jù)庫(kù)(Oracle體系結(jié)構(gòu))
?
SQL 基礎(chǔ)-->常用函數(shù)
?
SQL基礎(chǔ)-->過(guò)濾和排序
?
SQL 基礎(chǔ)-->SELECT 查詢
?
?
原文鏈接： http://blog.csdn.net/robinson_0612/article/details/5611672
轉(zhuǎn)載于:https://my.oschina.net/dtec/blog/47379
總結(jié)
以上是生活随笔為你收集整理的Oracle 密码文件的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 轻松得到C# ADO.NET的各种数据库
- 下一篇： 团队文化之表扬和批评