Re: [Objects and System Grants]The definitive query

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 20 Jun 2006 06:01:07 -0700
Message-ID: <1150808467.864923.77450@r2g2000cwb.googlegroups.com>

jbmorla_at_gmail.com wrote:
> Hi,
>
> I'm looking for a query or unions of queries that would list all grants
>
> The header of the query would look like this
>
> User_or_Role has_this_grant on_this_object granted_by....
>
> It's just because a consultant DBA has granted select on all tables and
> that's a System grant, it's quite tricky to remove it from 200+ users
> who might have lesser select grants.
>
>
> Many thanks
>
> JayBee

Try this...

SELECT grantee,

       granted_role,
       'ROLE' ROLE_OR_USER

  FROM dba_role_privs
  WHERE grantee=UPPER('&&username')
UNION
SELECT grantee,

       privilege,
       'USER' ROLE_OR_USER

  FROM dba_sys_privs
  WHERE grantee=UPPER('&&username')
UNION
SELECT grantee,

       privilege || ' ' || owner ||'.' || table_name granted_privilege,
       'USER' ROLE_OR_USER

  FROM dba_tab_privs
  WHERE grantee = UPPER('&&username')
  ORDER BY ROLE_OR_USER; Regards,

Steve Received on Tue Jun 20 2006 - 08:01:07 CDT