This SQL script can be used to bulk remove and then add back at a later date users from a specific role. This is Oracle syntax. The statements are meant to be run one at a time not as a long running script. This is useful during site launches or content freezes where you want to lock everyone but a limited set of users out of a site.
CREATE TABLE BACKUP_MAINTAINERS AS SELECT PSX_ROLES.ID AS ROLE_ID,PSX_ROLES.NAME AS ROLE_NAME, PSX_SUBJECTS.ID AS SUBJECT_ID,PSX_SUBJECTS.NAME AS SUBJECT_NAME
FROM PSX_SUBJECTS INNER JOIN PSX_ROLE_SUBJECTS ON PSX_SUBJECTS.ID =PSX_ROLE_SUBJECTS.SUBJECTID
INNER JOIN PSX_ROLES ON PSX_ROLE_SUBJECTS.ROLEID=PSX_ROLES.ID
WHERE LOWER(PSX_SUBJECTS.NAME) NOT IN(
'users',
'that',
'you',
'want',
'to',
'keep',
'in',
'role'
)
AND PSX_ROLES.NAME IN('Role_Name_That_You_Are_Working_With');
--Make sure we have backups
SELECT * FROM BACKUP_MAINTAINERS;
--Disable Access
DELETE FROM PSX_ROLE_SUBJECTS WHERE ROLEID
IN(SELECT ID FROM PSX_ROLES WHERE NAME IN('Role_Name_That_You_Are_Working_With'))
AND
SUBJECTID IN
(SELECT SUBJECT_ID FROM BACKUP_MAINTAINERS);
--Restore access
INSERT INTO psx_role_subjects (roleid,subjectid) SELECT ROLE_ID, SUBJECT_ID FROM BACKUP_MAINTAINERS;
--Get rid of backup table.
DROP TABLE BACKUP_MAINTAINERS;