请参考:http://space.itpub.net/7686358/viewspace-322976
WINDOWS环境下如何使用操作系统认证登录ORACLE数据库
说明:
通过语句CREATE USER DBUSER IDENTIFIED EXTERNALLY创建的OS认证的用户,实际上是OS USER与DB USER建立的映射关系,将OS用户也对应加入数据库用户中,这类的用户将由OS进行用户的验证,OS USER与DB USER之间的对应关系需告知ORACLE,这个对应关系就是由初始化参数OS_AUTHENT_PREFIX定义的。对应关系取决于注册表项OSAUTH_PREFIX_DOMAIN的值:
当OSAUTH_PREFIX_DOMAIN的值为TRUE
DB USER = OS_AUTHENT_PREFIX || ‘主机名\’ || OS USER
当OSAUTH_PREFIX_DOMAIN的值为FALSE
DB USER = OS_AUTHENT_PREFIX || OS USER
OS_AUTHENT_PREFIX的默认值是OPS$
一、涉及的参数
1.1注册表项:OSAUTH_PREFIX_DOMAIN(字符串键名)
OSAUTH_PREFIX_DOMAIN默认值为TRUE,该项位于注册表
Oracle9i版本:HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
Oracle10g版本:在HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraDb10g_home1
如果注册表中没有OSAUTH_PREFIX_DOMAIN这项,说明OSAUTH_PREFIX_DOMAIN为true,这时进行操作系统验证需要在用户名和os_authent_prefix之间增加'主机名\'信息;若在注册表中加入此参数OSAUTH_PREFIX_DOMAIN,并设其值为 FALSE,则其规则和UNIX系统中的操作系统验证一致,即:OS_AUTHENT_PREFIX的值||操作系统的用户名
1.2 SQLNET.ORA参数文件里,必须设置
SQLNET.AUTHENTICATION_SERVICES = (NTS)
否则,登录时系统会报:
SQL> CONNECT /
ERROR:
ORA-01004: default username feature not supported; logon denied
Warning: You are no longer connected to ORACLE.
二、实验环境
2.1运行环境
OS:Windows2003 ComputerName:LDY ORACLE:9201 DBNAME:LDYDB
2.2相关参数初值
A、SQL> $type F:\oracle\ora92\network\admin\sqlnet.ora
# SQLNET.ORA Network Configuration File: F:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
#SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
B、SQL> show parameter os
NAME TYPE VALUE
-----------------------------------------------
optimizer_index_cost_adj integer 100
os_authent_prefix string OPS$
os_roles boolean FALSE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
timed_os_statistics integer 0
三、实验步骤
3.1 OSAUTH_PREFIX_DOMAIN = TRUE
3.1.1以administrator 登录windows2003,并以SYS建立与ORACLE的连接
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 28 09:29:24 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> CONN / AS SYSDBA
已连接。
SQL> SHOW USER
USER 为"SYS"
SQL> SHOW PARAMETER OS_AUTHENT_PREFIX
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string OPS$
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
SCOTT
OUTLN
WMSYS
ORDSYS
ORDPLUGINS
MDSYS
CTXSYS
XDB
USERNAME
------------------------------
ANONYMOUS
WKSYS
WKPROXY
ODM
ODM_MTR
OLAPSYS
RMAN
HR
OE
PM
SH
USERNAME
------------------------------
QS_ADM
QS
QS_WS
QS_ES
QS_OS
QS_CBADM
QS_CB
QS_CS
已选择30行。
SQL> CREATE USER "OPS$LDY\USER1" IDENTIFIED EXTERNALLY;
用户已创建
SQL> GRANT CONNECT TO "OPS$LDY\USER1";
授权成功。
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OPS$LDY\USER1
SCOTT
OUTLN
WMSYS
ORDSYS
ORDPLUGINS
MDSYS
CTXSYS
USERNAME
------------------------------
XDB
ANONYMOUS
WKSYS
WKPROXY
ODM
ODM_MTR
OLAPSYS
RMAN
HR
OE
PM
USERNAME
------------------------------
SH
QS_ADM
QS
QS_WS
QS_ES
QS_OS
QS_CBADM
QS_CB
QS_CS
已选择31行。
3.1.2创建操作系统用户USER1(大写),并授予访问ORACLE程序的权限,可授予administrators用户组
3.1.3注销操作系统用户administrator,以USER1身份重新登陆操作系统
3.1.4登录数据库,进行OS身份认证
C:\Documents and Settings\USER1.LDY>SQLPLUS /
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 28 09:43:33 2007
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
SQL> SHOW USER
USER 为"OPS$LDY\USER1"
3.2 OSAUTH_PREFIX_DOMAIN = FALSE
3.2.1以administrator 登录windows2003,并以SYS建立与ORACLE的连接
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 28 18:16:13 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
已连接。
SQL> show user
USER 为"SYS"
SQL>
SQL> show parameter os_authent_prefix
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string OPS$
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OPS$LDY\USER1
SCOTT
OUTLN
WMSYS
ORDSYS
ORDPLUGINS
MDSYS
CTXSYS
USERNAME
------------------------------
XDB
ANONYMOUS
WKSYS
WKPROXY
ODM
ODM_MTR
OLAPSYS
RMAN
HR
OE
PM
USERNAME
------------------------------
SH
QS_ADM
QS
QS_WS
QS_ES
QS_OS
QS_CBADM
QS_CB
QS_CS
已选择31行。
SQL> create user "OPS$USER2" identified externally;
用户已创建
SQL> grant connect to "OPS$USER2";
授权成功。
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OPS$USER2
OPS$LDY\USER1
SCOTT
OUTLN
WMSYS
ORDSYS
ORDPLUGINS
MDSYS
USERNAME
------------------------------
CTXSYS
XDB
ANONYMOUS
WKSYS
WKPROXY
ODM
ODM_MTR
OLAPSYS
RMAN
HR
OE
USERNAME
------------------------------
PM
SH
QS_ADM
QS
QS_WS
QS_ES
QS_OS
QS_CBADM
QS_CB
QS_CS
已选择32行。
3.2.2创建操作系统用户USER2(大写),并授予访问ORACLE程序的权限,可授予administrators用户组
3.2.3注销操作系统用户administrator,以USER2身份重新登陆操作系统
3.2.4登录数据库,进行OS身份认证
C:\Documents and Settings\USER2>sqlplus /
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 3月 28 20:02:49 2007
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
SQL> SHOW USER
USER 为"OPS$USER2"