なからなLife

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

MySQLで、「ALTER権限だけ」でできることは何か?(1/2)

テーブルのRENAMEができない!と言われたことが発端で。。。

ALTERは与えてあるけど、ALTER TABLEの中には、他の権限を持ってないとできないことも混在してまして、このRENAMEも、裏でDROP&CREATEしているらしく、ALTERだけではできない操作の1つでした。

ドキュメント上の記述

こんなかんじ。

MySQL によってサポートされる権限

次の表は、GRANT および REVOKE ステートメントに対して指定できる許可される priv_type 権限タイプと、各権限を付与できるレベルについて要約したものです。これらの権限の詳細は、セクション6.2.1「MySQL で提供される権限」を参照してください。

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

権限 意味と付与可能なレベル
ALL [PRIVILEGES] GRANT OPTION を除き、指定されたアクセスレベルにあるすべての権限を付与します
ALTER ALTER TABLE の使用を有効にします。レベル: グローバル、データベース、テーブル。
・・・ (略)

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

ALTER TABLE を使用するには、このテーブルに対する ALTER、CREATE、および INSERT 権限が必要です。テーブルを名前変更するには、古いテーブル側で ALTER および DROP と、新しいテーブル側で ALTER、CREATE、および INSERT 権限が必要です。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

やってみた

準備

権限の強いユーザで接続し、以下のようにテスト用DB、ユーザ、テーブル、データを作成。

mysql> create user alttest identified by 'alttest';
Query OK, 0 rows affected (0.02 sec)

mysql> create database alttest_db;
Query OK, 1 row affected (0.00 sec)

mysql> grant alter on alttest_db.* to 'alttest'@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE TABLE alttest_db.alttest_tbl (
    ->   `col_1` char(10) PRIMARY KEY,
    ->   `col_2` char(10)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.88 sec)

mysql> INSERT INTO alttest_db.alttest_tbl VALUES ('aaaa','bbbb');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO alttest_db.alttest_tbl VALUES ('cccc','dddd');
Query OK, 1 row affected (0.02 sec)

準備はここまで。

ユーザを作った時点で、接続のためのグローバルレベル権限「USAGE」が付き、データベースレベル権限のALTERを付与しているので、SHOW GRANTSをすると2行表示されます。

準備運動

ここからは、準備の中で作った「altuser」で接続して、どのように見えているか確認です。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| alttest_db         |
+--------------------+
2 rows in set (0.00 sec)

mysql> use alttest_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_alttest_db |
+----------------------+
| alttest_tbl          |
+----------------------+
1 row 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)


本題に入る前に、序の口から。

「SHOW DATABASE権限」は与えていないので、GRANT ALTERでアクセス先に指定した「alttest_tbl」と、デフォルトで参照可能な「information_schema」だけが見えているのは想定通り。


次に、DML。SELECT、INSERT、UPDATE、DELETEを試してみます。

mysql> SELECT * FROM alttest_tbl;
ERROR 1142 (42000): SELECT command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> INSERT INTO alttest_tbl VALUES ('eeee','ffff');
ERROR 1142 (42000): INSERT command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> UPDATE alttest_tbl SET col2='zzzz' where col1='aaaa';
ERROR 1142 (42000): UPDATE command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> DELETE FROM alttest_tbl where col1 = 'cccc';
ERROR 1142 (42000): DELETE command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

これらも、権限を与えていないので、正しく拒否しています。


そしてDDL。とりあえず、焦らしで、CREATE TABLEとDROP TABLEを。

mysql> CREATE TABLE alttest_tbl_2 (
    -> `col_1` char(10) PRIMARY KEY,
    -> `col_2` char(10)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
ERROR 1142 (42000): CREATE command denied to user 'alttest'@'localhost' for table 'alttest_tbl_2'

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

これも、想定通り拒否しています。

そしてようやく本丸。「ALTER TABLE」へ。

ALTER TABLEと一言に言っても、色々なことができますので、おさらいしておきましょう。

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| FORCE
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

これ、全部やるのかな、俺。。。

COLUMN(列)

基本的なところ、列の増減からやってみます。

mysql> ALTER TABLE alttest_tbl ADD COLUMN `col_3` char(10);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE alttest_tbl;
ERROR 1142 (42000): SELECT command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

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,
  `col_3` 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 MODIFY COLUMN `col_3` varchar(100);
Query OK, 2 rows affected (0.09 sec)
Records: 2  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,
  `col_3` varchar(100) 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 CHANGE COLUMN `col_3` `col_4` varchar(200);
Query OK, 2 rows affected (0.07 sec)
Records: 2  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,
  `col_4` varchar(200) 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 DROP COLUMN `col_4`;
Query OK, 0 rows affected (0.10 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)

できました。


そして、想定外の、「DESCRIBE」コマンドが権限不足で弾かれるという事態。
テーブル定義は「SHOW CREATE TABLE」でも確認できるので、こちらでどうぞ。


INDEX(インデックス)

利用頻度の高い、インデックスの追加削除。

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

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

mysql> show creat table alttest_tbl;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat table alttest_tbl' at line 1

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)

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)

