Re: Finding empty Role(s)

Well, if you want roles with no object privileges, you will have to use anti-join between DBA_TAB_PRIVS and DBA_ROLES. That should do the trick.Regards

On Tue, 2020-05-12 at 20:16 +0200, Lyall Barbour wrote:
> 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 GogalaDatabase ConsultantTel: (347) 321-1217
>
>
>
>

-- 
Mladen GogalaDatabase ConsultantTel: (347) 321-1217Email:gogala.mladen_at_gmail.com


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