who_can_access.sql
Download SQL script
who_can_access.sql
-- -----------------------------------------------------------------------------
-- 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 (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 www.petefinnigan.com/tools.htm
prompt
spool off
whenever sqlerror continue
