PASSWORD_VERIFY_FUNCTION 值 FROM ROOT 与default 的差异

SYMPTOMS

When connected from PDB, "FROM ROOT" should be displayed in the LIMIT column of DBA_PROFILES.

CHANGES

 None

CAUSE

This is an expected behavior from 12c onwards that the text will be displayed as "FROM ROOT" when connected from PDB.
 

SOLUTION

From 12c onwards, in Root, query on DBA_PROFILES for this profile and the PASSWORD_VERIFY_FUNCTION should return the Function Name, whereas in PDB will return the text "FROM ROOT".

DBA_PROFILES displays "FROM ROOT" when connected in PDB for common profiles PASSWORD_VERIFY_FUNCTION.

eg:
Create a Common Profile and associate it with a password verification function and then connect with PDB and check the output shown.

Test Case:
======

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> CREATE PROFILE c##new_profile LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10;
2 3 4 5 6 7 8
Profile created.

SQL> SELECT * FROM PROFNAME$;

PROFILE#        NAME                       FLAGS
----------------  ----------------------   ----------
0                    DEFAULT                    0
1                    ORA_STIG_PROFILE    0
7                    C##NEW_PROFILE      1

SQL> select profile,resource_name,limit from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION';

PROFILE                          RESOURCE_NAME                              LIMIT
--------------------            ----------------------------------------    -------------------------------------------------------
DEFAULT                         PASSWORD_VERIFY_FUNCTION           NULL
ORA_STIG_PROFILE         PASSWORD_VERIFY_FUNCTION           ORA12C_STRONG_VERIFY_FUNCTION
C##NEW_PROFILE           PASSWORD_VERIFY_FUNCTION          ORA12C_STRONG_VERIFY_FUNCTION

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB1

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> select profile,resource_name,limit from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION';

PROFILE                      RESOURCE_NAME                              LIMIT
--------------------       --------------------------------                ----------------------------------------------------------------
DEFAULT                     PASSWORD_VERIFY_FUNCTION           NULL
ORA_STIG_PROFILE     PASSWORD_VERIFY_FUNCTION           ORA12C_STRONG_VERIFY_FUNCTION
C##NEW_PROFILE      PASSWORD_VERIFY_FUNCTION            FROM ROOT

**** TEST CASE ENDS ****

defalut是null，所以不同于其他值 default

PDB:

 

CDB:

上面第一个是default，在cdb下为NULL，在PDB下设置了ORA12C_STIG_VERIFY_FUNCTION，所以不是FROM ROOT.如果CDB/PDB 是相同的就是 FROM ROOT

C##的用户很多还是要在CDB下看，比如account status，pdb下的不准！！