5.6.40 MySQL Community Server (GPL)
MySQL账户是由用户名和主机名构成的。('root'@'localhost')
用户名长度限制为16个字符。
(经测试 MySQL 8.0 基本类似，除设置账户密码时 PASSWORD() 函数不能再使用)
一、查看账户信息
mysql 用户信息存储在数据库【mysql】的表【user】中。
mysql> use mysql; Database changed mysql> select * from user;
二、账户设置
1. 新增账户
创建账户'dogs'@'localhost'，只允许本机访问。明文密码‘123456’；
mysql> create user 'dogs'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
创建账户'cats'@'%'，允许所有网络访问。密文密码'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' (123456)；
mysql> create user 'cats'@'%' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; Query OK, 0 rows affected (0.00 sec)
创建账户不指定主机和密码；
mysql> create user 'other'; Query OK, 0 rows affected (0.00 sec)
在所有账户信息操作时，不声明主机默认为‘%’，允许所有主机访问。
不设置密码，则登录时不需要输入密码。
默认权限
mysql> show grants for 'dogs'@'localhost'; +--------------------------------------------------------------------------+ | Grants for dogs@localhost | +--------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dogs'@'localhost' IDENTIFIED BY PASSWORD <secret> | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec)
2.重命名账户
mysql> rename user 'other' to 'other'@'miselehe.com'; Query OK, 0 rows affected (0.00 sec) mysql> rename user 'other'@'miselehe.com' to 'other'@'localhost'; Query OK, 0 rows affected (0.00 sec)
3.修改密码
在不指定主机时，默认是’%‘，一下两句效果相同；
mysql> set password for 'other' = password('123456');
Query OK, 0 rows affected (0.00 sec)
mysql> set password for 'other'@'%' = password('123456');
Query OK, 0 rows affected (0.00 sec)账户不存在时 报错：
mysql> set password for 'other'@'localhost' = password('123456');
ERROR 1133 (42000): Can't find any matching row in the user table
注意：
1. 在修改密码时不使用password('')时，等号后边应该跟密文密码。
2. 在不指定账户时，默认修改当前账户密码(set password = password('123456'));
通过命令行修改密码：
mysqladmin -u user_name -h host_name password "password"
密码过期问题：https://dev.mysql.com/doc/refman/5.6/en/expired-password-handling.html
三、权限设置
查看权限列表
SHOW PRIVILEGES;
1. 设置权限
GRANT priv1,priv2... ON db_name.table_name TO 'username'@'host' [IDENTIFIED BY 'password'];
如果账户不存在将会创建该账户！！！
设置数据库 ’dogs‘ 下的所有权限给 'dogs'@'%'；
mysql> grant all on dogs.* to 'dogs'@'%'; Query OK, 0 rows affected (0.00 sec)
设置数据库‘cats’下表‘t_cats’的select和insert权限给账户'cats'@'%';
mysql> grant select,insert on cats.t_cats to 'cats'@'%'; Query OK, 0 rows affected (0.00 sec)
设置other@'%'所有权限;
mysql> grant all on *.* to 'other'@'%'; Query OK, 0 rows affected (0.00 sec)
2. 查看权限
SHOW GRANTS FOR 'username'@'host';
mysql> show grants for 'cats'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for cats@% | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'cats'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT SELECT, INSERT ON `cats`.`t_cats` TO 'cats'@'%' | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
3. 撤销权限
REVOKE priv1,priv2... ON db_name.table_name FROM 'username'@'host';
为了更好表达，在撤销权限前，将数据库‘dogs’的所有权限给与'cats'@'%'。
mysql> grant all on dogs.* to 'cats'@'%'; Query OK, 0 rows affected (0.00 sec)
查看'cats'@'%'权限：
mysql> show grants for 'cats'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for cats@% | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'cats'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `dogs`.* TO 'cats'@'%' | | GRANT SELECT, INSERT ON `cats`.`t_cats` TO 'cats'@'%' | +-----------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
可以看出每次授权都有新的信息。
想要删除'cats'@'%'的所有权限 如下是不可以的
mysql> revoke all on *.* from 'cats'@'%'; Query OK, 0 rows affected (0.00 sec)
我们查看后，'cats'@'%'的权限没有任何变化。
所以在撤销授权的时候需要参考授权信息。
去除'cats'@'%'账户对数据库'dogs'的权限：
mysql> revoke all on dogs.* from 'cats'@'%'; Query OK, 0 rows affected (0.00 sec)
再次查看：
mysql> show grants for 'cats'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for cats@% | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'cats'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT SELECT, INSERT ON `cats`.`t_cats` TO 'cats'@'%' | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
操作成功。
所以去除'cats'@'%'账户对数据库'cats'的权限，会出下如下情况：
mysql> revoke all on cats.* from 'cats'@'%'; ERROR 1141 (42000): There is no such grant defined for user 'cats' on host '%' mysql> revoke select,insert on cats.* from 'cats'@'%'; ERROR 1141 (42000): There is no such grant defined for user 'cats' on host '%' mysql> revoke select,insert on cats.t_cats from 'cats'@'%'; Query OK, 0 rows affected (0.00 sec)
权限设置官方手册：https://dev.mysql.com/doc/refman/5.6/en/grant.html
4. 刷新权限：
flush privileges;
在直接通过修改数据表，而不是通过指令进行操作时，需要执行。
四、删除账户
mysql> drop user 'cats'@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for 'cats'@'localhost' mysql> drop user 'cats'@'%'; Query OK, 0 rows affected (0.00 sec)
删除账户时用户名和主机需要一致。
而且，在账户登录服务器时，删除账户信息。该账户的会话不会被中断，且所有操作均有效，但退出再登录则被拒绝访问。
