はじめに

誤って、マスターユーザーを削除した場合や権限を剥奪した場合、パスワードがわからなくなってしまった場合に有効な方法をご紹介します。
もし、誤ってマスターユーザーを削除しても大きな作業なく復元することが可能です。

解決策

DB インスタンスの変更から「新しいマスターパスワード」を再設定します。
そうすることで、再度マスターユーザーが作成されユーザーを復元することができます。

今回は、マスターユーザーとして作成した「root ユーザー」を削除し復元する方法をご紹介します。

なお、本操作はインスタンスの再起動を問わないためいつでも実行することができます。

今回は Aurora MySQL で検証しております。
おそらく、本方法は RDS の仕様に基づくものなので、その他の DB エンジンでもできる可能性がありますが、その他の DB エンジンの動作については不明なためご注意下さい。

0. 下準備

本セクションでは以下を実施します。

1. root で接続
2. root2 ユーザーの作成
3. root2 ユーザーに root ユーザーと同じ権限を付与
4. root ユーザー削除

復元方法については次のセクションからご覧ください。

root で接続

まずは、root ユーザーで接続します。

MySQL [(none)]> select user();
+-----------------+
| user()          |
+-----------------+
| root@10.0.1.172 |
+-----------------+
1 row in set (0.00 sec)

権限を確認します。

MySQL [(none)]> show grants for root;
+----------------------------------------------+
| Grants for root@%                            |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `root`@`%`             |
| GRANT `rds_superuser_role`@`%` TO `root`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)

MySQL [(none)]> SHOW GRANTS FOR 'root'@'%' USING 'rds_superuser_role'\G;
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, 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 ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@%: GRANT APPLICATION_PASSWORD_ADMIN,CONNECTION_ADMIN,REPLICATION_APPLIER,ROLE_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@%: GRANT `rds_superuser_role`@`%` TO `root`@`%`
3 rows in set (0.00 sec)

root2 ユーザーの作成

root2 ユーザーを作成します。

MySQL [(none)]> CREATE USER 'root2' @'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]>  select user,host from mysql.user;
+-----------------------+-----------+
| user                  | host      |
+-----------------------+-----------+
| AWS_COMPREHEND_ACCESS | %         |
| AWS_LAMBDA_ACCESS     | %         |
| AWS_LOAD_S3_ACCESS    | %         |
| AWS_SAGEMAKER_ACCESS  | %         |
| AWS_SELECT_S3_ACCESS  | %         |
| rds_superuser_role    | %         |
| root                  | %         |
| root2                 | %         |
| mysql.infoschema      | localhost |
| mysql.session         | localhost |
| mysql.sys             | localhost |
| rdsadmin              | localhost |
+-----------------------+-----------+
12 rows in set (0.00 sec)

root2 ユーザーに root ユーザーと同じ権限を付与

root ユーザーを同じパスワードを付与します。
今回の場合は、rds_superuser_roleを付与すれば同じ権限になります。

MySQL [(none)]> GRANT 'rds_superuser_role' TO 'root2'@'%';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SHOW GRANTS FOR 'root2'@'%' ;
+-----------------------------------------------+
| Grants for root2@%                            |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `root2`@`%`             |
| GRANT `rds_superuser_role`@`%` TO `root2`@`%` |
+-----------------------------------------------+
2 rows in set (0.00 sec)

MySQL [(none)]> SHOW GRANTS FOR 'root2'@'%' USING 'rds_superuser_role'\G;
*************************** 1. row ***************************
Grants for root2@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, 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 ROLE, DROP ROLE ON *.* TO `root2`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root2@%: GRANT APPLICATION_PASSWORD_ADMIN,CONNECTION_ADMIN,REPLICATION_APPLIER,ROLE_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,XA_RECOVER_ADMIN ON *.* TO `root2`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root2@%: GRANT `rds_superuser_role`@`%` TO `root2`@`%`
3 rows in set (0.00 sec)

root ユーザー削除

では、root ユーザーを削除します。

MySQL [(none)]> drop user 'root'@'%';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> select user,host from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'10.0.1.172' for table 'user'
MySQL [(none)]> select user();
+-----------------+
| user()          |
+-----------------+
| root@10.0.1.172 |
+-----------------+
1 row in set (0.00 sec)

接続確認をしますが、root ユーザーが存在しないので接続できません。

[root@ip-10-0-1-172 ~]# mysql -u root  -p -h cold-airflow-aurora-peformance.cluster-co4nqylz6jpl.us-east-1.rds.amazonaws.com
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'10.0.1.172' (using password: YES)

1. DB インスタンスの変更を選択

では、root ユーザーを復元します。

RDS の場合は、一つのインスタンスですが、Aurora クラスターの場合も同じく各インスタンスを選択して実施いたします。

今回の場合は、ライターインスタンスを選択して右上から変更を選択します。

2. 「新しいマスターパスワード」を変更

「新しいマスターパスワード」の変更欄があるので、同じパスワードや忘れている場合は新しいパスワードを入力します。

3. 「すぐに適用」で変更

パスワードが入力できたら変更を適用します。
本設定だけだと「すぐに適用」しか選択できませんが、他の設定と合わせる場合も「すぐに適用」を選択することですぐに復元ができます。

4. マスターユーザーで接続確認

すぐに復元が行われるため、再度接続して root ユーザーを確認します。

MySQL [(none)]> select user();
+-----------------+
| user()          |
+-----------------+
| root@10.0.1.172 |
+-----------------+
1 row in set (0.00 sec)

MySQL [(none)]> select user,host from mysql.user;
+-----------------------+-----------+
| user                  | host      |
+-----------------------+-----------+
| AWS_COMPREHEND_ACCESS | %         |
| AWS_LAMBDA_ACCESS     | %         |
| AWS_LOAD_S3_ACCESS    | %         |
| AWS_SAGEMAKER_ACCESS  | %         |
| AWS_SELECT_S3_ACCESS  | %         |
| rds_superuser_role    | %         |
| root                  | %         |
| root2                 | %         |
| mysql.infoschema      | localhost |
| mysql.session         | localhost |
| mysql.sys             | localhost |
| rdsadmin              | localhost |
+-----------------------+-----------+
12 rows in set (0.00 sec)

接続ができましたので、復元完了です。

まとめ

マスターユーザーのパスワードを変更することで、パスワードだけでなくユーザーや権限を復元することができます。
もし、誤って削除した場合でも慌てず対応しましょう。

参考

RDS for SQL Server インスタンスへのマスターユーザーアクセスを復元する | AWS re:Post
Amazon RDS DB インスタンスのマスターユーザーのパスワードをリセットする | AWS re:Post
マスターユーザーアカウント特権 – Amazon Relational Database Service