講解Oracle數據庫的sysdba權限登錄問題

sysdba權限的登錄測試:

數據庫用sysdba登錄的驗證有兩種方式,一種是通過os認證,一種是通過密碼文件驗證;登錄方式有兩種,一種是在數據庫主機直接登錄(用os認證的方式),一種是通過網絡遠程登錄;需要設置的參數有兩個,一個是SQLNET.AUTHENTICATION_SERVICES,一個是REMOTE_LOGIN_PASSWORDFILE。

os認證:假如啓用了os認證,以sysdba登錄,那麽只需要使用oracle軟件的安裝用戶就能登錄:sqlplus 「/ as sysdba」。如果我們要禁用os認證,只利用密碼文件登錄,我們首先要有一個密碼文件:

D:\oracle\ora92\database>orapwd file=PWDoralocal.ora password=mypassword entries=10;

D:\oracle\ora92\database>

然後我們要把$ORACLE_HOME/network/admin/sqlnet.ora中設置:

SQLNET.AUTHENTICATION_SERVICES= none

大家需要注意,密碼文件只在數據庫啓動的時候加載進去,一旦加載進去,密碼文件就脫離了oracle管理,所以在你使用orapwd新建密碼文件後,裏面指定的密碼需要在數據重啓後才能發生作用:

D:\oracle\ora92\database>sqlplus "sys/mypassword as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 16 21:59:42 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

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

### 這裏我們通過改SQLNET.AUTHENTICATION_SERVICES= (NTS)用os認證登錄數據庫:

sys@ORALOCAL(192.168.50.29)> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@ORALOCAL(192.168.50.29)>

sys@ORALOCAL(192.168.50.29)>

sys@ORALOCAL(192.168.50.29)>

sys@ORALOCAL(192.168.50.29)> startup

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

sys@ORALOCAL(192.168.50.29)>

sys@ORALOCAL(192.168.50.29)>

sys@ORALOCAL(192.168.50.29)>

sys@ORALOCAL(192.168.50.29)> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

D:\oracle\ora92\database>

D:\oracle\ora92\database>

D:\oracle\ora92\database>

### 我們把SQLNET.AUTHENTICATION_SERVICES= (NTS)改回去。

D:\oracle\ora92\database>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 16 22:03:59 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

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

D:\oracle\ora92\database>

D:\oracle\ora92\database>

D:\oracle\ora92\database>

D:\oracle\ora92\database>sqlplus "sys/mypassword as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 16 22:04:07 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.50.29)> exit

在這裏,我們看到這個新改的密碼要數據庫重啓後加載才生效。同時我們看到,用os認證是無法登錄的,但是通過網絡(用@sid)是可以登錄。

D:\oracle\ora92\database>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 00:58:32 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

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

D:\oracle\ora92\database>

D:\oracle\ora92\database>sqlplus "sys/mypassword as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 00:59:15 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.50.29)>

sys@ORALOCAL(192.168.50.29)>

sys@ORALOCAL(192.168.50.29)> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

D:\oracle\ora92\database>sqlplus "sys/mypassword@oralocal as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 00:59:38 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.50.29)>

至此,我們已經實現不用os認證(sqlplus 「/ as sysdba」的方式登錄不了)。那麽我們怎麽限制網絡方面利用sysdba遠程登錄呢?我們可以設置初始化文件中的REMOTE_LOGIN_PASSWORDFILE=none。

注意,當REMOTE_LOGIN_PASSWORDFILE=none時,這個參數生效需要重啓數據庫,並且,一旦啓用這個參數,將使用操作系統認證,不使用口令文件。因此如果REMOTE_LOGIN_PASSWORDFILE=none且SQLNET.AUTHENTICATION_SERVICES= none這個時候數據庫是無法登錄的。

[coolcode lang=」sql」 linenum=」off」]

D:\oracle\ora92\database>sqlplus 「sys/change_on_install as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 01:28:58 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.50.29)> show parameter remote_login

NAME TYPE VALUE

———————————— ———– ——————————

remote_login_passwordfile string EXCLUSIVE

sys@ORALOCAL(192.168.50.29)> alter system set remote_login_passwordfile=none scope=spfile;

System altered.

Elapsed: 00:00:00.01

sys@ORALOCAL(192.168.50.29)> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@ORALOCAL(192.168.50.29)> startup

ORA-01031: insufficient privileges

sys@ORALOCAL(192.168.50.29)>exit

C:\Documents and Settings\Administrator>sqlplus 「/ as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 08:26:43 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

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

C:\Documents and Settings\Administrator>sqlplus 「sys/change_on_install as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 08:26:53 2008

Copyright (c) 1982, 2002, Oracle Corporation. 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

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>sqlplus 「sys/change_on_install@oralocal as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 08:27:03 2008

Copyright (c) 1982, 2002, Oracle Corporation. 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

C:\Documents and Settings\Administrator>

[coolcode]

這裏我們看到由于啓用了REMOTE_LOGIN_PASSWORDFILE=none,使用os認證,不用密碼文件認證,必須將SQLNET.AUTHENTICATION_SERVICES= none取消,不然是無法登錄。我們改成SQLNET.AUTHENTICATION_SERVICES= (NTS)後再次測試。

[coolcode lang=」sql」 linenum=」off」]

### 非oracle軟件安裝軟件用戶:###

C:\Documents and Settings\hejianmin>sqlplus 「/ as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 20:15:13 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

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

C:\Documents and Settings\hejianmin>

