mysqldatabasesql-grantfiltered

mysql grant permissions on all but system tables

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.

Solution

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