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

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

  •  
  •   zczy · 10 天前 · 1300 次点击

    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   10 天前
    习惯上最外层再套一个 select 进行处理
    zczy
        2
    zczy   10 天前
    @annielong 啥意思
    RRRoger
        3
    RRRoger   10 天前   ❤️ 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   10 天前
    @RRRoger

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

    不安全
    xuanbg
        5
    xuanbg   10 天前
    脱敏函数只接受一个 phoneNum 不就好了吗?

    select mask(phoneNum) phoneNum from table t where t.phoneNum = '13800001111';
    zczy
        6
    zczy   10 天前
    @xuanbg

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

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

    @buliugu 这个就是用 calcite 做的 sql parse
    zczy
        10
    zczy   9 天前
    @RRRoger

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

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

    原来只有 loadPolicy 和 loadMetadata 接口
    buliugu
        11
    buliugu   9 天前
    @zczy 那就是 hook 的不到位啊,遍历 AST 理论上可以拿到 sq 所有 l 的信息
    zczy
        12
    zczy   9 天前
    @buliugu

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

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

    where 放在里面可不可以呢?
    zczy
        16
    zczy   9 天前
    @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   9 天前
    你这个是查询 sql,返回脱敏的 sql ?
    zczy
        18
    zczy   9 天前
    @gengzi
    对,只是对 sql 语句的改写

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

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

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

    专业的做法是,另找一台服务器,专用于脱敏,上面安装一个数据库。然后由原数据库主动把脱敏后的数据,推送到脱敏专用服务器上的专用数据库。只能推,拉都不行。
    dorothyREN
        23
    dorothyREN   7 天前
    接口做脱敏不行吗,为啥非得用 sql
    zczy
        24
    zczy   7 天前 via Android
    @laminux29 你这个是数据脱敏,也有的

    我们现在是策略脱敏,原数据是干净的
    zczy
        25
    zczy   7 天前 via Android
    @dorothyREN 这个是个人开源项目
    yzdobest
        26
    yzdobest   7 天前 via Android
    想问下这个使用场景是什么样的?用户传入 sql 吗
    zczy
        27
    zczy   6 天前
    @yzdobest 我个人设想是这样的

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

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

    然后数据源是干净的,不是脱敏后的数据
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1313 人在线   最高记录 5268   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 23:35 · PVG 07:35 · LAX 15:35 · JFK 18:35
    ♥ Do have faith in what you're doing.