Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to turn on complex password authentication by oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

ORACLE 11g turns on complex password authentication

SQL > set line 200

SQL > SELECT * FROM Dba_Profiles d WHERE d.profileholders default

PROFILE RESOURCE_NAME RESOURCE LIMIT

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

DEFAULT CPU_PER_CALL KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

DEFAULT IDLE_TIME KERNEL UNLIMITED

DEFAULT CONNECT_TIME KERNEL UNLIMITED

DEFAULT PRIVATE_SGA KERNEL UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

PROFILE RESOURCE_NAME RESOURCE LIMIT

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected.

Note that after running this script, profile will be initialized, and the previously set unlimited will become the default

SQL > @? / rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.

SQL > SELECT * FROM Dba_Profiles d WHERE d.profileholders default

PROFILE RESOURCE_NAME RESOURCE LIMIT

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

DEFAULT CPU_PER_CALL KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

DEFAULT IDLE_TIME KERNEL UNLIMITED

DEFAULT CONNECT_TIME KERNEL UNLIMITED

DEFAULT PRIVATE_SGA KERNEL UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

PROFILE RESOURCE_NAME RESOURCE LIMIT

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected.

SQL > alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION

Profile altered.

PROFILE RESOURCE_NAME RESOURCE LIMIT

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

DEFAULT CPU_PER_CALL KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

DEFAULT IDLE_TIME KERNEL UNLIMITED

DEFAULT CONNECT_TIME KERNEL UNLIMITED

DEFAULT PRIVATE_SGA KERNEL UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

PROFILE RESOURCE_NAME RESOURCE LIMIT

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected.

After running the script, you will create two functions for password verification, VERIFY_FUNCTION_11G,VERIFY_FUNCTION.

ORACLE 12C turns on complex password authentication

CDB execution

SQL > set line 200

SQL > col PROFILE for A30

SQL > col LIMIT for A30

SQL > col RESOURCE_NAME for A30

SQL > SELECT * FROM Dba_Profiles d WHERE d.profileholders default

PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO NO NO

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO NO NO

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO NO NO

DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO NO NO

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO NO NO

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO NO NO

DEFAULT IDLE_TIME KERNEL UNLIMITED NO NO NO

DEFAULT CONNECT_TIME KERNEL UNLIMITED NO NO NO

DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO NO NO

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO NO NO

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO NO NO

PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO NO NO

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO NO NO

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 NO NO NO

DEFAULT INACTIVE_ACCOUNT_TIME PASSWORD UNLIMITED NO NO NO

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION ora12c_verify_function

Or

Alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION

Need to be executed in PDB

SQL > alter session set container=PDB1

The session has changed.

SQL > set line 200

SQL > col PROFILE for A30

SQL > col LIMIT for A30

SQL > col RESOURCE_NAME for A30

SQL > SELECT * FROM Dba_Profiles d WHERE d.profileholders default

PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO NO NO

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO NO NO

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO NO NO

DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO NO NO

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO NO NO

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO NO NO

DEFAULT IDLE_TIME KERNEL UNLIMITED NO NO NO

DEFAULT CONNECT_TIME KERNEL UNLIMITED NO NO NO

DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO NO NO

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO NO NO

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO NO NO

PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO NO NO

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO NO NO

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 NO NO NO

DEFAULT INACTIVE_ACCOUNT_TIME PASSWORD UNLIMITED NO NO NO

17 rows have been selected.

SQL > create user test identified by test

The user has been created.

SQL > ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION ora12c_verify_function

The configuration file has changed

SQL > alter user test identified by test

Alter user test identified by test

*

An error occurred on line 1:

ORA-28003: password verification failed for the specified password ORA-20001:

Password length less than 8

SQL > alter user test identified by "test123 thanks #"

Alter user test identified by "test 123 thanks #"

*

An error occurred on line 1:

ORA-28003: password verification failed for the specified password ORA-20002:

Password contains the username

SQL > alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION

The configuration file has changed

SQL > alter user test identified by "test123 thanks #"

The user has changed.

This is the end of the content of "how to turn on complex password authentication by oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.