C:\Documents and Settings\hejianmin>sqlplus 「sys/change_on_install as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 20:15:30 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

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

C:\Documents and Settings\hejianmin>

C:\Documents and Settings\hejianmin>sqlplus 「sys/change_on_install@oralocal as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 20:15:42 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

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

C:\Documents and Settings\hejianmin>

### oracle 軟件安裝用戶 ####

C:\Documents and Settings\Administrator>sqlplus 「/ as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 5月 17 20:19:13 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

連接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.0.29)> exit

從Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production中斷開

C:\Documents and Settings\Administrator>sqlplus 「sys/change_on_install as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 5月 17 20:19:33 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

連接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.0.29)> exit

從Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production中斷開

C:\Documents and Settings\Administrator>sqlplus 「sys/change_on_install@oralocal as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 5月 17 20:19:45 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

連接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.0.29)> exit

從Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production中斷開

C:\Documents and Settings\Administrator>sqlplus 「11/22 as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 5月 17 20:19:58 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

連接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.0.29)>

[/coolcode]

在這裏我們看到由于用了os認證,在oracle安裝用戶下,無論用什麽方式都能登錄。非oracle用戶無論用什麽用戶都無法登錄。

如果REMOTE_LOGIN_PASSWORDFILE=exclusive且SQLNET.AUTHENTICATION_SERVICES= none時:

[coolcode lang=」sql」 linenum=」off」]

C:\Documents and Settings\Administrator>sqlplus 「sys/change_on_install as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 20:30:57 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

sys@ORALOCAL(192.168.0.29)> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>sqlplus 「/ as sysdba」

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 17 20:31:04 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

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

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>

[/coolcode]

結論:

(1)REMOTE_LOGIN_PASSWORDFILE=none且SQLNET.AUTHENTICATION_SERVICES= none:

oracle安裝用戶本地sqlplus 「/ as sysdba」無法登錄

非oracle安裝用戶本機sqlplus 「sys/change_on_install as sysdba」無法登錄

非oracle安裝用戶遠程sqlplus 「/ as sysdba_on_install@sid as sysdba」無法登錄

(2)REMOTE_LOGIN_PASSWORDFILE=exclusive且SQLNET.AUTHENTICATION_SERVICES= none:

oracle安裝用戶本地sqlplus 「/ as sysdba」無法登錄

非oracle安裝用戶本機sqlplus 「sys/change_on_install as sysdba」能登錄

非oracle安裝用戶遠程sqlplus 「/ as sysdba_on_install@sid as sysdba」能登錄

(3)REMOTE_LOGIN_PASSWORDFILE=none且SQLNET.AUTHENTICATION_SERVICES= (NTS):

oracle安裝用戶本地sqlplus 「/ as sysdba」能登錄

非oracle安裝用戶本機sqlplus 「sys/change_on_install as sysdba」無法登錄

非oracle安裝用戶遠程sqlplus 「/ as sysdba_on_install@sid as sysdba」無法登錄

(4)REMOTE_LOGIN_PASSWORDFILE=exclusive且SQLNET.AUTHENTICATION_SERVICES= (NTS):

oracle安裝用戶本地sqlplus 「/ as sysdba」能登錄

非oracle安裝用戶本機sqlplus 「sys/change_on_install as sysdba」能登錄

非oracle安裝用戶遠程sqlplus 「/ as sysdba_on_install@sid as sysdba」能登錄

 
講解Oracle數據庫的全文索引設置步驟
一.創建數據庫 1、使用dbassist創建數據庫時要選擇jserver和intermedia兩個選項。 2、檢查你的數據庫是否安裝了intermedia,可以通過檢查是否有ctxsys用戶和ctxapp角色(role). 3、如果沒有這個用戶和角色,意味著數...查看完整版>>講解Oracle數據庫的全文索引設置步驟
 
解決使用ASP無法連接ORACLE 9i數據庫的問題
今天,在一台WIN2K SERVER 服務器上,通過ASP安裝在本機的 Oracle9i 數據庫時,發現錯誤,根本無法連接上數據庫。其錯誤描述如下: Microsoft OLE DB Provider for ODBC Drivers 錯誤 '80004005' [ODBC 驅動程...查看完整版>>解決使用ASP無法連接ORACLE 9i數據庫的問題
 
解決使用ASP無法連接 ORACLE 9i 數據庫的問題。
今天,在一台WIN2K SERVER 服務器上,通過ASP安裝在本機的 Oracle9i 數據庫時,發現錯誤,根本無法連接上數據庫。其錯誤描述如下:Microsoft OLE DB Provider for ODBC Drivers 錯誤 '80004005' [ODBC 驅動程序 管理...查看完整版>>解決使用ASP無法連接 ORACLE 9i 數據庫的問題。
 
全面解析Oracle數據庫的系統和對象權限
Oracle數據庫的系統和對象權限: alter any cluster 修改任意簇的權限   alter any index 修改任意索引的權限   alter any role 修改任意角色的權限   alter any sequence 修改任意序列的權限   alter any sn...查看完整版>>全面解析Oracle數據庫的系統和對象權限
 
Oracle 9i數據庫的用戶創建以及權限分配
1.數據庫安裝時的參數設定 下文中的數據庫版本爲Oracle 9i(9.2.0),安裝數據庫時,數據庫系統會創建一個數據庫實例,其中:安裝目錄選爲:\oracle,數據庫名與數據庫SID號都輸入:ora9i ,其中的字符集必須選爲:ZHS...查看完整版>>Oracle 9i數據庫的用戶創建以及權限分配