Re: a script to list all privileges for a user heirarchically

nice! and very usable, as I'm trying to mine through privileges and tighten security here.

thanks much!

• Pete Finnigan <oracle_list_at_peterfinnigan.demon.co.uk> wrote:
  > Hi everyone
  >
  > I just answered a post on the server newsgroup from someone who
  > wanted
  > to know if a privilege had been granted to a particular user
  > including
  > mining through all of the roles granted hierarchically to roles etc.
  > I
  > posted a reference to a PL/SQL script I have knocked up as an answer
  > there.
  >
  > I thought people here might be find it useful as well, its at
  > http://www
  > .petefinnigan.com/tools.htm, and its called find_all_privs.sql. A
  > sample
  > run is here:
  >
  > SQL> @find_all_privs
  > get user input
  >
  > NAME OF USER TO CHECK [ORCL]: PETE
  > OUTPUT METHOD [S/F]: S
  > FILE NAME FOR OUTPUT [priv.lst]:
  > OUTPUT DIRECTORY [/tmp]:
  >
  > old 162: lv_file_or_screen:='&&output_method';
  > new 162: lv_file_or_screen:='S';
  > old 164: open_file('&&file_name','&&output_dir');
  > new 164: open_file('priv.lst','/tmp');
  > old 166: get_privs('&&user_to_find',lv_tabs);
  > new 166: get_privs('PETE',lv_tabs);
  > ...USER => PETE has ROLE CONNECT which contains =>
  > ......SYS PRIV =>ALTER SESSION grantable => NO
  > ......SYS PRIV =>CREATE CLUSTER grantable => NO
  > ......SYS PRIV =>CREATE DATABASE LINK grantable => NO
  > ......SYS PRIV =>CREATE SEQUENCE grantable => NO
  > ......SYS PRIV =>CREATE SESSION grantable => NO
  > ......SYS PRIV =>CREATE SYNONYM grantable => NO
  > ......SYS PRIV =>CREATE TABLE grantable => NO
  > ......SYS PRIV =>CREATE VIEW grantable => NO
  > ...USER => PETE has ROLE RESOURCE which contains =>
  > ......SYS PRIV =>CREATE CLUSTER grantable => NO
  > ......SYS PRIV =>CREATE INDEXTYPE grantable => NO
  > ......SYS PRIV =>CREATE OPERATOR grantable => NO
  > ......SYS PRIV =>CREATE PROCEDURE grantable => NO
  > ......SYS PRIV =>CREATE SEQUENCE grantable => NO
  > ......SYS PRIV =>CREATE TABLE grantable => NO
  > ......SYS PRIV =>CREATE TRIGGER grantable => NO
  > ......SYS PRIV =>CREATE TYPE grantable => NO
  > ...USER => PETE has ROLE UNIX_ADMIN which contains =>
  > ......USER => UNIX_ADMIN has ROLE ADMIN which contains =>
  > .........SYS PRIV =>ALTER USER grantable => NO
  > .........SYS PRIV =>CREATE USER grantable => NO
  > ......SYS PRIV =>CREATE CLUSTER grantable => NO
  > ...SYS PRIV =>CREATE DATABASE LINK grantable => NO
  > ...SYS PRIV =>CREATE SESSION grantable => NO
  > ...SYS PRIV =>UNLIMITED TABLESPACE grantable => NO
  > ...TABLE PRIV =>SELECT table_name => V_$SESSION grantable => NO
  >
  > PL/SQL procedure successfully completed.
  >
  > SQL>
  >
  > you can choose to either send output to the screen via dbms_output or
  > to
  > a file via utl_file. choose 'S' or 'F' at run time and if you choose
  > 'F'
  > specify a file name and directory.
  >
  > Anyway its there if anyone would find it useful.
  >
  > kind regards
  >
  > Pete
  > --
  > Pete Finnigan
  > email:pete_at_petefinnigan.com
  > Web site: http://www.petefinnigan.com - Oracle security audit
  > specialists
  > Book:Oracle security step-by-step Guide - see http://store.sans.org
  > for details.
  >
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.net
  > --
  > Author: Pete Finnigan
  > INET: oracle_list_at_peterfinnigan.demon.co.uk
  >
  > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  > San Diego, California -- Mailing list and web hosting services
  > ---------------------------------------------------------------------
  > To REMOVE yourself from this mailing list, send an E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from). You may
  > also send the HELP command for other information (like subscribing).
  

  ---