未分類
11 11 月 2020

sql 改写类的数据脱敏遇到的问题求助

sql 改写类的数据脱敏遇到的问题求助

資深大佬 : zczy 3

https://github.com/xylou/sqlMask

为了找工作写的一个开源项目，但是觉得有点问题了

是通过改写 sql 来做的数据脱敏

比如要查询 phoneNum,phoneNum 配置了策略是隐藏最后四位,mask(col,8,11,*)

select phoneNum from table

那改写 sql 的结果就是

select mask(phoneNum,8,11,*) phoneNum from table

如果是上面的这种普通 sql 还比较好处理

但是现在有这么个问题，比如下面这种类型的 sql

  1. select phoneNum from (select phoneNum from table) t where phoneNum = ‘13800001111’;

如果改写成这种，这也是我现在的方案

select phoneNum from (select mask(phoneNum,8,11,*) phoneNum from table) t where t.phoneNum = ‘13800001111’;

那其实就有问题了,where 语句是有问题的

如果改写成这种：

select mask(phoneNum,8,11,*) phoneNum from (select phoneNum from table) t where t.phoneNum = ‘13800001111’;

那数据安全就不能保证，因为我可以写下面这种 sql 查到原始数据

select phoneNum from (select ‘12345678900’ + phoneNum as phoneNum from table) t where phoneNum = ‘13800001111’;

一定程度可以绕过了

应该怎么处理呢

大佬有話說 (26)

  • 資深大佬 : annielong

    习惯上最外层再套一个 select 进行处理

  • 主 資深大佬 : zczy

    @annielong 啥意思

  • 資深大佬 : RRRoger

    “`sql

    SELECT phoneNum_hide
    FROM
    (SELECT mask(phoneNum,8,11,*) AS phoneNum_hide,
    phoneNum AS phoneNum
    FROM TABLE) t
    WHERE t.phoneNum = ‘13800001111’;

    “`

    这样呢

  • 主 資深大佬 : zczy

    @RRRoger

    不行，这样在最外层 select * 就全能看到了

    不安全

  • 資深大佬 : xuanbg

    脱敏函数只接受一个 phoneNum 不就好了吗？

    select mask(phoneNum) phoneNum from table t where t.phoneNum = ‘13800001111’;

  • 主 資深大佬 : zczy

    @xuanbg

    不行啊，我这个是封装了一个 rest 服务

    不能规范用户行为的，要杜绝能够查出原始数据的行为

  • 資深大佬 : yangzh

    数据库里面弄一个脱敏视图，然后所有 select 都只走脱敏视图，保证没有任何办法查到敏感数据

  • 資深大佬 : buliugu

    釜底抽薪，直接写个 mask jbdc driver 吧，做一遍 sql parser 自动改写所有涉及字段的 sql

  • 主 資深大佬 : zczy

    @yangzh 你这个叫静态脱敏，我这个接口也是支持 create table/view as select 操作的

    @buliugu 这个就是用 calcite 做的 sql parse

  • 主 資深大佬 : zczy

    @RRRoger

    但是这个思路估计是可以的

    需要再写一个执行查询的接口，不让看到中间 sql 就行

    原来只有 loadPolicy 和 loadMetadata 接口

  • 資深大佬 : buliugu

    @zczy 那就是 hook 的不到位啊，遍历 AST 理论上可以拿到 sq 所有 l 的信息

  • 主 資深大佬 : zczy

    @buliugu

    没明白什么意思，现在查到原始列了啊

    问题是 where 语句里的列啊

  • 資深大佬 : buliugu

    @zczy 不仅仅是原始列，虚拟表也要 hook 处理。所有可能查询的地方都要 hook 的

  • 主 資深大佬 : zczy

    @buliugu
    那这种就不是改写 sql 了

  • 資深大佬 : dayeye2006199

    select phoneNum from (select mask(phoneNum,8,11,*) from table where t.phoneNum = ‘13800001111’);

    where 放在里面可不可以呢？

  • 主 資深大佬 : zczy

    @dayeye2006199
    当然不可以啦

    with t1 as (select name from emps union select name from depts) select name from t1

    你看一下这种类型的 sql

    “`
    @Test
    public void sqlTest4() throws Exception {
    String originSql = “with t1 as (select name from emps union select name from depts) select name from t1”;
    String expectSql = “with t1 as (select hash_fun(1, 5, emps.name, ‘*’) as namen” +
    “from sales.emps as empsn” +
    “unionn” +
    “select hash_fun3(1, 7, depts.name, ‘*’) as namen” +
    “from sales.depts as depts) (select t1.namen” +
    “from t1 as t1)”;
    String maskSql = qs3.getMaskSql(originSql);
    compareSql(maskSql, expectSql);
    }
    “`

    如果后面有 where 语句的话

  • 資深大佬 : gengzi

    你这个是查询 sql，返回脱敏的 sql ？

  • 主 資深大佬 : zczy

    @gengzi
    对，只是对 sql 语句的改写

    分了三个模块，policy,metadata,rewriteSql 部分

    https://github.com/xylou/sqlMask

  • 主 資深大佬 : zczy

    @buliugu
    还有啥好的思路吗，大佬

  • 資深大佬 : buliugu

    @zczy 一样啊，subquery factoring 语法也是可以通过 ast 发现的，说白了使用这种模式做 data masking，就是苦力活，sql 所有可能查询到数据的语法都得 hook 掉。。更要命的是不同数据库语法不一样（逃

  • 主 資深大佬 : zczy

    @buliugu
    如果是嵌在整个执行计划中，要适配不同的数据存储，可能也不太行

    有的比较复杂的 sql 语句估计还是会有脱敏后计算的场景

  • 資深大佬 : laminux29

    你们数据脱敏居然还敢在同服务器甚至同一个库上操作，真有你的，这种最容易因各种漏洞甚至 Hack 而出问题。

    专业的做法是，另找一台服务器，专用于脱敏，上面安装一个数据库。然后由原数据库主动把脱敏后的数据，推送到脱敏专用服务器上的专用数据库。只能推，拉都不行。

  • 資深大佬 : dorothyREN

    接口做脱敏不行吗，为啥非得用 sql

  • 主 資深大佬 : zczy

    @laminux29 你这个是数据脱敏，也有的

    我们现在是策略脱敏，原数据是干净的

  • 主 資深大佬 : zczy

    @dorothyREN 这个是个人开源项目

  • 資深大佬 : yzdobest

    想问下这个使用场景是什么样的？用户传入 sql 吗