-- ----------------------------------------------------------------------------- -- WWW.PETEFINNIGAN.COM LIMITED -- ----------------------------------------------------------------------------- -- Script Name : who_can_access.sql -- Author : Pete Finnigan -- Date : Jan 2004 -- ----------------------------------------------------------------------------- -- Description : This script can be used to find who can access an object that -- is passed in. It checks recursively for users hierarchically via -- roles. -- -- The output can be directed to either the screen via dbms_output -- or to a file via utl_file. The method is decided at run time -- by choosing either 'S' for screen or 'F' for File. If File is -- chosen then a filename and output directory are needed. The -- output directory needs to be enabled via utl_file_dir prior to -- 9iR2 and a directory object or utl_file_dir after. -- -- Limitations : 1. Access granted via "ANY" privileges such as SELECT ANY TABLE -- or EXECUTE ANY PROCEDURE are not considered in this script -- at present. It may be added if demand shows users want it. -- My own view is that %ANY% privileges should be checked -- separately. My script who_has_priv.sql can be used for this. -- 2. SYS owned base tables such as sys.obj$ are used to find out -- the objects type as using dictionary views such as DBA_OBJECTS -- and DBA_TAB_PRIVS causes extra joins to occur because many -- objects having the same name. This means that the script must -- be run as a user who has access to these tables. -- ----------------------------------------------------------------------------- -- Maintainer : Pete Finnigan (http://www.petefinnigan.com) -- Copyright : Copyright (C) 2004 PeteFinnigan.com Limited. All rights -- reserved. All registered trademarks are the property of their -- respective owners and are hereby acknowledged. -- ----------------------------------------------------------------------------- -- Usage : The script provided here is available free. You can do anything -- you want with it commercial or non commercial as long as the -- copyrights and this notice are not removed or edited in any way. -- The scripts cannot be posted / published / hosted or whatever -- anywhere else except at www.petefinnigan.com/tools.htm -- ----------------------------------------------------------------------------- -- Version History -- =============== -- -- Who version Date Description -- === ======= ====== ====================== -- P.Finnigan 1.0 Feb 2004 First Issue. -- P.Finnigan 1.1 Oct 2004 Added usage notes. -- P.Finnigan 1.2 Apr 2005 Added whenever sqlerror continue to stop -- subsequent errors barfing SQL*Plus. Thanks -- to Norman Dunbar for the update. -- P.Finnigan 1.3 May 2005 Added two new parameters to allow specification -- of users to be ommited from the report -- 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 clear screen set serveroutput on size 1000000 spool who_can_access.lis undefine object_to_find undefine owner_to_find undefine output_method undefine file_name undefine output_dir undefine skip_user undefine user_to_skip 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; prompt who_can_access: Release 1.0.3.0.0 - Production on &val_system_date prompt Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. set feed on prompt accept object_to_find char prompt 'NAME OF OBJECT TO CHECK [USER_OBJECTS]: ' default USER_OBJECTS accept owner_to_find char prompt 'OWNER OF THE OBJECT TO CHECK [USER]: ' default USER accept output_method char prompt 'OUTPUT METHOD Screen/File [S]: ' default S accept file_name char prompt 'FILE NAME FOR OUTPUT [priv.lst]: ' default priv.lst accept output_dir char prompt 'OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]: ' default /tmp accept skip_user char prompt 'EXCLUDE CERTAIN USERS [N]: ' default N accept user_to_skip char prompt 'USER TO SKIP [TEST%]: ' default TEST% prompt declare -- lg_fptr utl_file.file_type; lv_file_or_screen varchar2(1):='S'; -- procedure open_file (pv_file_name in varchar2, pv_dir_name in varchar2) is begin lg_fptr:=utl_file.fopen(pv_dir_name,pv_file_name,'A'); exception when utl_file.invalid_path then dbms_output.put_line('invalid path'); when utl_file.invalid_mode then dbms_output.put_line('invalid mode'); when utl_file.invalid_filehandle then dbms_output.put_line('invalid filehandle'); when utl_file.invalid_operation then dbms_output.put_line('invalid operation'); when utl_file.read_error then dbms_output.put_line('read error'); when utl_file.write_error then dbms_output.put_line('write error'); when utl_file.internal_error then dbms_output.put_line('internal error'); when others then dbms_output.put_line('ERROR (open_file) => '||sqlcode); dbms_output.put_line('MSG (open_file) => '||sqlerrm); end open_file; -- procedure close_file is begin utl_file.fclose(lg_fptr); exception when utl_file.invalid_path then dbms_output.put_line('invalid path'); when utl_file.invalid_mode then dbms_output.put_line('invalid mode'); when utl_file.invalid_filehandle then dbms_output.put_line('invalid filehandle'); when utl_file.invalid_operation then dbms_output.put_line('invalid operation'); when utl_file.read_error then dbms_output.put_line('read error'); when utl_file.write_error then dbms_output.put_line('write error'); when utl_file.internal_error then dbms_output.put_line('internal error'); when others then dbms_output.put_line('ERROR (close_file) => '||sqlcode); dbms_output.put_line('MSG (close_file) => '||sqlerrm); end close_file; -- procedure write_op (pv_str in varchar2) is begin if lv_file_or_screen='S' then dbms_output.put_line(pv_str); else utl_file.put_line(lg_fptr,pv_str); end if; exception when utl_file.invalid_path then dbms_output.put_line('invalid path'); when utl_file.invalid_mode then dbms_output.put_line('invalid mode'); when utl_file.invalid_filehandle then dbms_output.put_line('invalid filehandle'); when utl_file.invalid_operation then dbms_output.put_line('invalid operation'); when utl_file.read_error then dbms_output.put_line('read error'); when utl_file.write_error then dbms_output.put_line('write error'); when utl_file.internal_error then dbms_output.put_line('internal error'); when others then dbms_output.put_line('ERROR (write_op) => '||sqlcode); dbms_output.put_line('MSG (write_op) => '||sqlerrm); end write_op; -- function user_or_role(pv_grantee in dba_users.username%type) return varchar2 is -- cursor c_use (cp_grantee in dba_users.username%type) is select 'USER' userrole from dba_users u where u.username=cp_grantee union select 'ROLE' userrole from dba_roles r where r.role=cp_grantee; -- lv_use c_use%rowtype; begin open c_use(pv_grantee); fetch c_use into lv_use; close c_use; return lv_use.userrole; exception when others then dbms_output.put_line('ERROR (user_or_role) => '||sqlcode); dbms_output.put_line('MSG (user_or_role) => '||sqlerrm); end user_or_role; -- procedure get_obj (pv_object in varchar2,pv_owner in varchar2) is -- cursor c_main (cp_object in varchar2,cp_owner in varchar2) is select g.name grantee, decode(o.type#,2,'TABLE', 4,'VIEW', 6,'SEQUENCE', 7,'PROCEDURE', 8,'FUNCTION', 9,'PACKAGE', 13,'TYPE', 22,'LIBRARY', 23,'DIRECTORY', 24,'QUEUE', 29,'JAVA CLASS', 30,'JAVA RESOURCE', 32,'INDEXTYPE', 33,'OPERATOR', 48,'CONSUMER GROUP', 62,'EVALUATION CONTEXT', 'UNDEFINED') object_type, t.name privilege, decode(mod(a.option$,2),2,'YES','NO') grantable, '--' column_name, 'TAB' coltype from sys.objauth$ a, sys.obj$ o, sys.user$ u, sys.user$ g, sys.table_privilege_map t where a.obj#=o.obj# and a.grantee#=g.user# and a.col# is null and a.privilege#=t.privilege and u.user#=o.owner# and u.name=cp_owner and o.name=cp_object union select g.name grantee, decode(o.type#,2,'TABLE', 4,'VIEW', 6,'SEQUENCE', 7,'PROCEDURE', 8,'FUNCTION', 9,'PACKAGE', 13,'TYPE', 22,'LIBRARY', 23,'DIRECTORY', 24,'QUEUE', 29,'JAVA CLASS', 30,'JAVA RESOURCE', 32,'INDEXTYPE', 33,'OPERATOR', 48,'CONSUMER GROUP', 62,'EVALUATION CONTEXT', 'UNDEFINED') object_type, t.name privilege, decode(mod(a.option$,2),2,'YES','NO') grantable, c.name column_name, 'COL' coltype from sys.objauth$ a, sys.obj$ o, sys.user$ u, sys.user$ g, sys.col$ c, sys.table_privilege_map t where a.obj#=o.obj# and a.grantee#=g.user# and a.col#=c.col# and bitand(c.property,32)=0 and a.col# is not null and a.privilege#=t.privilege and u.user#=o.owner# and u.name=cp_owner and o.name=cp_object order by 2,3,6; -- lv_old_type dba_objects.object_type%type:='NOTSET'; lv_old_priv dba_tab_privs.privilege%type:='NOTSET'; -- lv_userrole dba_users.username%type; lv_tabstop number; -- procedure get_users(pv_grantee in dba_roles.role%type,pv_tabstop in out number) is -- lv_tab varchar2(50):=''; lv_loop number; lv_user_or_role dba_users.username%type; -- cursor c_user (cp_username in dba_role_privs.grantee%type) is select r.grantee, r.admin_option from dba_role_privs r where r.granted_role=cp_username; -- begin pv_tabstop:=pv_tabstop+1; for lv_loop in 1..pv_tabstop loop lv_tab:=lv_tab||chr(9); end loop; for lv_user in c_user(pv_grantee) loop lv_user_or_role:=user_or_role(lv_user.grantee); if lv_user_or_role = 'ROLE' then if lv_user.grantee = 'PUBLIC' then write_op(lv_tab||'Role => '||lv_user.grantee ||' (ADM = '||lv_user.admin_option||')'); else write_op(lv_tab||'Role => '||lv_user.grantee ||' (ADM = '||lv_user.admin_option||')' ||' which is granted to =>'); end if; get_users(lv_user.grantee,pv_tabstop); else if upper('&&skip_user') = 'Y' and lv_user.grantee like upper('&&user_to_skip') then null; else write_op(lv_tab||'User => '||lv_user.grantee ||' (ADM = '||lv_user.admin_option||')'); end if; end if; end loop; pv_tabstop:=pv_tabstop-1; lv_tab:=''; exception when others then dbms_output.put_line('ERROR (get_users) => '||sqlcode); dbms_output.put_line('MSG (get_users) => '||sqlerrm); end get_users; -- begin write_op(chr(10)); lv_tabstop:=1; for lv_main in c_main(pv_object,pv_owner) loop if (lv_old_type != lv_main.object_type) then write_op('Object type is => '||lv_main.object_type||' ('||lv_main.coltype||') '); end if; if (lv_old_priv != lv_main.privilege) or (lv_old_type != lv_main.object_type) then write_op(chr(9)||'Privilege => '||lv_main.privilege||' is granted to =>'); end if; lv_userrole:=user_or_role(lv_main.grantee); if lv_userrole='USER' then if upper('&&skip_user') = 'Y' and lv_main.grantee like upper('&&user_to_skip') then null; else write_op(chr(9)||'User => '||lv_main.grantee ||' (ADM = '||lv_main.grantable||')'); end if; else if lv_main.grantee='PUBLIC' then write_op(chr(9)||'Role => '||lv_main.grantee ||' (ADM = '||lv_main.grantable||')'); else write_op(chr(9)||'Role => '||lv_main.grantee ||' (ADM = '||lv_main.grantable||')' ||' which is granted to =>'); end if; get_users(lv_main.grantee,lv_tabstop); end if; lv_old_type:=lv_main.object_type; lv_old_priv:=lv_main.privilege; end loop; exception when others then dbms_output.put_line('ERROR (get_obj) => '||sqlcode); dbms_output.put_line('MSG (get_obj) => '||sqlerrm); end get_obj; begin lv_file_or_screen:= upper('&&output_method'); if lv_file_or_screen='F' then open_file('&&file_name','&&output_dir'); end if; write_op('Checking object => '||upper('&&owner_to_find')||'.'||upper('&&object_to_find')); write_op('===================================================================='); get_obj(upper('&&object_to_find'),upper('&&owner_to_find')); if lv_file_or_screen='F' then close_file; end if; exception when others then dbms_output.put_line('ERROR (main) => '||sqlcode); dbms_output.put_line('MSG (main) => '||sqlerrm); end; / prompt prompt For updates please visit http://www.petefinnigan.com/tools.htm prompt spool off whenever sqlerror continue