-1

I am trying to grant a set of permissions to " all databases all tables" . excepted mysql system databases (mysql,information_schema,performance_schema, and sys (I believed I named them all?)).

I am not sure how to do that.

I need for instance a GRANT SELECT ON . excepted system tables.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Bluz
  • 5,980
  • 11
  • 32
  • 40
  • refer to [this answer](https://dba.stackexchange.com/questions/98949/grant-select-on-all-databases-except-one-mysql). – Lelio Faieta Feb 13 '20 at 14:33
  • thank you, but the accepted solution doesn't make much sense to me: SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';") FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT LIKE 'mysql'; SCHEMATA? this has to be a typo? – Bluz Feb 13 '20 at 16:19
  • no, that's not a typo. That's a proper table. Have you checked information_schema database? You will find that table – Lelio Faieta Feb 13 '20 at 16:23

1 Answers1

1

Use:

SELECT CONCAT("GRANT ALL PRIVILEGES ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';") FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql','information_schema', 'performance_schema','sys')

This will provide you queries for individual databases that you can use to grant the privileges to a particular user.

After that, use:

FLUSH PRIVILEGES;

I hope this helps!

Ranvir
  • 181
  • 1
  • 8