Home > Database > Mysql Tutorial > Create a User With Access to Only One Schema in CloudSQL

Create a User With Access to Only One Schema in CloudSQL

WBOY
Release: 2024-07-18 08:07:13
Original
402 people have browsed it

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`@`%`|
+------------------------------------------------------------+
Copy after login

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'@'%';
Copy after login

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'@'%';
Copy after login

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!

source:dev.to
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template