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にはデータが残る。