MySQL权限开通的设计方案

MySQL中的权限管理和其他数据库还是有很大的不同，它能够实现几种很特别的权限场景：

1. 几个人公用一个账号，看起来用户名相同，密码相同，但是权限缺可以不同
2. 几个人用同一个账号，用户名相同，但是密码可以不同，权限可以相同
3. 几个人用同一个账号，用户名相同，但是密码可以不同，权限可以不同。

主要的原因就在于MySQL的权限认证中是按照用户和主机名两个维度结合来考量的。

所以我们的工作中，如果权限指定的太细，到了IP级别，其实需要创建的用户太多，所以我们很多时候是基于一个网段来创建，比如用户IP是192.168.3.100，那么我们可能会创建192.168.3.%的用户，这样后续要开通权限就能方便很多，从权限的管理来说，可能需要补充的就是系统级别的防火墙权限了。

这个看起来蛮简单的工作，一旦陷入琐碎的设置和流程之中，就会给我们带来很多的困扰和手工操作的复杂度，大体有几类体验比较深的痛点：

1. 开通权限需要手工构造很多基本的SQL语句，看起来没有技术含量
2. 如果指定一些表名，比如指定10个表，开通权限的时候可能权限语句就需要10条。
3. 如果有多个IP要开通权限，那么我们需要手工构造很多重复繁琐的权限语句
4. 每次开通权限的时候，对于密码都是一个头疼的格式，密码太简单不好，输入的多一些，手工输入的时候其实会发现密码好像不够随机。
5. 测试环境和生产环境的用户应该按照规范，现在可能就压根没有遵守规范。

所以实际接触一些场景，自己手工做了一些业务之后，我发现这个地方确实得改进一下了。

整体的思路就是根据输入的信息自动生成匹配的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.%';