use.sql

-- -----------------------------------------------------------------------------
--                 WWW.PETEFINNIGAN.COM LIMITED
-- -----------------------------------------------------------------------------
-- Script Name : use.sql
-- Author      : Pete Finnigan
-- Date        : September 2007
-- -----------------------------------------------------------------------------
-- Description : Use this script to give an indication of the type of user that
--               the figures relate to. This is done by doing simple comparisons
--               of privilege quantities and object quantities to assess what
--               type of user each is.
-- -----------------------------------------------------------------------------
-- Maintainer  : Pete Finnigan (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
--               /use.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         Sep 2007  First Issue.
-- P.Finnigan  1.0.1       Sep 2007  Bug fixes
-- P.Finnigan  1.0.2       Oct 2007  Added flags for output
-- -----------------------------------------------------------------------------
--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 use.lis

undefine output_flag

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 use.sql: Release 1.0.2.0.0 - Production on &val_system_date
prompt Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.
prompt
accept output_flag char prompt  'OUTPUT FLAG [A|O|C]                     [A]: ' default A
prompt

declare
	cursor c_main is
	select username,
		account_status
	from dba_users
	order by user_id;
	--
	cursor c_role_name (cp_user in varchar2) is
	select granted_role
	from dba_role_privs
	where grantee=cp_user;
	--
	cursor c_rol (cp_user in varchar) is
	select count(*) sum_role
	from dba_role_privs
	where grantee=cp_user;
	--
	cursor c_tab (cp_user in varchar) is
	select count(*) sum_tab
	from dba_tables
	where owner=cp_user;
	--
	cursor c_pl (cp_user in varchar) is
	select count(*) sum_pl
	from dba_objects
	where object_type in('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
	and owner=cp_user;
	--
	cursor c_sys (cp_user in varchar) is
	select count(*) sum_sys
	from dba_sys_privs
	where grantee=cp_user;
	--
	cursor c_ob (cp_user in varchar) is
	select count(*) sum_ob
	from dba_tab_privs
	where grantee=cp_user;

	lv_sys c_sys%rowtype;
	lv_pl c_pl%rowtype;
	lv_tab c_tab%rowtype;
	lv_rol c_rol%rowtype;
	lv_ob c_ob%rowtype;
	lv_def varchar2(3):='---';
	lv_flag varchar2(1):='A';
	lv_print boolean:=FALSE;
	--
	type role_priv_t is record (
		role_count number,
		sys_priv_count number,
		object_priv_count number,
		is_builtin boolean
	);
	rpcount role_priv_t;
	--
	lv_role_privs varchar2(30):='';
	lv_user_type varchar2(30):='';
	lv_built varchar2(1):='N';
	--
	function is_builtin(fv_role in varchar2) return boolean
	is
		lv_ret boolean:=FALSE;
	begin
		if(fv_role in (
			'CONNECT',
			'RESOURCE',
			'DBA',
			'SELECT_CATALOG_ROLE',
			'EXECUTE_CATALOG_ROLE',
			'DELETE_CATALOG_ROLE',
			'EXP_FULL_DATABASE',
			'IMP_FULL_DATABASE',
			'LOGSTDBY_ADMINISTRATOR',
			'AQ_ADMINISTRATOR_ROLE',
			'AQ_USER_ROLE',
			'DATAPUMP_EXP_FULL_DATABASE',
			'DATAPUMP_IMP_FULL_DATABASE',
			'GATHER_SYSTEM_STATISTICS',
			'RECOVERY_CATALOG_OWNER',
			'SCHEDULER_ADMIN',
			'HS_ADMIN_ROLE',
			'GLOBAL_AQ_USER_ROLE',
			'OEM_ADVISOR',
			'OEM_MONITOR',
			'WM_ADMIN_ROLE',
			'JAVAUSERPRIV',
			'JAVAIDPRIV',
			'JAVASYSPRIV',
			'JAVADEBUGPRIV',
			'EJBCLIENT',
			'JMXSERVER',
			'JAVA_ADMIN',
			'JAVA_DEPLOY',
			'CTXAPP',
			'XDBADMIN',
			'XDB_SET_INVOKER',
			'AUTHENTICATEDUSER',
			'XDB_WEBSERVICES',
			'XDB_WEBSERVICES_WITH_PUBLIC',
			'XDB_WEBSERVICES_OVER_HTTP',
			'ORDADMIN',
			'OLAPI_TRACE_USER',
			'OLAP_XS_ADMIN',
			'OLAP_DBA',
			'CWM_USER',
			'OLAP_USER',
			'SPATIAL_WFS_ADMIN',
			'WFS_USR_ROLE',
			'SPATIAL_CSW_ADMIN',
			'CSW_USR_ROLE',
			'WKUSER',
			'OWB$CLIENT',
			'OWB_DESIGNCENTER_VIEW',
			'OWB_USER') ) then
			return(TRUE);
		end if;
		return(FALSE);
	end;
	--
	-- -------------------------------------------------------
	-- This function gives an indication of the count of
	-- roles, system privileges and object privileges exposed
	-- by a single role. This is shown as [R,S,O]
	-- -------------------------------------------------------
	procedure get_count(pv_role in varchar2, rp in out role_priv_t)
	is
		--
		cursor c_main (cp_grantee in varchar2) is
		select  'R' typ,
		    grantee grantee,
		    granted_role priv,
		    admin_option ad,
		    '--' tabnm,
		    '--' colnm,
		    '--' owner
		from    dba_role_privs
		where   grantee=cp_grantee
		union
		select  'S' typ,
		    grantee grantee,
		    privilege priv,
		    admin_option ad,
		    '--' tabnm,
		    '--' colnm,
		    '--' owner
		from    dba_sys_privs
		where   grantee=cp_grantee
		union
		select  'T' typ,
		    grantee grantee,
		    privilege priv,
		    grantable ad,
		    table_name tabnm,
		    '--' colnm,
		    owner owner
		from    dba_tab_privs
		where   grantee=cp_grantee
		union
		select  'C' typ,
		    grantee grantee,
		    privilege priv,
		    grantable ad,
		    table_name tabnm,
		    column_name colnm,
		    owner owner
		from    dba_col_privs
		where   grantee=cp_grantee
		order by 1;
        --
	begin
		for lv_main in c_main(pv_role) loop
			if(lv_main.typ='R') then
				rp.role_count:=rp.role_count+1;
				if(is_builtin(lv_main.priv)) then
					rp.is_builtin:=TRUE;
				end if;
				get_count(lv_main.priv,rp);
			elsif(lv_main.typ='S') then
				rp.sys_priv_count := rp.sys_priv_count+1;
			elsif(lv_main.typ='T') then
				rp.object_priv_count:=rp.object_priv_count+1;
			elsif(lv_main.typ='C') then
				rp.object_priv_count:=rp.object_priv_count+1;
			end if;
		end loop;
	end;
	--
begin
	--
	lv_flag:=upper('&&output_flag');

	--
	rpcount.role_count:=0;
	rpcount.sys_priv_count:=0;
	rpcount.object_priv_count:=0;
	rpcount.is_builtin:=FALSE;
	--
	if(lv_flag='A' or lv_flag='O') then
		dbms_output.put_line('Typ'||chr(9)
				||'USER'||chr(9)||chr(9)
				||'Rol'||chr(9)
				||'RSO'||chr(9)||chr(9)
				||'Sys'||chr(9)
				||'Ob'||chr(9)
				||'Tab'||chr(9)
				||'PL'||chr(9));
		dbms_output.put_line('================================================================================');
	else
		dbms_output.put_line('USER'||chr(9)||chr(9)
				||'Rol'||chr(9)
				||'RSO'||chr(9)||chr(9)
				||'Sys'||chr(9)
				||'Ob'||chr(9)
				||'Tab'||chr(9)
				||'PL'||chr(9));
		dbms_output.put_line('==========================================================================');
	end if;
	for lv_main in c_main loop
		lv_def:='---';
		open c_rol(lv_main.username);
		fetch c_rol into lv_rol;
		if c_rol%notfound then
			lv_rol.sum_role:=0;
		end if;
		close c_rol;
		open c_tab(lv_main.username);
		fetch c_tab into lv_tab;
		if c_tab%notfound then
			lv_tab.sum_tab:=0;
		end if;
		close c_tab;
		open c_pl(lv_main.username);
		fetch c_pl into lv_pl;
		if c_pl%notfound then
			lv_pl.sum_pl:=0;
		end if;
		close c_pl;
		open c_sys(lv_main.username);
		fetch c_sys into lv_sys;
		if c_sys%notfound then
			lv_sys.sum_sys:=0;
		end if;
		close c_sys;
		open c_ob(lv_main.username);
		fetch c_ob into lv_ob;
		if c_ob%notfound then
			lv_ob.sum_ob:=0;
		end if;
		close c_ob;
		--
		if lv_main.username in (
				'DBSNMP',
				'OUTLN',
				'WMSYS',
				'ORDSYS',
				'ORDPLUGINS',
				'MDSYS',
				'CTXSYS',
				'XDB',
				'ANONYMOUS',
				'WKSYS',
				'WKPROXY',
				'ODM',
				'ODM_MTR',
				'OLAPSYS',
				'RMAN',
				'QS_ADM',
				'QS',
				'QS_WS',
				'QS_ES',
				'QS_OS',
				'QS_CBADM',
				'QS_CB',
				'QS_CS',
				'MGMT_VIEW',
				'EXFSYS',
				'WK_TEST',
				'OWBSYS',
				'SI_INFORMTN_SCHEMA',
				'ORACLE_OCM',
				'TSMSYS',
				'XS$NULL',
				'MDDATA',
				'DIP',
				'SPATIAL_CSW_ADMIN_USR',
				'SPATIAL_WFS_ADMIN_USR') then
			lv_def:='DEF';
		end if;
		if lv_main.username in('SYS','SYSTEM','SYSMAN') then
			lv_def:='ADM';
		end if;
		if lv_main.username in('FLOWS_FILES','FLOWS_030000','APEX_PUBLIC_USER') then
			lv_def:='APX';
		end if;
		if lv_main.username in('SCOTT','HR','OE','PM','SH','BI','IX') then
			lv_def:='SAM';
		end if;
		-- -----------------------------------------------------------------
		-- get the privilege counts from the roles. if the role count is
		-- greater than zero then use the username as the first entry
		-- -----------------------------------------------------------------
		rpcount.role_count:=0;
		rpcount.sys_priv_count:=0;
		rpcount.object_priv_count:=0;
		rpcount.is_builtin:=FALSE;

		if(lv_flag='C' or(lv_flag='A' and lv_def='---') ) then
			if(lv_rol.sum_role!=0) then
				for lv_role_name in c_role_name(lv_main.username) loop
					rpcount.role_count:=rpcount.role_count+1;
					if(is_builtin(lv_role_name.granted_role)) then
						rpcount.is_builtin:=TRUE;
					end if;
					get_count(lv_role_name.granted_role,rpcount);
				end loop;
				if(rpcount.is_builtin) then
					lv_built:='B';
				else
					lv_built:='N';
				end if;
				lv_role_privs:=rpad(lv_built||','||rpcount.role_count||','
					||rpcount.sys_priv_count||','||rpcount.object_priv_count,14);
			else
				lv_role_privs:='              ';
			end if;
		else
			lv_role_privs:='              ';
		end if;
		-- -----------------------------------------------------------------
		-- test the input flag and only output all if its set to "A"
		-- or output Oracle accounts if set to "O" or customer accounts
		-- if its set to "C".
		-- -----------------------------------------------------------------
		if(lv_flag='A') then
			lv_print:=TRUE;
			lv_user_type:=lv_def||chr(9);
		elsif(lv_flag='C' and lv_def='---') then
			lv_print:=TRUE;
			lv_user_type:='';
		elsif(lv_flag='O' and lv_def<>'---') then
			lv_print:=TRUE;
			lv_user_type:=lv_def||chr(9);
		end if;
		if(lv_print=TRUE) then
			if(length(lv_main.username) < 8) then
				dbms_output.put_line(lv_user_type
					||lv_main.username||chr(9)||chr(9)
					||lv_rol.sum_role||chr(9)
					||lv_role_privs||chr(9)
					||lv_sys.sum_sys||chr(9)
					||lv_ob.sum_ob||chr(9)
					||lv_tab.sum_tab||chr(9)
					||lv_pl.sum_pl||chr(9));
			else
				dbms_output.put_line(lv_user_type
					||substr(lv_main.username,1,9)||chr(9)
					||lv_rol.sum_role||chr(9)
					||lv_role_privs||chr(9)
					||lv_sys.sum_sys||chr(9)
					||lv_ob.sum_ob||chr(9)
					||lv_tab.sum_tab||chr(9)
					||lv_pl.sum_pl||chr(9));
			end if;
		end if;
		lv_print:=FALSE;
	end loop;
	--
	if(lv_flag='A' or lv_flag = 'O') then
		dbms_output.put_line('================================================================================');
		dbms_output.put_line('Typ'||chr(9)
				||'USER'||chr(9)||chr(9)
				||'Rol'||chr(9)
				||'RSO'||chr(9)||chr(9)
				||'Sys'||chr(9)
				||'Ob'||chr(9)
				||'Tab'||chr(9)
				||'PL'||chr(9));
	else
		dbms_output.put_line('==========================================================================');
		dbms_output.put_line('USER'||chr(9)||chr(9)
				||'Rol'||chr(9)
				||'RSO'||chr(9)||chr(9)
				||'Sys'||chr(9)
				||'Ob'||chr(9)
				||'Tab'||chr(9)
				||'PL'||chr(9));
	end if;	--
end;
/

prompt For updates please visit www.petefinnigan.com/use.sql
prompt
spool off

whenever sqlerror continue