[MySQL] mysql.userテーブル

ユーザーの確認を行う時に打つコマンド

mysql> select user,host from mysql.user;

(「よく使うコマンドまとめ」)

これって、普通に見ると、
“mysql”データベースの、”user”テーブルから、
“user”と”host”のカラムを抜き出して表示する。
っていうコマンドですよね。

なので、ユーザー情報については、
この”user”テーブルに保存されているのでしょう。
と思って見てみました。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              | <- これ
| performance_schema |
| phptest            |
(以下略)
+--------------------+

mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
(中略)
| time_zone_transition_type |
| user                      | <- これ
+---------------------------+

この"user"テーブル自体は、
ユーザーアカウント、グローバル権限等の
データが格納されているもの。

参考:
https://dev.mysql.com/doc/refman/5.6/ja/grant-tables.html

で、この"user"テーブルがどうなっているかというと、

mysql> use mysql
mysql> show columns from user;

って打つと、
コマンドプロンプト上で表示がすごいことになるので、
少しずつ区切って見ていきます。
(\Gでも、全部表示しきれないw)

カラムは全部で45列。

・User、Host(1,2列目)

mysql> show columns from user where field='user' or field='host';
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Host  | char(60) | NO   | PRI |         |       |
| User  | char(32) | NO   | PRI |         |       |
+-------+----------+------+-----+---------+-------+

mysql> select user,host from user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| mysql.sys | localhost |
| phpuser   | localhost | (<- PHPのテスト用に作ったユーザー)
| root      | localhost |
+-----------+-----------+

ユーザー名とホスト名。
この2列で複合プライマリーキー。

[user]@[host]に対するグローバル権限等の設定が
格納されているテーブルなんだね。

"host"に別のホストのIPアドレスとかを入れると、
そのlocalhost以外からアクセスできるようになる。
(「LS-WSXLのWebサーバーを使ってみる」の中段くらい。
 「○MySQLに外部ホストから接続」の項参照。)

とりあえずここで、
root@localhostについてのレコードを、
一覧表示しておきます。

mysql> select * from user where user='root'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
authentication_string: ***** (<- ハッシュだけど一応伏せておく)
      password_expired: N
password_last_changed: 2017-08-15 23:43:35
     password_lifetime: NULL
        account_locked: N

・*_priv(3~31列目)

操作に対する、権限のYes or No。

mysql> show columns from user like "%priv";
+------------------------+---------------+------+
| Field                  | Type          | Null |
+------------------------+---------------+------+
| Select_priv            | enum('N','Y') | NO   |
| Insert_priv            | enum('N','Y') | NO   |
| Update_priv            | enum('N','Y') | NO   |
| Delete_priv            | enum('N','Y') | NO   |
| Create_priv            | enum('N','Y') | NO   |
| Drop_priv              | enum('N','Y') | NO   |
| Reload_priv            | enum('N','Y') | NO   |
| Shutdown_priv          | enum('N','Y') | NO   |
| Process_priv           | enum('N','Y') | NO   |
| File_priv              | enum('N','Y') | NO   |
| Grant_priv             | enum('N','Y') | NO   |
| References_priv        | enum('N','Y') | NO   |
| Index_priv             | enum('N','Y') | NO   |
| Alter_priv             | enum('N','Y') | NO   |
| Show_db_priv           | enum('N','Y') | NO   |
| Super_priv             | enum('N','Y') | NO   |
| Create_tmp_table_priv  | enum('N','Y') | NO   |
| Lock_tables_priv       | enum('N','Y') | NO   |
| Execute_priv           | enum('N','Y') | NO   |
| Repl_slave_priv        | enum('N','Y') | NO   |
| Repl_client_priv       | enum('N','Y') | NO   |
| Create_view_priv       | enum('N','Y') | NO   |
| Show_view_priv         | enum('N','Y') | NO   |
| Create_routine_priv    | enum('N','Y') | NO   |
| Alter_routine_priv     | enum('N','Y') | NO   |
| Create_user_priv       | enum('N','Y') | NO   |
| Event_priv             | enum('N','Y') | NO   |
| Trigger_priv           | enum('N','Y') | NO   |
| Create_tablespace_priv | enum('N','Y') | NO   |
+------------------------+---------------+------+

key以下、いずれのカラムも共通
(ページの幅の関係でこう書くことにします。)
    Key:
Default: N
  Extra:

29列あります。
Selectとか、Insertとかはわかるけど、
結構わからないコマンドもあるんだなぁ。

mysql> select user,host,select_priv from user;
+-----------+-----------+-------------+
| user      | host      | select_priv |
+-----------+-----------+-------------+
| root      | localhost | Y           |
| mysql.sys | localhost | N           |
| phpuser   | localhost | N           |
+-----------+-----------+-------------+

rootはselectのグローバル権限を持っているが、
それ以外は持っていない。

・ssl_*, x509_*(32~35列目)

mysql> show columns from user like "ssl_%";
+------------+-----------------------------------+
| Field      | Type                              |
+------------+-----------------------------------+
| ssl_type   | enum('','ANY','X509','SPECIFIED') |
| ssl_cipher | blob                              |
+------------+-----------------------------------+

Null以下共通
   Null: NO
    Key:
Default: NULL
  Extra:

mysql> show columns from user like 'x509_%';
+--------------+------+------+-----+---------+-------+
| Field        | Type | Null | Key | Default | Extra |
+--------------+------+------+-----+---------+-------+
| x509_issuer  | blob | NO   |     | NULL    |       |
| x509_subject | blob | NO   |     | NULL    |       |
+--------------+------+------+-----+---------+-------+

