【MySQL】MySQL创建用户并授权

---

查看用户

查看当前用户

mysql> select user();  --当前用户及登录IP
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

查看用户列表

mysql> select user, host, password from mysql.user;
+----------+------+-------------------------------------------+
| user     | host | password                                  |
+----------+------+-------------------------------------------+
| readonly | %    | *963A4B420903CAD4E7FC36A23122AB927E061FE3 |
| root     | %    | *6BB4837EB79329105EE6568DDA7DC67ED2CA2AD9 |
+----------+------+-------------------------------------------+
2 rows in set (0.00 sec)

创建用户

语法：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

解释：

• username：登陆用户名
• host：指定可访问的ip，如果指定所有ip都能访问，将其设为通配符%即可。
• password：登陆密码，如果密码为空则无需密码

事例：
允许本地 IP访问localhost的Mysql数据库

CREATE USER 'fuyun'@'localhost' IDENTIFIED BY 'password';

允许外网IP访问数据库editest，本命令包含上面的命令，是所有的IP都可以访问该数据库

CREATE USER 'fuyun'@'%' IDENTIFIED BY 'password';

修改用户密码

5.7版本以前中mysql.user表中有password字段，可以通过修改该表该字段的方式修改用户密码

update mysql.user set password=password('readonly123') where user = 'readonly';

5.7版本后password字段变更为authentication_string，修改方式一样

update mysql.user set authentication_string=password('readonly123') where user = 'readonly';
flush privileges;

也可以通过以下方式修改密码

set password FOR 'fuyun'@'%' = password('fuyun123');
flush privileges;

授予权限

通过SQL的 GRANT 命令

创建用户fuyun并赋予test数据库所有表的select,insert,update,delete,create,drop权限
语法：

grant privileges on database.tablename to “username”@‘host’;

说明：

• privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
• database：如果不指定数据库，直接*.*即可，如果指定数据库但不指定表名，则database.*即可
• username：登陆的用户名
• host：给予授权的主机ip，例如我想让用户A的ip使用fuyun用户所授予的权限，但是不想让用户B的ip来使用fuyun用户的权限

实例：

grant select,insert,update,delete,create,drop on test.* to 'fuyun'@'%' identified by 'fuyun123';

以上命令会在mysql数据库中的user表创建一条用户信息记录。

创建用户reader对所有库只读权限

grant select on *.* to 'reader'@'%'  identified by '123456';

也可以通过往mysql库的user表中insert into语句实现

INSERT INTO mysql.user (host, user, select_priv) VALUES ('%', 'reader', 'Y');

创建用户testread对test库的test_tb表赋予select权限

grant select on test.test_tb to 'test'@'%'  identified by '123456';

撤销用户权限

语法：

revoke privileges on database.tablename from 'username'@'host';

撤销（revoke）的和授予（grant）的基本一样，除了revoke（对应grant）和from（对应to）

撤销fuyun用户test.test的drop权限

revoke drop on test.test from "fuyun"@'%';

⚠️注意：grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

密码过期和锁定用户

在MySQL5.6.6版本起，增加了password_expired功能，它允许设置MySQL数据库用户的密码过期时间。这个特性已经添加到mysql.user数据表，它的默认值是”N”，表示已禁用密码过期功能

alter user 'fuyun'@'%' password expire;

也可以通过以下sql进行设置

update mysql.user set password_expired='Y' where user = 'fuyun';
flush privileges;

强制设置密码过期后，虽然能够登陆，但是一切权限都为空了。

撤销强制设置密码过期

update mysql.user set password_expired='N' where user = 'fuyun';
flush privileges;

在创建的时候锁定用户(mysql5.7版本)

create user 'test01'@'%' identified by 'password' account lock;

解锁账号

alter user 'test01'@'%' account unlock;
flush privileges;

已存在的时候锁定用户

alter user 'test01'@'%' account lock;

删除用户

drop user 'test01'@'%';

权限常用关键字