实验:
oracle服务器位于Linux操作系统,客户端位于windows操作系统。
首先,查看remote_login_passwordfile参数值:
SYS@orcl 11-SEP-14>show parameter remote_login_passwordfile
NAME TYPE VALUE------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
找到$ORACLE_HOME/network/admin目录下的sqlnet.ora,在文件末尾加上:
SQLNET.AUTHENTICATION_SERVICES=NONE
#Purpose:Use parameter SDP.PF_INET_SDP to specify the protocol family or# address family constantfor the SDP protocol onyour system.
#
#Supported since:11.0#
SQLNET.AUTHENTICATION_SERVICES=none
即使用密码文件认证方式,那么如果我们在本地使用sqlplus "/as sysdba"就会提示错误信息:
[]$ sqlplus "/assysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 12 22:45:56 2014Copyright (c)1982, 2009, Oracle. Allrights reserved.
ERROR:
ORA-01031: insufficient privileges
此时我们必须使用sys用户名和密码才可以登录:
[]$ sqlplus "sys/sys assysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 12 22:47:08 2014Copyright (c)1982, 2009, Oracle. Allrights reserved.
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 -ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
进入sqlnet.ora把SQLNET.AUTHENTICATION_SERVICES=none改成“=all”,存盘退出。
再次使用sqlplus "/as sysdba"登录的时候使用的就是本地认证:
[]$ sqlplus "/assysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 12 22:49:51 2014Copyright (c)1982, 2009, Oracle. Allrights reserved.
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 -ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
另外,我们可以使用orapwd这个工具来生成密码文件。
首先看看orapwd的用法:
[]$ orapwd
Usage: orapwdfile= entries= force= ignorecase= nosysdba=
where
file - name of password file(required),
password- password for SYS will be prompted if notspecified at command line,
entries- maximum number of distinctDBA (optional),
force- whether to overwrite existing file(optional),
ignorecase- passwords are case-insensitive (optional),
nosysdba- whether to shut out the SYSDBA logon (optional Database Vault only).
There must be no spaces around the equal-to (=) character.
我们把位于$ORACLE_HOME/dbs目录下的原orapworcl移到其它目录。注意,remote_login_passwordfile=exclusive,且sqlnet.ora中SQLNET.AUTHENTICATION_SERVICES=none
在windows上的客户端尝试远程oracle:
SQL> conn sys/sys@win assysdba
ERROR:
ORA-01031: insufficient privileges
现在我们使用orapwd来重建密码文件:
[]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=sys entries=5
[]$ ls
hc_DBUA0.dat initorcl lkORCL peshm_DUMMY_0 spfileorcl.ora
hc_orcl.dat initorcl.ora orapworcl peshm_orcl_0
init.ora lkDUMMY peshm_DBUA0_0 snapcf_orcl.f
再次尝试远程连接oracle:
SQL> conn sys/sys@win assysdba
connected.
看看这个密码文件的内容:
[]$ strings orapworclORACLE Remote Password file
INTERNAL
6A75B1BBE50E66AB
4DE42795E66117AE
在数据库中创建一个新的用户并授予sysdba的权限:
SYS@orcl 11-SEP-14>create user sunny identified bysunny;Usercreated.
SYS@orcl 11-SEP-14>grant sysdba tosunny;Grant succeeded.
再去看看orapworcl的内容,发现多了sunny这个用户的信息:
[]$ strings orapworcl
ORACLE Remote PasswordfileINTERNAL
6A75B1BBE50E66AB
4DE42795E66117AE
SUNNY
53801465943A91BE
也可以通过动态性能视图v$pwfile_users查看有哪些用户是拥有sysdba权限的:
SYS@orcl 11-SEP-14>select * fromv$pwfile_users;
USERNAME SYSDB SYSOP SYSAS------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SUNNY TRUE FALSE FALSE