Shulou(Shulou.com)06/01 Report--

Sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Monday March 25 22:53:24 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Transferred from: http://blog.itpub.net/23135684/viewspace-757083/

Or a custom script-- the script is as follows--

CREATE OR REPLACE FUNCTION PASSWORD_VERIFY_FUNCTION

(

Username varchar2

Password varchar2

Old_password varchar2)

RETURN boolean IS

N boolean

M integer

Differ integer

Isdigit boolean

Ischar boolean

Ispunct boolean

Db_name varchar2 (40)

Digitarray varchar2 (20)

Punctarray varchar2 (25)

Specialarray varchar2 (14)

Chararray varchar2 (52)

I_char varchar2 (10)

Simple_password varchar2 (10)

Reverse_user varchar2 (32)

BEGIN

Digitarray:= '0123456789'

Chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

Specialarray:='! @ # $% ^ & * () _'

-- verify that the password length is at least 8 digits.

IF length (password)

< 8 THEN raise_application_error(-20001, 'Password length less than 8'); END IF; -- 校验密码是否和用户名相同 IF NLS_LOWER(password) = NLS_LOWER(username) THEN raise_application_error(-20002, 'Password same as or similar to user'); END IF; FOR i IN 1..100 LOOP i_char := to_char(i); if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN raise_application_error(-20005, 'Password same as or similar to user name '); END IF; END LOOP; -- 校验密码是不是用户名的反序 FOR i in REVERSE 1..length(username) LOOP reverse_user := reverse_user || substr(username, i, 1); END LOOP; IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN raise_application_error(-20003, 'Password same as username reversed'); END IF; -- 校验密码是否是服务名 select name into db_name from sys.v$database; if NLS_LOWER(db_name) = NLS_LOWER(password) THEN raise_application_error(-20004, 'Password same as or similar to server name'); END IF; FOR i IN 1..100 LOOP i_char := to_char(i); if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN raise_application_error(-20005, 'Password same as or similar to server name '); END IF; END LOOP; -- 检查用户密码是否过于简单， -- 检查密码是否有列表中的关键字 -- 是否存在弱口令 IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN raise_application_error(-20006, 'Password too simple'); END IF; -- 检查密码是否为oracle simple_password := 'oracle'; FOR i IN 1..100 LOOP i_char := to_char(i); if simple_password || i_char = NLS_LOWER(password) THEN raise_application_error(-20007, 'Password too simple '); END IF; END LOOP; -- 检验密码至少包含至少一个字母，一个数字 ,一个特殊字符 -- 1. 校验数字 isdigit:=FALSE; m := length(password); FOR i IN 1..10 LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(digitarray,i,1) THEN isdigit:=TRUE; GOTO findchar; END IF; END LOOP; END LOOP; IF isdigit = FALSE THEN raise_application_error(-20008, 'Password must contain at least one digit, one character'); END IF; -- 2. 检验字符 ischar:=FALSE; FOR i IN 1..length(chararray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(chararray,i,1) THEN ischar:=TRUE; GOTO endsearch; END IF; END LOOP; END LOOP; IF ischar = FALSE THEN raise_application_error(-20009, 'Password must contain at least one \ digit, and one character'); END IF; -- 3. 检验特殊字符 ischar:=FALSE; FOR i IN 1..length(specialarray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(specialarray,i,1) THEN ischar:=TRUE; GOTO endsearch; END IF; END LOOP; END LOOP; IF ischar = FALSE THEN raise_application_error(-20009, 'Password must contain at least one special_char'); END IF; --检验密码和上一个密码至少有三个不同字符 -- IF old_password IS NOT NULL THEN differ := length(old_password) - length(password); differ := abs(differ); IF differ < 3 THEN IF length(password) < length(old_password) THEN m := length(password); ELSE m := length(old_password); END IF; FOR i IN 1..m LOOP IF substr(password,i,1) != substr(old_password,i,1) THEN differ := differ + 1; END IF; END LOOP; IF differ < 3 THEN raise_application_error(-20011, 'Password should differ from the \ old password by at least 3 characters'); END IF; END IF; END IF; -- Everything is fine; return TRUE ; RETURN(TRUE); END; 在测试环境学习oracle,出现用户密码过期的问题，更改密码提示验证未通过，因为为测试环境，为方便期间，不需要经常修改密码，在此发现profile的使用方法，记录如下。 　　SQL>

SELECT * FROM dba_profiles

Check the profile to which the user belongs, usually DEFAULT

SQL > select * from dba_profiles where resource_type='PASSWORD'

SQL > alter user lh identified by lh

Alter user lh identified by lh

ORA-28003: password verification for the specified password failed

ORA-20001: Password same as or similar to user

Cancel password management:

SQL > alter profile DEFAULT limit unlimited

Such as:

SQL > alter profile DEFAULT limit password_reuse_time unlimited

Stop the password check function:

SQL > alter profile DEFAULT limit password_verify_function null

Create a user-specific profile:

CREATE PROFILE test LIMIT

SESSIONS_PER_USER UNLIMITED

CPU_PER_SESSION UNLIMITED

CPU_PER_CALL 3000

CONNECT_TIME 45

LOGICAL_READS_PER_SESSION DEFAULT

LOGICAL_READS_PER_CALL 1000

PRIVATE_SGA 15K

COMPOSITE_LIMIT 5000000

To change:

SQL > alter profile test limit PASSWORD_LIFE_TIME 60

Change user profile

SQL > alter user scott profile test

*

Processing of user password expiration in Oracle database

1. Sqlplus user / password @ database service as sysdba

2 、 select username,profile from dba_users

3. Select * from dba_profiles 's where s.ProfileDefault' and resource_name='password_life_time'

4 、 alter profile default limit password_life_time unlimited

If the user is locked, execute 5, otherwise there is no need to execute 5

5. Alter user user identified by password account unlock

/ /

/ * check the profile to which the user belongs, usually DEFAULT*/

SELECT * FROM dba_profiles

Select * from dba_profiles where resource_type='PASSWORD' and profile=upper ('PROFILE1')

/ * stop password checking function: * /

Alter profile PROFILE1 limit PASSWORD_VERIFY_FUNCTION null

Alter user yufeng profile DEFAULT

Alter profile PROFILE1 limit PASSWORD_LIFE_TIME 60

Alter profile PROFILE1 limit password_life_time unlimited

/ * if the user has been locked, execute the following SQL, otherwise there is no need to execute; * /

Alter user user identified by password account unlock

*

= "Oracle password complexity setting (Oracle_Password_Complexity)

1. Oracle_Password_Complexity:

SQL > alter system set resource_limit = true

SQL > @ $ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql → [verify_function | verify_function_11G]

SQL > alter profile default limit password_verify_function verify_function

# cancel Oracle password complexity check:

SQL > alter profile default limit password_verify_function null

SQL > SELECT profile,resource_type,resource_name,limit FROM dba_profiles WHERE resource_type='PASSWORD' AND profile='DEFAULT'

1.FAILED_LOGIN_ATTEMPTS: the user is locked out after n failed login attempts.

2.PASSWORD_LOCK_TIME: failed login attempts for a specified number of times, and the user is locked out for a period of time, in units of "Day".

3.PASSWORD_LIFE_TIME: the life cycle of the user's password.

4.PASSWORD_GRACE_TIME: indicates the number of days a user's password can continue to be used after it has been used for more than its life cycle, and there will be a prompt that the corresponding password is about to expire within the duration of login.

5.PASSWORD_REUSE_TIME: specifies the number of days before the password cannot be reused.

6.PASSWORD_REUSE_MAX: the number of times that must be changed before the same password can be used again after the PASSWORD_REUSE_TIME specified time has been reached.

For example: PASSWORD_REUSE_TIME=30,PASSWORD_REUSE_MAX=10, the user can reuse the password after 30 days, requiring that the password must be changed more than 10 times.

7.PASSWORD_VERIFY_FUNCTION: Oracle allows complex PL/SQL password validation scripts to be passed to PASSWORD_VERIFY_FUNCTION as parameters. And it provides a default script, but users can create their own authentication rules or use third-party software to validate.

8.Password Verify Function:

When you create a password verify function for verifying the user password, this function can verify the following password characteristics:

1.The minimum number of characters for the password.

2.The characters that the password must contain, such as when a password should contain a specific number of numeric, alphabetic or special characters.

3.Whether or not the password can be the same as the username.Whether or not the new password can be similar to the previous password.

Oracle 11g database password case-sensitive setting → SEC_CASE_SENSITIVE_LOGON = TRUE.

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.