Re: Querying a user's privileges

Hi,

Yes, go to my web site and get an SQL script called find_all_privs.sql . It is able to list object, system and role privileges granted and also do it hierarchically. i.e, its lists which roles are granted and then all of the privileges granted to those roles and so on down the role hierarchy. You can get it from http://www.petefinnigan.com/tools.sql An example of its use for the user OUTLN is here:

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Feb 13 22:21:27 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> @find_all_privs.sql
FIND_ALL_PRIVS: Release 1.3.0.0.0 - Production - (http://www.petefinnigan.com)
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

get user input

NAME OF USER TO CHECK [ORCL]: OUTLN
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [/tmp]:

        USER => OUTLN 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 => OUTLN 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
        SYS PRIV =>EXECUTE ANY PROCEDURE grantable => NO
        SYS PRIV =>UNLIMITED TABLESPACE grantable => NO
        TABLE PRIV =>EXECUTE table_name => OUTLN_PKG grantable => NO

PL/SQL procedure successfully completed.

SQL> hth

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.