-- 1. 创建mydb_admin管理员账户 -- 格式:`CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';` CREATEUSER'mydb_admin'@'%' IDENTIFIED WITH caching_sha2_password BY'your_password';
-- 2. 授权数据库/表访问权限 -- 格式:`GRANT PRIVILEGE ON database.table TO 'username'@'host';` GRANTALL PRIVILEGES ON mydb.*TO'mydb_admin'@'%'; -- 如果不清楚有哪些权限,执行 -- SHOW PRIVILEGES;
-- 3. 立即生效 FLUSH PRIVILEGES;
-- 4. 查看账户列表核对 SELECT*FROM mysql.user; -- SELECT * FROM mysql.user WHERE User='mydb_admin' AND Host='%';
-- 1. 创建mydb_admin管理员账户 CREATE ROLE mydb_admin LOGIN PASSWORD 'your_password';
-- 2. 授权数据库/表访问权限(需要多条语句) GRANTALL PRIVILEGES ON DATABASE mydb TO mydb_admin; -- 如果不想让账号访问其他db -- REVOKE CONNECT ON DATABASE otherdb1 FROM PUBLIC;
-- 所有跟public相关的授权都需要先切换到指定数据库 \c mydb -- 授权public GRANTALL PRIVILEGES ON SCHEMA public TO mydb_admin; -- 后续所有变更自动授权(由super admin执行) ALTERDEFAULT PRIVILEGES IN SCHEMA public GRANTALLON TABLES TO mydb_admin; ALTERDEFAULT PRIVILEGES IN SCHEMA public GRANTALLON SEQUENCES TO mydb_admin; ALTERDEFAULT PRIVILEGES IN SCHEMA public GRANTALLON FUNCTIONS TO mydb_admin;
-- 3. 删除角色 -- DROP OWNED BY mydb_admin; -- DROP ROLE mydb_admin;