読者です 読者をやめる 読者になる 読者になる

なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLの「ALTER TABLE ADD INDEX」と「CREATE INDEX」

MySQL

ALTER権限とALTER TABLEの検証作業の中で。。。

atsuizo.hatenadiary.jp
ここで、ALTERしか付与してないのに、INDEXのADD/DROPが成功してたことが、どうにも引っかかってまして。


公式ドキュメント上は。。。

表 13.1 GRANT および REVOKE に対して許容可能な権限

権限 意味と付与可能なレベル
(略)
INDEX インデックスの作成または削除を有効にします。レベル: グローバル、データベース、テーブル。
(略)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.1.4 GRANT 構文

CREATE INDEX は、インデックスを作成するために ALTER TABLE ステートメントにマップされます。セクション13.1.7「ALTER TABLE 構文」を参照してください。CREATE INDEX を使用して PRIMARY KEY を作成することはできません。代わりに ALTER TABLE を使用します。インデックスの詳細は、セクション8.3.1「MySQL のインデックスの使用の仕組み」を参照してください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.13 CREATE INDEX 構文

とまあ、こんな感じです。



CREATE INDEXを試してみる。

前回同様、ALTERしか権限を持っていないユーザで接続し、CREATE INDEXを実行してみましょう。
引き続きMySQLは5.6.31を使用しています。

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| alttest@%      |
+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for alttest@%                                                |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alttest'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALTER ON `alttest_db`.* TO 'alttest'@'%'                      |
+---------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> CREATE INDEX alttest_idx ON alttest_tbl (col_2);
ERROR 1142 (42000): INDEX command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> ALTER TABLE alttest_tbl ADD INDEX idx_col2 (col_2);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`),
  KEY `idx_col2` (`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

えっ!?


CREATE INDEXコマンドは、権限不足で拒否られました。
ALTER TABLE ADD INDEXは、前回同様、さっくり通りました。


なんだこれ?INDEX権限が独立している意味が皆無www



同様に、

mysql> DROP INDEX alttest_idx ON alttest_tbl;
ERROR 1142 (42000): INDEX command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> ALTER TABLE alttest_tbl DROP INDEX idx_col2;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)


DROP INDEXコマンドは、権限不足で拒否られました。
ALTER TABLE DROP INDEXは、前回同様、さっくり通りました。



うーん、この。


逆に、INDEX権限しか持っていなかったらどうなるんだろか。気になります。


気になったら、やってみる。INDEX権限しかないユーザの場合。

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| idxtest@%      |
+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for idxtest@%                                                |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'idxtest'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT INDEX ON `alttest_db`.* TO 'idxtest'@'%'                      |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> CREATE INDEX alttest_idx ON alttest_tbl (col_2);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`),
  KEY `alttest_idx` (`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> DROP INDEX alttest_idx ON alttest_tbl;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl ADD INDEX idx_col2 (col_2);
ERROR 1142 (42000): ALTER command denied to user 'idxtest'@'localhost' for table 'alttest_tbl'

mysql> show index from alttest_tbl;
ERROR 1142 (42000): SELECT command denied to user 'idxtest'@'localhost' for table 'alttest_tbl'


予想通り、って言えば予想通りな結果です。
INDEX権限のみの場合、「CREATE/DROP INDEX」はできるけど、「ALTER TABLE ADD/DROP INDEX」はできませんでした。


おまけでSHOW INDEXコマンドも実験しましたが、これは、インデックスの付与先テーブルに対するSELECT権限がないと、やっぱり拒否されました。

まとめ

  • 「ALTER TABLE ADD INDEX」と「CREATE INDEX」は、それぞれ「ALTER権限」「INDEX権限」に対応している。
  • INDEX権限がなくても、ALTER権限があれば、ALTER TABLE からインデックスの操作は可能。
  • ALTER権限がなくても、INDEX権限があれば、CREATE/DROP INDEXは可能
  • CREATE INDEXは、プライマリキーの操作には使えない。

INDEX権限がなくてもALTER権限があればインデックス操作はできるため、INDEX操作を保護する目的でINDEX権限の付与剥奪を利用することはできませんでした。


INDEX権限だけを付与するケースって、ALTERでは権限過剰なケース、「テーブルの定義は絶対にいじらせたくないけど、インデックスのメンテナンスはさせたい」みたいなケースでしょうか。


そんなニーズがどれほどあるのか、よくわかりませんが。

MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル