mysql权限控制

mysql的权限控制

1.授权与撤权

-- 创建用户（参考mysql8文档CREATE USER Statement章节）
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
-- 创建用户并赋予角色
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password' DEFAULT ROLE developer ;
-- 查看用户
select user,host from mysql.user;
-- 修改用户名，不影像用户密码
rename user 'u1'@'172.16.12.24' to 'win.user'@'172.16.12.24';
-- 修改密码
alter user '用户名'@'登录主机' identified by '密码(自定义)';
-- 删除用户
DROP USER 'jeffrey'@'localhost';

-- 此外，mysql还支持角色创建，赋具体的参考mysql8的相关文档
CREATE ROLE 'app_developer', 'app_read', 'app_write';
GRANT ALL ON app_db.* TO 'app_developer';
-- 将角色赋予给用户
GRANT 'app_developer' TO 'dev1'@'localhost';
DROP ROLE 'administrator', 'developer';
-- 授权
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';
GRANT select(cust_id,cust_name) on mysql_test.customers to'zhangsan'@'localhost';
-- 如果此处授权的时候报错，说密码设置过于简单啥的，则加上IDENTIFIED BY(新建一个用户,并赋予权限)
GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'password';
-- 撤销权限
REVOKE ALL ON db1.* FROM 'jeffrey'@'localhost';
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';
-- 注：以上操作完成后，必须刷新权限
FLUSH PRIVILEGES;

• 权限范围：

①全局权限Global Privileges：管理权限，应用到服务器上的所有数据库上(全局权限保存在 mysql.user 表中)-----on .
②数据库权限Database Privileges：应用到某个特定数据库的所有对象上(数据库权限保存在 mysql.db 表中)-----on db_name.*
③表权限Table Privileges：应用到某个特定表的所有列上(表权限保存在 mysql.tables_priv 表中)-----on db_name.tbl_name
④列权限Column Privileges：应用到某个特定表的单个列上(列权限保存在 mysql.columns_priv 表中)-----在权限名称后面跟小括号，其中写上列名
⑤存储例程权限Stored Routine Privileges：应用到存储过程和函数上(存储例程权限保存在 mysql.procs_priv表中)
⑥代理用户权限Proxy User Privileges：使一个用户成为另一个用户的代理(代理用户权限保存在 mysql.proxies_priv表中)

• 权限转移与限制：

转移（with grant option）:授予当前系统中一个不存在的用户tom在数据库mysql_test的表customers上拥有select 和update 的权限，并允许其将自身的这个权限授予其他用户（通过with grant option）
grant select,update on mysql_test.customers to'tom'@'localhost'identified by'123' with grant option;
限制：如果上面with子句后面跟的是max_queries_per_hour count、max_updates_per_hour count、
max_connections_per_hour count、max_user_connections count中的某一项，则该grant语句可用于限制权限;
grant delete on mysql_test.customers to 'zhangsan'@'localhost' with max_queries_per_hour 1;(每小时只能处理一条delete语句的权限)

2.使用案例

-- 开发者
CREATE USER 'dev'@'%' IDENTIFIED BY 'dev';
GRANT ALL ON db1.* TO 'dev'@'%';
FLUSH PRIVILEGES;
-- 浏览者
CREATE USER 'view'@'%' IDENTIFIED BY 'view';
GRANT SELECT ON db1.* TO 'view'@'%';
FLUSH PRIVILEGES;
-- 正常运行后
CREATE USER 'write'@'%' IDENTIFIED BY 'write';
GRANT SELECT,INSERT, UPDATE,DELETE ON db1.* TO 'write'@'%';
FLUSH PRIVILEGES;

参考地址

1.MySQL用户认证及权限控制
2.Mysql之访问控制与权限管理