使用profile的password_verify_function实现用户自定义的密码验证规则。

先决条件：

1.此函数必须在sys用户下创建。

2.必须有三个varchar2类型的输入参数，第一个参数表示要修改的用户名，第二个参数表示输入的新密码，第三个参数表示旧密码

3.返回类型为bool类型。

纤细测试如下：

创建步骤如下：

1.创建测试表password_verify

2.创建验证函数user_password_verify

3.创建profile test_profile

4.创建测试用户test_user

rorke>show user
USER is "SYS"
sql>create table password_verify
  2  (user_name varchar2(30),
  3  old_password varchar2(30),
  4  new_password varchar2(100));

Table created.

sql>create or replace function user_password_verify (username varchar2,password varchar2,old_password varchar2) return boolean is
  2  begin
  3  if length(password) < 6 then
  4  raise_application_error(-20001,'Password must be at least 6 characters long')
  5  end if
  6  insert into password_verify values(username,old_password,password);
  7  return(true);
  8  end;
  9  /

Warning: Function created with compilation errors.

sql>show error
Errors for FUNCTION USER_PASSWORD_VERIFY:

LINE/COL ERROR                                                                 
-------- -----------------------------------------------------------------     
5/1      PLS-00103: Encountered the symbol "END" when expecting one of         
         the following:                                                        
         := . ( % ;                                                            
                                                                               
sql>list
  1  create or replace function user_password_verify (username varchar2,password varchar2,old_password varchar2) return boolean is
  2  begin
  3  if length(password) < 6 then
  4  raise_application_error(-20001,'Password must be at least 6 characters long')
  5  end if
  6  insert into password_verify values(username,old_password,password);
  7  return(true);
  8* end;
sql>edit
Wrote file afiedt.buf

  1  create or replace function user_password_verify (username varchar2,password varchar2,old_password varchar2) return boolean is
  2  begin
  3  if length(password) < 6 then
  4  raise_application_error(-20001,'Password must be at least 6 characters long')
  5  end if;
  6  insert into password_verify values(username,old_password,password);
  7  return(true);
  8* end;
sql>/

Warning: Function created with compilation errors.

sql>show error
Errors for FUNCTION USER_PASSWORD_VERIFY:

LINE/COL ERROR                                                                 
-------- -----------------------------------------------------------------     
5/1      PLS-00103: Encountered the symbol "END" when expecting one of         
         the following:                                                        
         := . ( % ;                                                            
         The symbol ";" was substituted for "END" to continue.                 
                                                                               
sql>edit
Wrote file afiedt.buf

  1  create or replace function user_password_verify (username varchar2,password varchar2,old_password varchar2) return boolean is
  2  begin
  3  if length(password) < 6 then
  4  raise_application_error(-20001,'Password must be at least 6 characters long');
  5  end if;
  6  insert into password_verify values(username,old_password,password);
  7  return(true);
  8* end;
sql>/

Function created.

sql>create profile test_profile limit password_verify_function user_password_verify;

Profile created.

sql>create user test_user
  2  identified by test
  3  default tablespace users
  4  temporary tablespace temp
  5  profile test_profile;
create user test_user
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password must be at least 6 characters long

sql>create user test_user
  2  identified by test12
  3  profile test_profile;

User created.

sql>select * from password_verify;

USER_NAME                      OLD_PASSWORD                                    
------------------------------ ------------------------------                  
NEW_PASSWORD                                                                   
--------------------------------------------------------------------------------
TEST_USER                                                                      
TEST12                                                                         
                                                                               

sql>alter user test_user identified by test;
alter user test_user identified by test
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password must be at least 6 characters long

sql>alter user test_user identified by test123;

User altered.

sql>select * from password_verify;

USER_NAME                      OLD_PASSWORD                                    
------------------------------ ------------------------------                  
NEW_PASSWORD                                                                   
--------------------------------------------------------------------------------
TEST_USER                                                                      
TEST12                                                                         
                                                                               
TEST_USER                                                                      
TEST123