Re: getting ALL user's sys privileges [from Oracle9i DBA 101]

Hi,

I wrote a script some time back that goes further. Its called
find_all_privs.sql and is available on my tools page. It gets all system
privileges for a particular user and all the roles granted and also the
object privileges. It does this hierarchically so that if a user is
granted a privilege via a role granted to a role, granted to a role....
it will be shown.

An example is here for the user SCOTT who in this test databases has a
number of roles granted and roles granted to roles etc:

find_all_privs: Release 1.0.6.0.0 - Production on Sat Nov 06 18:06:02
2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF USER TO CHECK                 [ORCL]: scott
OUTPUT METHOD Screen/File                [S]: s
FILE NAME FOR OUTPUT              [priv.lst]: 
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: 

User => SCOTT has been granted the following privileges
====================================================================
        ROLE => APP_ROLE which contains =>
                ROLE => APPTEST which contains =>
                        ROLE => APPWORK which contains =>
                                SYS PRIV => AUDIT ANY grantable => NO
                                TABLE PRIV => SELECT object =>
SCOTT.DEPT grantable => NO
                        SYS PRIV => SELECT ANY TABLE grantable => NO
                        TABLE PRIV => SELECT object => SCOTT.EMP
grantable => NO
                SYS PRIV => ALTER SESSION grantable => NO
        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
        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
        SYS PRIV => INSERT ANY TABLE grantable => NO
        SYS PRIV => UNLIMITED TABLESPACE grantable => NO
        SYS PRIV => UPDATE ANY TABLE grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DUMPDIANA grantable => NO
        TABLE PRIV => EXECUTE object => SYS.UTL_FILE grantable => NO
        TABLE PRIV => EXECUTE object => SYSTEM.VALIDATE_APP grantable =>
NO

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL> 

I hope that you can see the hierarchy. You can get this script on my
tools page http://www.petefinnigan.com/tools.htm and also there is some
examples and information about using this script in an entry of my
Oracle security weblog when i write about it there :- 
http://www.petefinnigan.com/weblog/archives/00000011.htm

Hope this helps

Kind regards

Pete
-- 
Pete Finnigan (email:pete@xxxxxxxxxxxxxxxx)
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

--
http://www.freelists.org/webpage/oracle-l

Other related posts: