ユーザーに権限を設定する(GRANT文)

MySQL では作成したユーザーに権限を設定するときに GRANT 文を使用します。ここでは MySQL でユーザーに対して権限を設定する方法について解説します。

(Last modified: 2023年01月07日)

ユーザーに権限を設定する
作成したばかりのユーザーに設定されている権限
グローバルレベルの権限を設定する
データベースレベルの権限を設定する
テーブルレベルの権限を設定する
カラムレベルの権限を設定する
すべての権限を設定する(ALL)
他のユーザーに権限を設定することができる権限(GRANT OPTION)

ユーザーに権限を設定する

ユーザーに対して権限を設定するには GRANT 文を使用します。書式は次の通りです。

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user
  | role
}

複雑な書式ですが、基本となる書式は次のようになります。

GRANT priv_type[, priv_type] ON priv_level TO user

ユーザー( user )に対して指定の種類の権限( priv_type )を指定のレベル( priv_level )で設定します。複数の権限をまとめて設定する場合はカンマ(,)で区切って続けて記述します。

※権限のレベル及び種類については「ユーザーに設定できる権限の種類と一覧」を参照して下さい。

各レベルに応じて権限を設定するには、それぞれ次のように記述します。

グローバルレベル
GRANT ALL ON *.* TO user;
GRANT SELECT, INSERT ON *.* TO user;

データベースレベル
GRANT ALL ON db_name.* TO user;
GRANT SELECT, INSERT ON mydb.* TO user;

テーブルレベル
GRANT ALL ON db_name.table_name TO user;
GRANT SELECT, INSERT ON db_name.mytbl TO user;

カラムレベル
GRANT SELECT (col1), INSERT (col1, col2) ON db_name.table_name TO user;

では実際に試しながら確認してみます。

作成したばかりのユーザーに設定されている権限

権限の設定の仕方を確認する前に、ユーザーを新しく作成したときにどんな権限が設定されているのかを確認してみます。次のように新しいユーザーを作成しました。

create user saru@localhost identified by 'monkey';

作成したばかりのユーザーに設定されている権限(1)

作成したユーザーに設定されている権限を確認します。(ユーザーに設定されている権限を確認する方法は「ユーザーに設定されている権限を確認する」を参照されてください)。次のように実行してください。

show grants for saru@localhost;

作成したばかりのユーザーに設定されている権限(2)

GRANT USAGE ON *.* TO `ユーザー名` と表示されました。これはグローバルレベルで USAGE という権限が設定されていることを示しています。この USAGE という権限は「何も権限がない」という権限です。つまり新しく作成したユーザーは、すべてのデータベースを対象に何も権限がない状態となっています。

グローバルレベルの権限を設定する

グローバルレベルはすべてのデータベースが対象となります。例えばグローバルレベルで CREATE 権限を設定すると、データベースを作成する CREATE DATABASE 文が制限なしで実行できます。またテーブルを作成する CREATE TABLE がすべてのデータベースで実行できます。

それではユーザーを新しく作成したあと、グローバレルレベルで CREATE 権限を設定してみます。次のように実行してください。

create user karasu@localhost identified by 'crow';
grant create on *.* to karasu@localhost;

グローバルレベルの権限を設定する(1)

karasu@localhost ユーザーにグローバルレベルで CREATE 権限を設定しました。では権限が設定されたかどうか確認してみます。

show grants for karasu@localhost;

グローバルレベルの権限を設定する(2)

karasu@localhost ユーザーにグローバルレベルの権限が設定されていることが確認できました。

データベースレベルの権限を設定する

データベースレベルは指定したデータベースが対象となります。例えばデータベースレベルで CREATE 権限を設定すると、指定したデータベースでテーブルを作成する CREATE TABLE を実行できるようになります。

それではユーザーを新しく作成したあと、データベースレベルで CREATE 権限を設定してみます。次のように実行してください。

create user kitsune@localhost identified by 'fox';
grant create on mydb.* to kitsune@localhost;

データベースレベルの権限を設定する(1)

kitsune@localhost ユーザーにデータベースレベルで mydb データベースを対象とした CREATE 権限を設定しました。このユーザーは mydb データベースであれば任意のテーブルを作成することができますが、他のデータベースではテーブルを作成することができません。

では実際に試してみます。コマンドプロンプトから kitsune@localhost ユーザーで MySQL に接続してください。

データベースレベルの権限を設定する(2)

mydb データベースを選択します。

use mydb;

データベースレベルの権限を設定する(3)

kitsune@localhost ユーザー mydb データベースで CREATE 権限がありますのでテーブルを作成することができます。今回は animal テーブルを作成してみます。

create table animal (id int, name varchar(10));

データベースレベルの権限を設定する(4)

テーブルを作成することができました。

なお権限を何も持っていない他のデータベースを use 文を使って選択しようとしても Access denied for user 'ユーザー名' to database 'データベース名' というエラーが発生してデータベースを選択することができません。

データベースレベルの権限を設定する(5)

テーブルレベルの権限を設定する

テーブルレベルは指定したテーブルが対象となります。例えばテーブルレベルで SELECT 権限を設定すると、指定したデータベースの中にある指定したテーブルで SELECT を実行できるようになります。

それではユーザーを新しく作成したあと、テーブルレベルで SELECT 権限を設定してみます。次のように実行してください。

create user neko@localhost identified by 'cat';
grant select on mydb.animal to neko@localhost;

テーブルレベルの権限を設定する(1)

