def query_masking(self, db_name=None, sql='', resultset=None):
"""传入 sql语句, db名, 结果集,
返回一个脱敏后的结果集"""
# 解析语法树
mask_tool = Masking()
resultset_dict = resultset.__dict__
inception_mask_result = mask_tool.data_masking(self.instance_name,
db_name, sql,
resultset_dict)
# 传参进去之后, 就已经被处理
resultset.rows = resultset_dict['rows']
hit_rule = inception_mask_result['data']['hit_rule']
if hit_rule == 1:
resultset.is_masked = True
if inception_mask_result['status'] != 0:
resultset.is_critical = True
return resultset
def query_priv_check(user, instance_name, db_name, sql_content, limit_num):
result = {
'status': 0,
'msg': 'ok',
'data': {
'priv_check': 1,
'limit_num': 0
}
}
instance = Instance.objects.get(instance_name=instance_name)
table_ref = None # 查询语句涉及的表信息
# 检查用户是否有该数据库/表的查询权限
if user.is_superuser:
user_limit_num = int(SysConfig().get('admin_query_limit', 5000))
limit_num = int(user_limit_num) if int(limit_num) == 0 else min(
int(limit_num), int(user_limit_num))
result['data']['limit_num'] = limit_num
return result
# 查看表结构的语句，inception语法树解析会报错，故单独处理，explain直接跳过不做校验
elif re.match(r"^show\s+create\s+table", sql_content.lower()):
tb_name = re.sub('^show\s+create\s+table',
'',
sql_content,
count=1,
flags=0).strip()
# 先判断是否有整库权限
db_privileges = QueryPrivileges.objects.filter(
user_name=user.username,
instance_name=instance_name,
db_name=db_name,
priv_type=1,
valid_date__gte=datetime.datetime.now(),
is_deleted=0)
# 无整库权限再验证表权限
if len(db_privileges) == 0:
tb_privileges = QueryPrivileges.objects.filter(
user_name=user.username,
instance_name=instance_name,
db_name=db_name,
table_name=tb_name,
priv_type=2,
valid_date__gte=datetime.datetime.now(),
is_deleted=0)
if len(tb_privileges) == 0:
result['status'] = 1
result[
'msg'] = '你无' + db_name + '.' + tb_name + '表的查询权限！请先到查询权限管理进行申请'
return result
# sql查询, 可以校验到表级权限
elif instance.db_type == 'mysql':
# 首先使用inception的语法树打印获取查询涉及的的表
table_ref_result = Masking().query_table_ref(sql_content + ';',
instance_name, db_name)
# 正确解析拿到表数据，可以校验表权限
if table_ref_result['status'] == 0:
table_ref = table_ref_result['data']
# 获取表信息,校验是否拥有全部表查询权限
QueryPrivilegesOb = QueryPrivileges.objects.filter(
user_name=user.username, instance_name=instance_name)
# 先判断是否有整库权限
for table in table_ref:
db_privileges = QueryPrivilegesOb.filter(
db_name=table['db'],
priv_type=1,
valid_date__gte=datetime.datetime.now(),
is_deleted=0)
# 无整库权限再验证表权限
if len(db_privileges) == 0:
tb_privileges = QueryPrivilegesOb.filter(
db_name=table['db'],
table_name=table['table'],
valid_date__gte=datetime.datetime.now(),
is_deleted=0)
if len(tb_privileges) == 0:
result['status'] = 1
result['msg'] = '你无' + table['db'] + '.' + table[
'table'] + '表的查询权限！请先到查询权限管理进行申请'
return result
# 获取表数据报错，检查配置文件是否允许继续执行，并进行库权限校验
else:
# 校验库权限，防止inception的语法树打印错误时连库权限也未做校验
privileges = QueryPrivileges.objects.filter(
user_name=user.username,
instance_name=instance_name,
db_name=db_name,
valid_date__gte=datetime.datetime.now(),
is_deleted=0)
if len(privileges) == 0:
result['status'] = 1
result['msg'] = '你无' + db_name + '数据库的查询权限！请先到查询权限管理进行申请'
return result
if SysConfig().get('query_check'):
return table_ref_result
else:
result['data']['priv_check'] = 2
# 获取查询涉及表的最小limit限制
if table_ref:
db_list = [table_info['db'] for table_info in table_ref]
table_list = [table_info['table'] for table_info in table_ref]
user_limit_num = QueryPrivileges.objects.filter(
user_name=user.username,
instance_name=instance_name,
db_name__in=db_list,
table_name__in=table_list,
valid_date__gte=datetime.datetime.now(),
is_deleted=0).aggregate(Min('limit_num'))['limit_num__min']
if user_limit_num is None:
# 如果表没获取到则获取涉及库的最小limit限制
user_limit_num = QueryPrivileges.objects.filter(
user_name=user.username,
instance_name=instance_name,
db_name=db_name,
valid_date__gte=datetime.datetime.now(),
is_deleted=0).aggregate(Min('limit_num'))['limit_num__min']
else:
# 如果表没获取到则获取涉及库的最小limit限制
user_limit_num = QueryPrivileges.objects.filter(
user_name=user.username,
instance_name=instance_name,
db_name=db_name,
valid_date__gte=datetime.datetime.now(),
is_deleted=0).aggregate(Min('limit_num'))['limit_num__min']
limit_num = int(user_limit_num) if int(limit_num) == 0 else min(
int(limit_num), int(user_limit_num))
result['data']['limit_num'] = limit_num
return result
import datetime
import time
from sql.utils.extend_json_encoder import ExtendJSONEncoder
from sql.utils.aes_decryptor import Prpcrypt
from sql.utils.dao import Dao
from .const import WorkflowDict
from .models import MasterConfig, SlaveConfig, QueryPrivilegesApply, QueryPrivileges, QueryLog, SqlGroup
from sql.utils.data_masking import Masking
from sql.utils.workflow import Workflow
from sql.utils.config import SysConfig
from sql.utils.group import user_slaves
dao = Dao()
prpCryptor = Prpcrypt()
datamasking = Masking()
workflowOb = Workflow()
# 查询权限申请用于工作流审核回调
def query_audit_call_back(workflow_id, workflow_status):
# 更新业务表状态
apply_info = QueryPrivilegesApply()
apply_info.apply_id = workflow_id
apply_info.status = workflow_status
apply_info.save(update_fields=['status'])
# 审核通过插入权限信息，批量插入，减少性能消耗
if workflow_status == WorkflowDict.workflow_status['audit_success']:
apply_queryset = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
# 库权限
if apply_queryset.priv_type == 1: