MySQL权限篇之ALTER

根据mysql文档定义：

ALTER权限是：To alter the table。就是仅仅有更改表结构的权限。

mysql> grant alter on test.t1 to 'ut01'@'%';
Query OK, 0 rows affected (0.06 sec)


mysql> show grants for 'ut01'@'%';
+------------------------------------------+
| Grants for ut01@%                        |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'ut01'@'%'         |
| GRANT ALTER ON `test`.`t1` TO 'ut01'@'%' |
+------------------------------------------+
2 rows in set (0.00 sec)


mysql> 

此时，'ut01'@'%'用户只有usage和在test.t1表的alter权限。

根据定义，该用户对于test.t1表只能alter表结构操作。

而不能进行select和dml操作，也不能执行alter table rename 操作。

下面验证：

mysql> select user();
+----------------+
| user()         |
+----------------+
| ut01@localhost |
+----------------+
1 row in set (0.00 sec)


mysql> use mysql
ERROR 1044 (42000): Access denied for user 'ut01'@'%' to database 'mysql'  #看不到其他库
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+ 
| t1             |   #看不到其他表
+----------------+
1 row in set (0.00 sec)


mysql> select * from t1;
ERROR 1142 (42000): SELECT command denied to user 'ut01'@'localhost' for table 't1'  #不能查询，很奇葩
mysql> desc t1;
Empty set (0.00 sec)


mysql> insert into t1(a,b,h) values(4,5,'pp');
ERROR 1142 (42000): INSERT command denied to user 'ut01'@'localhost' for table 't1'  #也不能插入
mysql> ALTER TABLE `test`.`t1` ADD COLUMN `g` BIT NULL AFTER `h`;
Query OK, 0 rows affected (0.81 sec)
Records: 0  Duplicates: 0  Warnings: 0
 #但是可以修改表结构，很奇葩

mysql> ALTER TABLE `test`.`t1` RENAME TO test.t2;
ERROR 1142 (42000): DROP command denied to user 'ut01'@'localhost' for table 't1' #重命名表失败
mysql> RENAME TABLE `test`.`t1` TO test.t2;
ERROR 1142 (42000): DROP command denied to user 'ut01'@'localhost' for table 't1' #这种方式重命名表也失败
mysql>

mysql> ALTER TABLE `test`.`t1` ADD INDEX (`category`);
Query OK, 0 rows affected (0.84 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE `test`.`t1`
    ->   DROP INDEX `category`;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

可以使用alter方式add和drop index，但是直接create index是不行的，当然drop index也不行。
mysql> CREATE INDEX idx_t1_category ON `test`.`t1`(`category`);
ERROR 1142 (42000): INDEX command denied to user 'ut01'@'localhost' for table 't1'
mysql>