テーブルの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 の使用を有効にします。レベル: グローバル、データベース、テーブル。 ・・・ (略)
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
これ、全部やるのかな、俺。。。
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 インデックスの作成または削除を有効にします。レベル: グローバル、データベース、テーブル。 ・・・ (略)
さらに、ここでもまさかの、「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ファイルは消えてしまっています。
長くなったので、パーティション操作関係のコマンド検証をまるっと次のエントリへ。
- 作者: 鈴木啓修,山田奈緒子
- 出版社/メーカー: 技術評論社
- 発売日: 2014/12/18
- メディア: 単行本(ソフトカバー)
- この商品を含むブログ (3件) を見る