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@xxxxxxxxx> 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@xxxxxxxxxxxxxxx>
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@xxxxxxxxx>
*To:* oracle-l@xxxxxxxxxxxxx
*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
-- //www.freelists.org/webpage/oracle-l
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
-- //www.freelists.org/webpage/oracle-l
