Search code examples
sqlsql-grantaccounts

In SQL can you GRANT privileges to MULTIPLE accounts with a single command?

This is just for a uni assignment. Instead of doing this:

DENY DELETE ON SECRETAGENT, SECRETMISSION, AGENTMISSION TO A1;

DENY DELETE ON SECRETAGENT, SECRETMISSION, AGENTMISSION TO A2;

DENY DELETE ON SECRETAGENT, SECRETMISSION, AGENTMISSION TO A3;

is it possible to just write one line like this?

DENY DELETE ON SECRETAGENT, SECRETMISSION, AGENTMISSION TO A1, A2, A3;

I haven't found any site that specifies whether this can or cannot be done.

Solution

  • It's all in the manual:

    REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

    The [] mean, it's optional, so yes, you can do it in one line for multiple users, but not for multiple named objects (, but for multiple objects with wildcards, like ON databasename.*).