スマートスタイル TECH BLOG

データベース&クラウド技術情報

MySQL 8.0から追加されたロール機能について

MySQL 8.0から追加されたロール機能について

MySQL 8.0 からロール機能が追加されました。

すでにOracle DatabaseやPostgreSQLには存在しているため、MySQLではまだかと待ち望んでいた方も多いのではないでしょうか。

今回の記事では、MySQLのロールについての使用方法と、その他のデータベースのロールとの差異について見ていこうと思います。

環境

今回比較した環境は以下の通りです。

プロダクト バージョン
MySQL 8.0.11
Oracle 12.1.2
PostgreSQL 10.4

ロールの概念

MySQL

A MySQL role is a named collection of privileges.
Like user accounts, roles can have privileges GRANTed to and revoked from them.

PostgreSQL

PostgreSQL manages database access permissions USING the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.

Oracle

ユーザー・ロールは、作成したり他のユーザーに割り当てることができる権限の名前付きコレクションです。

MySQLでは、権限のコレクションでありユーザーアカウントのようなものと言っています。
PostgreSQLではそもそもロールによりデータベースへのアクセス権限を制御していて、ロールはユーザーであり、グループであると言っています。
Oracleの場合は、権限の名前付きコレクションであると言っています。

Oracleの場合はあくまでユーザーに適用するためのオブジェクトですが、MySQLやPostgreSQLではロールはほぼユーザーと同様です(ロールでログインすることも可能です)。

データベースによって、このように内部的な差異はありますが、いずれの製品でも事前定義済みの権限のコレクションを、ユーザーに適用して使う事に変わりありません。

ロールの作成

全てのデータベースにおいて、 CREATE ROLEコマンドで作成します。

MySQL/CREATE ROLE

PostgreSQL/CREATE ROLE

Oracle/CREATE ROLE

MySQL、PostgreSQLではCREATE ROLEを実行すると実際にユーザーが作成されます。
CREATE USERと異なるのは、デフォルトではログイン不可である点です。

Oracleではロールは別のオブジェクトです。

OracleではCREATE ROLEの際にパスワードを設定する事が可能です。
パスワード付きロールは、SET ROLE .. IDENTIFIED BY で有効化します。

ロールへの権限付与、ユーザーへのロールの付与、ロールへのロールの付与

いずれのデータベースも、GRANT を使用します。

MySQL/GRANT
PostgreSQL/GRANT
Oracle/GRANT

通常の権限付与と同じように、ロールへ権限を付与します。

ユーザーへのロールの付与も、同じように実施します。

ロールへのロールの付与も可能です。

ロールはユーザーと同じですので、他のユーザーと同じ権限を持つユーザーを作成する事も可能です。
MySQLでデフォルトで作成されるroot@localhostユーザーの全権限を付与したい場合は、以下のようにできます。

PostgreSQLも同様に、管理ユーザーであるpostgresの権限を付与できます。

例えば、別のアプリ用のユーザーが持つ権限を使う必要がある場合には、予め当該ユーザーをロールとして適用しておき、後述するSET ROLEで切り替えるような使い方が可能ですので、管理するユーザーを削減できるかもしれません。

Oracleの場合は、ユーザーにはロールのみを付与するという運用ルールを決めた上で、あるユーザーと同じロールを付与して同じ権限とする形になります。

ロールの有効化

MySQLでは、GRANTでロールを付与しただけではユーザーセッションでロールが有効になりません。

SET DEFAULT ROLEで設定したデフォルトのロールのみが、ログイン直後に有効化されます。

または、activate_all_roles_on_loginシステム変数を有効化している場合、付与されたロールが全て有効化されます。

現在接続しているユーザーで、使用可能なロールを有効化したい場合は、SET ROLEを実行します。

有効化されたロールはcurrent_role()ファンクションで確認できます。

PostgreSQLでは、適用したロールは一部の権限を除き、ログイン時にデフォルトで有効化されます。

詳しくはCREATE ROLEを参照してください。

Oracleも同様に、ログイン時にはパスワード付きロールを除いてデフォルトでは有効化されます。

ロールの権限確認

MySQLではSHOW GRANTSで確認可能です。

ユーザーに付与されたロールの権限はデフォルトでは表示されませんがUSING句を使う事で表示されます。

PostgreSQLではpsqlの\z、\duコマンドで確認可能です。

user1.tab1にはmyroleがr(read)権限を持ち、user2はmyroleのメンバであることがわかります。

Oracleだとユーザーに付与されたロールはデータ・ディクショナリから確認可能です。

全てのロールはDBA_ROLE_PRIVSで確認できます。

接続したユーザーに付与されているロールを確認するにはUSER_ROLE_PRIVSを確認します。

ロールに適用した権限は、DBA_TAB_PRIVSDBA_SYS_PRIVSを確認します。
※ALL_XXX_PRIVSでも可能

例えば全ロールに付与した全権限を確認する場合は以下のようなSQLになります。

定義済みロールと、PUBLICロール

Oracleには、Oracleデータベースが持つ機能等の単位で事前に定義されたロールが多数ありますが、PostgreSQLやMySQLにはそのようなものはありません。

また、OracleやPostgreSQLにはあって、MySQLには無いものとしては、PUBLICロールがあります。

PUBLICロールは、全ユーザーに暗黙的に付与されているロールで、デフォルトの権限を定義する事が可能です。

MySQLでは、似たような機能として SET DEFAULT ROLEによるデフォルトロールの定義が可能ですが、ユーザー単位の設定になるため、使用感としてはmandatory_roles + init_connectの組み合わせの方が近いように思います。

サンプル構成

今後MySQLではロールが使用できるという事で、以下のようなロールを用意しておくと管理上見通しがよくなります。

ロール名 説明
ROLE_SCHEMA_READ 特定のスキーマへのSELECT権限が含まれるロール
ROLE_SCHEMA_INSERT 特定のスキーマへのINSERT権限が含まれるロール
ROLE_SCHEMA_WRITE 特定のスキーマへのUPDATE, DELETE, REPLACE権限が含まれるロール
ROLE_APP アプリケーションに必要な権限が含まれるロールをまとめるロール
ROLE_ADMIN 管理系操作に必要な権限が含まれるロール
ROLE_REPLICATION レプリケーションに必要な権限が含まれるロール

まとめ

ロールを使用するメリットとしては、以下のようなものがあります。

  • ユーザーに適用する権限の差異を気にする必要がなくなる
    棚卸しや、運用時の権限付与、剥奪が楽に
  • 何度もREVOKEやGRANTコマンドを実行する必要が無くなる
    長文なコマンドを実行する等ヒューマンエラーやバグの温床を回避
  • ユーザーを増やすのではなく、ロールをユーザー側で切り替えて使う運用が可能に
    不要なユーザー増加の回避。インフラ作業の減少

MySQLのロールは新しく追加されたものですが、他データベースと遜色ない機能が備わっており、MySQLへの移行をお考えの方はよりハードルが低くなりました。

ますます使いやすいデータベースになったのでは無いでしょうか。


MySQL

 

Return Top