0

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'));
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
zeldabluess
  • 27
  • 1
  • 4

1 Answers1

0

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

CompEng
  • 7,161
  • 16
  • 68
  • 122