MySQL8.0、创建新用户与角色授权

# 1、查询系统用户
SELECT host, user, select_priv, insert_priv, drop_priv, update_priv, 
authentication_string FROM mysql.user;

# 2、创建用户  -下文均执行方法一语句 
# 方式一： 默认host是 % 
CREATE USER zhang3 IDENTIFIED BY '123456';
# 方式二： 自定义host：localhost
CREATE USER 'zhang3'@'localhost' IDENTIFIED BY '123456';

# 3、修改用户信息
# 3.1 修改名称
UPDATE mysql.user SET USER = 'zhangsan' WHERE USER = 'zhang3';
# 3.2 修改密码
ALTER USER zhang3 IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '123';

# 4、删除用户信息
方式一：默认删除host为%的用户(推荐)  
DROP USER zhangsan;
方式二:
DELETE FROM mysql.user WHERE Host='%' AND User='zhangsan';
FLUSH PRIVILEGES;

ALTER USER USER() IDENTIFIED BY 'new_password';
# root用户也可以使用set修改登录密码
SET PASSWORD='new_password';
# root其他用户密码
SET PASSWORD FOR 'username'@'hostname'='new_password';

# 1、查看当前用户权限
SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();

# 2、查看某用户的全局变量
SHOW GRANTS FOR 'user'@'主机地址' ;

# 3、授予权限 -授予zhangsan在dbtest1库下的增删改查权限。*表示所有表。*.*表示所有库及表
GRANT SELECT,INSERT,DELETE,UPDATE ON dbtest1.* TO zhangsan;

# 4、授予zhangsan所有库、表权限，但是不包括GRANT权限
GRANT ALL PRIVILEGES ON *.* TO zhangsan@'%' IDENTIFIED BY '123';

# 收回删除权限
REVOKE DELETE ON dbtest1.* FROM zhangsan;

# 1、创建user角色
CREATE ROLE 'worker'@'localhost';

# 2、删除角色
DROP ROLE 'worker';

# 1、查看角色权限
SHOW GRANTS FOR 'worker';

# 2、user角色授予dbtest1库权限
GRANT SELECT, UPDATE, INSERT ON dbtest1.* TO 'worker';

# 3、回收角色权限
REVOKE UPDATE ON dbtest1.* FROM 'worker';

# 4、激活角色
SET DEFAULT ROLE ALL TO 'zhangsan'@'%';

GRANT 'worker' TO 'zhangsan';

SHOW GRANTS FOR 'zhangsan';

REVOKE 'worker' FROM 'zhangsan'@'%';

# 1、在查看角色之前需要先激活角色
SET DEFAULT ROLE ALL TO 'zhangsan'@'%';

# 2、查看当前角色
SELECT CURRENT_ROLE();