SQL Grant on - for multiple users

Question

i was curious if there was a way to grant multiple users simultaneously rights

Example: I want to give certain rights to multiple people that start with the letter AAR.

Usual grant on : GRANT SELECT ON Abteilung TO Herr_Mueller

How it goes for multiple people that start with AAR?

I don't think you can grant rights on table to multiples users using "LIKE 'AAR%'" .... I have not tried it, neither have I read anything similar. You can try on a test server but query will most probably fail. — Shahzaib, Aug 07 '17 at 09:35

score 18 · Accepted Answer · answered Aug 06 '17 at 10:20

If you have the list of users you can just append them to the statement:

GRANT SELECT ON Abteilung TO AAR1, AAR2, AAR3;

If you do not have the list ready your best bet is to do a select on all users to generate the statements necessary.

SELECT 'GRANT SELECT ON Abteilung TO ' || username || ';' FROM all_users WHERE username LIKE 'AAR%'

Then you just mark the result and execute them in a different session.

1 Answers1