DBA Tasks for Dawnbreaker¶

Introduction¶

The database tasks for database instance inno-dawnbreaker

Database Engine: MySQL version 8.0

Provider: Google Cloud Platform

Connection name: <project_id>:<project_region>:inno-dawnbreaker

Read through:

[1] Cloud MySQL Document: Cloud SQL overview | Cloud SQL for MySQL | Google Cloud

Registered Database:

Initiation Setting¶

[1] Setting SQL Modes Docs official: MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes

Connect with flags in Cloud SQL: Configure database flags | Cloud SQL for MySQL | Google Cloud

Currently, not turn off STRICT_MODE → This is create problems instead of supports.

1. Grant Super user instead using root¶

[1] Currently, a super user is created and managed by Terraform and by using gcloud CLI

[2] Grant all by using global privileges

GRANT ALL ON *.* TO 'someuser'@'somehost';

This required root credentials.

Note:

Default Users can't modify

There are seven system users and you cannot delete or modify these users.

root@localhost, root@127.0.0.1, root@::1

Used to provide the managed database service.

cloudsqlreplica@%

Used as a replication user for replicas.

cloudsqlimport@localhost

Used for data imports.

cloudsqlexport@localhost

Used for data exports.

cloudsqloneshot

Used for other database operations.

2. Manage users with IAM authentication¶

References: Manage users with IAM database authentication | Cloud SQL for PostgreSQL | Google Cloud

3. Default System Databases for MySQL Cloud SQL¶

A newly-created instance has four system databases:

information_schema: Provides access to database metadata, and information about the MySQL server.

mysql: The system schema. It contains tables that store data required by the MySQL server as it runs.

performance_schema: A feature for monitoring MySQL Server execution at a low level.

sys: Contains a set of objects that helps DBAs and developers interpret data collected by the performance schema.

Ref: Configure new and existing instances for IAM database authentication | Cloud SQL for MySQL | Google Cloud

4. Query - Get information of session user authentication method of yourself¶

Once you log in, please run this

SELECT USER(), CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to show in MySQL from the mysql.user table

Ref: MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

5. DDL - Create User¶

Ref: MySQL :: MySQL 8.0 Reference Manual :: 15.7.1.3 CREATE USER Statement

Example on runner_bao_truong

Note: Password has been changed, this is just an example.

For password: Using LastPass Generator Password Generator - LastPass

[1] Create Users

CREATE USER 'runner_bao_truong'@'%'
  IDENTIFIED BY '3nzMySh9mZPg3G6PPOw5uwqQ18cpdyeN'
  PASSWORD EXPIRE INTERVAL 365 DAY
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

[2] [Optional], Grant to roles exists. E.g: team_data

GRANT 'team_data' TO 'runner_bao_truong';
SET DEFAULT ROLE team_data to 'runner_bao_truong';
SET DEFAULT ROLE ALL TO 'runner_bao_truong';

[3] Drop User

DROP USER 'runner_bao_truong'@'%'

Ref: MySQL :: MySQL 8.0 Reference Manual :: 15.7.1.5 DROP USER Statement

[4] Reset new password for User

ALTER USER runner_bao_truong IDENTIFIED BY 'new_password';

In case the password's lifespan has expired, we will fight the fire by extending the password with the following command:

-- Note: Please calculate the number of days you want to extend the password from the time in the password_last_change column and replace it with XX

ALTER USER 'runner_bao_truong' PASSWORD EXPIRE INTERVAL XX DAY

6. DDL - Create Role for Data Team¶

[1] Create Role

CREATE ROLE 'team_data';

[2] Grant Perms: syntax GRANT <privileges> ON <database>.<object> TO '<role>'@'<host>';

GRANT
    CREATE,CREATE TEMPORARY TABLES,CREATE VIEW,
    SELECT,INSERT,UPDATE,DELETE,DROP,
    INDEX,REFERENCES,TRIGGER
ON basement.*
TO 'team_data'

[3] Grant users membership

GRANT 'team_data' TO 'bao.truong';

This will apply for selected users, you can see the Users tab in SQL in Google Cloude SQL Dashboard

[4] Set default

SET DEFAULT ROLE team_data to 'bao.truong';
SET DEFAULT ROLE ALL TO 'bao.truong';

[5] Check available permission;

SHOW GRANTS FOR 'bao.truong';

[6] Revoke users

REVOKE 'bao.truong' FROM 'team_data';

[7] Revoke roles

DROP ROLE 'team_data';

Ref: Well document at Using roles to manage privileges for users with MySQL | Prisma

Ref: MySQL :: MySQL 8.0 Reference Manual :: 15.7.1.4 DROP ROLE Statement

Must Read: MySQL Bugs: #91869: Documentation issue for SET DEFAULT ROLE

7. DDL - Priviledges of MySQL¶

For the full list, see: MySQL :: MySQL 8.0 Reference Manual :: 8.2.2 Privileges Provided by MySQL

Snapshot:

Summary of Available Privileges:

The following table shows the static privilege names used in GRANT and REVOKE statements, along with the column name associated with each privilege in the grant tables and the context in which the privilege applies.

Table 6.2 Permissible Static Privileges for GRANT and REVOKE:

8. DBA - Show Current Logged User¶

SELECT user, host, db, command FROM information_schema.processlist;

Ref: MySQL Show Users/List All Users - javatpoint

How to manage table ownership in MySQL¶

This is different compared to Postgres

There is no such thing as a table owner in MySQL.

A table typically belongs to a schema (ie a database), but not to a user.

The way to enforce access control is to use GRANT or REVOKE to give or withdraw privileges or roles to users (or roles) :

GRANT  SELECT, INSERT ON mydb.mytbl TO   'someuser'@'somehost';
REVOKE SELECT, INSERT ON mydb.mytbl FROM 'someuser'@'somehost';

Ref: How to manage table ownership in MySQL

9. Create runner_<IAM_user> for team members in application¶

Currently, based on the document in cloud-sql-python-connector,

# Connections using Automatic IAM database authentication are supported
# when using the Postgres driver. This feature is unsupported for other drivers.
# If automatic IAM authentication is not supported for your driver,
# you can use Manual IAM database authentication to connect.
# First, make sure to configure your Cloud SQL Instance to allow IAM authentication and
# add an IAM database user. Now, you can connect using user or service account credentials
# instead of a password. In the call to connect, set the enable_iam_auth keyword
# argument to true and user to the email address associated with your IAM user.

Currently, required to set up SSL handshake manually, which is not supproted from the driver itself.

So we will use the CREATE USER with the password authentication method.

10. Create Profiles for Streaming 2 Dawnbreaker¶

CREATE USER 'datastream'@'%'
  IDENTIFIED BY '--------------------------------------------'
  PASSWORD EXPIRE INTERVAL 365 DAY
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
GRANT 'team_data' TO 'datastream';
SET DEFAULT ROLE team_data to 'datastream';
SET DEFAULT ROLE ALL TO 'datastream';
GRANT REPLICATION SLAVE, SELECT, RELOAD, REPLICATION CLIENT, LOCK TABLES, EXECUTE
ON *.* TO 'datastream'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'datastream';

Source Reference¶

[1] Official Cloud Storage of GCP: Cloud SQL overview | Cloud SQL for MySQL | Google Cloud

[2] Terraform create user: Terraform Registry

[3] Backend MySQL Statements for DBA MySQL :: MySQL 8.0 Reference Manual :: 15.7 Database Administration Statements

[4] Troubleshooting for Cloud SQL: Troubleshooting Cloud SQL | Cloud SQL Documentation | Google Cloud