Re: Finding empty Role(s)

my mistake, previous query was not correct query for finding roles without object grants

select role from dba_roles
where role not in (select distinct grantee from dba_tab_privs);

ut 12. 5. 2020 o 20:40 Marián Bednár <marian.bednar_at_gmail.com> napísal(a):

> 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