Find out users who do not have a particular role

I have a set of DB roles defined in my oracle database. say

• VIEWER- Select only
• UPDATER – select & update only
• EDITOR – select & delete only
• BYPASSER- select & insert only

And I have a some users who has been granted all the roles and some who do not .Either way, all the users will be having at least one of the mentioned role granted to them.

My goal is to identify all the users who does not have say, UPDATER and BYPASSER role

select grantee from dba_role_privs 
where granted_role not in ('UPDATER','BYPASSER')

The above code is listing all the users available in DB as all the users are having at least one of the role granted to them.

How should I reframe the query to get the desired output ?

Answer

You need to group by grantee and put a condition in HAVING clause:

select grantee 
from dba_role_privs
group by grantee
having sum(
  case 
    when granted_role in ('UPDATER','BYPASSER') then 1
    else 0
  end 
) < 2

This will return uses who don’t have both 'UPDATER' and 'BYPASSER' roles.
If you want the users who don’t have either of these roles, change the condition to = 0 instead of < 2.