以sysdba角色登陸oracle的兩種認證方式測試備記

  今天在客戶現場處理了關於以sysdba角色進入oracle報錯的相關問題,所以簡要測試整理了一個小文件,
sysdba角色(就類似於os的root使用者,可以理解為資料庫上的超級管理員),進入oracle(用sqlplus),

以sysdba角色進入oracle有兩種方式(認證方式):
1,作業系統方式 ---就是以安裝oracle軟體的使用者進入oracle系統(可以理解為只要
 (這種方式也是你配置完oracle之後預設的方式,但這種方式不安全,因為它以sysdba角色進入,不用你輸入密碼)
2,密碼檔案方式(也叫資料庫方式) ---就是採用密碼檔案(建議在生產庫中採用這種方式),另:密碼檔案在:$ORACLE_HOME/dbs下,檔名格式為:orapw$ORACLE_SID


下為測試過程:
[oracle@rhel5 ~]$ id
uid=501(oracle) gid=500(dba) groups=500(dba)
[oracle@rhel5 ~]$ cd /home/oracle/product/10.2.0/db_1/network/admin  ---sqlnet.ora檔案儲存在這個目錄下
[oracle@rhel5 admin]$ more sqlnet.ora   --這個檔案可能不存在,你可以vi新建一個,新增以下引數
sqlnet.authentication_services=(NONE)   -----這個引數就是控制以sysdba角色進入oracle是採取何種方式(同上:作業系統或是密碼檔案方式)
                                             值為NONE表明採用密碼檔案方式認證(但你要有一個密碼檔案)
[oracle@rhel5 admin]$ sqlplus sys/capitek as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:11:19 2009

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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@rhel5 admin]$ sqlplus sys/zxy as sysdba  ---當你sys使用者密碼不對,就會提示拒絕登陸,這樣安全性就大為提升了

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:11:27 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@rhel5 dbs]$ cd $ORACLE_HOME/dbs   ---密碼檔案在這個目錄下(預設)
[oracle@rhel5 dbs]$ orapwd file=./orapwtarget password=hand force=y  ---建立或變更密碼檔案的命令,file就是密碼檔案儲存在哪個目錄下,password就是sys使用者的密碼,entries就是可以有多少個使用者以sys使用者登陸oracle,force就是是否強制覆蓋已經存在的密碼檔案
 
[oracle@rhel5 dbs]$ sqlplus sys/system as sysdba  --可以發現變更密碼檔案後(sys使用者密碼變為hand),馬上就生效了,再以system登陸就報錯了

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:52:00 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
[oracle@rhel5 dbs]$
[oracle@rhel5 dbs]$ sqlplus sys/hand as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:52:10 2009

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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@rhel5 dbs]$ sqlplus sys/hand as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:52:16 2009

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> alter user sys identified by other;  --利用命令變更sys使用者密碼

User altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@rhel5 dbs]$ sqlplus sys/hand as sysdba   --再以hand密碼登陸就報錯,說明透過上述命令變更資訊已經寫入到密碼檔案中

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:52:36 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@rhel5 dbs]$ sqlplus sys/other as sysdba  --以other登陸,正常進入oracle(對應上面)

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:52:42 2009

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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$pwfile_users;   --查詢這個動態效能檢視,可以得到哪些使用者使用了密碼檔案方式登陸oracle

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE






[oracle@rhel5 dbs]$
[oracle@rhel5 admin]$ more sqlnet.ora
#sqlnet.authentication_services=(NONE)  --註解它,就是採用作業系統方式認證
[oracle@rhel5 admin]$ sqlplus '/as sysdba'  --你以sysdba角色進入oracle,就可以不用輸入密碼或者給一個錯密碼都可以進去,這個就比較可怕了,安全方面有問題了

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:29:39 2009

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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@rhel5 admin]$ sqlplus sys/ca as sysdba  --給sys使用者隨便給一個錯密碼,雖然可以進去

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 10 12:37:03 2009

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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@rhel5 admin]$ more /etc/group   ---當你註解掉dba這個組,雖然此是仍是作業系統認證,你再不輸入密碼也是進不去oracle的
root:x:0:root
bin:x:1:root,bin,daemon
daemon:x:2:root,bin,daemon
sys:x:3:root,bin,adm
adm:x:4:root,adm,daemon
tty:x:5:
disk:x:6:root
lp:x:7:daemon,lp
mem:x:8:
kmem:x:9:
wheel:x:10:root
mail:x:12:mail
news:x:13:news
uucp:x:14:uucp
man:x:15:
games:x:20:
gopher:x:30:
dip:x:40:
ftp:x:50:
lock:x:54:
nobody:x:99:
users:x:100:
rpm:x:37:
dbus:x:81:
utmp:x:22:
utempter:x:35:
avahi:x:70:
mailnull:x:47:
smmsp:x:51:
nscd:x:28:
floppy:x:19:
vcsa:x:69:
haldaemon:x:68:
rpc:x:32:
rpcuser:x:29:
nfsnobody:x:65534:
sshd:x:74:
pcap:x:77:
ntp:x:38:
slocate:x:21:
gdm:x:42:
xfs:x:43:
sabayon:x:86:
screen:x:84:
#dba:x:500:  --請看這行,註解此行和刪除此行一個道理(作用就是讓oracle使用者從dba組中脫離出來)
[oracle@rhel5 admin]$


總結:
1,以sysdba進入oracle兩大方式:作業系統方式和密碼檔案方式
2,密碼檔案方式相對安全性高一些(但是密碼檔案必須存在)
3,作業系統方式認證就是,登陸oracle的作業系統使用者(以sysdba角色)必須在dba組中,否則作業系統方式不起作用
4,當然為了更高業務及oracle安全性,可以採用oracle advanced security or label vault之類的選件(供參)