Privs

-- -----------------------------------------------------------------------------
--                 WWW.PETEFINNIGAN.COM LIMITED
-- -----------------------------------------------------------------------------
-- Script Name : find_all_privs.sql
-- Author      : Pete Finnigan
-- Date        : June 2003
-- -----------------------------------------------------------------------------
-- Description : Use this script to find which privileges have been granted to a
--               particular user. This scripts lists ROLES, SYSTEM privileges
--               and object privileges granted to a user. If a ROLE is found
--               then it is checked recursively.
--
--               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 after.
-- -----------------------------------------------------------------------------
-- 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
-- -----------------------------------------------------------------------------
-- To Do       :
--               1 - add proxy connection authorities
--               2 - add SELECT ANY TABLE and SELECT ANY DICTIONARY access
-- -----------------------------------------------------------------------------
-- Version History
-- ===============
--
-- Who         version     Date      Description
-- ===         =======     ======    ======================
-- P.Finnigan  1.0         Jun 2003  First Issue.
-- P.Finnigan  1.1         Jun 2003  Output to file added.
-- P.Finnigan  1.2         Jan 2004  Corrected exit/exists bug in 'whenever'.
-- N.Dunbar    1.3         Jan 2004  Added real TAB characters and uppercased
--                                   user input for username and output method.
-- P.Finnigan  1.4         Feb 2004  Clarified use of utl_file for 9ir2.
-- P.Finnigan  1.5         Feb 2004  Added the owner to output for object privs
--                                   (Thanks to Guy Dallaire for this addition)
-- P.Finnigan  1.6         Oct 2004  Changed output to include title in line
--                                   with other reports in the toolkit. Also added
--                                   usage notes.
-- P.Finnigan  1.7         Apr 2005  Added whenever sqlerror continue to stop
--                                   subsequent errors barfing SQL*Plus. Thanks
--                                   to Norman Dunbar for the update.
-- D. Arthur   1.8         Nov 2014  1) Added ability to skip table privileges;
--                                   2) Added sorting: system privs, table privs,
--                                   roles, then execute before table privs;
--                                   3) Added parallel for performance;
--                                   4) Indicate if role is password protected;
--                                   5) Fix: in column privs, owner prefix
--                                   on the table instead of the column
--                                   6) PF: put all original formatting back.
-- P.Finnigan  1.9         Nov 2014  David Litchfield found a bug ORA-12801 with
--                                   parallel hint. You can try uncommenting the
--                                   hint and if it works fine; if not leave
--                                   commented out.
-- -----------------------------------------------------------------------------

--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 find_all_privs.lis

undefine user_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;

SET feed ON

prompt find_all_privs: RELEASE 1.9.0.0.0 - Production ON &val_system_date
prompt Copyright (c) 2004 - 2014 PeteFinnigan.com LIMITED. ALL rights reserved.
prompt
accept user_to_find CHAR prompt   'NAME OF USER TO CHECK                 [ORCL]: ' DEFAULT orcl
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 examine_tables CHAR prompt 'EXAMINE TABLE PRIVILEGES?              [N/Y]: ' DEFAULT "N"
prompt

DECLARE
   --
   lv_tabs             NUMBER := 0;
   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;
    --
   PROCEDURE get_privs (pv_grantee IN VARCHAR2, lv_tabstop IN OUT NUMBER)
   IS
      --
      lv_tab    VARCHAR2 (50) := NULL;
      lv_loop   NUMBER;

      --
      CURSOR c_main (
         cp_grantee IN VARCHAR2)
      IS
    -- the hint is commented out as David got an ORA-12801 and an ORA-00600 because of the
    -- hint. You can try the hint, if it works, then fine, if not comment out
