はじめに

AWSのRDSにて、MariaDBを使ってみました。
サービスごとに権限を分けてロールで管理して使うようにしたのですが、
そのような例がネット上にあまり見つからなかったので、
記録として残しておきます。

実行したMariaDBのバージョンは10.4です。
ロール機能は10.0.5からですが、
実質的にはデフォルトロールが使える10.1からが
ロール対応バージョンということのようです。

ちなみにMySQLでは8.0からロール機能が使えます。

構成

サービスごとにDB(スキーマ)をservice01_db, service02_dbの２つ作ります。
service01_roleに属しているservice01_userはservice01_dbにのみアクセス可能にします。
service02についても同様です。

どちらのDBにもアクセス可能なpoweruser_roleとそのユーザ(poweruser_user)を作ります。
これらのユーザには管理者権限は与えません。

また、最初に作成したマスターユーザー(admin)と同じ権限を持つ、
admin_roleとadmin_userを作ります。

WebコンソールでのRDS構築

初期DBとしてservice01_dbを作り、
adminという管理者ユーザを作ります。
最初のRDSの構築についてはここでは省略。

以下、service01_dbにadminユーザで接続して作業します。
なお、接続する際のクライアントとしてはRedshiftの接続クライアントツールの比較でも紹介したDBeaverが使えます。
接続先設定としてMariaDBがデフォルトで用意されているのがいい感じです。

データベース

サービスごとにデータベースを分けて作ります。
サービス1用のDBとして初期構築したservice01_dbを使い、
サービス2用のDBとして新たにservice02_dbを作ります。

作成

create database service02_db;

確認

show databases;

ロール

以下のようにロール、ユーザを作っていきます。

ロール名	権限タイプ	範囲	ユーザ
admin_role	すべての権限	すべて	admin_user
poweruser_role	権限管理以外	すべて	poweruser_user
service01_role	権限管理以外	service01_db	service01_user
service02_role	権限管理以外	service02_db	service02_user

作成

----- admin_role -----
create role admin_role;
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
ON *.* TO admin_role
WITH GRANT OPTION;

----- poweruser_role -----
create role poweruser_role;
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, 
	EVENT, TRIGGER
ON *.* TO poweruser_role;

----- service01_role -----
create role service01_role;
-- グローバル単位
grant 
	RELOAD, PROCESS, 
	SHOW DATABASES, 
	REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO service01_role;
-- DB単位
grant 
	SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
	REFERENCES, INDEX, ALTER, 
	CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, 
	CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, 
	EVENT, TRIGGER
ON service01_db.* TO service01_role;

----- service02_role -----
create role service02_role;
-- グローバル単位
grant 
	RELOAD, PROCESS, 
	SHOW DATABASES, 
	REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO service02_role;
-- DB単位
grant 
	SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
	REFERENCES, INDEX, ALTER, 
	CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, 
	CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, 
	EVENT, TRIGGER
ON service02_db.* TO service02_role;

admin_role はALLで作ってしまえばいい気がしますが、
RDSではALLが使えないので以下のSQLで得られた結果の権限をずらずらと並べています。

-- 管理者ユーザの権限一覧
show grants for admin;

参考：MySQL を実行している Amazon RDS DB インスタンス用に別のマスターユーザーを作成する方法を教えてください

service01_role, service02_roleについては、
権限によってグローバル単位のものとDB単位のものがあるので
2回に分けて権限を付与しています。

今回それぞれに与えた権限を表にすると以下のようになります。

権限の付与単位にはDB単位以外にもテーブル単位などもありますので、
詳細はMariaDB公式のGRANTの説明を参照してください。

確認

-- 作成されたロールの確認
select * from mysql.user u where is_role = 'Y';
-- データベースとロールの関連を確認
select db, user from mysql.db order by db, user;

作成したロールはmysql.userに入ります。
テーブル名通り、ユーザもこのテーブルに格納されるのですが、
ロールかどうかはis_role = 'Y'で判別します。
なお、このis_roleカラムはMySQLにはありません。

ユーザ

ユーザを作成します。
パスワードはダミーで入れています。

作成

create user admin_user identified by 'PASSWORD_AAA';
create user poweruser_user identified by 'PASSWORD_BBB';
create user service01_user identified by 'PASSWORD_XXX';
create user service02_user identified by 'PASSWORD_YYY';

確認

select user from mysql.user where is_role = 'N' order by user;

ユーザへのデフォルトロール割り当て

ログイン時にロールが割り当たるように
デフォルトロールとして設定します。

作成

-- admin_user
grant admin_role to admin_user;
set default role admin_role for admin_user;
-- poweruser_user 
grant poweruser_role to poweruser_user;
set default role poweruser_role for poweruser_user;
-- service01_user 
grant service01_role to service01_user;
set default role service01_role for service01_user;
-- service02_user 
grant service02_role to service02_user;
set default role service02_role for service02_user;

確認

-- ユーザにロールが割り当たっているかを確認
select * from mysql.roles_mapping rm order by User;
-- それぞれのユーザのデフォルトロールを確認
select user, default_role from mysql.user u where is_role = 'N' order by user;

なお、MySQLではmysql.role_edges、mysql.default_rolesで確認するようです。

おわりに

MariaDBとMySQL、内部的には違っていても人間が触る部分は一緒と思っていたのですが、
確認に使うシステム系のテーブルなど
違っているところもあり、いろいろと注意点がありそうです。