neko@localhost ユーザーにテーブルレベルで mydb.animal テーブルを対象とした SELECT 権限を設定しました。このユーザーは mydb.animal テーブルであれば SELCT 文を使ってデータの取得ができますが、他のテーブルでは SELECT 文を実行することはできません。

では実際に試してみます。コマンドプロンプトから neko@localhost ユーザーで MySQL に接続してください。その後で mydb データベースに接続します。

テーブルレベルの権限を設定する(2)

neko@localhost ユーザーは mydb.animal テーブルで SELECT 権限がありますのでテーブルからデータを取得してみます。

select * from animal;

テーブルレベルの権限を設定する(3)

テーブルからデータを取得することができました。

このユーザーが SELECT 権限を持っているのは mydb.animal テーブルだけなので、他のテーブルから SELECT 文を使ってデータを取得しようとすると SELECT command denied to user 'ユーザー名' for table 'テーブル名' というエラーが発生してデータを取得することができません。

テーブルレベルの権限を設定する(4)

カラムレベルの権限を設定する

カラムレベルは指定したカラムが対象となります。例えばカラムレベルで SELECT 権限を設定すると、指定したテーブルの中の指定したカラムに対して SELECT を実行できるようになります。

それではユーザーを新しく作成したあと、カラムレベルで SELECT 権限を設定してみます。次のように実行してください。

create user inu@localhost identified by 'dog';
grant select (name) on mydb.animal to inu@localhost;

カラムレベルの権限を設定する(1)

inu@localhost ユーザーにカラムレベルで animal テーブルの中の name カラムを対象とした SELECT 権限を設定しました。このユーザーは mydb.animal テーブルの name カラムであれば SELCT 文を使ってデータの取得ができますが、同じテーブルであっても別のカラムに対しては SELECT 文を実行することはできません。

では実際に試してみます。コマンドプロンプトから inu@localhost ユーザーで MySQL に接続してください。その後で mydb データベースに接続します。

カラムレベルの権限を設定する(2)

inu@localhost ユーザーは mydb.animal テーブルの name カラムに対して SELECT 権限がありますのでテーブルからデータを取得してみます。

select name from animal;

カラムレベルの権限を設定する(3)

テーブルから name カラムデータを取得することができました。

このユーザーが SELECT 権限を持っているのは mydb.animal テーブルの name カラムだけなので、同じテーブルであっても別のカラムの値を SELECT command denied to user 'ユーザー名' for column 'カラム名' in table 'テーブル名' というエラーが発生してデータを取得することができません。

カラムレベルの権限を設定する(4)

すべての権限を設定する(ALL)

ALL 権限(または ALL PRIVILEGES 権限)は GRANT OPTION 権限と PROXY 権限以外の全ての権限を与える場合に使用します。ALL 権限はグローバル、データベース、テーブルレベルで設定することができ、グローバルレベルで ALL 権限を設定した場合はすべてのデータベースでほぼすべての権限を持つことになります。

なお ALL 権限を設定するために GRANT 文を実行する場合は、この権限を単独で設定してください。

それではユーザーを新しく作成したあと、グローバルレベルで ALL 権限を設定してみます。次のように実行してください。

create user kirin@localhost identified by 'giraffe';
grant all on *.* to kirin@localhost;

すべての権限を設定する(ALL)(1)

それでは設定された権限を確認してみます。

show grants for kirin@localhost¥G

すべての権限を設定する(ALL)(2)

ALL 権限が表示されるのではなく、ALL 権限を設定することで実際に設定された権限が全部表示されます。非常に多いためか2つに分けられていますが、グローバルレベルで次の権限が設定されています。

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `kirin`@`localhost`

GRANT APPLICATION_PASSWORD_ADMIN, BACKUP_ADMIN,BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_SLAVE_ADMIN, RESOURCE_GROUP_ADMIN, RESOURCE_GROUP_USER, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN, XA_RECOVER_ADMIN ON *.* TO `kirin`@`localhost`

他のユーザーに権限を設定することができる権限(GRANT OPTION)

例えばグローバルレベルで CREATE 権限を他のユーザーに設定する時、 GRANT OPTION を設定すると自分が設定された権限を別のユーザーに設定することができるようになります。

GRANT OPTION を使用する場合の書式は次のようになります。

GRANT priv_type ON priv_level TO user WITH GRANT OPTION

それではユーザーを新しく作成したあと、グローバルレベルで CREATE 権限を GRANT OPTION 付きで設定してみます。次のように実行してください。

create user uma@localhost identified by 'horse'; grant create on *.* to uma@localhost with grant option;

他のユーザーに権限を設定することができる権限(GRANT OPTION)(1)

uma@localhost ユーザーはグローバルレベルで CREATE 権限を持つと同時に、他のユーザーに同じ権限を設定することができるようになります。

では実際に試してみます。コマンドプロンプトから uma@localhost ユーザーで MySQL に接続してください。

他のユーザーに権限を設定することができる権限(GRANT OPTION)(2)

uma@localhost ユーザーはグローバルレベルの CREATE 権限を他のユーザーに設定する権限がありますので、 kame@localhost ユーザーに対してグローバルレベルで CREATE 権限を設定してみます。

grant create on *.* to kame@localhost;

他のユーザーに権限を設定することができる権限(GRANT OPTION)(3)

自分が GRANT OPTION 付きで設定された権限を他のユーザーに対して設定することができました。(なお root@localhost ユーザーはグローバルレベルの ALL 権限を GRANT OPTION 付きで設定されていますので、すべてのユーザーにどんな権限でも設定することができます)。

-- --

GRANT 文を使ってユーザーに権限を設定する方法について解説しました。

( Written by Tatsuo Ikura )

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。