oracle以dba角色登录,以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之类的选件(供参)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-619360/，如需转载，请注明出处，否则将追究法律责任。