grant a role to a selected subset

user_1659500 0
Viewed 34

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!

oracle sql
2 Answers

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;
user_38379610

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;
/
user_71100990
Related