1.for CDBS
run as sysDBa
CREATE OR REPLACE FUNCTION verify_function (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS n boolean; m integer; differ integer; isdigit boolean; ischar boolean; ispunct boolean; digitarray varchar2(20); punctarray varchar2(25); chararray varchar2(52); BEGIN digitarray:= '0123456789'; chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; punctarray:='!"#$%&()``*+,-/:;<=>?_'; -- Check if the password is same as the username IF NLS_LOWER(password) = NLS_LOWER(username) THEN raise_application_error(-20001, 'Password same as or similar to user'); END IF; -- Check for the minimum length of the password IF length(password) < 4 THEN raise_application_error(-20002, 'Password length less than 4'); END IF; -- Check if the password is too simple. A dictionary of words may be -- maintained and a check may be made so as not to allow the words -- that are too simple for the password. IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN raise_application_error(-20002, 'Password too simple'); END IF; -- Check if the password contains at least one letter, one digit and one -- punctuation mark. -- 1. Check for the digit 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(-20003, 'Password should contain at least one digit, one character and one punctuation'); END IF; -- 2. Check for the character <<findchar>> 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 findpunct; END IF; END LOOP; END LOOP; IF ischar = FALSE THEN raise_application_error(-20003, 'Password should contain at least one \ digit, one character and one punctuation'); END IF; -- 3. Check for the punctuation <<findpunct>> ispunct:=FALSE; FOR i IN 1..length(punctarray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(punctarray,i,1) THEN ispunct:=TRUE; GOTO endsearch; END IF; END LOOP; END LOOP; IF ispunct = FALSE THEN raise_application_error(-20003, 'Password should contain at least one \ digit, one character and one punctuation'); END IF; <<endsearch>> -- Check if the password differs from the previous password by at least -- 3 letters IF old_password IS NOT NULL THEN differ := length(old_password) - length(password); IF abs(differ) < 3 THEN IF length(password) < length(old_password) THEN m := length(password); ELSE m := length(old_password); END IF; differ := abs(differ); 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(-20004, 'Password should differ by at \ least 3 characters'); END IF; END IF; END IF; -- Everything is fine; return TRUE ; RETURN(TRUE); END; / GRANT EXECUTE ON verify_function TO PUBLIC; CREATE PROFILE c##APP_PROFILE LIMIT COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME UNLIMITED CONNECT_TIME UNLIMITED PRIVATE_SGA UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_LOCK_TIME 1 PASSWORD_GRACE_TIME 7 ; alter user C##BACKUPDB profile c##APP_PROFILE; alter user C##OPER profile c##APP_PROFILE; alter user system profile c##APP_PROFILE; alter user sys profile c##APP_PROFILE; ##change password alter user system profile default; alter user system identified by oracle; alter user sys profile default; alter user sys identified by oracle; alter user C##OPER profile default; alter user C##OPER identified by oper123; alter user sys profile c##APP_PROFILE; alter user system profile c##APP_PROFILE; alter user C##OPER profile c##APP_PROFILE; For pDBs run as DBa user pDB alter user IC_ADMIN profile APP_PROFILE; alter user IC_READONLY profile APP_PROFILE; alter user IC_USER profile APP_PROFILE; alter user oper profile APP_PROFILE; alter user PDBADMIN profile APP_PROFILE; ##change password alter user IC_ADMIN profile default; alter user IC_ADMIN identified by ic_admin12cu; alter user IC_READONLY profile default; alter user IC_READONLY identified by ic_readonly12cu; alter user IC_USER profile default; alter user IC_USER identified by ic_user12cu; alter user oper profile default; alter user oper identified by oper123; alter user IC_ADMIN profile APP_PROFILE; alter user IC_READONLY profile APP_PROFILE; alter user IC_USER profile APP_PROFILE; alter user oper profile APP_PROFILE;
2.for 11.2.0.4
CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- Check for the minimum length of the password
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
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(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
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 findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
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(-20004, 'Password should differ by at \
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
GRANT EXECUTE ON verify_function TO PUBLIC;
drop profile APP_PROFILE;
CREATE PROFILE APP_PROFILE LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION verify_function
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
;
--@env_DBdev.sql
define v_DBdata_un=DBdata
define v_DBdata_pw=DBdata.123
define v_DBusr_un=DBusr
define v_DBusr_pw=DBdev_usr2
define v_DBquery_un=DBquery
define v_DBquery_pw=DBdev_query1
define v_DBpatch_un=DBpatch
define v_DBpatch_pw=DBdev_patch1
##change password
alter user system profile default;
alter user system identified by oracle;
alter user sys profile default;
alter user sys identified by oracle;
alter user &V_DBDATA_UN profile default;
alter user &V_DBUSR_UN profile default;
alter user &V_DBPATCH_UN profile default;
alter user &V_DBQUERY_UN profile default;
alter user &V_DBDATA_UN identified by "&V_DBDATA_PW" ;
alter user &V_DBUSR_UN identified by "&V_DBUSR_PW" ;
alter user &V_DBPATCH_UN identified by "&V_DBPATCH_PW" ;
alter user &V_DBQUERY_UN identified by "&V_DBQUERY_PW" ;
alter user &V_DBDATA_UN profile APP_PROFILE;
alter user &V_DBUSR_UN profile APP_PROFILE;
alter user &V_DBUSR_UN profile APP_PROFILE;
alter user &V_DBUSR_UN profile APP_PROFILE;
alter user system profile APP_PROFILE;
alter user sys profile APP_PROFILE;
https://blog.csdn.net/wwlhz/article/details/68059524
更改Oracle用户的idle_time
https://blog.csdn.net/gelyon/article/details/6586790