sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】深入理解密码文件与V$PWFILE_USERS视图相互之间关联影响

时间:2016-06-29 22:43   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 分享一篇Oracle数据库密码文件与v$pwdfile_users视图关系影响,介绍如何使用orapwd创建密码文件的案例
关联文件:【学习笔记】深入理解V$PWFILE_USERS和密码文件关系案例

一、V$PWFILE_USERS视图定义

V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, and SYSASM privileges.
Column	    Datatype	Description
USERNAM    VARCHAR2(30)	Name of the user that is contained in the password file
SYSDBA	   VARCHAR2(5)	Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE)
SYSOPER	   VARCHAR2(5)	Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE)
SYSASM	   VARCHAR2(5)	Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE)

二、v$pwfile_users与密码文件关系

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 4 19:08:06 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

--查看密码文件用户权限
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SQL> show parameter instance_name;        

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
instance_name                        string      ora11g

--查看系统级别查看密码文件内容
SQL> !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC

--创建新sysdba用户,查看视图和密码文件变化
SQL> create user xff01 identified by xifenfei;

User created.

SQL> grant sysdba to xff01;

Grant succeeded.

SQL>  select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          TRUE  FALSE FALSE

SQL>  !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

--删除密码文件
SQL> ! mv $ORACLE_HOME/dbs/orapwora11g $ORACLE_HOME/dbs/orapwora11g_bak

SQL> !ls $ORACLE_HOME/dbs/orapwora11g
ls: /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g: 没有那个文件或目录

--查看视图
SQL>  select * from v$pwfile_users;

no rows selected

SQL>  ! mv $ORACLE_HOME/dbs/orapwora11g_bak $ORACLE_HOME/dbs/orapwora11g

SQL> !ls $ORACLE_HOME/dbs/orapwora11g
/opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          TRUE  FALSE FALSE

--改变sysdba用户权限,视图内容变化
SQL> grant sysoper to xff01;

Grant succeeded.

SQL>  select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          TRUE  TRUE  FALSE

--密码文件内容无变化
SQL> !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

SQL> revoke sysdba from xff01;

Revoke succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          FALSE TRUE  FALSE

SQL> revoke sysoper  from xff01;

Revoke succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XFF01                          FALSE FALSE FALSE

SQL> revoke sysoper  from xff01;

Revoke succeeded.

--回收sysdba,sysoper权限后,视图记录消失
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

--密码文件内容无变化
SQL>  !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

--删除其中sysdba用户
SQL> drop user xff01;

User dropped.

--密码文件内容还是无变化
SQL>  !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

--重启数据库密码文件依然无变化
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !strings $ORACLE_HOME/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

1、如果密码文件不存在或者名称错误,查询v$pwfile_users将得到空记录
2、添加sysdba等权限用户,会记录到密码文件和v$pwfile_users中
3、到回收sysdba等权限用户,密码文件记录依然存在,但是v$pwfile_users中无对应记录

三、远程登录测试

--密码文件记录存在,视图不记录不存在,登录失败
[oracle@node1 ~]$ sqlplus xff01/xifenfei@ora11g as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sun Dec 4 19:42:26 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name

--密码文件视图记录均存在,登录成功
[oracle@node1 ~]$ sqlplus sys/xifenfei@ora11g as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sun Dec 4 19:42:10 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

是否能远程登录,依照v$pwfile_users为准

四、创建密码文件

win:
orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora entries=3 password=manager force=y
linux:
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID entries=3 password=manager force=y

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】深入理解密码文件与V$PWFILE_USERS视图相互之间关联影响

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/634.html

Oracle研究中心

关键词:

密码文件与V$PWFILE_USERS视图相互之间关联影响

配置密码文件时v$pwfile_users视图的作用

orapwd创建密码文件方法