Mysql权限处理

查看用户信息

select `Host`,`user` from user;

mysql> select `Host`,`user` from user;
+-----------+------------+
| Host      | user       |
+-----------+------------+
| 127.0.0.1 | root       |
| ::1       | root       |
| localhost | root       |
+-----------+------------+
4 rows in set (0.00 sec)

创建一个用户
示例：create user '用户名'@'host权限' identified by '密码';

mysql> create user 'test'@'%' identified by 'MV5vVQUu';
Query OK, 0 rows affected (0.00 sec)

授予用户权限
示例：grant 权限类型(select、update、delete可多个，all 表示所有) privileges on 表名.操作权限(*表示所有) to '用户名'@'host权限'

mysql> grant all privileges on test_db.* to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

查询用户权限

mysql> show grants for test@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*3EB9B6C888B00C67DA138D280D13B8C5129A1DD2' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'test'@'%'                                                 |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

取消用户权限
示例：revoke all on 表名.操作权限 from test@'%'

mysql> revoke all on test_db.* from test@'%';
Query OK, 0 rows affected (0.00 sec)

再次查看用户权限

mysql> show grants for test@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*3EB9B6C888B00C67DA138D280D13B8C5129A1DD2' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改用户密码

//update 需要指定密码和password函数
//推荐方法 
ALTER USER test@'%' IDENTIFIED BY '123456';

最后别忘了刷新权限

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>