get_tab2.sql

-- Script Name : get_tab2.sql
-- Author      : Pete Finnigan
-- Date        : August 2007
-- -----------------------------------------------------------------------------
-- Description : See below
-- -----------------------------------------------------------------------------
-- Maintainer  : Pete Finnigan (http://www.petefinnigan.com)
-- Copyright   : Copyright (C) 2007 - 2017 PeteFinnigan.com Limited. All rights
--               reserved. All registered trademarks are the property of their
--               respective owners and are hereby acknowledged.
-- -----------------------------------------------------------------------------
-- License     : This software is licensed to you by PeteFinnigan.com Limited. All
--               rights and ownership and copyright in the software are retained by
--               PeteFinnigan.com Limited in all possible cases. Posession of this
--               software does not infer any additonal rights. If you receive this
--               software without these copyright notices and license text, this text,
--               license and copyright still applies. This text, copyright and license
--               must not be removed under any circumstances. This applies to all 
--               text displayed as comments up until and including the version history,
--               This text also applies to any copyright, banner or other text 
--               refering to PeteFinnigan.com Limited ownership that is output by
--               the program.
--
--               This software is free to use BUT it is NOT open source and NOT GPL 
--               or any similar license and it is NOT in the public domain.
--
--               You are permitted to use this software commercially or privately 
--               provided these notices or banners as described are not removed. You
--               may modify the software and use it internally but this does not
--               infer any additonal rights in the software. i.e. if you delete
--               some of our code or change variable names or add features that does 
--               not make it your code and does not give you the right to remove 
--               our ownership in this software shown in these notices. This software must
--               not be made available or published in anyway, any language, any 
--               modified form or original form except by PeteFinnigan.com Limited. 
--               You must not incorporate this software into any free or commercial 
--               product or software and you must not sell or give away any software that 
--               includes this sofware. 
--               
--
--               In short this text is not written by a lawyer so please respect the 
--               intent that you can use or modify it freely but not give it away 
--               yourself or take away our right to attribution.
--               If someone else needs a copy please ask them to come to PeteFinnigan.com
--               Limited and we will happily let them also have a free copy. Also
--               as you may expect freely use does not include adding this software to 
--               a commercial or free product (without our permission) but you
--               can use it internally in projects. We put our time into the free scripts
--               on our website or training courses and give these tools away for free and 
--               in return we expect our copyright and ownership to always remain. 
--               We like to help people but we also want to benefit from the fact
--               our name becomes known through these scripts and tools and software 
--               that we make. We hope this makes sense.
--
--
-- 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
-- P.Finnigan  1.2         Aug 2017  Updated to include READ privilege for 12c


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

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.2.0.0.0 - Production on &val_system_date
prompt Copyright (c) 2007, 2017, 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_rea char(1):=chr(32);
	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        R 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 'READ' then
					if(lv_obj.grantable='YES') then
						lv_rea:='G';
					else
						lv_rea:='X';
					end if;					
				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_rea||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_rea:=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);
	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