-- ----------------------------------------------------------------------------- -- WWW.PETEFINNIGAN.COM LIMITED -- ----------------------------------------------------------------------------- -- Script Name : get_tab2.sql -- Author : Pete Finnigan -- Date : August 2004 -- ----------------------------------------------------------------------------- -- Description : Use this script assess privileges in the database as a flat -- model output. -- ----------------------------------------------------------------------------- -- Maintainer : Pete Finnigan (http://www.petefinnigan.com) -- Copyright : Copyright (C) 2004, 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/get_tab2.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 Aug 2007 First Issue. -- P.Finnigan 1.1 Sep 2009 Update for Oak Table book -- ----------------------------------------------------------------------------- --whenever sqlerror exit rollback set feed on set head on set arraysize 1 set space 1 set verify off set pages 25 set lines 128 set termout on --clear screen set serveroutput on size 1000000 spool get_tab2.lis.lis undefine object_to_find undefine owner_to_find undefine output_method undefine file_name undefine output_dir 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 get_tab2: Release 1.0.1.0.0 - Production on &val_system_date prompt Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved. set feed on prompt accept object_to_find char prompt 'OBJECT TO CHECK [XXX_XXXX]: ' default XXX_XXXX accept owner_to_find char prompt 'SCHEMA/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 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 char is -- cursor c_use (cp_grantee in dba_users.username%type) is select 'U' userrole from dba_users u where u.username=cp_grantee union select 'R' 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 distinct owner, table_name, grantor, grantee, decode(type#,0,'R',1,'U') type# from dba_tab_privs,sys.user$ where owner=cp_owner and table_name=cp_object and name=grantee order by type# desc; -- cursor c_obj (cp_object in varchar2, cp_owner in varchar2, cp_grantee in varchar2, cp_grantor in varchar2) is select privilege, grantable from dba_tab_privs where owner=cp_owner and table_name=cp_object and grantor=cp_grantor and grantee=cp_grantee; -- lv_sel char(1):=chr(32); lv_ins char(1):=chr(32); lv_upd char(1):=chr(32); lv_del char(1):=chr(32); lv_alt char(1):=chr(32); lv_fla char(1):=chr(32); lv_deb char(1):=chr(32); lv_inx char(1):=chr(32); lv_rfs char(1):=chr(32); lv_qrt char(1):=chr(32); lv_cmr char(1):=chr(32); lv_exe char(1):=chr(32); lv_row varchar2(256):=''; lv_privs varchar2(30):=''; -- procedure get_users(pv_grantee in varchar2,pv_grantor in varchar2, pv_privs in varchar2, pv_role in varchar2) is -- lv_type char(1); lv_use_cnt number:=0; -- cursor c_user (cp_username in dba_role_privs.grantee%type) is select grantee, decode(admin_option,'YES','A','NO','') admin_option, decode(default_role,'YES','D','NO','') default_role from dba_role_privs where granted_role=cp_username; -- begin for lv_user in c_user(pv_grantee) loop lv_use_cnt:=lv_use_cnt+1; lv_type:=user_or_role(lv_user.grantee); if (lv_type = 'R') then if (lv_user.grantee = 'PUBLIC') then -- its PUBLIC so just print it lv_row:=rpad(pv_grantor,14)|| rpad(lv_user.grantee,14)||pv_privs ||chr(32) ||'['||lv_user.admin_option||','||lv_user.default_role||']' ||pv_role; write_op(lv_row); else -- its a role so recurse get_users(lv_user.grantee,pv_grantor, pv_privs,'['||lv_user.grantee||']'||pv_role); end if; else -- its a user so print it lv_row:=rpad(pv_grantor,14)|| rpad(lv_user.grantee,14)||pv_privs ||chr(32) ||'['||lv_user.admin_option||','||lv_user.default_role||']' ||pv_role; write_op(lv_row); end if; end loop; if (lv_use_cnt = 0) then -- --------------------------------------------- -- There were no grantees of this role so -- output the role and say its a role and no -- grantees -- --------------------------------------------- lv_row:=rpad(pv_grantor,14)|| rpad(pv_grantee,14)||pv_privs ||chr(32) ||'[R-NOGRANT]'; write_op(lv_row); end if; exception when others then dbms_output.put_line('ERROR (get_users) => '||sqlcode); dbms_output.put_line('MSG (get_users) => '||sqlerrm); end get_users; -- begin -- ----------------------------------------- -- outout the header of the report -- ----------------------------------------- write_op('GRANTOR GRANTEE S I U D A F D I R Q C E'); write_op('------------- -------------- - - - - - - - - - - - -'); -- ----------------------------------------------------------- -- Lopp through all entries for object privileges -- ----------------------------------------------------------- for lv_main in c_main(pv_object,pv_owner) loop -- --------------------------------------------------- -- Loop through each privilege for each table to -- create a one line entry for each grantor/grantee -- --------------------------------------------------- for lv_obj in c_obj(lv_main.table_name,lv_main.owner, lv_main.grantee,lv_main.grantor) loop -- -------------------------------------- -- This is currently limited to the -- privileges for a TABLE. This should be -- extended to all objects -- -------------------------------------- case lv_obj.privilege when 'SELECT' then if(lv_obj.grantable='YES') then lv_sel:='G'; else lv_sel:='X'; end if; when 'INSERT' then if(lv_obj.grantable='YES') then lv_ins:='G'; else lv_ins:='X'; end if; when 'UPDATE' then if(lv_obj.grantable='YES') then lv_upd:='G'; else lv_upd:='X'; end if; when 'DELETE' then if(lv_obj.grantable='YES') then lv_del:='G'; else lv_del:='X'; end if; when 'ALTER' then if(lv_obj.grantable='YES') then lv_alt:='G'; else lv_alt:='X'; end if; when 'FLASHBACK' then if(lv_obj.grantable='YES') then lv_fla:='G'; else lv_fla:='X'; end if; when 'DEBUG' then if(lv_obj.grantable='YES') then lv_deb:='G'; else lv_deb:='X'; end if; when 'INDEX' then if(lv_obj.grantable='YES') then lv_inx:='G'; else lv_inx:='X'; end if; when 'REFERENCES' then if(lv_obj.grantable='YES') then lv_rfs:='G'; else lv_rfs:='X'; end if; when 'QUERY REWRITE' then if(lv_obj.grantable='YES') then lv_qrt:='G'; else lv_qrt:='X'; end if; when 'ON COMMIT REFRESH' then if(lv_obj.grantable='YES') then lv_cmr:='G'; else lv_cmr:='X'; end if; when 'EXECUTE' then if(lv_obj.grantable='YES') then lv_exe:='G'; else lv_exe:='X'; end if; else dbms_output.put_line('ERROR (get_obj) => invalid privilege'); end case; end loop; -- ------------------------------------------------ -- set up the privilege string as we need it more -- than once. -- ------------------------------------------------ lv_privs:=chr(32) ||lv_sel||chr(32) ||lv_ins||chr(32) ||lv_upd||chr(32) ||lv_del||chr(32) ||lv_alt||chr(32) ||lv_fla||chr(32) ||lv_deb||chr(32) ||lv_inx||chr(32) ||lv_rfs||chr(32) ||lv_qrt||chr(32) ||lv_cmr||chr(32) ||lv_exe||chr(32); -- ------------------------------------------------- -- Deal with roles, record the child grantees and -- loop recursively but only record the role if -- there are no children -- ------------------------------------------------- if (lv_main.type# = 'R') then -- ------------------------------------------------- -- Build the output string if its a role but output -- the same privileges for all grantees and children -- ------------------------------------------------- if (lv_main.grantee = 'PUBLIC') then -- ---------------------------------------- -- If its PUBLIC we dont want to list all -- of the users in the database explicitly -- as we can summarise this at the end -- ---------------------------------------- lv_row:=rpad(lv_main.grantor,14)|| rpad(lv_main.grantee,14)||lv_privs; write_op(lv_row); else -- ---------------------------------------- -- It is a role so print out all the users -- for the ROLE - recursively and also if -- the role has no grantees then print out -- the role BUT mark it as so -- ---------------------------------------- get_users(lv_main.grantee,lv_main.grantor, lv_privs,'['||lv_main.grantee||']'); end if; else -- ------------------------------------------------- -- Build the single row output string for each pair -- of grantee/grantor per privileges for 'U'sers -- ------------------------------------------------- lv_row:=rpad(lv_main.grantor,14)|| rpad(lv_main.grantee,14)||lv_privs; write_op(lv_row); end if; -- ------------------------------------------------------------- -- zero out the vars for next time -- ------------------------------------------------------------- lv_sel:=chr(32); lv_ins:=chr(32); lv_upd:=chr(32); lv_del:=chr(32); lv_alt:=chr(32); lv_fla:=chr(32); lv_deb:=chr(32); lv_inx:=chr(32); lv_rfs:=chr(32); lv_qrt:=chr(32); lv_cmr:=chr(32); lv_exe:=chr(32); 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; -- procedure get_sch (pv_owner in varchar2) is begin null; end get_sch; begin lv_file_or_screen:= upper('&&output_method'); if (lv_file_or_screen='F') then open_file('&&file_name','&&output_dir'); end if; if (upper('&&object_to_find') = 'XXX_XXXX') then write_op('Testing schema => '||upper('&&owner_to_find')); get_sch(upper('&&owner_to_find')); else write_op('Testing root object => ['||upper('&&owner_to_find')||'.'||upper('&&object_to_find')||']'); write_op(chr(10)); get_obj(upper('&&object_to_find'),upper('&&owner_to_find')); end if; 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 undefine object_to_find undefine owner_to_find undefine output_method undefine file_name undefine output_dir