mysql之权限管理

如何创建角色:CREATE ROLE 角色名;
给角色授权的角色：GRANT 权限 ON 表名 TO 角色名;
查看角色权限：SHOW GRANTS FOR 'manager';
删除角色：DROP ROLE 角色名称;
创建用户：CREATE USER 用户名 [IDENTIFIED BY 密码];
给用户授权：GRANT 角色名称 TO 用户名称;或GRANT 权限 ON 表名 TO 用户名;
查看用户权限：SHOW GRANTS FOR 用户名;
删除用户：DROP USER 用户名;
激活角色和用户：SET global activate_all_roles_on_login=ON;

1、创建角色名称：manager，角色可以登录的主机是 ：localhost，从数据库服务器运行的这台计算机登录这个账号。如果不写主机名，mysql默认是通配符，这个账号可以从任何一台主机上登录数据库。

--创建经理的角色：manager, 角色可以登录的主机： localhost
mysql> CREATE ROLE 'manager'@'localhost';
Query OK, 0 rows affected (0.06 sec)

--创建库管的角色： stocker,可以从任何一台主机上登录数据库
mysql> CREATE ROLE 'stocker';
Query OK, 0 rows affected (0.02 sec)

2、给角色授权

--给角色manager授权：SELECT 只读的权限
mysql> GRANT SELECT ON demo.settlement TO 'manager';
Query OK, 0 rows affected (0.03 sec)
 
mysql> GRANT SELECT ON demo.goodsmaster TO 'manager';
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT SELECT ON demo.invcount TO 'manager';
Query OK, 0 rows affected (0.01 sec)

--给角色stocker授权：增删改查
mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON demo.invcount TO 'stocker';
Query OK, 0 rows affected (0.02 sec)
 
mysql> GRANT SELECT ON demo.goodsmaster TO 'stocker';
Query OK, 0 rows affected (0.02 sec)

3、查看角色权限

mysql> SHOW GRANTS FOR 'manager';
+-------------------------------------------------------+
| Grants for manager@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |  --创建角色，系统自动给你一个USAGE权限，连接登录数据库的权限
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%` |
+-------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'stocker';
+----------------------------------------------------------------------------+
| Grants for stocker@% |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `stocker`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `stocker`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `demo`.`invcount` TO `stocker`@`%` |
+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

4、创建用户

mysql> CREATE USER 'zhangsan' IDENTIFIED BY 'mysql';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT 'stocker' TO 'zhangsan';
Query OK, 0 rows affected (0.01 sec)

5、查看用户权限

mysql> SHOW GRANTS FOR 'zhangsan';
+---------------------------------------+
| Grants for zhangsan@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`%` |
| GRANT `stocker`@`%` TO `zhangsan`@`%` |
+---------------------------------------+
2 rows in set (0.00 sec)