Can I create a read-only user in Google Cloud SQL

Question

Google Cloud SQL only allows you to control the database root user password. I need to create a new user and limit his access to read-only, while maintaining the full privileged user.

Is there a way of doing that ?

Thanks in advance,

Answer 1

You can achieve that using the regular MySQL permissions. Here is how you can add a user that has SELECT access to a database test.

mysql> GRANT ALL ON test.* TO user@'%';
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW GRANTS FOR user@'%'\G
*************************** 1. row ***************************
Grants for user@%: GRANT USAGE ON *.* TO 'user'@'%'
*************************** 2. row ***************************
Grants for user@%: GRANT ALL PRIVILEGES ON `test`.* TO 'user'@'%'
2 rows in set (0.07 sec)

mysql> 

If you want to grant SELECT access to all the databases you can use GRANT ALL ON `%`.*.

Reference: Cloud SQL: How can I use GRANT ALL?

Answer 2

Should not use ALL

GRANT SELECT ON test.* TO user@'%';

Answer 3

Steps within GCP:

First create database user using Built-in authentication option at https://console.cloud.google.com/sql/instances/YOUR_INSTANCE_NAME/users

Then revoke cloudsqlsuperuser privilege:

REVOKE `cloudsqlsuperuser`@`%` FROM `user`@`%`;

Grant SELECT privilege:

GRANT SELECT ON *.* TO `user`@`%`;

Verify:

SHOW GRANTS FOR 'user';
-- You should see:
-- GRANT SELECT ON *.* TO `user`@`%`