17 | 08 | 2017
Latest Articles
Popular Articles

Scripts

Create a profile with unlimited password lifetime ( 11G )

User Rating:  / 2
PoorBest 

Create a profile with unlimited password lifetime ( 11G ).

 

Meant for Oracle 11G if you do not want that password lifetime is limited.

( starting from 11G R1 password lifetime is limited t 180 days, ... )

 

set serveroutput on;
set linesize 200;
set pagesize0;

PROMPT "We create a non default profile";

CREATE  PROFILE "DEFAULT_UNEXPIRE"  LIMIT CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;


PROMPT "The user which have the default profile and are not locked are mapped with the new profile";


declare

statement varchar2(1000);
cursor lc1 is select username,account_status,profile from dba_users;
in_profile varchar2(20)   := 'DEFAULT';
out_profile varchar2(20 ) := 'DEFAULT_UNEXPIRE';

begin

for i in lc1 loop

/* WE DON 'T TOUCH THOSE USERS WHICH ALREADY HAVE A NON DEFAULT PROFILE */

if i.profile != in_profile then

dbms_output.put_line('Profile not changed for user '||i.username||' account status '||i.account_status||' because user does not have '||in_profile);

end if;


/* WE DON 'T TOUCH THOSE USERS WHICH ARE ALREADY LOCKED OR EXPIRED */

if i.account_status != 'OPEN' then

dbms_output.put_line('Profile not changed for user '||i.username||' account status '||i.account_status||' because account status is not OPEN');

end if;


/* WE TOUCH THOSE USERS WHICH HAVE A DEFAULT PROFILE AND AREN' T LOCKED NOR EXPIRED */

if ( i.account_status = 'OPEN' and i.profile = in_profile ) then

statement := q'! alter user !'||i.username||q'! profile !'||out_profile;

execute immediate (statement);

dbms_output.put_line('Profile changed for user '||i.username||' account status '||i.account_status);

end if;


end loop;

end;
/




Profile not changed for user TUNING account status OPEN because user does not have DEFAULT
Profile not changed for user LAURENT account status OPEN because user does not have DEFAULT
Profile changed for user YORICK account status OPEN
Profile changed for user TEST11G account status OPEN
Profile not changed for user APEX_PUBLIC_USER account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user DIP account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user XS$NULL account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user ORACLE_OCM account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user SYSMAN account status OPEN because user does not have DEFAULT
Profile not changed for user DBSNMP account status OPEN because user does not have DEFAULT
Profile not changed for user SI_INFORMTN_SCHEMA account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user ORDPLUGINS account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user XDB account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user ANONYMOUS account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user CTXSYS account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user ORDDATA account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user APEX_030200 account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user APPQOSSYS account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user WMSYS account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user EXFSYS account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user ORDSYS account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user MDSYS account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user FLOWS_FILES account status EXPIRED & LOCKED because account status is not OPEN
Profile not changed for user SYSTEM account status OPEN because user does not have DEFAULT
Profile not changed for user SYS account status OPEN because user does not have DEFAULT
Profile not changed for user MGMT_VIEW account status OPEN because user does not have DEFAULT
Profile not changed for user OUTLN account status EXPIRED & LOCKED because account status is not OPEN

PL/SQL procedure successfully completed.