MySQL权限篇之DROP

DROP权限呢是删除对象，拥有该权限的用户可以删除指定范围内的对象（schema、表和视图）。

不能删除除这些对象之外的其他对象。

mysql> show grants for 'ut01'@'%';
+----------------------------------+
| Grants for ut01@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'ut01'@'%' |
+----------------------------------+
1 row in set (0.00 sec)


mysql> grant drop on test1.* to 'ut01'@'%';
Query OK, 0 rows affected (0.06 sec)


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


mysql> use test1;
Database changed
mysql> show tables;
Empty set (0.00 sec)


mysql> create table t1(id int,name char(10));
Query OK, 0 rows affected (0.32 sec)


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


mysql> create view v_t1 as select id,name from t1;
Query OK, 0 rows affected (0.11 sec)


mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t1              |
| v_t1            |
+-----------------+
2 rows in set (0.00 sec)


mysql>

而该用户只能：

mysql> use test1;
Database changed
mysql> show tables;
Empty set (0.00 sec)


mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| 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;
ERROR 1142 (42000): SELECT command denied to user 'ut01'@'localhost' for table 't1'
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t1              |
| v_t1            |
+-----------------+
2 rows in set (0.00 sec)


mysql> drop view v_t1;
Query OK, 0 rows affected (0.08 sec)


mysql> create view v_t1 as select id,name from t1;
ERROR 1142 (42000): CREATE VIEW command denied to user 'ut01'@'localhost' for table 'v_t1'
mysql> create database test2;
ERROR 1044 (42000): Access denied for user 'ut01'@'%' to database 'test2'
mysql>

只能删除表和视图。那么能删除test1这个schema吗？

mysql> drop database test1;
Query OK, 0 rows affected (0.06 sec)

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)


mysql>

可见是可以删除test1这个schema的。