DEV Community

Tony Vlček
Tony Vlček

Posted on

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`@`%`|
+------------------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

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'@'%';
Enter fullscreen mode Exit fullscreen mode

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'@'%';
Enter fullscreen mode Exit fullscreen mode

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)