mysql8.0创建增删改查用户

创建增删改查用户

# 创建用户（仅本机登录）
CREATE USER 'lizedba'@'localhost' IDENTIFIED BY 'NxN4eXZ9zchD';
# 授予增删改查权限（仅本机登录）
GRANT SELECT, INSERT, UPDATE, DELETE ON giacp.* TO 'lizedba'@'localhost';

#回收所有表的删除权限
REVOKE DELETE ON giacp.* FROM 'lizedba'@'localhost';

#排除event_record, system_login_log, system_operation_log三张表，查询出所有要赋予删除权限的表
#注意：需要查出结果后执行
SELECT 
  CONCAT('GRANT DELETE ON giacp.`', table_name, '` TO ''lizedba''@''localhost'';') AS grant_sql
FROM 
  information_schema.tables
WHERE 
  table_schema = 'giacp'
  AND table_name NOT IN ('event_record', 'system_login_log', 'system_operation_log');

执行查询出来的语句

#最后单独在给event_record, system_login_log, system_operation_log增改查的权限
GRANT SELECT, INSERT, UPDATE ON giacp.event_record TO 'lizedba'@'localhost';
GRANT SELECT, INSERT, UPDATE ON giacp.system_login_log TO 'lizedba'@'localhost';
GRANT SELECT, INSERT, UPDATE ON giacp.system_operation_log TO 'lizedba'@'localhost';

#刷新（可选）
FLUSH PRIVILEGES;

创建只读用户

#创建用户（可远程登录）
CREATE USER 'lizero'@'%' IDENTIFIED BY 'lizero123';
#授予只查权限
GRANT SELECT ON giacp.* TO 'readonly_giacp'@'%';
#刷新（可选）
FLUSH PRIVILEGES;