Oracle 创建用户profile和密码验证函数

1.创建用户脚本


DROP USER ERMP CASCADE;


CREATE USER ERMP
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE ERMP
  TEMPORARY TABLESPACE TEMP
  PROFILE PROFILEUSER
  ACCOUNT UNLOCK;
  -- 1 System Privilege for ERMP 
  GRANT UNLIMITED TABLESPACE TO ERMP;


2.ProfileUser脚本


DROP PROFILE PROFILEUSER CASCADE;


CREATE PROFILE PROFILEUSER LIMIT
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  CONNECT_TIME UNLIMITED
  IDLE_TIME UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  COMPOSITE_LIMIT UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;


3.验证密码函数脚本


CREATE OR REPLACE FUNCTION SYS.verify_function_11G
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boole