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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
