How to Grant User Privileges in Google Cloud MySQL

When you create a new user account in your Google Cloud MySQL database, it has the same privileges as a root user. It is, therefore, a good idea to limit the admin privileges of the new MySQL user with the REVOKE command and explicitly grant the required user privileges with the GRANT statement.

Google Cloud SQL User Privileges

You can use MySQL Workbench or Sequel Pro to connect to your Cloud SQL database with the root user. Make sure that your database has a public IP and your computer’s IP address is added as an authorized network in the Connections tab of your Database console.

SHOW GRANTS FOR db_user

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

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.

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

Execute the Flush Privileges statement again to apply the changes.

FLUSH PRIVILEGES

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

Amit Agarwal

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

0

Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

Google Developer Expert

Google Developer Expert

Google awarded us the Google Developer Expert award recogizing our work in Google Workspace.

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards in 2017.

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator title recognizing our technical skill and expertise.

Email Newsletter

Sign up for our email newsletter to stay up to date.

We will never send any spam emails. Promise.