RE: Display all privilleges belong to user

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 01 Aug 2002 14:23:25 -0800
Message-ID: <F001.004A95D0.20020801142325@fatcity.com>

> -----Original Message-----
> From: Nguyen, David M [mailto:david.m.nguyen_at_xo.com]
>
> How do I display all privilleges belong to an user?

Here is a sample script. It will show all privileges granted to a user and all privileges to the roles granted to the user. If you have roles granted to roles then you would need extra seach levels.

set verify off
set linesize 110
column sort_id noprint
column priv_type format a30
column priv format a60
column grantable heading "ADM" format a3 column default_role heading "DEF" format a3  select

    1 as sort_id,
    'ROLE' as priv_type,

    a.granted_role as priv,
    a.admin_option as grantable,
    a.default_role as default_role

  from
    dba_role_privs a
  where
    grantee = '&&enter_username'
union
 select

    2 as sort_id,
    'SYS PRIV' as priv_type,
    b.privilege as priv,
    b.admin_option as grantable,
    null as default_role
  from
    dba_sys_privs b
  where
    grantee = '&&enter_username'
union
 select

    5 as sort_id,
    'TAB PRIV (ROLE "' || c.granted_role || '")' as priv_type,     d.privilege || ' on "' || d.owner ||

       '"."' || d.table_name || '"'
       as priv,

    d.grantable as grantable,
    c.default_role as default_role
  from
    dba_role_privs c,
    dba_tab_privs d
  where
    c.grantee = '&&enter_username'
    and d.grantee = c.granted_role
union
 select

    7 as sort_id,
    'COL PRIV (ROLE "' || e.granted_role || '")' as priv_type,     f.privilege || ' on "' || f.owner ||

       '"."' || f.table_name || '" ("' || f.column_name || '")'
       as priv,

    f.grantable as grantable,
    e.default_role as default_role
  from
    dba_role_privs e,
    dba_col_privs f
  where
    e.grantee = '&&enter_username'
    and f.grantee = e.granted_role
union
 select

    4 as sort_id,
    'TAB PRIV' as priv_type,
    g.privilege || ' on "' || g.owner ||

       '"."' || g.table_name || '"'
       as priv,

    g.grantable as grantable,
    null as default_role
  from
    dba_tab_privs g
  where
    g.grantee = '&&enter_username'
union
 select

    6 as sort_id,
    'COL PRIV' as priv_type,
    h.privilege || ' on "' || h.owner ||

       '"."' || h.table_name || '" ("' || h.column_name || '")'
       as priv,

    h.grantable as grantable,
    null as default_role
  from
    dba_col_privs h
  where
    h.grantee = '&&enter_username'
union
 select

    3 as sort_id,
    'SYS PRIV (ROLE "' || i.granted_role || '")' as priv_type,

    j.privilege as priv,
    j.admin_option as grantable,
    i.default_role as default_role

  from
    dba_role_privs i,
    dba_sys_privs j
  where
    i.grantee = '&&enter_username'
    and j.grantee = i.granted_role
order by 1, 2, 3 ;
undefine enter_username
clear columns
set linesize 80
set verify on

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Aug 01 2002 - 17:23:25 CDT