SQLPLUS 的权限问题
来源：互联网 发布：java乘法表代码对齐 编辑：程序博客网 时间：2024/05/16 23:52
近的时间，在搞数据库的安全加固问题，关于数据库中关于sysdba权限的登录，通过最近的一些实践和测试，在这里记录一下：
数据库用sysdba登录的验证有两种方式， 一种是通过os认证，一种是通过密码文件验证;登录方式有两种，一种是在数据库主机直接登录(用os认证的方式)，一种是通过网络远程登录;需要设置的参 数有两个，一个是SQLNET.AUTHENTICATION_SERVICES，一个是REMOTE_LOGIN_PASSWORDFILE。
os认证：如果启用了os认证，以sysdba登录，那么我们只要用oracle软件的安装用户就能登录：sqlplus “/ as sysdba”。如果我们要禁用os认证，只利用密码文件登录，我们首先要有一个密码文件：
D:/oracle/ora92/database>orapwdfile=PWDoralocal.orapassword=mypasswordentries=10;
D:/oracle/ora92/database>
然后我们要把$ORACLE_HOME/network/admin/sqlnet.ora中设置：
SQLNET.AUTHENTICATION_SERVICES=none
注意一下，密码文件只在数据库启动的时候加载进去，一旦加载进去，密码文件就脱离了oracle管理，所以我们用orapwd新建密码文件后，里面指定的密码要在数据重启后才能生效：
D:/oracle/ora92/database>sqlplus"sys/mypasswordassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononFriMay1621:59:422008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01031:insufficientprivileges
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
###这里我们通过改SQLNET.AUTHENTICATION_SERVICES=(NTS)用os认证登录数据库：
sys@ORALOCAL(192.168.50.29)>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
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
ORACLEinstancestarted.
TotalSystemGlobalArea135338868bytes
FixedSize453492bytes
VariableSize109051904bytes
DatabaseBuffers25165824bytes
RedoBuffers667648bytes
Databasemounted.
Databaseopened.
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
DisconnectedfromOracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.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"/assysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononFriMay1622:03:592008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01031:insufficientprivileges
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
D:/oracle/ora92/database>
D:/oracle/ora92/database>
D:/oracle/ora92/database>
D:/oracle/ora92/database>sqlplus"sys/mypasswordassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononFriMay1622:04:072008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
Connectedt
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
sys@ORALOCAL(192.168.50.29)>exit
在这里，我们看到这个新改的密码要数据库重启后加载才生效。同时我们看到，用os认证是无法登录的，但是通过网络(用@sid)是可以登录。
D:/oracle/ora92/database>sqlplus"/assysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1700:58:322008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01031:insufficientprivileges
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
D:/oracle/ora92/database>
D:/oracle/ora92/database>sqlplus"sys/mypasswordassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1700:59:152008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
Connectedt
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
sys@ORALOCAL(192.168.50.29)>
sys@ORALOCAL(192.168.50.29)>
sys@ORALOCAL(192.168.50.29)>exit
DisconnectedfromOracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
D:/oracle/ora92/database>sqlplus"sys/mypassword@oralocalassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1700:59:382008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
Connectedt
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.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这个时候数据库是无法登录的。D:/oracle/ora92 /database>sqlplus"sys/change_on_installassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1701:28:582008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
Connectedt
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
sys@ORALOCAL(192.168.50.29)>showparameterremote_login
NAMETYPEVALUE
-----------------------------------------------------------------------------
remote_login_passwordfilestringEXCLUSIVE
sys@ORALOCAL(192.168.50.29)>altersystemsetremote_login_passwordfile=nonescope=spfile;
Systemaltered.
Elapsed:00:00:00.01
sys@ORALOCAL(192.168.50.29)>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
sys@ORALOCAL(192.168.50.29)>startup
ORA-01031:insufficientprivileges
sys@ORALOCAL(192.168.50.29)>exit
C:/DocumentsandSettings/Administrator>sqlplus"/assysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1708:26:432008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01031:insufficientprivileges
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
C:/DocumentsandSettings/Administrator>sqlplus"sys/change_on_installassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1708:26:532008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
C:/DocumentsandSettings/Administrator>
C:/DocumentsandSettings/Administrator>sqlplus"sys/change_on_install@oralocalassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1708:27:032008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
C:/DocumentsandSettings/Administrator>
这里我们看到由于启用了 REMOTE_LOGIN_PASSWORDFILE=none，使用os认证，不用密码文件认证，必须将 SQLNET.AUTHENTICATION_SERVICES= none取消，不然是无法登录。我们改成SQLNET.AUTHENTICATION_SERVICES= (NTS)后再次测试。
###非oracle软件安装软件用户：###
C:/DocumentsandSettings/hejianmin>sqlplus"/assysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1720:15:132008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01031:insufficientprivileges
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
C:/DocumentsandSettings/hejianmin>
C:/DocumentsandSettings/hejianmin>sqlplus"sys/change_on_installassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1720:15:302008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01031:insufficientprivileges
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
C:/DocumentsandSettings/hejianmin>
C:/DocumentsandSettings/hejianmin>sqlplus"sys/change_on_install@oralocalassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1720:15:422008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01031:insufficientprivileges
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
C:/DocumentsandSettings/hejianmin>
###oracle软件安装用户####
C:/DocumentsandSettings/Administrator>sqlplus"/assysdba"
SQL*Plus:Release9.2.0.1.0-Productionon星期六5月1720:19:132008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
连接到:
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
sys@ORALOCAL(192.168.0.29)>exit
从Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production中断开
C:/DocumentsandSettings/Administrator>sqlplus"sys/change_on_installassysdba"
SQL*Plus:Release9.2.0.1.0-Productionon星期六5月1720:19:332008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
连接到:
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
sys@ORALOCAL(192.168.0.29)>exit
从Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production中断开
C:/DocumentsandSettings/Administrator>sqlplus"sys/change_on_install@oralocalassysdba"
SQL*Plus:Release9.2.0.1.0-Productionon星期六5月1720:19:452008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
连接到:
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
sys@ORALOCAL(192.168.0.29)>exit
从Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production中断开
C:/DocumentsandSettings/Administrator>sqlplus"11/22assysdba"
SQL*Plus:Release9.2.0.1.0-Productionon星期六5月1720:19:582008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
连接到:
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
sys@ORALOCAL(192.168.0.29)>
在这里我们看到由于用了os认证，在oracle安装用户下，无论用什么方式都能登录。非oracle用户无论用什么用户都无法登录。
如果REMOTE_LOGIN_PASSWORDFILE=exclusive且SQLNET.AUTHENTICATION_SERVICES= none时：
C:/DocumentsandSettings/Administrator>sqlplus"sys/change_on_installassysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1720:30:572008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
Connectedt
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
sys@ORALOCAL(192.168.0.29)>exit
DisconnectedfromOracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
C:/DocumentsandSettings/Administrator>
C:/DocumentsandSettings/Administrator>sqlplus"/assysdba"
SQL*Plus:Release9.2.0.1.0-ProductiononSatMay1720:31:042008
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
ERROR:
ORA-01031:insufficientprivileges
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
Enteruser-name:
ERROR:
ORA-01017:invalidusername/password;logondenied
SP2-0157:unabletoCONNECTtoORACLEafter3attempts,exitingSQL*Plus
C:/DocumentsandSettings/Administrator>
C:/DocumentsandSettings/Administrator>
总结：
(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”能登录
- SQLPLUS 的权限问题
- sqlplus的一个问题
- sqlplus 锁的问题。
- sqlplus的显示问题
- sqlplus 乱码问题的解决
- sqlplus不能初始化以及sqlplus自动关闭的问题
- oracle sqlplus中文显示乱码的问题
- sqlplus 汉字乱码问题的解决
- sqlplus 汉字乱码问题的解决
- sqlplus 汉字乱码问题的解决
- 解决sqlplus下方向键的问题
- 解决sqlplus下方向键的问题
- sqlplus 汉字乱码问题的解决
- 解决sqlplus下方向键的问题
- 解决sqlplus下方向键的问题
- oracle sqlplus中文显示乱码的问题
- 10g的sqlplus区分大小写问题
- SQLPLUS下出现显示问号的问题
- JSP数据和JavaScirpt数据交互使用问题的一种解决方法
- 关于union和struct的内存分配
- Lesson 71 He's awful！ 他讨厌透了！
- PHP 二维数组排序
- 在多线程系统中读取配置文件
- SQLPLUS 的权限问题
- 反向思维:房价要大跌,20090120立此存照
- 关于Microsoft Word 和 Microsoft NoteBook的趣事与启示
- 向左走，向右走
- 被征用电脑后的小风波
- mysql 处理时间函数
- 防止程序启动两次的方法CreateMutex()
- Change Reference to Value(将引用对象改变为实值对象)
- 房地产开发不是暴利行业,而是超级暴力行业.
