Learn How to Grant User Privileges in Google Cloud MySQL

Advertisements

When you create a new user account in your Google Cloud MySQL database, it has the same permissions as root. As a result, it’s a good idea to use the REVOKE command to limit the new MySQL user’s admin capabilities and the Provide statement to expressly grant the needed user access.

To connect to your Cloud SQL database as the root user, utilise MySQL Workbench or Sequel Pro. Check that your database has a public IP address and that the IP address of your computer is listed as an approved network in the Connections tab of your Database panel.

SHOW GRANTS FOR db_user

Advertisements

If your MySQL user has root privileges, the statement will output the following:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'db_user'@'%' WITH GRANT OPTION

As a first step, you can revoke all privileges from the user account. You can either specify individual permissions, separated by commas, but since the root user has many privileges, we can revoke them all and grant the required one in another statement.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM db_user

Advertisements

Next, we tell the server to reload the privileges from the grant tables in the MySQL system schema.

FLUSH PRIVILEGES

Finally, grant the required privileges to the user. In our case, the user should only be able to read, insert, view and delete rows from all tables in a specific database.

Advertisements

GRANT SELECT, UPDATE, INSERT, DELETE ON db_name.* TO db_user

Execute the Flush Privileges statement again to apply the changes.

FLUSH PRIVILEGES

Advertisements

You may also run the SHOW GRANTS statement to verify that the correct privileges have been applied to the user.

SHOW GRANTS FOR db_user

Advertisements

Leave a Comment