Re: Listing User Privileges

From: Pete Finnigan <plsql_at_petefinnigan.com>
Date: Fri, 23 Jul 2004 16:10:06 +0100
Message-ID: <ylWwTpAOpSABRxTi@peterfinnigan.demon.co.uk>

In article <91721cf.0407211513.2fe67bfd_at_posting.google.com>, Michael <melliott42_at_yahoo.com> writes
>Hello,
>
>On an Oracle 8.17 system I need to list all the privileges for a user
>so as I can duplicate some of them for another user.
>
>How can I do this from SQLPlus (I cannot use the OEM in this
>environment)?

Hi,

Have a look at a script i wrote some time ago called find_all_privs.sql that does a hierarchical listing of all privileges granted to a user even via roles. here is an example for OUTLN:

find_all_privs: Release 1.0.6.0.0 - Production on Fri Jul 23 16:08:45 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

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

OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

User => OUTLN has been granted the following privileges

---

        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 => EXECUTE ANY PROCEDURE grantable => NO
        SYS PRIV => UNLIMITED TABLESPACE grantable => NO
        TABLE PRIV => DELETE object => SCOTT.EMP grantable => NO
        TABLE PRIV => EXECUTE object => SYS.OUTLN_PKG grantable => NO

PL/SQL procedure successfully completed.

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

SQL> The script is available from http://www.petefinnigan.com/tools.htm

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.

Received on Fri Jul 23 2004 - 10:10:06 CDT