查看用户信息
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>
