Re: Finding empty Role(s)

hi,
try this, for 0 no object granted

select grantee,count(*) from dba_tab_privs where grantee in (select role from dba_roles) group by grantee
order by 2;

Marian

ut 12. 5. 2020 o 20:17 Lyall Barbour <lyallbarbour_at_sanfranmail.com> napísal(a):

> not quite what i want, but that might be useful too.
>
> I've revoked Object privileges FROM Roles. Now i have Roles that exist
> for no reason, maybe. I want to find those, not ones that aren't granted
> to anyone.
>
> Lyall
>
> *Sent:* Tuesday, May 12, 2020 at 1:36 PM
> *From:* "Mladen Gogala" <gogala.mladen_at_gmail.com>
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: Finding empty Role(s)
>
>
> select role from dba_roles
> minus
> select granted_role from dba_role_privs;
>
> ROLE
>
> --------------------------------------------------------------------------------
> DV_ACCTMGR
> DV_OWNER
> GLOBAL_AQ_USER_ROLE
>
> Elapsed: 00:00:00.17
>
>
> On 5/12/20 10:13 AM, Lyall Barbour wrote:
>
> Hello everyone,
> I'm either Googling the wrong keywords, or thinking about this too
> hard. I just revoked a ton of object privs from a non-prod database in
> preparation for doing a decommission of a specific schema in a large
> database for the Business. I'm now trying to find any Roles that are empty
> because of my revoking. Does anyone have a script to do this?
>
> TIA
> Lyall Barbour
> -- http://www.freelists.org/webpage/oracle-l
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
> -- http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l