根据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>