MySQL权限开通的设计方案
公众号：杨建荣的学习笔记 · 作者：r16笔记第36天 · 发布：2018-05-28 23:45:02 · 原文链接
MySQL中的权限管理和其他数据库还是有很大的不同，它能够实现几种很特别的权限场景：
-
几个人公用一个账号，看起来用户名相同，密码相同，但是权限缺可以不同
-
几个人用同一个账号，用户名相同，但是密码可以不同，权限可以相同
-
几个人用同一个账号，用户名相同，但是密码可以不同，权限可以不同。
主要的原因就在于MySQL的权限认证中是按照用户和主机名两个维度结合来考量的。
所以我们的工作中，如果权限指定的太细，到了IP级别，其实需要创建的用户太多，所以我们很多时候是基于一个网段来创建，比如用户IP是192.168.3.100，那么我们可能会创建192.168.3.%的用户，这样后续要开通权限就能方便很多，从权限的管理来说，可能需要补充的就是系统级别的防火墙权限了。
这个看起来蛮简单的工作，一旦陷入琐碎的设置和流程之中，就会给我们带来很多的困扰和手工操作的复杂度，大体有几类体验比较深的痛点：
-
开通权限需要手工构造很多基本的SQL语句，看起来没有技术含量
-
如果指定一些表名，比如指定10个表，开通权限的时候可能权限语句就需要10条。
-
如果有多个IP要开通权限，那么我们需要手工构造很多重复繁琐的权限语句
-
每次开通权限的时候，对于密码都是一个头疼的格式，密码太简单不好，输入的多一些，手工输入的时候其实会发现密码好像不够随机。
-
测试环境和生产环境的用户应该按照规范，现在可能就压根没有遵守规范。
所以实际接触一些场景，自己手工做了一些业务之后，我发现这个地方确实得改进一下了。
整体的思路就是根据输入的信息自动生成匹配的SQL语句，人工初步审核和过滤，确认后执行。如果梳理需求，大体就会有一些功能点需要完善，这些通过手工眼里去鉴别还是有些费神的。整体来说是有两个大的功能需要做，一个是开通权限，一个是查询权限。
开通权限的部分需要的改进如下：
1. 根据客户端IP信息，取IP的前三段动态生成用户
2. 根据输入对象动态生成SQL
3. 根据输入权限动态生成SQL
4. 根据环境类型，检查用户名是否符合规范，根据业务匹配用户名
5. 根据权限类型，检查用户名是否符合规范
6. 帮助生成随机密码，转储密码信息到数据库中
7. 输入参数保证健壮性，自动过滤空格
查询权限的部分需要的改进如下：
1. 根据客户端IP和端口在服务器中得到权限信息列表
2. 根据用户来查找相应的权限
进一步发掘需求，其实我们需要的基本信息是下面的几个参数:
环境类型： 测试开发环境，线上环境
用户名:
密码：
权限类型：
数据库：
对象:
客户端列表：
def handle_grant():
# client IP list
ipaddr_list = ‘192.168.10.202,192.168.10.203, 192.168.3.20’
# username
username =’dev_user’
password = ”.join(random.sample(string.ascii_letters + string.digits, 15))
privilege_list = ‘select,insert,delete’
database_list = ‘test1,test2’
object_list = ‘tab1,tab2,tab3’
env_type = ‘test’
error_info = []
if env_type == ‘test’:
if username.startswith(“dev_”) == 0 :
error_info.append(“username is not following standard…”)
print(error_info)
return;
elif env_type == ‘prod’:
if username.startswith(“prod”) == 0:
error_info.append(“username is not following standard…”)
print(error_info)
return;
print(“aaa”)
ipaddr_list = ipaddr_list.replace(‘ ‘, ”)
print(ipaddr_list)
username_list = []
for ipaddr in ipaddr_list.split(‘,’):
tmp_ipaddr = ‘.’.join(ipaddr.split(‘.’)[0:3])
if tmp_ipaddr not in username_list:
username_list.append(tmp_ipaddr)
print(username_list)
create_user_SQLs = []
for host_prefix in username_list:
create_user_SQLs.append( ‘create user ‘ +username+ ‘@’+”‘”+host_prefix+”.%’ identified by ‘”+password+”‘;”)
for sql in create_user_SQLs:
print(sql)
grant_SQLs = []
for owner_db in database_list.split(‘,’):
for obj in object_list.split(‘,’):
for user_host in username_list:
grant_SQLs.append(“grant “+privilege_list+” on “+owner_db+”.”+obj+” to “+username+”@'”+user_host+”.%’;”)
print(grant_SQLs)
for sql in grant_SQLs:
print(sql)
handle_grant()
输出如下：
192.168.10.202,192.168.10.203,192.168.3.20
[‘192.168.10’, ‘192.168.3’]
create user dev_user@’192.168.10.%’ identified by ‘sRBgTNIei0XdUYZ’;
create user dev_user@’192.168.3.%’ identified by ‘sRBgTNIei0XdUYZ’;
。。。。。
grant select,insert,delete on test2.tab3 to dev_user@’192.168.10.%’;
grant select,insert,delete on test2.tab3 to dev_user@’192.168.3.%’;