Search code examples
mysqlgoogle-cloud-sqlprivileges

How do I prevent a user from seeing a list of databases and tables in MySQL?

I'm using MySQL version 5.7.25 on Google Cloud.

I revoked all privileges:

REVOKE ALL ON *.* FROM 'user1';

I added SELECT privileges on one table:

GRANT SELECT ON database1.table1 TO 'user1';

I check privileges using:

SHOW GRANTS FOR 'user1'

Result:
GRANT USAGE ON *.* TO 'user1'@'%' WITH GRANT OPTION
GRANT SELECT ON `database1`.`table1` TO 'user1'@'%'

Now 'user1' cannot access other tables. However, 'user1' can still see a list of databases and tables using:

SHOW DATABASES
SHOW TABLES

I've tried using:

REVOKE SHOW DATABASES ON *.* FROM 'user1';

In the documentation, this seems to be the command that I want to revoke. However, this has no additional effect on privileges because the USAGE privilege overrides this SHOW DATABASES privilege.

I've seen suggestions to use "skip-show-database" on startup, but this turns off the SHOW DATABASES command for all users.

How can I limit a user to only see DATABASES and TABLES that are usable? Or how can I completely turn off those commands for a single user?

Solution

  • According to the MySQL documentation for the SHOW DATABASES statement:

    If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege

    The skip_show_database flag is on the list of supported flags for Cloud SQL.