Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Even if the password_verify_function limit of a profile is changed in Oracle databases, it does not change as requested and changes to “FROM ROOT”. The reason for this problem is that the FLAGS value in the “profname$” data dictionary is 1 for that profile. When this value is updated to 0, the profile limit can be changed without any problems.
When you want to change the profile limit as follows, the value appears as FROM ROOT.
1 | SQL> alter profile users limit password_verify_function custom_verify_function; |
When the relevant profile limit is checked, it will appear as FROM ROOT.
1 2 3 4 5 6 | SQL> select * from dba_profiles where resource_name='PASSWORD_VERIFY_FUNCTION'; PROFILE RESOURCE_NAME RESOURCE LIMIT COM -------- ------------------------- -------------- ---------------- ---- DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO USERS PASSWORD_VERIFY_FUNCTION PASSWORD FROM ROOT YES |
When we check the flags value of the related profile, it is seen that it is 1.
1 2 3 4 5 6 | select * from profname$; PROFILE# NAME FLAGS ---------- ------------------------- ---------- 0 DEFAULT 0 1 USERS 1 |
If the flags of the profile are updated as follows, the problem will be resolved.
1 2 | SQL> update profname$ set flags=0; SQL> commit; |
We need to see it updated when we check again.
1 2 3 4 5 6 | select * from profname$; PROFILE# NAME FLAGS ---------- ------------------------ ---------- 0 DEFAULT 0 1 USERS 0 |
Then, when we alter the profile again, the limit will change successfully.
1 | SQL> alter profile users limit password_verify_function custom_verify_function; |
When we check the profile, we can see it changed.
1 2 3 4 5 6 | Select * from dba_profiles where resource_name='PASSWORD_VERIFY_FUNCTION'; PROFILE RESOURCE_NAME RESOURCE LIMIT COM -------- ------------------------- -------------- ---------------- ---- DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO USERS PASSWORD_VERIFY_FUNCTION PASSWORD CUSTOM_VERIFY_FUNCTION YES |
