SQL user administration¶
Use cases:
- Add user access to MySQL instance/database/table
- Modify user access privileges
- Remove user
Database Glossary¶
Where to find DBs requested by devs. Add additional names used by teams
| DB name | Host / IP | Type | GCP resource name | GCP project | Admin Auth | Dev Auth |
|---|---|---|---|---|---|---|
| beta DB / beta_ziskejucet / tbl_ziskejucet / aff_tracker | beta.db.fftrader.cz | VM | sanitizer-vm | ethereal-accord-263421 | root + BitWarden password + local socket | require SSL + public IP |
| HLF_PRODUCTION / hlf_master / sanitized_hfl_master | api.ftmo.com | VM | ftmo-web-trader | ethereal-accord-263421 | root + local socket no password | SSH tunnel |
Cookbook¶
1. Find database¶
# new infra
gcloud beta sql instances list --project=devops-309909
# legacy infra
gcloud beta sql instances list --project=ethereal-accord-263421
OR via GCP console:
2. Create temporary Devops superuser (if does not exist)¶
MYSQL_PASSWORD=$(openssl rand -base64 18)
MYSQL_INSTANCE="ftmo-dev-cluster-mysql-private-instance-1f9d0fc1"
gcloud beta sql users create devops --host='%' --password=$MYSQL_PASSWORD --instance=$MYSQL_INSTANCE
3. Login as superuser¶
gcloud beta sql connect $MYSQL_INSTANCE --user=devops
Connecting to database with SQL user [devops].Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
4. List users & permissions, show databases (Optional)¶
mysql> select user,host from mysql.user;
+--------------------------------+-----------------+
| user | host |
+--------------------------------+-----------------+
| viliam | % |
| root | 127.0.0.1 |
| frantisek | 195.39.45.226 |
| meno | cloudsqlproxy~% |
+--------------------------------+-----------------+
mysql> show grants for 'meno'@'cloudsqlproxy~%';
+----------------------------------------------------------------------------------------+
| Grants for meno@cloudsqlproxy~% |
+----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `meno`@`cloudsqlproxy~%` |
| GRANT SELECT, UPDATE, DELETE, CREATE, ALTER ON `tbl-dev`.* TO `meno`@`cloudsqlproxy~%` |
+----------------------------------------------------------------------------------------+
mysql> show databases ;
+--------------------------------+
| Database |
+--------------------------------+
| accounting-service-dev |
| affiliate_tracker |
| crm-dev |
| duplication-service-dev |
| file_service_team_file_service |
| gamification-dev |
| information_schema |
| mysql |
| notifications_service_nc |
| notifications_service_ws |
| performance_schema |
| sys |
| tbl-dev |
| verified-identity |
+--------------------------------+
6. Create new user¶
It is recommended to setup access via cloudsqlproxy
mysql> create user 'jan.novak'@'cloudsqlproxy~%' identified by 'heslo';
mysql> select user,host from mysql.user;
+--------------------------------+-----------------+
| user | host |
+--------------------------------+-----------------+
| jan.novak | cloudsqlproxy~% |
+--------------------------------+-----------------+
Users in Sanitizer VM are set up with SSL enabled access
mysql> create user 'some-username'@'cloudsqlproxy~%' identified by 'some-secret-password' require ssl;
7. Grant access¶
Read only access for specific database
mysql> GRANT select ON `tbl-dev`.* TO `jan.novak`@`cloudsqlproxy~%` ;
mysql> flush privileges;
mysql> show grants for 'jan.novak'@'cloudsqlproxy~%';
+--------------------------------------------------------------+
| Grants for jan.novak@cloudsqlproxy~% |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jan.novak`@`cloudsqlproxy~%` |
| GRANT SELECT ON `tbl-dev`.* TO `jan.novak`@`cloudsqlproxy~%` |
+--------------------------------------------------------------+
Read-write access for specific database
mysql> GRANT ALL PRIVILEGES ON `tbl-dev`.* TO `jan.novak`@`cloudsqlproxy~%` ;
mysql> flush privileges;
mysql> show grants for 'jan.novak'@'cloudsqlproxy~%';
+----------------------------------------------------------------------+
| Grants for jan.novak@cloudsqlproxy~% |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jan.novak`@`cloudsqlproxy~%` |
| GRANT ALL PRIVILEGES ON `tbl-dev`.* TO `jan.novak`@`cloudsqlproxy~%` |
+----------------------------------------------------------------------+
7. Remove access¶
Remove all privileges
mysql> REVOKE all ON `tbl-dev`.* FROM `jan.novak`@`cloudsqlproxy~%` ;
mysql> flush privileges;
mysql> show grants for 'jan.novak'@'cloudsqlproxy~%';
+-----------------------------------------------------+
| Grants for jan.novak@cloudsqlproxy~% |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `jan.novak`@`cloudsqlproxy~%` |
+-----------------------------------------------------+
Remove specific access privileges
mysql> REVOKE update ON `tbl-dev`.* FROM `jan.novak`@`cloudsqlproxy~%` ;
mysql> flush privileges;
mysql> show grants for 'jan.novak'@'cloudsqlproxy~%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for jan.novak@cloudsqlproxy~% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jan.novak`@`cloudsqlproxy~%` |
| GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `tbl-dev`.* TO `jan.novak`@`cloudsqlproxy~%` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+