nice! and very usable, as I'm trying to mine through privileges and tighten security here.
thanks much! --- Pete Finnigan <[EMAIL PROTECTED]> 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:[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). __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).