Re: system privilege

From: Pete Finnigan <pete_at_petefinnigan.com>
Date: Tue, 15 Jul 2003 14:20:55 +0100
Message-ID: <49+W8AB3+$E$EwRm@peterfinnigan.demon.co.uk>

Hi Anton,

Thanks for the reply and questions, yes you are right I was naive in my reply in just looking for AUDIT privileges and not considering roles containing roles containing privs etc..

I have relooked at the problem and written a simple script in PL/SQL to get all of the privileges (role, system, table and column) granted to a particular user cascading down through roles as you suggest. It displays roles granted and the all privileges (system, table, role and column) granted to each role. I have not removed duplicate system privileges as that would not give a complete picture of privileges granted down the hierarchy.

The script is on my web site at http://www.petefinnigan.com/tools.htm and the script is called find_all_privs.sql, its the first one on the page.

It uses dbms_output.put_line so will encounter the 1000000 byte limit on users with a large amount of privileges.

It also doesn't display PUBLIC privileges which the user being queried would also have access to. These can be listed using the same script and pass in PUBLIC as the user.

I will update the script to be able to use utl_file as well. I will post when it is done.

A sample run is as follows:

SQL> @find_all_privs
Enter value for user_to_find: PETE

old  84:        select '&&user_to_find'
new  84:        select 'PETE'

...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> hope this helps

kind regards

Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Received on Tue Jul 15 2003 - 08:20:55 CDT