.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();

   punctarray varchar2();

   chararray varchar2();

BEGIN

   digitarray:= '';

   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

   punctarray:='!"#$%&()``*+,-/:;<=>?_';

   -- Check if the password is same as the username

   IF NLS_LOWER(password) = NLS_LOWER(username) THEN

     raise_application_error(-, 'Password same as or similar to user');

   END IF;

   -- Check for the minimum length of the password

   IF length(password) <  THEN

      raise_application_error(-, '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(-, 'Password too simple');

   END IF;

   -- Check if the password contains at least one letter, one digit and one

   -- punctuation mark.

   -- . Check for the digit

   isdigit:=FALSE;

   m := length(password);

   FOR i IN .. LOOP

      FOR j IN ..m LOOP

         IF substr(password,j,) = substr(digitarray,i,) THEN

            isdigit:=TRUE;

             GOTO findchar;

         END IF;

      END LOOP;

   END LOOP;

   IF isdigit = FALSE THEN

      raise_application_error(-, 'Password should contain at least one digit, one character and one punctuation');

   END IF;

   -- . Check for the character

   <<findchar>>

   ischar:=FALSE;

   FOR i IN ..length(chararray) LOOP

      FOR j IN ..m LOOP

         IF substr(password,j,) = substr(chararray,i,) THEN

            ischar:=TRUE;

             GOTO findpunct;

         END IF;

      END LOOP;

   END LOOP;

   IF ischar = FALSE THEN

      raise_application_error(-, 'Password should contain at least one \

              digit, one character and one punctuation');

   END IF;

   -- . Check for the punctuation

   <<findpunct>>

   ispunct:=FALSE;

   FOR i IN ..length(punctarray) LOOP

      FOR j IN ..m LOOP

         IF substr(password,j,) = substr(punctarray,i,) THEN

            ispunct:=TRUE;

             GOTO endsearch;

         END IF;

      END LOOP;

   END LOOP;

   IF ispunct = FALSE THEN

      raise_application_error(-, '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

   --  letters

   IF old_password IS NOT NULL THEN

     differ := length(old_password) - length(password);

     IF abs(differ) <  THEN

       IF length(password) < length(old_password) THEN

         m := length(password);

       ELSE

         m := length(old_password);

       END IF;

       differ := abs(differ);

       FOR i IN ..m LOOP

         IF substr(password,i,) != substr(old_password,i,) THEN

           differ := differ + ;

         END IF;

       END LOOP;

       IF differ <  THEN

         raise_application_error(-, 'Password should differ by at \

         least  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

PASSWORD_GRACE_TIME

;

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;

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;

关于Oracle profile文件connect_time时间限制超时的问题探究