できました。


あれ?「INDEX」って権限は別に存在していて、付与していないんだけどな。

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

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

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

さらに、ここでもまさかの、「SHOW INDEX」コマンドが権限不足で拒否られた。

ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...

はINDEXの一種ってことで、権限一覧みても独立したそれらしきものが見当たらないので、飛ばします。(単にFULLTEXT用の準備するのが面倒くさい)

CONSTRAINTS(制約)

とりあえずUNIQUE KEYの追加・削除してみます。

mysql> ALTER TABLE alttest_tbl ADD CONSTRAINT UNIQUE UQ_COL2(col_2);
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`),
  UNIQUE KEY `UQ_COL2` (`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl DROP KEY UQ_COL2;
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

全然問題なし。


ADDの時はCONSTRAINTだけど、DROPのときはKEYなんだな。
制約自体を消さずに有効無効のコントロールする「ENABLE KEY/DISABLE KEY」も省略で。
追加削除できるのに、これができないとか想像できない。

RENAME

弾かれたから、今回の調査が始まっているわけで、実験するまでも無いのですが、一応。

mysql> ALTER TABLE alttest_tbl RENAME TO alttest_tbl_rn;
ERROR 1142 (42000): DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

失敗しました。
「DROP command denied」って言ってます。
冒頭で触れたとおり、「DROP」が必要でした。内部的に、新しい名前でテーブルをCREATEし、INSERT SELECTして、古いテーブルをDROPしているってことで。


CONVERT TO CHARACTER SET

MySQLではテーブルごとに指定できるので、それを変更するコマンドも当然のように用意されています。

mysql> ALTER TABLE alttest_tbl CONVERT TO CHARACTER SET latin1;
Query OK, 2 rows affected (0.09 sec)
Records: 2  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) NOT NULL,
  `col_2` char(10) DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 2 rows affected (0.36 sec)
Records: 2  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)


できました。
内部的な入れ替えは無し、保存済データについては放置、ってことでしょうね。


COLLATEも一緒に指定しないと、消えてしまうようです。


DISCARD TABLESPACE・IMPORT TABLESPACE

これ自体、初めてやるのですが、テーブルの実体ファイルの差し替え用のコマンドです。
Oracleでは多少聞き慣れた「トランスポータブル表領域」というヤツ。
なので、innodb_file_per_table=1(ON)のモードのみ有効です。



事前に、MySQLを停止した状態から、実験対象のテーブルのibdファイルをcpコマンドでバックアップしておきます。
でないと、DISCARDしたときに削除されてしまいますので。

mysql> ALTER TABLE alttest_tbl DISCARD TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show tables;
+----------------------+
| Tables_in_alttest_db |
+----------------------+
| alttest_tbl          |
+----------------------+
1 row in set (0.00 sec)

(バックアップしておいたalttest_tbl.ibdファイル)を、本来の場所・名前で配置する)

mysql> ALTER TABLE alttest_tbl IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.09 sec)

できました。SHOW TABLESに表示されていることからも分かる通り、テーブル定義のDROP/CREATEはしないようです。


今回のテストユーザはALTER権限でしか持っていないので、他にSELECT権限を持っているユーザからSELECTを発行すると、以下のようになります。

mysql> select * from alttest_tbl;
ERROR 1814 (HY000): Tablespace has been discarded for table 'alttest_tbl'


こんな感じで、テーブルの定義はあるけど、テーブルスペースが無いよ、って怒られます。
実際にOSレベルで確認してみると、DISCARDが成功すると、frmファイルは残っていますが、ibdファイルは消えてしまっています。


長くなったので、パーティション操作関係のコマンド検証をまるっと次のエントリへ。

MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル