2

I am trying the query -

GRANT SELECT ON company.*, school.*, onlineshop.* TO user1@localhost;

To which I am getting a syntax error.

I can grant permissions one by one using -

GRANT SELECT ON company.* TO user1@localhost;
GRANT SELECT ON school.* TO user1@localhost;
GRANT SELECT ON onlineshop.* TO user1@localhost;

So is there a way to grant all the three permissions in a single query?

1

2 Answers 2

4

MySQL 8.0

You would create a role using CREATE ROLE. Next, you would run GRANT SELECT ON db.* to the role for all the databases you need. (You only need to do this once.)

Then, you would grant the role to the user.

See the MySQL documentation for examples of this.

MySQL 5.7 and Prior

You can create the grants, one for each database, like you just did in the question. Or it is possible in principle with two SQL commands (see Bill Karwin's comment).

2
  • Don't you still need multiple GRANT statements to give the role permission to each DB? This is more of a solution for multiple users, not multiple databases.
    – Barmar
    Dec 11, 2021 at 15:03
  • @Barmar agreed. Roles group actors/users, not resources.
    – mckenzm
    Dec 11, 2021 at 21:34
4

You can manipulate the system tables directly without using GRANT statements, but if you do that, you must use FLUSH PRIVILEGES to put the changes into effect.

INSERT INTO mysql.db (user, host, db, select_priv) VALUES
('user1', 'localhost', 'company', 'Y'),
('user1', 'localhost', 'school', 'Y'),
('user1', 'localhost', 'onlineshop', 'Y')
ON DUPLICATE KEY SET select_priv = 'Y';

FLUSH PRIVILEGES;

I show the usage of ON DUPLICATE KEY in case rows already exist in that table.

Technically this is not a single SQL statement because you need to use the FLUSH PRIVILEGES.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.