mysql: 用户权限的操作

一，查看mysql内置的权限有哪些?

SHOW PRIVILEGES;

如图:

二，管理给用户的权限

1，授予权限 :

mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON news.* TO 'laoliu'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

2，查询指定用户有哪些权限？

mysql> show grants for 'laoliu'@'127.0.0.1';
+--------------------------------------------------------------------------+
| Grants for [email protected]                                              |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `laoliu`@`127.0.0.1`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `news`.* TO `laoliu`@`127.0.0.1` |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3，取消权限

例：取消删除权限

mysql> REVOKE DELETE ON news.* FROM `laoliu`@`127.0.0.1`;
Query OK, 0 rows affected (0.02 sec)

查看效果:

mysql> show grants for 'laoliu'@'127.0.0.1';
+------------------------------------------------------------------+
| Grants for [email protected]                                      |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `laoliu`@`127.0.0.1`                       |
| GRANT SELECT, INSERT, UPDATE ON `news`.* TO `laoliu`@`127.0.0.1` |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

三，其他例子:

1, 授予对指定库中所有表的全部权限

mysql> GRANT all privileges ON news.* TO 'laoliu'@'127.0.0.1';
Query OK, 0 rows affected (0.02 sec)

查看效果:

mysql> show grants for 'laoliu'@'127.0.0.1';
+----------------------------------------------------------+
| Grants for [email protected]                              |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `laoliu`@`127.0.0.1`               |
| GRANT ALL PRIVILEGES ON `news`.* TO `laoliu`@`127.0.0.1` |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

2,取消对指定库中所有表的全部权限

mysql> REVOKE all privileges ON news.* FROM 'laoliu'@'127.0.0.1';
Query OK, 0 rows affected (0.02 sec)

查看效果:

mysql> show grants for 'laoliu'@'127.0.0.1';
+--------------------------------------------+
| Grants for [email protected]                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `laoliu`@`127.0.0.1` |
+--------------------------------------------+
1 row in set (0.00 sec)