-- ----------------------------------------------------------------------------- -- WWW.PETEFINNIGAN.COM LIMITED -- ----------------------------------------------------------------------------- -- Script Name : profiles.sql -- Author : Pete Finnigan -- Date : November 2007 -- ----------------------------------------------------------------------------- -- Description : Use this script to give an overview of the profile settings -- for each user in the dataabse. -- ----------------------------------------------------------------------------- -- Maintainer : Pete Finnigan (http://www.petefinnigan.com) -- Copyright : Copyright (C) 2007, 2009 PeteFinnigan.com Limited. All rights -- reserved. All registered trademarks are the property of their -- respective owners and are hereby acknowledged. -- ----------------------------------------------------------------------------- -- License : This software is free software BUT it is not in the public -- domain. This means that you can use it for personal or -- commercial work but you cannot remove this notice or copyright -- notices or the banner output by the program or edit them in any -- way at all. You also cannot host/distribute/copy or in anyway -- make this script available through any means either in original -- form or any derivitive work based on it. The script is -- only available from its own webpage -- http://www.petefinnigan.com/profiles.sql or any other page that -- PeteFinnigan.com Limited hosts it from. -- This script cannot be incorporated into any other free or -- commercial tools without permission from PeteFinnigan.com -- Limited. -- -- In simple terms use it for free but dont make it available in -- any way or build it into any other tools. -- ----------------------------------------------------------------------------- -- Version History -- =============== -- -- Who version Date Description -- === ======= ====== ====================== -- P.Finnigan 1.0 Nov 2007 First Issue. -- ----------------------------------------------------------------------------- --whenever sqlerror exit rollback set feed on set head on set arraysize 1 set space 1 set verify off set pages 25 set lines 80 set termout on set serveroutput on size 1000000 spool profiles.lis set feed off col system_date noprint new_value val_system_date select to_char(sysdate,'Dy Mon dd hh24:mi:ss yyyy') system_date from sys.dual; set feed on prompt profiles.sql: Release 1.0.0.0.0 - Production on &val_system_date prompt Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved. prompt declare -- cursor c_main is select substr(username,1,12) username, profile from dba_users; -- cursor c_profile (cp_name in varchar2) is select limit,resource_name from dba_profiles where profile=cp_name; -- lv_profile_str varchar2(200); type prof_t is record ( fl varchar2(3), pv varchar2(30), prt varchar2(3), spu varchar2(3), plt varchar2(3), prm varchar2(3), pgt varchar2(3), pli varchar2(3) -- password life time ); p prof_t; begin p.fl:=''; -- F - failed_login_attempts p.prt:=''; -- T - password_reuse_time p.spu:=''; -- S - sessions_per_user p.plt:=''; -- L - password_lock_time p.prm:=''; -- M - password_reuse_max p.pgt:=''; -- G - password_grace_time p.pli:=''; -- L - password_life_time p.pv:=NULL; dbms_output.put_line('USER '||chr(9)||'Profile'||chr(9)||chr(9)||'F T S L M G L V'); dbms_output.put_line('================================================================================'); for lv_main in c_main loop -- --------------------------------------------------------- -- open the profiles cursor and select out the details -- we should cache this really as lots of people share a -- profile -- --------------------------------------------------------- for lp in c_profile(lv_main.profile) loop -- dbms_output.put_line('here - '||lv_main.username||':'||lp.resource_name||':'||lp.limit); if(lp.resource_name='FAILED_LOGIN_ATTEMPTS') then if (lp.limit='UNLIMITED') then p.fl:='U'; elsif(lp.limit='DEFAULT') then p.fl:='D'; else p.fl:=lp.limit; end if; end if; if(lp.resource_name='PASSWORD_REUSE_TIME') then if (lp.limit='UNLIMITED') then p.prt:='U'; elsif(lp.limit='DEFAULT') then p.prt:='D'; else p.prt:=lp.limit; end if; end if; if(lp.resource_name='SESSIONS_PER_USER') then if (lp.limit='UNLIMITED') then p.spu:='U'; elsif(lp.limit='DEFAULT') then p.spu:='D'; else p.spu:=lp.limit; end if; end if; if(lp.resource_name='PASSWORD_LOCK_TIME') then if (lp.limit='UNLIMITED') then p.plt:='U'; elsif(lp.limit='DEFAULT') then p.plt:='D'; else p.plt:=lp.limit; end if; end if; if(lp.resource_name='PASSWORD_REUSE_MAX') then if (lp.limit='UNLIMITED') then p.prm:='U'; elsif(lp.limit='DEFAULT') then p.prm:='D'; else p.prm:=lp.limit; end if; end if; if(lp.resource_name='PASSWORD_GRACE_TIME') then if (lp.limit='UNLIMITED') then p.pgt:='U'; elsif(lp.limit='DEFAULT') then p.pgt:='D'; else p.pgt:=lp.limit; end if; end if; if(lp.resource_name='PASSWORD_LIFE_TIME') then if (lp.limit='UNLIMITED') then p.pli:='U'; elsif(lp.limit='DEFAULT') then p.pli:='D'; else p.pli:=lp.limit; end if; end if; if(lp.resource_name='PASSWORD_VERIFY_FUNCTION') then if (lp.limit='UNLIMITED') then p.pv:='U'; elsif(lp.limit='DEFAULT') then p.pv:='D'; else p.pv:=lp.limit; end if; end if; end loop; lv_profile_str:=rpad(p.fl,3)||' '||rpad(p.prt,3)||' '||rpad(p.spu,3) ||' '||rpad(p.plt,3)||' '||rpad(p.prm,3) ||' '||rpad(p.pgt,3)||' '||rpad(p.pli,3) ||' '||p.pv; p.fl:=''; p.pv:=NULL; p.prt:=''; p.spu:=''; p.plt:=''; p.prm:=''; p.pgt:=''; p.pli:=''; if(length(lv_main.username)<8) then dbms_output.put_line(lv_main.username||chr(9)||chr(9)||substr(rpad(lv_main.profile,10),1,10)||chr(9)||lv_profile_str); elsif(length(lv_main.username)<=12) then dbms_output.put_line(lv_main.username||chr(9)||substr(rpad(lv_main.profile,10),1,10)||chr(9)||lv_profile_str); end if; lv_profile_str:=''; end loop; dbms_output.put_line('================================================================================'); dbms_output.put_line('USER '||chr(9)||'Profile'||chr(9)||chr(9)||'F T S L M G L V'); end; / prompt For updates please visit http://www.petefinnigan.com/tools.htm prompt spool off whenever sqlerror continue