patternsqlModerate

grant select on all databases except one mysql

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

databasesallgrantonemysqlselectexcept

Problem

I have user called test_user created under MySQL 5.5.17 under MS Windows 2008R2, I want to grant this user select privileges on all databases except MySQL database, note I have around 200 database inside this instance.

EDIT:

EDIT2:

Solution

Run the output of the following query:

SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';")
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME NOT LIKE 'mysql';

Code Snippets

SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';")
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME NOT LIKE 'mysql';

Context

StackExchange Database Administrators Q#98949, answer score: 10

Revisions (0)

No revisions yet.