Oracle 密码文件 --==============================
-- Oracle 密码文件
一、密码文件
作用：主要进行DBA权限的身份认证
DBA用户：具有sysdba，sysoper权限的用户被称为dba用户。默认情况下sysdba角色中存在sys用户，sysoper角色中存在system用户
二、Oracle的两种认证方式；
1.使用与操作系统集成的身份验证
2.使用Oracle数据库的密码文件进行身份认证
三、密码文件的位置
Linux下的存放位置：$ORACLE_HOME/dbs/orapw$ORACLE_SID
即：ORACLE_HOME/dbs/orapw<sid>
Linux下密码文件无扩展名
Windows下的存放位置:$ORACLE_HOME/database/PW%ORACLE_SID%.ora
两种认证方式：类似于 SQL server中的windows认证和SQL server认证(操作系统认证和数据库密码文件认证)，决定认证的方式取决于两个参数，需注意的是
当oracle密码文件丢失时，DBA用户采用密码认证时是无效的，因为oracle会先去访问密码文件，验证是否有效。
1. 参数remote_login_passwordfile = none | exclusive |shared，该参数保存于参数文件Pfile<ORACLE_SID>.ora(或Spfile<ORACLE_SID>)文件中
none : 表示不接受密码认证文件，只接受操作系统认证(OS Authentication)
exclusive :表示数据库接受密码认证文件，同时，可将SYSDBA角色授权给其他用户
shared ：表示数据库接受密码认证文件，但SYSDBA角色无法授权给其他用户
2. 参数SQLNET.AUTHENTICATION_SERVICES= none | all | ntf(windows)，该参数位于$ORACLE_HOME/network/admin/sqlnet.ora文件中，当文件中不存在该参数时，表示DBA用户可以采用密码认证和操作系统认证方式连接数据库。
none : 表示关闭操作系统认证，只接受密码认证
all : 用于linux或unix平台，关闭本机密码文件认证，采用操作系统认证，但远程<异机>可以使用密码文件认证
nts : 用于windows平台
以上两个参数共同验证SYS用户的权限认证，两者都必须满足用户才能正常访问数据库，如下所示则sys用户无论是本机还是远程均不可用
不同的组合
SQLNET.AUTHENTICATION_SERVICES REMOT_LOGIN_PASSWORDFILE
none none
四、DBA用户的连接方式
DBA连接数据库的方式有以下几种：
SQLPLUS / AS SYSDBA ：这是典型的操作系统认证，不需要listener进程；
SQLPLUS SYS/PASSWORD AS SYSDBA ：此种方法只能连接本地数据库，这里我认为也属于操作系统认证，当password不对时，用户也能连接数据库，不需要listener进程
SQLPLUS SYS/PASSWORD@ORACLE_SID AS SYSDBA ：此种方法可访问本地数据库，同时亦可远程访问数据库，属于典型的密码认证
五、演示：
1.在sqlnet.ora 中追加SQLNET.AUTHENTICATION_SERVICES = none */
[oracle@robinson ~]$ sqlplus / as sysdba /*登陆失败*/
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 10:41:28 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
--------------------------------------------------------------------------------
[oracle@robinson ~]$ sqlplus sys/redhat as sysdba /*使用密码文件认证，登陆成功*/
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 10:42:35 2010
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>
--=================================================================================
2.将SQLNET.AUTHENTICATION_SERVICES的值改为all
[oracle@robinson admin]$ sqlplus / as sysdba /*采用本机认证可以登陆*/
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 10:46:55 2010
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>
--------------------------------------------------------------------------------------
[oracle@robinson admin]$ sqlplus sys/redhat@orcl as sysdba /*使用密码文件登陆认证失败*/
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 10:48:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12641: Authentication service failed to initialize
Enter user-name:
--注：此时可以使用远程登陆。
--使用#符号将新增的SQLNET.AUTHENTICATION_SERVICES行注释掉恢复到缺省值
3.在spfile<ORACLE_SID>.ora中设置参数remote_login_passwordfile=none
[oracle@localhost ~]$ sqlplus / as sysdba /*支持操作系统认证方式*/
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:47:10 2013
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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ sqlplus sys/181818 as sysdba /*登录成功，说明在未指定ORACLE_SID情况下，oracle把此模式认为是操作系统认证*/
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:47:20 2013
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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ sqlplus sys/181818@testdb as sysdba /*登录失败，不支持密码认证*/
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:47:31 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
4.在spfile<ORACLE_SID>参数文件中设置参数remote_login_passwordfile=EXCLUSIVE（默认值）
oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:58:58 2013
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
oracle@localhost ~]$ sqlplus sys/181818 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:58:58 2013
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
oracle@localhost ~]$ sqlplus sys/181818@testdb as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 24 15:58:58 2013
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
六、密码文件的建立：orapwd
[oracle@robinson ~]$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>
where
file - name of password file (mand), /*密码文件的名字orapw<sid>*/
password - password for SYS (mand), /*sys用户的密码*/
entries - maximum number of distinct DBA and /*可以有多少个sysdba,sysoper权限用户放到密码文件中去，去掉重复记录*/
/*注意entries中存放的个数但不是实际个数，这个是二进制数据*/
force - whether to overwrite existing file (opt), /*10g新增的参数，默认值为n ,y表示允许覆盖*/
OPERs (opt),
There are no spaces around the equal-to (=) character.
--修改密码：
[oracle@robinson ~]$ cd $ORACLE_HOME/dbs
[oracle@robinson dbs]$ ll orapworcl
-rw-r----- 1 oracle oinstall 1536 Apr 7 15:50 orapworcl
[oracle@robinson dbs]$ orapwd file=orapworcl password=oracle force=y
[oracle@robinson dbs]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 11:34:09 2010
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>
--将密码改回原来的密码
[oracle@robinson dbs]$ orapwd file=orapworcl password=redhat
OPW-00005: File with same name exists - please delete or rename
[oracle@robinson dbs]$ orapwd file=orapworcl password=redhat force=y
[oracle@robinson dbs]$ rm orapworcl /*删除密码文件*/
[oracle@robinson dbs]$ orapwd file=orapworcl password=redhat /*重建密码文件*/
--演示将entries 改为，然后将多个用户设置为sysdba或sysoper
[oracle@robinson dbs]$ orapwd file=orapworcl password=redhat entries=1
[oracle@robinson dbs]$ strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
F7AC0C5E9C3C37AB
E100B964899CDDDF
--创建PL/SQL 增加个新用户
SQL> begin
2 for i in 1..20 loop
3 execute immediate 'create user u'||i||' identified by u'||i||'';
4 end loop;
5 end;
6 /
--将新用户赋予sysdba角色
PL/SQL procedure successfully completed.
SQL> begin
2 for i in 1..20 loop
3 execute immediate 'grant sysdba to u'||i||'';
4 end loop;
5 end;
6 /
begin /*得到和密码文件相关的错误提示*/
*
ERROR at line 1:
ORA-01996: GRANT failed: password file '' is full
ORA-06512: at line 3
--再次查看orapworcl发现多出了行，即当设置为的时候多出了个用户。原因是该密码文件是二进制文件，按矩阵计算可存放多少
[oracle@robinson dbs]$ strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
F7AC0C5E9C3C37AB
E100B964899CDDDF
3E81B724A296E296
668509DF9DD36B43
9CE6AF1E3F609FFC
7E19965085C9ED47
--注意不要轻易删掉密码文件，这样会将其他账户的信息也删除
/*
六、导致密码文件内容修改的几种方式：
1.使用orapwd建立，修改密码文件，不建议使用
2.使用alter user sys identified by <>
3.使用grant sysdba to <> 或grant sysoper to <> 或revoke sysdba |sysoper from <>
七、查看密码文件内容 */
[oracle@robinson dbs]$ strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
F7AC0C5E9C3C37AB
E100B964899CDDDF
--当sys密码不记得可以使用OS系统身份认证登陆到sqlplus,再使用alter user修改密码
SQL> alter user sys identified by oracle;
User altered
--再次查看密码文件与上一次对比，已经发生变化
SQL> ho strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
--通过授予权限来修改密码，密码文件中多出了scott的信息
SQL> grant sysdba to scott;
Grant succeeded.
SQL> ho strings orapworcl
]/[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
SCOTT
F894844C34402B67
--注意此处中登陆后，显示的账户信息还是sys,而不是scott,但此时的scott已经具备了sys权限
[oracle@robinson dbs]$ sqlplus scott/tiger@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 9 11:56:09 2010
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> show user
USER is "SYS"
/*
八、sysdba 与sysoper的区别*/
SQL> select * from system_privilege_map where name like '%SYS%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-3 ALTER SYSTEM 0
-4 AUDIT SYSTEM 0
-83 SYSDBA 0
-84 SYSOPER 0
--下面的链接是两者不同的权限说明
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#sthref137
The manner in which you are authorized to use these privileges depends upon the method of authentication that you use.
When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema,
not with the schema that is generally associated with your username.
For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
--两者的schema不同
SQL> show user
USER is "SYS"
SQL> conn / as sysoper
Connected.
SQL> show user
USER is "PUBLIC"
--查看密码文件视图，可以得到哪些用户为sysdba,哪些用户为sysoper
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SCOTT TRUE FALSE
USER1 FALSE TRUE
--下面演示了使用不同的角色来登陆
SQL> conn scott/tiger@orcl as sysdba
Connected.
SQL> conn scott/tiger@orcl as sysoper /*scott的sysop列为false*/
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
SQL> conn user1/user1@orcl as sysdba
ERROR:
ORA-01031: insufficient privileges
转自：http://blog.csdn.net/olnathen/article/details/8595518
扫一扫
2176
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
