Re: getting ALL user's sys privileges [from Oracle9i DBA 101]
- From: Pete Finnigan <oracle_list@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 6 Nov 2004 18:16:32 +0000
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
