Create a User With Access to Only One Schema in CloudSQL

TL;DR: After you create a user in Google Cloud Console, don't forget to REVOKE 'cloudsqlsuperuser'@'%' FROM 'your-user'@'%'; if you only want this user to access specific schemas.

Creating a MySQL user via the Google Cloud Console automatically adds the cloudsqlsuperuser role that allows the user access to everything on that MySQL instance:

SHOW GRANTS FOR 'user-from-gcp-console'@'%';

+------------------------------------------------------------+
|Grants for user-from-gcp-console@%                          |
+------------------------------------------------------------+
|GRANT USAGE ON *.* TO `user-from-gcp-console`@`%`           |
|GRANT `cloudsqlsuperuser`@`%` TO `user-from-gcp-console`@`%`|
+------------------------------------------------------------+

Google mentions this in the About MySQL users article of their Knowledge Base.

To create a user with access to only one schema, you either need to create the user without the console by running something along the lines of:

CREATE USER 'your-user'@'%' IDENTIFIED
    WITH 'mysql_native_password'
    BY '<some-strong-password>';

GRANT ALL ON your-schema.* TO 'your-user'@'%';

Or by creating the user via the console but then not forgetting to remove the cloudsqlsuperuser role:

// Create a user via the Google Cloud Console

REVOKE 'cloudsqlsuperuser'@'%' FROM 'your-user'@'%';
GRANT ALL ON your-schema.* TO 'your-user'@'%';

Closing notes

• The user is uniquely identified by the user name and the host. % stands for any host, the commands might differ when you’re limiting the user access only to some hosts (e.g. your-user%localhost).
• You might make use of the Cloud SQL Proxy in combination with IAM instead. Read more in the IAM Authentication article.
• There is now also the Cloud SQL Studio [in preview] that might come in handy.
• More info about user management in MySQL db here.

The above is the detailed content of Create a User With Access to Only One Schema in CloudSQL. For more information, please follow other related articles on the PHP Chinese website!