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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

请不要在回答技术问题时复制粘贴 AI 生成的内容

这是一个创建于 1854 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

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

2020-11-19 18:03:39 +08:00

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

zczy

2020-11-19 18:08:50 +08:00

@annielong 啥意思

RRRoger

2020-11-19 18:29:10 +08:00

```sql

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

```

这样呢

zczy

2020-11-19 18:39:56 +08:00

@RRRoger

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

不安全

xuanbg

2020-11-19 18:42:39 +08:00

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

select mask(phoneNum) phoneNum from table t where t.phOneNum= '13800001111';

zczy

2020-11-19 18:57:20 +08:00

@xuanbg

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

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

yangzh

2020-11-19 23:34:48 +08:00 via iPhone

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

buliugu

2020-11-20 09:38:06 +08:00

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

zczy

2020-11-20 09:52:29 +08:00

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

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

zczy

2020-11-20 10:15:57 +08:00

@RRRoger

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

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

原来只有 loadPolicy 和 loadMetadata 接口

buliugu

2020-11-20 13:50:39 +08:00

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

zczy

2020-11-20 13:59:41 +08:00

@buliugu

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

问题是 where 语句里的列啊

buliugu

2020-11-20 14:42:11 +08:00

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

zczy

2020-11-20 14:49:07 +08:00

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

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

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

2020-11-20 17:21:52 +08:00

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

zczy

2020-11-20 17:48:48 +08:00

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

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

https://github.com/xylou/sqlMask

zczy

2020-11-20 17:59:26 +08:00

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

buliugu

2020-11-21 12:17:26 +08:00

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

zczy

2020-11-21 16:21:19 +08:00

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

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

laminux29

2020-11-21 21:41:29 +08:00

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

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

dorothyREN

2020-11-22 10:26:27 +08:00

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

zczy

2020-11-22 16:08:36 +08:00 via Android

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

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

zczy

2020-11-22 16:09:10 +08:00 via Android

@dorothyREN 这个是个人开源项目

yzdobest

2020-11-22 22:23:14 +08:00 via Android

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

zczy

2020-11-23 11:26:53 +08:00

@yzdobest 我个人设想是这样的

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

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

然后数据源是干净的，不是脱敏后的数据