sqloraclesql-revoke

Revoke Multiple Users in Oracle Developer

I want to remove a role from a list of users based on a select statement. My select statement works fine, but when I try to implement the revoke statement I get "missing or invalid privilege".

I am assuming I'm missing something for the command to read the list of users, but not sure what to do to make it cycle through the list.

REVOKE ORACLE_ROLE FROM (SELECT GRANTEE
  FROM DBA_ROLE_PRIVS 
  JOIN SYS.DBA_USERS ON DBA_ROLE_PRIVS.GRANTEE = DBA_USERS.USERNAME 
  WHERE DBA_ROLE_PRIVS.GRANTED_ROLE = 'ROLE_1'
  AND DBA_USERS.ACCOUNT_STATUS != 'OPEN'
  AND EXISTS (SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE != 'ROLE_1'));
Solution

  • you can do it like this:

    SELECT 'REVOKE ORACLE_ROLE FROM  ' || GRANTEE || ' ;'
  FROM DBA_ROLE_PRIVS 
  JOIN SYS.DBA_USERS ON DBA_ROLE_PRIVS.GRANTEE = DBA_USERS.USERNAME 
  WHERE DBA_ROLE_PRIVS.GRANTED_ROLE = 'ROLE_1'
  AND DBA_USERS.ACCOUNT_STATUS != 'OPEN'
  AND EXISTS (SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE != 'ROLE_1');

    then execute the outputs.

    if you want you can do it manually or in execute immediate