Search code examples
sqloracle-databaseselectsql-grantrights

SQL Grant on - for multiple users

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?

Solution

  • 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.