Mysql权限
最简单的MySql权限
解决新手们删库跑路的问题:只给一个查询权限(例)
使用root用户,执行
grant SELECT on mall.* TO 'dev'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
创建了一个dev的用户,密码为123,可以在任意IP的主机上操作。
WITH GRANT OPTION代表用户可以授予权限通过GRANT语句授权给其它用户。
注:
%—— 匹配所有主机
localhost——localhost不会被解析成IP地址,直接通过UNIXsocket连接
127.0.0.1——会通过TCP/IP协议连接,并且只能在本机访问;
::1 ——::1就是兼容支持ipv6的,表示同ipv4的127.0.0.1
show grants for 'dev'
深入研究MySQL权限
用户标识是什么
上面一句简单的SQL堪称完美的解决了程序员新手的删库跑路的问题,高兴吧,你学到了新姿势,但是如果想面试给面试官留下好映像,上面的知识好像还不够,有必要好好深入研究下MySql的权限了。
这里有个小的知识点需要先具备,在mysql中的权限不是单纯的赋予给用户的,而是赋予给”用户+IP”的。
比如dev用户是否能登陆,用什么密码登陆,并且能访问什么数据库等都需要加上IP,这样才算一个完整的用户标识。换句话说 ‘dev’@’ 192.168.100.1’ 、‘dev’@‘127.0.0.1’与’dev’@‘localhost’ 这3个是完全不同的用户标识(假设你本机的ip就是 192.168.100.1)。
对列授权
有了用户标识的概念接下来就可以看权限涉及的表了,这也是面试的时候加分项哦。
有几张表你可以好好的记记的,mysql.user,mysql.db,mysql.tables_priv,mysql.column_priv
你可以熟悉其中的user表,甚至手动的改过里面的数据(不合规范哦!)
那这些表有什么用,和权限又有什么关系呢?
grant SELECT on mall.* TO 'dev'@'192.168.100.1' IDENTIFIED BY '123' WITH GRANT OPTION;
- user的一行记录代表一个用户标识
- db的一行记录代表对数据库的权限
- tables_priv的一行记录代表对表的权限
- column_priv的一行记录代表对某一列的权限
更多的示例:
建表:
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`balance` int(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_balance` (`balance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `account` VALUES ('1', 'lilei', '900');
INSERT INTO `account` VALUES ('2', 'hanmei', '100');
INSERT INTO `account` VALUES ('3', 'lucy', '250');
INSERT INTO `account` VALUES ('5', 'tom', '0');
对列做权限控制:
对于前面创建的dev用户我不想让他访问balance列,但是id和name列是可以访问的,这样的需求在工作中不是没有。
grant select(id,name) on mall.account to 'dev'@'%';
再看看mysql.column_priv:
mysql.tables_priv:
还要删除以前给的权限:
REVOKE SELECT on mall.* from 'dev'@'%';
REVOKE SELECT on mall.* from 'dev'@'192.168.100.1';
再尝试打开表:
不行了,因为对数据库的访问禁止了。
而访问被授权的列时:
select id,name from account;
成功
Mysql的角色
准备工作
如果有很多用户的权限相同,这样对于每个用户都要赋权的方式是不是太麻烦了。
在MySQL5.7开始正式支持“角色”这个功能。MySQL官方把它叫做“Role Like”,翻译过来就是类似角色,它的本质还是一个用户标识。
show variables like "%proxy%";
角色功能默认关闭,你得先把check_proxy_users,mysql_native_password_proxy_users这两个变量设置成true才行:
set GLOBAL check_proxy_users =1;
set GLOBAL mysql_native_password_proxy_users = 1;
当然,你也可以把这两个配置设置到my.cnf中
创建一个角色
CREATE USER 'dev_role'@'%' IDENTIFIED BY '123' ;
可能被你发现了,我这里创建的其实是个USER,为了稍微像角色一点点,我给这USER取名叫dev_role。
创建2个开发人员账号:
create USER 'user1';
create USER 'user2';
这两个用户我没设置密码
把两个用户加到组里面
grant proxy on 'dev_role' to 'user1';
grant proxy on 'dev_role' to 'user2';
可以看下其中一个用户的权限:
show grants for 'user1';
给角色dev_role应该有的权限
有了用户,用户也归属到了dev_role这角色下面,那接下来要做的就很简单了,根据业务需求给这角色设置权限就好了
grant select(id,name) on mall.account to 'dev_role'
现在,所有需要做的就都做完了。用user1登录测试一下:
登录进去,只能看到account表:
点击访问,失败:
查询被授权的列:
select id,name from account;
大功告成!
学会这一招,再也不怕新手删库跑路啦~
用Navicat设置权限
用户管理
新建用户:
权限管理
是不是挺方便呢?
参考:Deer——mysql优化
相关文章:
MySQL存储引擎
详解MySQL中的锁
详解MySQL中的事务(四种隔离级别、间隙锁等),看完还不懂你来打我