MySQLユーザーとinformation_schemaデータベースについて

information_schemaデータベースについてリファレンスマニュアルの抜粋。

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

INFORMATION_SCHEMAは、データベース・テーブルの名前、カラムのデータ型やアクセス権限といったMySQLサーバーの情報を持ったデータベースのメタデータへのアクセスを提供します。この情報は時々「データディクショナリ」や「システムカタログ」といった別の用語が使用されます。

引用元: MySQL 5.6 Reference Manual :: 21 INFORMATION_SCHEMA Tables

ユーザーの権限によってinformation_shemaの見え方がどのようになるのか気になったので、試してみました。

環境:MySQL 5.6.16

検証用環境準備

検証用環境は以下の様にしました。

-- db01データベースとtable01・table02を作成
CREATE DATABASE `db01` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
CREATE TABLE `db01`.`table01` ( `c1` INT AUTO_INCREMENT , `c2` VARCHAR(25) , PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;
CREATE TABLE `db01`.`table02` ( `c1` INT AUTO_INCREMENT , `c2` VARCHAR(25) , PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

-- db02データベースとtable03・table04を作成
CREATE DATABASE `db02` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
CREATE TABLE `db02`.`table03` ( `c1` INT AUTO_INCREMENT , `c2` VARCHAR(25) , PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;
CREATE TABLE `db02`.`table04` ( `c1` INT AUTO_INCREMENT , `c2` VARCHAR(25) , PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

-- すべてのデータベースにアクセス出来るユーザー"user01"を作成
GRANT ALL PRIVILEGES ON *.* TO 'user01'@'localhost' ;

-- データベース"db01"のみにアクセス出来るユーザー"user02"を作成
GRANT ALL PRIVILEGES ON db01.* TO 'user02'@'localhost' ;

-- データベース"db01"のテーブル"table01"のみにアクセス出来るユーザー"user03"を作成
GRANT ALL PRIVILEGES ON db01.table01 TO 'user03'@'localhost' ;

-- データベース"db01"のテーブル"table01"のみSELECTが使えるユーザー"user04"を作成
GRANT SELECT ON db01.table01 TO 'user04'@'localhost' ;

-- データベース"db02"のテーブル"table03"のカラム"c1"のみSELECTが使えるユーザー"user05"を作成
GRANT SELECT (c1) ON db02.table03 TO 'user05'@'localhost' ;

イメージとしてはこんな感じ。

Database

SCHEMATAテーブル

SCHEMATAテーブルはデータベースに関する情報を提供します。各ユーザーでログインした各セッションからは以下のように見えます。

mysql> SELECT *FROM `information_schema`.`SCHEMATA`;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | db01               | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
| def          | db02               | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
| def          | mysql              | utf8mb4                    | utf8mb4_general_ci     | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.01 sec)
mysql> SELECT *FROM `information_schema`.`SCHEMATA`;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | db01               | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
2 rows in set (0.00 sec)
mysql> SELECT *FROM `information_schema`.`SCHEMATA`;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | db01               | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
2 rows in set (0.00 sec)
mysql> SELECT *FROM `information_schema`.`SCHEMATA`;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | db01               | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
2 rows in set (0.00 sec)
mysql> SELECT *FROM `information_schema`.`SCHEMATA`;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | db02               | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
2 rows in set (0.01 sec)

各ユーザーが参照出来るデータベースの情報が取得出来ました。

データベースに関する権限の情報はSCHEMA_PRIVILEGESテーブルで知ることが出来ます。

SCHEMA_PRIVILEGESテーブル

こちらも各ユーザーからの見え方を確認してみます。

mysql> SELECT * FROM `information_schema`.`SCHEMA_PRIVILEGES` WHERE `TABLE_SCHEMA` LIKE 'db%';
+----------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+----------------------+---------------+--------------+-------------------------+--------------+
| 'user02'@'localhost' | def           | db01         | SELECT                  | NO           |
| 'user02'@'localhost' | def           | db01         | INSERT                  | NO           |
| 'user02'@'localhost' | def           | db01         | UPDATE                  | NO           |
| 'user02'@'localhost' | def           | db01         | DELETE                  | NO           |
| 'user02'@'localhost' | def           | db01         | CREATE                  | NO           |
| 'user02'@'localhost' | def           | db01         | DROP                    | NO           |
| 'user02'@'localhost' | def           | db01         | REFERENCES              | NO           |
| 'user02'@'localhost' | def           | db01         | INDEX                   | NO           |
| 'user02'@'localhost' | def           | db01         | ALTER                   | NO           |
| 'user02'@'localhost' | def           | db01         | CREATE TEMPORARY TABLES | NO           |
| 'user02'@'localhost' | def           | db01         | LOCK TABLES             | NO           |
| 'user02'@'localhost' | def           | db01         | EXECUTE                 | NO           |
| 'user02'@'localhost' | def           | db01         | CREATE VIEW             | NO           |
| 'user02'@'localhost' | def           | db01         | SHOW VIEW               | NO           |
| 'user02'@'localhost' | def           | db01         | CREATE ROUTINE          | NO           |
| 'user02'@'localhost' | def           | db01         | ALTER ROUTINE           | NO           |
| 'user02'@'localhost' | def           | db01         | EVENT                   | NO           |
| 'user02'@'localhost' | def           | db01         | TRIGGER                 | NO           |
+----------------------+---------------+--------------+-------------------------+--------------+
18 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`SCHEMA_PRIVILEGES` WHERE `TABLE_SCHEMA` LIKE 'db%';
+----------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+----------------------+---------------+--------------+-------------------------+--------------+
| 'user02'@'localhost' | def           | db01         | SELECT                  | NO           |
| 'user02'@'localhost' | def           | db01         | INSERT                  | NO           |
| 'user02'@'localhost' | def           | db01         | UPDATE                  | NO           |
| 'user02'@'localhost' | def           | db01         | DELETE                  | NO           |
| 'user02'@'localhost' | def           | db01         | CREATE                  | NO           |
| 'user02'@'localhost' | def           | db01         | DROP                    | NO           |
| 'user02'@'localhost' | def           | db01         | REFERENCES              | NO           |
| 'user02'@'localhost' | def           | db01         | INDEX                   | NO           |
| 'user02'@'localhost' | def           | db01         | ALTER                   | NO           |
| 'user02'@'localhost' | def           | db01         | CREATE TEMPORARY TABLES | NO           |
| 'user02'@'localhost' | def           | db01         | LOCK TABLES             | NO           |
| 'user02'@'localhost' | def           | db01         | EXECUTE                 | NO           |
| 'user02'@'localhost' | def           | db01         | CREATE VIEW             | NO           |
| 'user02'@'localhost' | def           | db01         | SHOW VIEW               | NO           |
| 'user02'@'localhost' | def           | db01         | CREATE ROUTINE          | NO           |
| 'user02'@'localhost' | def           | db01         | ALTER ROUTINE           | NO           |
| 'user02'@'localhost' | def           | db01         | EVENT                   | NO           |
| 'user02'@'localhost' | def           | db01         | TRIGGER                 | NO           |
+----------------------+---------------+--------------+-------------------------+--------------+
18 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`SCHEMA_PRIVILEGES` WHERE `TABLE_SCHEMA` LIKE 'db%';
Empty set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`SCHEMA_PRIVILEGES` WHERE `TABLE_SCHEMA` LIKE 'db%';
Empty set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`SCHEMA_PRIVILEGES` WHERE `TABLE_SCHEMA` LIKE 'db%';
Empty set (0.00 sec)

※ 行数が多かったのでWHERE句をつけて絞り込んでいます。

user01・user02からはdb01を操作できる権限が確認できました。user03・user04からは権限が無いので、確認できませんでした。

user01はすべてのデータベースに対しての権限を付与していますが、SCHEMA_PRIVILEGESからは権限情報が確認できませんでした。これは権限のスコープがグローバルなのでUSER_PRIVILEGESに情報があるためです。

USER_PRIVILEGESテーブル

mysql> SELECT * FROM `information_schema`.`USER_PRIVILEGES`;
+----------------------------------------+---------------+-------------------------+--------------+
| GRANTEE                                | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+----------------------------------------+---------------+-------------------------+--------------+
-- 中略 --
| 'user01'@'localhost'                   | def           | SELECT                  | NO           |
| 'user01'@'localhost'                   | def           | INSERT                  | NO           |
| 'user01'@'localhost'                   | def           | UPDATE                  | NO           |
| 'user01'@'localhost'                   | def           | DELETE                  | NO           |
| 'user01'@'localhost'                   | def           | CREATE                  | NO           |
| 'user01'@'localhost'                   | def           | DROP                    | NO           |
| 'user01'@'localhost'                   | def           | RELOAD                  | NO           |
| 'user01'@'localhost'                   | def           | SHUTDOWN                | NO           |
| 'user01'@'localhost'                   | def           | PROCESS                 | NO           |
| 'user01'@'localhost'                   | def           | FILE                    | NO           |
| 'user01'@'localhost'                   | def           | REFERENCES              | NO           |
| 'user01'@'localhost'                   | def           | INDEX                   | NO           |
| 'user01'@'localhost'                   | def           | ALTER                   | NO           |
| 'user01'@'localhost'                   | def           | SHOW DATABASES          | NO           |
| 'user01'@'localhost'                   | def           | SUPER                   | NO           |
| 'user01'@'localhost'                   | def           | CREATE TEMPORARY TABLES | NO           |
| 'user01'@'localhost'                   | def           | LOCK TABLES             | NO           |
| 'user01'@'localhost'                   | def           | EXECUTE                 | NO           |
| 'user01'@'localhost'                   | def           | REPLICATION SLAVE       | NO           |
| 'user01'@'localhost'                   | def           | REPLICATION CLIENT      | NO           |
| 'user01'@'localhost'                   | def           | CREATE VIEW             | NO           |
| 'user01'@'localhost'                   | def           | SHOW VIEW               | NO           |
| 'user01'@'localhost'                   | def           | CREATE ROUTINE          | NO           |
| 'user01'@'localhost'                   | def           | ALTER ROUTINE           | NO           |
| 'user01'@'localhost'                   | def           | CREATE USER             | NO           |
| 'user01'@'localhost'                   | def           | EVENT                   | NO           |
| 'user01'@'localhost'                   | def           | TRIGGER                 | NO           |
| 'user01'@'localhost'                   | def           | CREATE TABLESPACE       | NO           |
| 'user02'@'localhost'                   | def           | USAGE                   | NO           |
| 'user04'@'localhost'                   | def           | USAGE                   | NO           |
| 'user03'@'localhost'                   | def           | USAGE                   | NO           |
| 'user05'@'localhost'                   | def           | USAGE                   | NO           |
+----------------------------------------+---------------+-------------------------+--------------+
mysql> SELECT * FROM `information_schema`.`USER_PRIVILEGES`;
+----------------------+---------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+----------------+--------------+
| 'user02'@'localhost' | def           | USAGE          | NO           |
+----------------------+---------------+----------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `information_schema`.`USER_PRIVILEGES`;
+----------------------+---------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+----------------+--------------+
| 'user03'@'localhost' | def           | USAGE          | NO           |
+----------------------+---------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`USER_PRIVILEGES`;
+----------------------+---------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+----------------+--------------+
| 'user04'@'localhost' | def           | USAGE          | NO           |
+----------------------+---------------+----------------+--------------+
1 row in set (0.01 sec)
mysql> SELECT * FROM `information_schema`.`USER_PRIVILEGES`;
+----------------------+---------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+----------------+--------------+
| 'user05'@'localhost' | def           | USAGE          | NO           |
+----------------------+---------------+----------------+--------------+
1 row in set (0.00 sec)

user01以外はグローバルの権限がUSAGEとなっています。USAGEは権限が何も無いということです。

SCHEMA_PRIVILEGESテーブルはmysqlデータベースのdbテーブルから、USER_PRIVILEGESテーブルはmysqlデータベースのuserテーブルから権限を読み込んでいます。

TABLESテーブル

TABLESテーブルはデータベースのテーブルに関する情報を提供します。

mysql> SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db01
     TABLE_NAME: table01
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:39:56
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*************************** 2. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db01
     TABLE_NAME: table02
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:40:07
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*************************** 3. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db02
     TABLE_NAME: table03
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:40:29
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*************************** 4. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db02
     TABLE_NAME: table04
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:40:42
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
4 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db01
     TABLE_NAME: table01
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:39:56
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*************************** 2. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db01
     TABLE_NAME: table02
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:40:07
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
2 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db01
     TABLE_NAME: table01
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:39:56
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db01
     TABLE_NAME: table01
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:39:56
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: db02
     TABLE_NAME: table03
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-08-27 15:40:29
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.01 sec)

※ こちらも結果が大きくなったのでWHERE句で絞り込んだのと、カラム数が多いのでメタコマンド「\G」で結果を縦に表示しています。

ユーザーが扱えるテーブルの情報が取得出来ました。こちらもテーブルの権限を参照するにはTABLE_PRIVILEGESを使用します。

TABLE_PRIVILEGESテーブル

mysql> SELECT * FROM `information_schema`.`TABLE_PRIVILEGES`;
+----------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+--------------+------------+----------------+--------------+
| 'user04'@'localhost' | def           | db01         | table01    | SELECT         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | SELECT         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | INSERT         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | UPDATE         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | DELETE         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | CREATE         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | DROP           | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | REFERENCES     | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | INDEX          | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | ALTER          | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | CREATE VIEW    | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | SHOW VIEW      | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | TRIGGER        | NO           |
+----------------------+---------------+--------------+------------+----------------+--------------+
13 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`TABLE_PRIVILEGES`;
Empty set (0.01 sec)
mysql> SELECT * FROM `information_schema`.`TABLE_PRIVILEGES`;
+----------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+--------------+------------+----------------+--------------+
| 'user03'@'localhost' | def           | db01         | table01    | SELECT         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | INSERT         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | UPDATE         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | DELETE         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | CREATE         | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | DROP           | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | REFERENCES     | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | INDEX          | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | ALTER          | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | CREATE VIEW    | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | SHOW VIEW      | NO           |
| 'user03'@'localhost' | def           | db01         | table01    | TRIGGER        | NO           |
+----------------------+---------------+--------------+------------+----------------+--------------+
12 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`TABLE_PRIVILEGES`;
+----------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+--------------+------------+----------------+--------------+
| 'user04'@'localhost' | def           | db01         | table01    | SELECT         | NO           |
+----------------------+---------------+--------------+------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`TABLE_PRIVILEGES`;
Empty set (0.00 sec)

TABLE_PRIVILEGESテーブルもmysqlデータベースのtables_privテーブルから権限情報を読み込んだものになります。

COLUMNSテーブル

COLUMNSテーブルはカラムの情報が取得できます。

mysql> SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table01
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table01
             COLUMN_NAME: c2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
  CHARACTER_OCTET_LENGTH: 100
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(25)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 3. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table02
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 4. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table02
             COLUMN_NAME: c2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
  CHARACTER_OCTET_LENGTH: 100
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(25)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 5. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db02
              TABLE_NAME: table03
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 6. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db02
              TABLE_NAME: table03
             COLUMN_NAME: c2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
  CHARACTER_OCTET_LENGTH: 100
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(25)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 7. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db02
              TABLE_NAME: table04
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 8. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db02
              TABLE_NAME: table04
             COLUMN_NAME: c2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
  CHARACTER_OCTET_LENGTH: 100
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(25)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
8 rows in set (0.01 sec)
mysql> SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table01
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table01
             COLUMN_NAME: c2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
  CHARACTER_OCTET_LENGTH: 100
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(25)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 3. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table02
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 4. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table02
             COLUMN_NAME: c2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
  CHARACTER_OCTET_LENGTH: 100
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(25)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
4 rows in set (0.01 sec)
mysql> SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table01
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table01
             COLUMN_NAME: c2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
  CHARACTER_OCTET_LENGTH: 100
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(25)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
2 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table01
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select
          COLUMN_COMMENT: 
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db01
              TABLE_NAME: table01
             COLUMN_NAME: c2
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
  CHARACTER_OCTET_LENGTH: 100
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(25)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select
          COLUMN_COMMENT: 
2 rows in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` LIKE 'db%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db02
              TABLE_NAME: table03
             COLUMN_NAME: c1
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select
          COLUMN_COMMENT: 
1 row in set (0.01 sec)

※ こちらも結果が大きくなったのでWHERE句で絞りk(ry

COLUMNSテーブルは権限の情報も含まれます。そしてしっかりとこちらも例にもれずCOLUMN_PRIVILEGESテーブルがあり、権限の情報を持っています。

COLUMN_PRIVILEGESテーブル

mysql> SELECT * FROM `information_schema`.`COLUMN_PRIVILEGES`;
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'user05'@'localhost' | def           | db02         | table03    | c1          | SELECT         | NO           |
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`COLUMN_PRIVILEGES`;
Empty set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`COLUMN_PRIVILEGES`;
Empty set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`COLUMN_PRIVILEGES`;
Empty set (0.00 sec)
mysql> SELECT * FROM `information_schema`.`COLUMN_PRIVILEGES`;
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'user05'@'localhost' | def           | db02         | table03    | c1          | SELECT         | NO           |
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
1 row in set (0.04 sec)

他の権限テーブル同様こちらもmysqlデータベースのcolumns_privテーブルから読み込まれています。

コメントを残す