I am trying to write a script for my Oracle 12C database which would allow me to DROP all users of a certain role but can't seem to get it right.
declare
cursor ID_CURSOR is
SELECT USERNAME
from all_users;
WHERE granted_role = 'STUDENT'
begin
for REC in ID_CURSOR loop
'DROP USER REC CASCADE';
end loop;
end;
/
Well your subject line and your example cursor do not match to start. Nor can you issue DDL from PL/SQL. You would have to use execute immediate. See PL/SQL manual. - - So to find users granted a specific role you can use
select grantee from dba_role_privs where granted_role='ROLE_NAME'
and in cursor you use something like
execute immediate 'drop user '||rec.username||' cascade;';
however why use PL/SQL to do this? If this is a one-time effort then just use SQL to generate the drop statements, spool the result, edit to set SQLPlus settings like "set echo on" then run the generated script? - - Mark D Powell --
Following up on Mark's answer - the simplest way is just to run this script, copy & paste the output, then run that.
select 'drop user ' || grantee || ' cascade;' as script
from dba_role_privs
where granted_role = 'STUDENT';
If you really want to use PL/SQL, though, you'd have to do it like this. I used an implicit cursor loop because it's easy and shorter to type.
begin
for REC in (select grantee from dba_role_privs where granted_role = 'STUDENT')
loop
execute immediate 'drop user ' || REC.grantee || ' cascade';
end loop;
end;
/
