mysql关于权限

use db;

-- 
select user(),database();
-- 建立账号

create user user1;
create user user2@'192,168.21.76' identified with caching_sha2_password by 'user2';

-- 查看账号

select host,user,plugin,authentication_string from mysql.user;

-- 删除账号

drop user user1;
drop user user2@'192,168.21.76';

-- 修改密码

set password='123';
set password='';
-- user1@'%'
set password for user1@'%'='123';
alter user user1 identified by '456';

-- mysql8 修改密码插件

alter user user1 identified with caching_sha2_password ;
alter user user1 identified with caching_sha2_password by '';
alter user user1 identified with caching_sha2_password by '123';

-- mysql5
set password =password('123');
-- 创建账号，多少天过期，登录后必须设置密码

create user wu
identified with caching_sha2_password
by 'wu'
PASSWORD EXPIRE;
create user lisi
identified with caching_sha2_password
by 'lisi'
PASSWORD EXPIRE interval 180 day;
alter user lisi account lock;
alter user lisi account unlock;

-- 使用root账号登录，授权 lisi就是d3数据库的管理员
grant all on d3.* to lisi;
-- 查看权限

SHOW GRANTS FOR lisi;
show grants for root@localhost;

-- 修改账号名称

rename user aaa to admin;

-- 查看权限

show grants for admin;
show grants for root@localhost;
grant all on db1.* to 'jeffrey'@'localhost';
grant 'role1', 'role2' to 'user1'@'localhost', 'user2'@'localhost';
create user ww identified by 'ww';

-- root 给ww授权db3数据库管理员

grant all on db4.* to ww;

-- 将数据库d3的stu表的id,name查询权限给ww账号

grant select(id,name) on d3.stu to ww; 

-- 收权

show grants for ww;
revoke all on *.* from ww;
revoke select(name) on d3.stu from ww;

思考：建立一个账号，允许有查询权限，1个月后，此账号自己删除，也可以自己收权，我们应该怎么做？ 

create user aa identified by 'aa';

alter user aa identified with mysql_native_password by 'aa';

grant select on db.* to aa;

flush privileges;

create event edu on schedule at current_timestamp + interval 30 day do revoke all on db.* from aa;