V$PWFILE_USERS和密码文件关系(续)

1、验证密码文件已经修改

SQL> create user xff01 identified by xifenfei;

User created.

SQL> grant sysdba to xff01;

Grant succeeded.

SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g
04cedb56b62d94fd7e14124619722348  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

SQL> revoke sysdba from xff01;

Revoke succeeded.

SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g
1f6d120acb913a1877cfb0ab57702744  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

2、查看基表

SQL> col owner for a20
SQL> col object_name for a30
SQL> SELECT owner,object_name,object_type FROM DBA_objects WHERE object_NAME ='V$PWFILE_USERS';

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
PUBLIC               V$PWFILE_USERS                 SYNONYM

SQL> SELECT table_owner,TABLE_name FROM Dba_Synonyms a WHERE a.synonym_name='V$PWFILE_USERS';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$PWFILE_USERS

SQL> SELECT owner,object_name,object_type FROM dba_objects WHERE object_name='V_$PWFILE_USERS';

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
SYS                  V_$PWFILE_USERS                VIEW

SQL> set long 1000
SQL> set line 200
SQL> SELECT dbms_metadata.get_ddl('VIEW','V_$PWFILE_USERS','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','V_$PWFILE_USERS','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PWFILE_USERS" ("USERNAME", "SYSDBA", "SYSOPER", "SYSASM") AS
  select "USERNAME","SYSDBA","SYSOPER","SYSASM" from v$pwfile_users

SQL> select * from v$fixed_table where name ='V$PWFILE_USERS';

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
V$PWFILE_USERS                 4294951116 VIEW       65537


SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$PWFILE_USERS';

VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------
select  USERNAME , SYSDBA , SYSOPER, SYSASM from GV$PWFILE_USERS where inst_id = USERENV('Instance')

SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='GV$PWFILE_USERS';

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------
select inst_id,username,decode(sysdba,1,'TRUE','FALSE'),  decode(sysoper,1,'TRUE','FALSE'),
 decode(sysasm,1,'TRUE','FALSE')  from x$kzsrt where valid=1  and username != 'INTERNAL'

SQL> set line 100
SQL> desc x$kzsrt
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 USERNAME                                                       VARCHAR2(30)
 SYSDBA                                                         NUMBER
 SYSOPER                                                        NUMBER
 SYSASM                                                         NUMBER
 VALID                                                          NUMBER

SQL> col username for a10
SQL> select * from x$kzsrt;

ADDR                   INDX    INST_ID USERNAME       SYSDBA    SYSOPER     SYSASM      VALID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00002B9563678690          0          1 INTERNAL            1          1          0          1
00002B9563678690          1          1 SYS                 1          1          0          1
00002B9563678690          2          1 XFF01               0          0          0          0

从这里可以看出V$PWFILE_USERS视图的基表是x$kzsrt，这里和我们查看的密码文件一样，这些记录都存在，而是在密码文件中有不被strings显示的字符表示了账号是否启用，x$kzsrt.valid用1和0标示
上篇:V$PWFILE_USERS和密码文件关系