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

From: Pete Finnigan <oracle_list_at_peterfinnigan.demon.co.uk>
Date: Sat, 6 Nov 2004 18:16:32 +0000
Message-ID: <8P1PblAAURjBBx5W@peterfinnigan.demon.co.uk>

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_at_petefinnigan.com) 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 Received on Sat Nov 06 2004 - 12:12:31 CST