对于密码文件认证 还是os认证取决于remote_login_passwordfile 参数与SQLNET.AUTHENTICATION_SERVICES参数。
remote_login_passwordfile参数有三个值:默认为(exclusive)
none----不使用密码文件认证
exclusive---需要密码文件认证 自己独占使用
shared ---需要密码文件认证 不同实例dba用户可以共享密码文件
还有一个参数,这个参数位于$ORACLE_HOME/network/admin/sqlnet.ora文件中
参数是:SQLNET.AUTHENICATION_SERVICES
SQLNET.AUTHENTICATION_SERVICES也有三个值:
none---关闭OS认证,只能密码文件认证
all---linux、unix关闭本机密码文件认证,采用操作系统认证,远程(异机)可以
nts---为windows密码认证
密码文件的作用
密码文件是用来为对具有sysdba权限与sysoper权限用户进行密码认证的。
|
密码文件的默认位置
$ORACLE_HOME/dbs/orapw$ORACLE_SID
|
操作系统认证
我数据库sys用户的正确密码为oracle111
SQL> conn sys/oracle111 as sysdba conn sys/oracle111 as sysdba Connected. SQL> conn sys/qwer as sysdba conn sys/ws qwer sysdba Connected. SQL> conn / as sysdba conn / as sysdba Connected. SQL> 可以看到,密码正确不正确或者没有用户密码也是一样登录的,这就是操作系统认证 |
关闭操作系统认证
[oracle@dongyang admin]$ vi $ORACLE_HOME/network/admin/sqlnet.ora SQLNET.AUTHENTICATION_SERVICES=none
[oracle@dongyang admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 21 15:21:57 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/oracle111 as sysdba Connected. SQL> conn sys/qwer as sysdba ERROR: ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE. SQL> conn / as sysdba ERROR: ORA-01031: insufficient privileges
SQL> [oracle@dongyang admin]$ sqlplus sys/oracle111@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 21 15:34:48 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> [oracle@dongyang admin]$ sqlplus sys/qwer@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 21 15:35:34 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR: ORA-01017: invalid username/password; logon denied
Enter user-name: 可以看到,关闭操作系统认证就需要使用密码文件认证,密码错误就登录不了 |
将SQLNET.AUTHENTICATION_SERVICES参数值设置为all
[oracle@dongyang admin]$ sed 's/none/all/' -i $ORACLE_HOME/network/admin/sqlnet.ora [oracle@dongyang admin]$ cat $ORACLE_HOME/network/admin/sqlnet.ora SQLNET.AUTHENTICATION_SERVICES=all
[oracle@dongyang admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 21 15:29:13 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/oracle111 as sysdba Connected. SQL> conn sys/qwer as sysdba Connected. SQL> conn / as sysdba Connected. SQL> 但是使用连接字符串就登录不了 [oracle@dongyang admin]$ sqlplus sys/oracle111@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 21 15:31:57 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR: ORA-12641: Authentication service failed to initialize
Enter user-name: 使用远程连接(使用win的CMD) C:\>D:\app\oracle10g\sqlplus sys/oracle111@orclasm as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 21 15:34:48 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL>
|
修改密码文件
所谓的DBA用户就是有sysdba权限或者sysoper权限。
具有以上权限的用户的密码都会以二进制方式存放在密码文件中。
不推荐使用ORAPWD命令重建密码文件。(删除密码文件在重建,用户具有sysdba、sysoper权限是登录不上的)
密码文件存放的位置 ongyang admin]$ ll $ORACLE_HOME/dbs/orapw$ORACLE_SID -rw-r----- 1 oracle oinstall 1536 Mar 20 10:34 /u01/app/oracle/product/10.2.0/db_1/dbs/orapwfengzi [oracle@dongyang admin]$ 查看orapwd命令 [oracle@dongyang admin]$ orapwd Usage: orapwd file= password= entries= force=
where file - name of password file (mand), password - password for SYS (mand), entries - maximum number of distinct DBA and –可以有多少sysdba,sysoper权限用户放到密码文件中 force - whether to overwrite existing file (opt), OPERs (opt), There are no spaces around the equal-to (=) character. [oracle@dongyang admin]$ 修改密码为oracle [oracle@dongyang admin]$ cd $ORACLE_HOME/dbs [oracle@dongyang dbs]$ orapwd file=orapwfengzi password=oracle force=y [oracle@dongyang dbs]$
|
推荐修改密码方式
[oracle@dongyang ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 24 09:23:12 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL>alter user sys identified by oracle; User altered. SQL> 这时候密码文件中的内容。 [oracle@dongyang dbs]$ strings orapwfengzi ]\[Z ORACLE Remote Password file INTERNAL AB27B53EDC5FEF41 8A8F025737A9097A [oracle@dongyang dbs]$ 在修改一次密码查看密码文件是否改变。 [oracle@dongyang ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 24 09:23:12 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> alter user sys identified by oracle123;
User altered.
SQL> 再次查看密码文件的值 [oracle@dongyang dbs]$ strings orapwfengzi ]\[Z ORACLE Remote Password file INTERNAL 87C5F4BF47942D0E 4CCF4A082AD3F312 [oracle@dongyang dbs]$ 根据这个特性,推荐使用alter user 修改用户的密码。 |
普通用户具备sysdba的权限
密码文件当前值 [oracle@dongyang dbs]$ strings orapwfengzi ]\[Z ORACLE Remote Password file INTERNAL 87C5F4BF47942D0E 4CCF4A082AD3F312 [oracle@dongyang dbs]$ 授予普通用户sysdba权限 SQL> grant sysdba to qiu;
Grant succeeded.
SQL> 再次查看密码文件 [oracle@dongyang dbs]$ strings orapwfengzi ]\[Z ORACLE Remote Password file INTERNAL 87C5F4BF47942D0E 4CCF4A082AD3F312 41B03979723DCD31 [oracle@dongyang dbs]$ 使用密码文件认证登陆到数据库 [oracle@dongyang ~]$ sqlplus qiu/oracle@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 24 09:51:02 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> SQL> show user USER is "SYS" (sysdba权限的用户登录到数据库其实就是登陆的sys用户) SQL> 当然密码错误是登录不上的 [oracle@dongyang ~]$ sqlplus qiu/qwer@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 24 09:52:36 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR: ORA-01017: invalid username/password; logon denied
Enter user-name: |
给予sysoper权限一样是会记录在密码文件中的
SQL> create user bj1 identified by bj1;
User created.
SQL> grant sysoper to bj1;
Grant succeeded.
SQL> 查看密码文件 [oracle@dongyang dbs]$ strings orapwfengzi ]\[Z ORACLE Remote Password file INTERNAL 87C5F4BF47942D0E 4CCF4A082AD3F312 41B03979723DCD31 533C4A5F0CA43E68 (发现又多了一条记录,这条记录对应的就是bj1的密码) [oracle@dongyang dbs]$ |
Sysdba权限与sysoper权限的区别
Sysdba与sysoper都是系统权限
Sysdba: 默认 schema 为sys 可以启动关闭数据库,可以启动到open、mount。 可以进行备份,改变字符集 可以创建数据库、删除数据库 可以创建spfile 可以将数据库改为归档 可以进行恢复 可以进行限制会话操作 Sysoper: 默认schema 为public 也可以进行数据库的启动关闭,可以启动到open、mount。 也可以进行备份,但是不能改变字符集 不能进行创建数据库、删除数据库 也可以创建spfile 也可以将数据库改为归档 也可以进行恢复,但是仅仅只能进行完全恢复,不能使用不完全恢复 也可以限制会话操作
默认schema SQL> conn / as sysdba Connected. SQL> show user USER is "SYS" SQL> conn /as sysoper Connected. SQL> show user USER is "PUBLIC" SQL>
|
查看那些用户具有dba相关权限
这个视图记录具有sysdba与sysoper权限的用户 SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP ------------------------------ ----- ----- SYS TRUE TRUE QIU TRUE FALSE BJ1 FALSE TRUE
SQL> 可以看到,sys用户默认sysdba与sysoper权限都是具备的。 我只授予了qiu用户sysdba权限,所以只能sysdba权限登录数据库 同样的只授予bj1用户sysoper权限,所以只能sysoper权限登录数据库 |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29532781/viewspace-1130071/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29532781/viewspace-1130071/