MySQL权限篇之DELETE及SELECT

DELETE权限，该权限仅仅能删除表中的记录。

但是要注意如果仅仅只授予了DELETE权限。

比如：

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


mysql> grant delete on test.t1 to 'ut01'@'%';  #在test.t1表上有delete权限

Query OK, 0 rows affected (0.03 sec)


mysql> use test
Database changed
mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

那么请问ut01@%用户执行 delete from  t1 where id=3; 能够成功吗？

答案是不会的。因为该用户没有id列上的select权限，所以会由于where子句而失败。

下面来看：

C:\Users\Administrator>mysql -uut01
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.11-log MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)


mysql> desc t1;
Empty set (0.00 sec)


mysql> select * from t1;
ERROR 1142 (42000): SELECT command denied to user 'ut01'@'localhost' for table 't1'
mysql> delete from t1 where t1.id=3;
ERROR 1143 (42000): SELECT command denied to user 'ut01'@'localhost' for column 'id' in table 't1'
mysql> delete from t1;
Query OK, 3 rows affected (0.09 sec)


mysql> delete from t1 where 1=1;
Query OK, 0 rows affected (0.05 sec)


mysql>

不加where条件的delete可以删除成功。

SELECT权限，很好理解。就是可以对某个库的某个表的某个列（或者所有列）的查询权限。

不演示。但是下面的SELECT查询并不需要SELECT权限：

SELECT 1+1;
SELECT PI()*2;