V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, and SYSASM privileges.
USERNAME 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)
sys@ORCL>select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------------------------------------------------------------------ --------------- --------------- ---------------
SYS TRUE TRUE FALSE
--给用户zx赋予SYSDBA权限可以看到v$pwfile_users多了一条记录，而密码文件orapworcl也多了一行串码。
sys@ORCL>grant sysdba to zx;
Grant succeeded.
sys@ORCL>select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------------------------------------------------------------------ --------------- --------------- ---------------
SYS TRUE TRUE FALSE
ZX TRUE FALSE FALSE
sys@ORCL>!strings /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
mHD2
7B06550956254585
--给用户zx赋予SYSOPER的权限，可以看到v$pwfile_users的zx行状态发生了变化，但是orapworcl没有变化
sys@ORCL>grant sysoper to zx;
Grant succeeded.
sys@ORCL>select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------------------------------------------------------------------ --------------- --------------- ---------------
SYS TRUE TRUE FALSE
ZX TRUE TRUE FALSE
sys@ORCL>!strings /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
mHD2
7B06550956254585
--移除密码文件再移回来，移除密码文件后v$pwfile_users变为空，移回后v$pwfile_users又有记录。
sys@ORCL>! mv /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl_orcl
sys@ORCL>select * from v$pwfile_users;
no rows selected
sys@ORCL>! mv /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl_orcl /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl
sys@ORCL>select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------------------------------------------------------------------ --------------- --------------- ---------------
SYS TRUE TRUE FALSE
ZX TRUE TRUE FALSE
--测试zx用户远程以SYSDBA登录
C:\Users\victor>sqlplus zx/zx@orcl as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期四 12月 15 22:34:09 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user;
USER 为 "SYS"
--回收用户zx的SYSDBA和SYSOPER权限，v$pwfile_users中的zx记录行没有了，密码文件orapworcl没有变化
sys@ORCL>revoke sysdba,sysoper from zx;
Revoke succeeded.
sys@ORCL>select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------------------------------------------------------------------ --------------- --------------- ---------------
SYS TRUE TRUE FALSE
sys@ORCL>!strings /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
mHD2
7B06550956254585
--再次测试zx用户远程以SYSDBA登录，现在无法登录
C:\Users\victor>sqlplus zx/zx@orcl as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期四 12月 15 22:35:17 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied