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:
Post a Comment