V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
zczy
V2EX  ›  程序员

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

  •  
  •   zczy · 2020-11-19 18:00:10 +08:00 · 2383 次点击
    这是一个创建于 1225 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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';

    一定程度可以绕过了

    应该怎么处理呢

    27 条回复    2020-11-23 11:26:53 +08:00
    annielong
        1
    annielong  
       2020-11-19 18:03:39 +08:00
    习惯上最外层再套一个 select 进行处理
    zczy
        2
    zczy  
    OP
       2020-11-19 18:08:50 +08:00
    @annielong 啥意思
    RRRoger
        3
    RRRoger  
       2020-11-19 18:29:10 +08:00   ❤️ 1
    ```sql

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



    ```

    这样呢
    zczy
        4
    zczy  
    OP
       2020-11-19 18:39:56 +08:00
    @RRRoger

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

    不安全
    xuanbg
        5
    xuanbg  
       2020-11-19 18:42:39 +08:00
    脱敏函数只接受一个 phoneNum 不就好了吗?

    select mask(phoneNum) phoneNum from table t where t.phoneNum = '13800001111';
    zczy
        6
    zczy  
    OP
       2020-11-19 18:57:20 +08:00
    @xuanbg

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

    不能规范用户行为的,要杜绝能够查出原始数据的行为
    yangzh
        7
    yangzh  
       2020-11-19 23:34:48 +08:00 via iPhone
    数据库里面弄一个脱敏视图,然后所有 select 都只走脱敏视图,保证没有任何办法查到敏感数据
    buliugu
        8
    buliugu  
       2020-11-20 09:38:06 +08:00
    釜底抽薪,直接写个 mask jbdc driver 吧,做一遍 sql parser 自动改写所有涉及字段的 sql
    zczy
        9
    zczy  
    OP
       2020-11-20 09:52:29 +08:00
    @yangzh 你这个叫静态脱敏,我这个接口也是支持 create table/view as select 操作的

    @buliugu 这个就是用 calcite 做的 sql parse
    zczy
        10
    zczy  
    OP
       2020-11-20 10:15:57 +08:00
    @RRRoger

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

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

    原来只有 loadPolicy 和 loadMetadata 接口
    buliugu
        11
    buliugu  
       2020-11-20 13:50:39 +08:00
    @zczy 那就是 hook 的不到位啊,遍历 AST 理论上可以拿到 sq 所有 l 的信息
    zczy
        12
    zczy  
    OP
       2020-11-20 13:59:41 +08:00
    @buliugu

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

    问题是 where 语句里的列啊
    buliugu
        13
    buliugu  
       2020-11-20 14:42:11 +08:00
    @zczy 不仅仅是原始列,虚拟表也要 hook 处理。所有可能查询的地方都要 hook 的
    zczy
        14
    zczy  
    OP
       2020-11-20 14:49:07 +08:00
    @buliugu
    那这种就不是改写 sql 了
    dayeye2006199
        15
    dayeye2006199  
       2020-11-20 15:11:22 +08:00
    select phoneNum from (select mask(phoneNum,8,11,*) from table where t.phoneNum = '13800001111');

    where 放在里面可不可以呢?
    zczy
        16
    zczy  
    OP
       2020-11-20 16:02:58 +08:00
    @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 name\n" +
    "from sales.emps as emps\n" +
    "union\n" +
    "select hash_fun3(1, 7, depts.name, '*') as name\n" +
    "from sales.depts as depts) (select t1.name\n" +
    "from t1 as t1)";
    String maskSql = qs3.getMaskSql(originSql);
    compareSql(maskSql, expectSql);
    }
    ```

    如果后面有 where 语句的话
    gengzi
        17
    gengzi  
       2020-11-20 17:21:52 +08:00
    你这个是查询 sql,返回脱敏的 sql ?
    zczy
        18
    zczy  
    OP
       2020-11-20 17:48:48 +08:00
    @gengzi
    对,只是对 sql 语句的改写

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

    https://github.com/xylou/sqlMask
    zczy
        19
    zczy  
    OP
       2020-11-20 17:59:26 +08:00
    @buliugu
    还有啥好的思路吗,大佬
    buliugu
        20
    buliugu  
       2020-11-21 12:17:26 +08:00
    @zczy 一样啊,subquery factoring 语法也是可以通过 ast 发现的,说白了使用这种模式做 data masking,就是苦力活,sql 所有可能查询到数据的语法都得 hook 掉。。更要命的是不同数据库语法不一样(逃
    zczy
        21
    zczy  
    OP
       2020-11-21 16:21:19 +08:00
    @buliugu
    如果是嵌在整个执行计划中,要适配不同的数据存储,可能也不太行

    有的比较复杂的 sql 语句估计还是会有脱敏后计算的场景
    laminux29
        22
    laminux29  
       2020-11-21 21:41:29 +08:00
    你们数据脱敏居然还敢在同服务器甚至同一个库上操作,真有你的,这种最容易因各种漏洞甚至 Hack 而出问题。

    专业的做法是,另找一台服务器,专用于脱敏,上面安装一个数据库。然后由原数据库主动把脱敏后的数据,推送到脱敏专用服务器上的专用数据库。只能推,拉都不行。
    dorothyREN
        23
    dorothyREN  
       2020-11-22 10:26:27 +08:00
    接口做脱敏不行吗,为啥非得用 sql
    zczy
        24
    zczy  
    OP
       2020-11-22 16:08:36 +08:00 via Android
    @laminux29 你这个是数据脱敏,也有的

    我们现在是策略脱敏,原数据是干净的
    zczy
        25
    zczy  
    OP
       2020-11-22 16:09:10 +08:00 via Android
    @dorothyREN 这个是个人开源项目
    yzdobest
        26
    yzdobest  
       2020-11-22 22:23:14 +08:00 via Android
    想问下这个使用场景是什么样的?用户传入 sql 吗
    zczy
        27
    zczy  
    OP
       2020-11-23 11:26:53 +08:00
    @yzdobest 我个人设想是这样的

    不同的用户有不同的策略,也就是配置了不同的 udf 函数

    使用同一个 sql 语句,执行查询出来的结果看到的不一样

    然后数据源是干净的,不是脱敏后的数据
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   976 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 21:55 · PVG 05:55 · LAX 14:55 · JFK 17:55
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.