grant a role to a selected subset-CodePudding

I have a role I want to grant to a selected subset of users. Can someone help me with the syntax? Something like this:

grant my_special_role to (select username from dba_users where username like 'john%')

This is supposed to grant my_special_role to all users named John!

CodePudding user response：

You could do it in a loop, declarating a block of code

declare
begin
for rec in (select username from dba_users where username like 'john%')
loop
 execute immediate 'grant my special role to ' || rec.username;
end loop;
end;

CodePudding user response：

You can use a simple PL/SQL loop to execute the grants to all the users

BEGIN
    FOR i IN (SELECT username
                FROM dba_users
               WHERE username LIKE 'john%')
    LOOP
        EXECUTE IMMEDIATE 'grant my_special_role to ' || i.username;
    END LOOP;
END;
/