多分、SSL通信とか、x.509証明書に関する設定なのかな。
詳しいことはわかりません。

BLOB型は、
https://www.dbonline.jp/mysql/type/index6.html
を参照。

・max_*(36~39列目)

mysql> show columns from user like 'max_%';
+----------------------+------------------+------+
| Field                | Type             | Null |
+----------------------+------------------+------+
| max_questions        | int(11) unsigned | NO   |
| max_updates          | int(11) unsigned | NO   |
| max_connections      | int(11) unsigned | NO   |
| max_user_connections | int(11) unsigned | NO   |
+----------------------+------------------+------+

key以下共通
    Key:
Default: 0
  Extra:

参考:
https://dev.mysql.com/doc/refman/5.6/ja/user-resources.html

上から、
・1時間ごとに発行できるクエリ数
・1時間ごとに発行できる更新回数
・1時間ごとに発行できる接続回数
・同時接続できる数

値が"0"だと無制限。

試しに、設定してみる。

mysql> grant usage on *.* to root@localhost
    -> with max_user_connections 1;
mysql> exit
>mysql -u root -p
Enter password:

で"max_user_connections"を"1"に設定し、
接続しなおして、
コマンドプロンプトをもう一個立ち上げて、
mysqlにrootでログインしてみる。

>mysql -u root -p
Enter password: **********
ERROR 1226 (42000): User 'root' has exceeded the 'max_user_connections' resource
(current value: 1)

ちゃんとエラーを吐いて拒否られました。

ちなみに、"max_user_connections"を"0"に戻すと、
いくらでもrootでログインできるw

・それ以降(40~45列目)

mysql> show columns from user like 'plugin';
+--------+----------+------+-----------------------+
| Field  | Type     | Null | Default               |
+--------+----------+------+-----------------------+
| plugin | char(64) | NO   | mysql_native_password |
+--------+----------+------+-----------------------+

※key,Extraは、空欄のため省略。

プラグイン・・・なんでしょうね。
とりあえずは、"mysql_native_password"が適用されているようです。

参考:
https://dev.mysql.com/doc/refman/5.6/ja/server-plugin-loading.html
https://dev.mysql.com/doc/refman/5.6/ja/native-authentication-plugin.html

mysql> show columns from user like 'auth%';
+-----------------------+------+------+---------+
| Field                 | Type | Null | Default |
+-----------------------+------+------+---------+
| authentication_string | text | YES  | NULL    |
+-----------------------+------+------+---------+

※key,Extraは、空欄。

パスワードのハッシュが保存されている。

mysql> show columns from user like 'password%';
+-----------------------+----------------------+------+---------+
| Field                 | Type                 | Null | Default |
+-----------------------+----------------------+------+---------+
| password_expired      | enum('N','Y')        | NO   | N       |
| password_last_changed | timestamp            | YES  | NULL    |
| password_lifetime     | smallint(5) unsigned | YES  | NULL    |
+-----------------------+----------------------+------+---------+

※key,Extraは、いずれも空欄。

・パスワードの期限切れの有効/無効
・パスワードの最終変更日時
・パスワードの有効期限

参考:
https://dev.mysql.com/doc/refman/5.6/ja/password-expiration-sandbox-mode.html

mysql> show columns from user like 'account%';
+----------------+---------------+------+---------+
| Field          | Type          | Null | Default |
+----------------+---------------+------+---------+
| account_locked | enum('N','Y') | NO   | N       |
+----------------+---------------+------+---------+

※key,Extraは、空欄。

○その他のテーブル

その他、レベルごとの権限が格納されているテーブルたち。

参考:
https://www.dbonline.jp/mysql/user/index5.html

●db
データベースレベルの権限が格納されているテーブル。
Host,Db,Userが複合プライマリーキーで、全22列。
"user"テーブルと比べて、
・Reload
・Shutdown
・Process
・File
・Show_db
・Super
・Repl_slave
・Repl_client
・Create_user
・Create_tablespace
の10個がない。

●tables_priv
テーブルレベルの権限が格納されているテーブル。
Host,Db,User,Table_nameが複合プライマリーキーで、全8列。
権限の関係は、"Table_priv","Column_priv"カラムに集約されてる感じ。

mysql> show columns from tables_priv like '%_priv'\G
*************************** 1. row ***************************
  Field: Table_priv
   Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','Refere
nces','Index','Alter','Create View','Show view','Trigger')
   Null: NO
    Key:
Default:
  Extra:
*************************** 2. row ***************************
  Field: Column_priv
   Type: set('Select','Insert','Update','References')
   Null: NO
    Key:
Default:
  Extra:

それ以外は、Genelator,Timestampとか。

●columns_priv
カラムレベルの権限が格納されているテーブル。
Host,Db,User,Table_name,Column_nameが
複合プライマリーキーで、全7列。
権限の関係は、"Column_priv"カラムに集約されてる感じ。

mysql> show columns from columns_priv like 'column_priv'\G
*************************** 1. row ***************************
  Field: Column_priv
   Type: set('Select','Insert','Update','References')
   Null: NO
    Key:
Default:
  Extra:

●procs_priv
ルーチンレベルの権限が格納されているテーブル。
Host,Db,User,Routine_name,Routine_typeが
複合プライマリーキーで、全8列。

mysql> show columns from procs_priv like 'proc_priv'\G
*************************** 1. row ***************************
  Field: Proc_priv
   Type: set('Execute','Alter Routine','Grant')
   Null: NO
    Key:
Default:
  Extra:

コメントを残す

メールアドレスが公開されることはありません。