Script to Get User Permissions

Introduction

The following script is a wrapper to a very slightly amened version of Pete Finningans great find all privs script.

The wrapper script allows you to run Pete's script for 1 user or many and directs the out put to a reports directory. This is useful for providing GDPR users the ability to get permissions for application and system users.

In order to run this script place both scripts in a directory of your choising. Update the SDS_APP_BUILD variable in the script with the directory location of the script.

Ensure your .profile has the correct Oracle variables. Oracle home and base etc.

As with most of the KSH scripts on this site, run it with a -h parameter for details on how to run the script.

get_user_privs.ksh

#!/bin/ksh 
############################################################################
#
#       Script :        get_user_privs.ksh
#
#       Date :          12th Jan 2011
#
#       Author :        Mark Ramsay
#
#       Copyright:      www.markramsay.com 2011
#
#       Description :   Get User Privs for ALL Users in a given Database
#
#       Parameters :    Run the script with -h for details.
#
# History       Date      Name      Reason
#               ----      ----      ------
#               dd/mm/yy  xxx       yyyyyyy
#
############################################################################
#
# Set environment variables
#

$HOME/.profile

SDS_APP_BUILD=<insert script location>
echo "Update script location above and then remove this line and the exit below"
exit
SDS_REPORT_DIR=${SDS_APP_BUILD_LOC}/REPORTS

#
# Perform getopts loops
#

while getopts ":d:m:p:u:xh" opt;
do
case $opt in
d)  SDS_DBNAME_SOURCE=$OPTARG;;
m)  SDS_DBMAST_SOURCE=$OPTARG;;
p)  SDS_DBMASTPASS_SOURCE=$OPTARG;;
u)  SDS_USERS=$OPTARG;;
x)  set -x;;
h)  echo "This script generates reports for all users in the specified Database or a single user."
    echo "A report is created for each user and written to SDS_REPORT_DIR=${SDS_APP_BUILD_LOC}/REPORTS"
    echo ""
    echo "Command line options for $SDS_scriptname"
    echo ""
    echo "Mandatory Parameters"
    echo "--------------------"
    echo "-d <Source Database TNS name>      -- Defines the TNS Entry of the Destination DB.  Typically, this is the DB SID."
    echo "-m <Source Database DBA account>   -- Defines the Destination database master account.  Typically, this will be dbmaster."
    echo "-p <Source Database DBA password>  -- Defines the Destination database master account password."
    echo ""
    echo "Optional Parameters"
    echo "-------------------"
    echo "-u <Username>                      -- Username(s) to be checked."
    echo "                                      If more than one user specified then they must be in quotes. e.g. "MyUser1 MyUser2"."
    echo "                                      If the username is not specified all users will be checked."
    echo "Misc"
    echo "----"
    echo "-x                         -- Switch debug on"
    echo "-h                         -- Display this help"
    echo ""
    exit 0;;
\?) errcheck 1;;
:)  errcheck 2;;
esac
done

#
# Parameters validation
#
#
# Check mandatory parameter SDS_RELEASE_DATE is set and set to a valid value
#

: ${SDS_DBNAME_SOURCE:?"$(echo -ne ${LGREEN})Mandatory parameter -d not specified. Try -h for help$(echo -e ${NORM})"}
: ${SDS_DBMAST_SOURCE:?"$(echo -ne ${LGREEN})Mandatory parameter -m not specified. Try -h for help$(echo -e ${NORM})"}
: ${SDS_DBMASTPASS_SOURCE:?"$(echo -ne ${LGREEN})Mandatory parameter -p not specified. Try -h for help$(echo -e ${NORM})"}

if [ -z ${SDS_USERS} ]
then
  SDS_USERS=$(sqlplus -S ${SDS_DBMAST_SOURCE}@${SDS_DBNAME_SOURCE}/${SDS_DBMASTPASS_SOURCE} <<EOF
set hea off
set pages 0
set lines 100
set trimspool on
set feedback off
select username from dba_users where username not in ('X$NULL')
/
EOF)
fi

mkdir -p REPORTS/${SDS_DBNAME_SOURCE}

for f in ${SDS_USERS}
do
sqlplus -S ${SDS_DBMAST_SOURCE}@${SDS_DBNAME_SOURCE}/${SDS_DBMASTPASS_SOURCE} @get_user_privs.sql $f ${SDS_DBNAME_SOURCE}
done

get_user_privs.sql

-- -----------------------------------------------------------------------------
--                 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.
-- M.Ramsay    1.8         July 2018 Changed the location of the output to make compatible
--                                   with the wrapper script
-- -----------------------------------------------------------------------------
 
whenever sqlerror exit rollback
set feed on
set head on
set arraysize 1
set space 1
set verify off
set pages 25
set lines 100
set termout on
set serveroutput on size 1000000
 
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.0.7.0.0 - Production on &val_system_date
prompt Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. 
prompt
define 1
define 2
spool REPORTS/&2/find_privs_&1..txt
define user_to_find=&1
define output_method=S
define file_name=priv.lst
define output_dir=/tmp
 
--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
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):='';
        lv_loop number;
        --
        cursor c_main (cp_grantee in varchar2) is
        select  'ROLE' typ,
            grantee grantee,
            granted_role priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
        from    dba_role_privs
        where   grantee=cp_grantee
        union
        select  'SYSTEM' typ,
            grantee grantee,
            privilege priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
        from    dba_sys_privs
        where   grantee=cp_grantee
        union
        select  'TABLE' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            '--' colnm,
            owner owner
        from    dba_tab_privs
        where   grantee=cp_grantee
        union
        select  'COLUMN' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            column_name colnm,
            owner owner
        from    dba_col_privs
        where   grantee=cp_grantee
        order by 1;
    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||' 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.tabnm
                    ||' column_name => '
                    ||lv_main.owner||'.'||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
 
quit
dba_col_privsdba_role_privsdba_sys_privsdba_tab_privspermissions
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License