a script to list all privileges for a user heirarchically

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

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.