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.
Top comments (0)