--           select /*+ PARALLEL a */
           SELECT
                 a.*
             FROM (SELECT 'ROLE' typ,
                          grantee grantee,
                          granted_role priv,
                          admin_option ad,
                          '--' tabnm,
                          '--' colnm,
                          '--' owner,
                          r.password_required pwd
                     FROM dba_role_privs rp join dba_roles r ON rp.granted_role = r.ROLE
                    WHERE grantee = cp_grantee
                   UNION
                   SELECT 'SYSTEM' typ,
                          grantee grantee,
                          privilege priv,
                          admin_option ad,
                          '--' tabnm,
                          '--' colnm,
                          '--' owner,
                          '--' pwd
                     FROM dba_sys_privs
                    WHERE grantee = cp_grantee
                   UNION
                   SELECT 'TABLE' typ,
                          grantee grantee,
                          privilege priv,
                          grantable ad,
                          table_name tabnm,
                          '--' colnm,
                          owner owner,
                          '--' pwd
                     FROM dba_tab_privs
                    WHERE grantee = cp_grantee AND '&&examine_tables' = 'Y'
                   UNION
                   SELECT 'COLUMN' typ,
                          grantee grantee,
                          privilege priv,
                          grantable ad,
                          table_name tabnm,
                          column_name colnm,
                          owner owner,
                          '--' pwd
                     FROM dba_col_privs
                    WHERE grantee = cp_grantee AND '&&examine_tables' = 'Y') a
         ORDER BY CASE
                     WHEN a.typ = 'ROLE' THEN 4
                     WHEN a.typ = 'SYSTEM' THEN 1
                     WHEN a.typ = 'TABLE' THEN 2
                     WHEN a.typ = 'COLUMN' THEN 3
                     ELSE 5
                  END,
                  CASE WHEN a.priv IN ('EXECUTE') THEN 1 WHEN a.priv IN ('SELECT', 'UPDATE', 'INSERT', 'DELETE') THEN 3 ELSE 2 END,
                  a.tabnm,
                  a.colnm,
                  a.priv;

   BEGIN
      lv_tabstop := lv_tabstop + 1;

      FOR lv_loop IN 1 .. lv_tabstop
      LOOP
         lv_tab := lv_tab || CHR (9);
      END LOOP;

      FOR lv_main IN c_main (pv_grantee)
      LOOP
         IF lv_main.typ = 'ROLE' THEN
            write_op (
                  lv_tab
               || 'ROLE => '
               || lv_main.priv
               || CASE WHEN lv_main.pwd = 'YES' THEN ' (password)' ELSE NULL END
               || ' which contains =>');
            get_privs (lv_main.priv, lv_tabstop);
         ELSIF lv_main.typ = 'SYSTEM' THEN
            write_op (lv_tab || 'SYS PRIV => ' || lv_main.priv || ' grantable => ' || lv_main.ad);
         ELSIF lv_main.typ = 'TABLE' THEN
            write_op (
                  lv_tab
               || 'TABLE PRIV => '
               || lv_main.priv
               || ' object => '
               || lv_main.owner
               || '.'
               || lv_main.tabnm
               || ' grantable => '
               || lv_main.ad);
         ELSIF lv_main.typ = 'COLUMN' THEN
            write_op (
                  lv_tab
               || 'COL PRIV => '
               || lv_main.priv
               || ' object => '
               || lv_main.owner
               || '.'
               || lv_main.tabnm
               || ' column_name => '
               || lv_main.colnm
               || ' grantable => '
               || lv_main.ad);
         END IF;
      END LOOP;

      lv_tabstop := lv_tabstop - 1;
      lv_tab := '';
   EXCEPTION
      WHEN OTHERS THEN
         dbms_output.put_line ('ERROR (get_privs) => ' || SQLCODE);
         dbms_output.put_line ('MSG (get_privs) => ' || SQLERRM);
   END get_privs;

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 (
      'User => ' || UPPER ('&&user_to_find') || ' has been granted the following privileges');
   write_op ('====================================================================');
   get_privs (UPPER ('&&user_to_find'), lv_tabs);

   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 FOR updates please visit http://www.petefinnigan.com/tools.htm
prompt
--spool off

WHENEVER sqlerror continue