新建用户

create user <userName>@'%' identified by '<密码>';

create user <userName>@'localhost' identified by '<密码>';

create user <userName>@'%' identified by '<密码>';

grant select,insert on `<schemaName>`.* to '<userName>'@'%';

grant select,insert on `<schemaName>`.* to '<userName>'@'%';

修改密码

 set password for <userName>@'%' = password('<密码>');

查看用户

select * from mysql.user;

查看当前用户

select user();

SELECT CURRENT_USER();

查看用户权限

select * from mysql.user;  查看用户的系统级权限

select * from mysql.db;     查看用户的库级权限

select * from mysql.tables_priv; 查看用户的表级权限

select * from mysql.columns_priv;

select * from mysql.procs_priv;

select * from information_schema.USER_PRIVILEGES where GRANTEE ='\'<userName>\'@\'%\'';

查看用户授权的命令

show grants for <userName>@'%';

mysql权限级别

语法grant privilege on *.* to user;

根据 *.*指定的范围可以区分力度，*.*记录在mysql.user表，schema.*记录在mysql.db表，如此类推

关于flush privilege命令

grant/revoke/create user/ set password for <userName>@'%' = password('<密码>')

这些命令会同步内存和磁盘的数据

只有直接操控mysql.user表才需要 flush privilege

角色

mysql 8.0 开始才有角色， 同样存储在mysql.user表（区别于用户是列account_locked='Y' ）

列出角色：

SELECT DISTINCT User 'Role Name', if(from_user is NULL,0, 1) Active

       FROM mysql.user LEFT JOIN mysql.role_edges ON from_user=user

       WHERE account_locked='Y' AND password_expired='Y' AND authentication_string='';

角色授予给用户， 还需要激活才能生效