首页 > 代码库 > fix for 12c profile

fix for 12c profile

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 oracle123; alter user sys profile default; alter user sys identified by oracle123; 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;

 

fix for 12c profile