Oracle 密碼檔案 --==============================
-- Oracle 密碼檔案
一、密碼檔案
作用：主要進行DBA權限的身份認證
DBA使用者：具有sysdba，sysoper權限的使用者被稱為dba使用者。預設情況下sysdba角色中存在sys使用者，sysoper角色中存在system使用者
二、Oracle的兩種認證方式；
1.使用與作業系統內建的身份驗證
2.使用Oracle資料庫的密碼檔案進行身份認證
三、密碼檔案的位置
Linux下的存放位置：$ORACLE_HOME/dbs/orapw$ORACLE_SID
即：ORACLE_HOME/dbs/orapw<sid>
Linux下密碼檔案無擴充名
Windows下的存放位置:$ORACLE_HOME/database/PW%ORACLE_SID%.ora
兩種認證方式：類似于 SQL server中的windows認證和SQL server認證(作業系統認證和資料庫密碼檔案認證)，決定認證的方式取決于兩個參數，需注意的是
當oracle密碼檔案丢失時，DBA使用者采用密碼認證時是無效的，因為oracle會先去通路密碼檔案，驗證是否有效。
1. 參數remote_login_passwordfile = none | exclusive |shared，該參數儲存于參數檔案Pfile<ORACLE_SID>.ora(或Spfile<ORACLE_SID>)檔案中
none : 表示不接受密碼認證檔案，隻接受作業系統認證(OS Authentication)
exclusive :表示資料庫接受密碼認證檔案，同時，可将SYSDBA角色授權給其他使用者
shared ：表示資料庫接受密碼認證檔案，但SYSDBA角色無法授權給其他使用者
2. 參數SQLNET.AUTHENTICATION_SERVICES= none | all | ntf(windows)，該參數位于$ORACLE_HOME/network/admin/sqlnet.ora檔案中，當檔案中不存在該參數時，表示DBA使用者可以采用密碼認證和作業系統認證方式連接配接資料庫。
none : 表示關閉作業系統認證，隻接受密碼認證
all : 用于linux或unix平台，關閉本機密碼檔案認證，采用作業系統認證，但遠端<異機>可以使用密碼檔案認證
nts : 用于windows平台
以上兩個參數共同驗證SYS使用者的權限認證，兩者都必須滿足使用者才能正常通路資料庫，如下所示則sys使用者無論是本機還是遠端均不可用
不同的組合
SQLNET.AUTHENTICATION_SERVICES REMOT_LOGIN_PASSWORDFILE
none none
四、DBA使用者的連接配接方式
DBA連接配接資料庫的方式有以下幾種：
SQLPLUS / AS SYSDBA ：這是典型的作業系統認證，不需要listener程序；
SQLPLUS SYS/PASSWORD AS SYSDBA ：此種方法隻能連接配接本地資料庫，這裡我認為也屬于作業系統認證，當password不對時，使用者也能連接配接資料庫，不需要listener程序
SQLPLUS SYS/[email protected]_SID AS SYSDBA ：此種方法可通路本地資料庫，同時亦可遠端通路資料庫，屬于典型的密碼認證
五、示範：
1.在sqlnet.ora 中追加SQLNET.AUTHENTICATION_SERVICES = none */
[[email protected] ~]$ 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:
--------------------------------------------------------------------------------
[[email protected] ~]$ sqlplus sys/redhat as sysdba
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
[[email protected] admin]$ sqlplus / as sysdba
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>
--------------------------------------------------------------------------------------
[[email protected] admin]$ sqlplus sys/[email protected] as sysdba
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:
--注：此時可以使用遠端登陸。
--使用#符号将新增的SQLNET.AUTHENTICATION_SERVICES行注釋掉恢複到預設值
3.在spfile<ORACLE_SID>.ora中設定參數remote_login_passwordfile=none
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:47:10 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[[email protected] ~]$ sqlplus sys/181818 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:47:20 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[[email protected] ~]$ sqlplus sys/[email protected] as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:47:31 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
4.在spfile<ORACLE_SID>參數檔案中設定參數remote_login_passwordfile=EXCLUSIVE（預設值）
[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:58:58 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[email protected] ~]$ sqlplus sys/181818 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:58:58 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[email protected] ~]$ sqlplus sys/[email protected] as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:58:58 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
六、密碼檔案的建立：orapwd
[[email protected] ~]$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and
force - whether to overwrite existing file (opt),
OPERs (opt),
There are no spaces around the equal-to (=) character.
--修改密碼：
[[email protected] ~]$ cd $ORACLE_HOME/dbs
[[email protected] dbs]$ ll orapworcl
-rw-r----- 1 oracle oinstall 1536 Apr 7 15:50 orapworcl
[[email protected] dbs]$ orapwd file=orapworcl password=oracle force=y
[[email protected] dbs]$ sqlplus sys/[email protected] 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>
--将密碼改回原來的密碼
[[email protected] dbs]$ orapwd file=orapworcl password=redhat
OPW-00005: File with same name exists - please delete or rename
[[email protected] dbs]$ orapwd file=orapworcl password=redhat force=y
[[email protected] dbs]$ rm orapworcl
[[email protected] dbs]$ orapwd file=orapworcl password=redhat
--示範将entries 改為，然後将多個使用者設定為sysdba或sysoper
[[email protected] dbs]$ orapwd file=orapworcl password=redhat entries=1
[[email protected] dbs]$ strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
F7AC0C5E9C3C37AB
E100B964899CDDDF
--建立PL/SQL 增加個新使用者
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
*
ERROR at line 1:
ORA-01996: GRANT failed: password file '' is full
ORA-06512: at line 3
--再次檢視orapworcl發現多出了行，即當設定為的時候多出了個使用者。原因是該密碼檔案是二進制檔案，按矩陣計算可存放多少
[[email protected] dbs]$ strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
F7AC0C5E9C3C37AB
E100B964899CDDDF
3E81B724A296E296
668509DF9DD36B43
9CE6AF1E3F609FFC
7E19965085C9ED47
--注意不要輕易删掉密碼檔案，這樣會将其他賬戶的資訊也删除
[[email protected] dbs]$ strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
F7AC0C5E9C3C37AB
E100B964899CDDDF
--當sys密碼不記得可以使用OS系統身份認證登陸到sqlplus,再使用alter user修改密碼
SQL> alter user sys identified by oracle;
User altered
--再次檢視密碼檔案與上一次對比，已經發生變化
SQL> ho strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
--通過授予權限來修改密碼，密碼檔案中多出了scott的資訊
SQL> grant sysdba to scott;
Grant succeeded.
SQL> ho strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
SCOTT
F894844C34402B67
--注意此處中登陸後，顯示的賬戶資訊還是sys,而不是scott,但此時的scott已經具備了sys權限
[[email protected] dbs]$ sqlplus scott/[email protected] 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"
SQL> select * from system_privilege_map where name like '%SYS%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-3 ALTER SYSTEM 0
-4 AUDIT SYSTEM 0
-83 SYSDBA 0
-84 SYSOPER 0
--下面的連結是兩者不同的權限說明
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
--下面示範了使用不同的角色來登陸
SQL> conn scott/[email protected] as sysdba
Connected.
SQL> conn scott/[email protected] as sysoper
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
SQL> conn user1/[email protected] as sysdba
ERROR:
ORA-01031: insufficient privileges
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)