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;
Original: https://www.cnblogs.com/zcxxcvbn/p/15945749.html
Author: 一份人间烟火
Title: mysql关于权限
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/590769/
转载文章受原作者版权保护。转载请注明原作者出处!