-- -----------------------------------------------------------------------------
--                 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
