Thursday, March 31, 2022

Script - Enable Customized Password Complexity in Oracle non-cdb or cdb environment

The script creates a password complexity verification function, and enable the function with database profile. It also sets password policy with profile.

Script notes:

1. The script is executed by SYS in single instance database or RAC database

2. The script enable password complexity and policy by setting database profile, by default, it will change DEFAULT profile, it can be changed by changing the value of SQL*Plus substitution variable S_PROFILE in the script.

3. The verification function name (default name is dbaplus_verify_function) and all password complexity and password policy options can be changed by yourself with the substitution variables (define statements in the script)

4. The script can be executed in non-cdb or pluggable database (pdb) which is open in READ WRITE mode

5. If script is executed in root container (CDB$ROOT) of container database (CDB), it will apply the changes in all pdbs which are open in READ WRITE mode. If seed pdb (PDB$SEED) is open in READ ONLY mode, it will be re-open in READ WRITE mode to apply the change, then re-open in READ ONLY mode. Any pdbs which is not open will be skipped.
--------------------------------------------------
define S_VERIFY_FUNCTION='dbaplus_verify_function'
define S_PROFILE='DEFAULT'
define S_PASSWORD_LIFE_TIME='unlimited'
define S_PASSWORD_GRACE_TIME='7'
define S_PASSWORD_REUSE_TIME='1'
define S_PASSWORD_REUSE_MAX='10'
define S_PASSWORD_LOCK_TIME='.0104'
define S_FAILED_LOGIN_ATTEMPTS='5'
define S_NO_OF_CHAR='12'
define S_NO_OF_DIFF='1'
define S_NO_OF_UPPERCASE='1'
define S_NO_OF_LOWERCASE='1'
define S_NO_OF_DIGIT='1'
define S_NO_OF_SPECIAL='1'
--------------------------------------------------
set echo off
set verify off
set feedback off
set trimspool on
set serveroutput on
declare
  isRAC varchar2(5);
  isContainer varchar2(5);
  i_return integer;
  i_handle integer;
  s_container varchar2(255);
  s_open_mode varchar2(255);
  -- SQL to grant execute privilege on the function to public
  s_grant varchar2(100) := 'grant execute on &S_VERIFY_FUNCTION to public';
  s_close_seed varchar2(255) := 'alter pluggable database PDB$SEED close immediate';
  s_open_seed varchar2(255) := 'alter pluggable database PDB$SEED open read only';
  s_open_rw_seed varchar2(255) := 'alter pluggable database PDB$SEED open read write';
  -- SQL to alter profile
  s_modify_profile varchar2(2000):=q'[
    alter profile &S_PROFILE limit
      password_life_time &S_PASSWORD_LIFE_TIME
      password_grace_time &S_PASSWORD_GRACE_TIME
      password_reuse_time &S_PASSWORD_REUSE_TIME
      password_reuse_max &S_PASSWORD_REUSE_MAX
      password_lock_time &S_PASSWORD_LOCK_TIME
      failed_login_attempts &S_FAILED_LOGIN_ATTEMPTS
      password_verify_function &S_VERIFY_FUNCTION]';
  -- PL/SQL block to create custom password verify function
  s_create_func varchar2(2000):=q'[
    create or replace function &S_VERIFY_FUNCTION( username varchar2, password varchar2, old_password varchar2)
     return boolean is
       p_diff    integer;
       p_lang    varchar2(512);
       p_message varchar2(512);
       p_return  number;
    begin
       -- Get the context p_lang and use utl_lms for p_messages
       p_lang := sys_context('userenv','lang');
       p_lang := substr(p_lang,1,instr(p_lang,'_')-1);
       --19c ora_complexity_check(password varchar2, chars number, letter number, uppercase number, lowercase number, digit number, special number)
       --12c ora_complexity_check(password varchar2, chars number, letter number, upper number, lower number, digit number, special number)
       if not ora_complexity_check(password, &S_NO_OF_CHAR,null,&S_NO_OF_UPPERCASE,&S_NO_OF_LOWERCASE,&S_NO_OF_DIGIT,&S_NO_OF_SPECIAL) then
          return(false);
       end if;
       -- Check if the password differs from the previous password by at least S_NO_OF_DIFF characters
       if old_password is not null then
          p_diff := ora_string_distance(old_password, password);
          if p_diff < &S_NO_OF_DIFF then
             p_return := utl_lms.get_message(28211, 'RDBMS', 'ORA', p_lang, p_message);
             raise_application_error(-20000, utl_lms.format_message(p_message, '&S_NO_OF_DIFF'));
          end if;
       end if;
       return(true);
    end;]';
begin
  -- CDB
  select value into isContainer from v$parameter where name='enable_pluggable_database';
  -- RAC database
  select value into isRAC from v$parameter where name='cluster_database';
  if isRAC='TRUE' then
     s_close_seed := s_close_seed||' instances=all';
     s_open_seed := s_open_seed||' instances=all';
  end if;
  s_container := sys_context('userenv','con_name');
  if s_container<>'CDB$ROOT' then -- In Non-CDB or PDB
     execute immediate s_create_func;
     execute immediate s_grant;
     execute immediate s_modify_profile;
     dbms_output.new_line;
     if isContainer='TRUE' then
        dbms_output.put_line('Succeeded in pluggable database '''||s_container||'''');
     else
        dbms_output.put_line('Succeeded in non-cdb database '''||s_container||'''');
     end if;
  else -- In root container CDB$ROOT
     execute immediate s_create_func;
     execute immediate s_grant;
     execute immediate s_modify_profile;
     dbms_output.new_line;
     dbms_output.put_line('Succeeded in root container '''||s_container||'''.');
     begin
       i_handle := dbms_sql.open_cursor;
       --for c1 in (select name,open_mode from v$pdbs where open_mode='READ WRITE' or (name='PDB$SEED' and open_mode='READ ONLY'))
       for c1 in (select name,open_mode from v$pdbs)
       loop
         s_open_mode := c1.open_mode;
         if c1.name='PDB$SEED' and c1.open_mode='READ ONLY' then
            execute immediate s_close_seed;
            execute immediate s_open_rw_seed;
            s_open_mode := 'READ WRITE';
         end if;
         if s_open_mode='READ WRITE' then
            dbms_sql.parse(c=>i_handle, statement=>s_create_func, language_flag=>DBMS_SQL.NATIVE, container=>c1.name);
            i_return := dbms_sql.execute(i_handle);
            dbms_sql.parse(c=>i_handle, statement=>s_grant, language_flag=>DBMS_SQL.NATIVE, container=>c1.name);
            i_return := dbms_sql.execute(i_handle);
            dbms_sql.parse(c=>i_handle, statement=>s_modify_profile, language_flag=>DBMS_SQL.NATIVE, container=>c1.name);
            i_return := dbms_sql.execute(i_handle);
            dbms_output.new_line;
            dbms_output.put_line('Succeeded in pluggable database '''||c1.name||'''.');
            if c1.name='PDB$SEED' then
               execute immediate s_close_seed;
               execute immediate s_open_seed;
            end if;
         else
            dbms_output.new_line;
            dbms_output.put_line('Skipped pluggable database '''||c1.name||''' which is in '''||s_open_mode||''' mode.');
         end if;
       end loop;
       dbms_sql.close_cursor(i_handle);
     exception
       when others then
         dbms_sql.close_cursor(i_handle);
         raise;
     end;
  end if;
  dbms_output.new_line;
end;
/

No comments: