Y's note

Web技術・プロダクトマネジメント・そして経営について

本ブログの更新を停止しており、今後は下記Noteに記載していきます。
https://note.com/yutakikuchi/
トップ > Mysql > Mysqlのユーザ/権限管理

Mysqlのユーザ/権限管理

Mysql

GRANT文でユーザ設定をする!

  • GRANT構文はユーザの追加とユーザに対して与える権限の指定や接続可能元のhost名やパスワードの設定が可能。
  • GRANT文実行後はFLUSH PRIVILEGES;の実行により権限テーブルの再読み込みを行い、権限データを反映させる。
  • 権限の範囲は全てのDB、特定DB内部の全てのテーブル、特定テーブルの全てのカラム、指定テーブルの特定テーブルの計4種類。
構文
GRANT 付与する権限の種類[(カラムリスト)] [,付与する権限の種類[(カラムリスト) …] 
    ON データベース名.テーブル名 
    TO ユーザー名@ホスト名 [,ユーザー名@ホスト名 …]
    [IDENTIFIED BY [パスワード]]
    [WITH [GRANT_OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]
全データベースに何でもできる権限を与える。

sample

  • googleユーザを作成。
  • googleユーザに対して全ての権限(ALL PRIVILEGES)を付与。
  • 権限の範囲は全てのDB(*.*)。
  • localhostからの接続を許可する。
  • パスワードは'all'として設定。
mysql> GRANT ALL PRIVILEGES ON *.* TO google@localhost IDENTIFIED BY 'all';
mysql> FLUSH PRIVILEGES;
特定のデータベースにのみ特定の権限を与える。

sample

  • yahooユーザを作成。
  • yahooユーザに対して特定の権限を付与(SELECT,INSERT)。
  • 権限の範囲はtestDB内の全てのテーブル(test.*)。
  • 127.0.0.1というIPからの接続を許可する。
  • パスワードは'db'として設定。
mysql> GRANT SELECT,INSERT ON test.* TO yahoo@127.0.0.1 IDENTIFIED BY 'db';
mysql> FLUSH PRIVILEGES;
特定のテーブルにのみ特定の権限を与える。

sample

  • greeユーザを作成。
  • greeユーザに対して特定の権限を付与(INSERT)。
  • 権限の範囲はtestDB内のbenchテーブル(test.bench)。
  • dev-serverというhostからの接続を許可する。
  • パスワードは'table'として設定。
mysql> GRANT SELECT,INSERT ON test.bench TO gree@'dev-server' IDENTIFIED BY 'table';
mysql> FLUSH PRIVILEGES;
特定のカラムにのみ特定の権限を与える。

sample

  • mobagaユーザを作成。
  • mobagaユーザに対して特定の権限を付与(SELECT,INSERT)。
  • 権限の範囲はtestDB内のdataテーブルのnameとmailaddressカラム。
  • localhostからの接続を許可する。
  • パスワードは'column'として設定。
mysql> GRANT SELECT(name,mailaddress),INSERT(name,mailaddress) ON test.data TO mobaga@localhost IDENTIFIED BY 'column';
mysql> FLUSH PRIVILEGES;

どこにデータが格納されるか？

  • GRANTにより設定したデータはどこに格納されるのか？→ mysqlという名前のDBに権限設定を行ったデータが格納される。
  • mysql.user,mysql.db,msyql.tables_priv,mysql.columns_privなどにデータが格納される。それぞれの権限レベルでデータの格納先が異なるので注意が必要。
table名 格納データ
mysql.user 全てのユーザ
mysql.db 特定のDBに対して権限を持つユーザ
mysql.tables_priv 特定のTABLEに対して権限を持つユーザ
mysql.columns_priv 特定のCOLUMNに対して権限を持つユーザ

上の例で指定したgreeユーザのデータを見てみる。greeは特定のTABLEに対してのみ権限を付与している。

mysql> SELECT * FROM mysql.user where user='gree'\G
*************************** 1. row ***************************
                 Host: dev-server
                 User: gree
             Password: *673B711112AA80C2AA1714270F281E4FBFA0D15C
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
          Reload_priv: N
        Shutdown_priv: N
         Process_priv: N
            File_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
         Show_db_priv: N
           Super_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
         Execute_priv: N
      Repl_slave_priv: N
     Repl_client_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
           Event_priv: N
         Trigger_priv: N
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.db where user='gree'\G
Empty set (0.00 sec)

mysql> SELECT * FROM mysql.tables_priv where user='gree'\G
*************************** 1. row ***************************
       Host: dev-server
         Db: test
       User: gree
 Table_name: bench
    Grantor: root@localhost
  Timestamp: 2011-06-08 23:46:51
 Table_priv: Select,Insert
Column_priv: 
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.columns_priv where user='gree'\G
Empty set (0.00 sec)

データの格納されていたのはmysql.userとmysql.tables_privのみ。

権限を剥奪する

構文
REVOKE 取消する権限の種類[(カラムリスト)] [,取消する権限の種類[(カラムリスト) …] 
    ON データベース名.テーブル名 
    FROM ユーザー名@ホスト名 [,ユーザー名@ホスト名 …]
特定のテーブルの権限を剥奪する

上の例で権限を与えたgreeユーザのtest.benchに対する権限を全て剥奪する。

mysql> REVOKE ALL PRIVILEGES ON test.bench FROM gree@'dev-server';
mysql> FLUSH PRIVILEGES;

mysql> SELECT * FROM mysql.user where user='gree'\G
*************************** 1. row ***************************
                 Host: dev-server
                 User: gree
             Password: *673B711112AA80C2AA1714270F281E4FBFA0D15C
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
          Reload_priv: N
        Shutdown_priv: N
         Process_priv: N
            File_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
         Show_db_priv: N
           Super_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
         Execute_priv: N
      Repl_slave_priv: N
     Repl_client_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
           Event_priv: N
         Trigger_priv: N
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.tables_priv where user='gree'\G
Empty set (0.00 sec)

mysql.tables_privからはデータが消えるが、mysql.userにはデータが残る。

リンク

mysql refman grant
http://dev.mysql.com/doc/refman/5.1/ja/grant.html