doris icon indicating copy to clipboard operation
doris copied to clipboard

[Feature](privilege) support data mask in doris internal auth

Open qzsee opened this issue 7 months ago • 44 comments

Proposed changes

Before, data masking could only be managed by ranger, and ranger was not applicable to users in most cases. doris built-in permission management did not have the management function of data masking, and now it is added.

supported data mask policy

    MASK_REDACT("Replace lowercase with 'x', uppercase with 'X', digits with '0'",
        "regexp_replace(regexp_replace(regexp_replace({col},'([A-Z])', 'X'),'([a-z])','x'),'([0-9])','0')"),
    MASK_SHOW_LAST_4("Show last 4 characters; replace rest with 'X'",
        "LPAD(RIGHT({col}, 4), CHAR_LENGTH({col}), 'X')"),
    MASK_SHOW_FIRST_4("Show first 4 characters; replace rest with 'x'",
        "RPAD(LEFT({col}, 4), CHAR_LENGTH({col}), 'X')"),
    MASK_HASH("Hash the value of a varchar with sha256",
        "hex(sha2({col}, 256))"),
    MASK_NULL("Replace with NULL", "NULL"),
    MASK_DATE_SHOW_YEAR("Date: show only year",
        "date_trunc({col}, 'year')"),
    MASK_DEFAULT("Replace with data type default",
        "");
create database test;

CREATE TABLE `stu` (
  `id` INT NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `phone` INT NOT NULL,
  `birth` DATETIME NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(`id`, `name`)
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
); 
  1. add data mask policy
create data mask policy test on internal.test.stu.phone to 'jack' using MASK_DEFAULT;
create data mask policy test1 on internal.test.stu.id to 'jack' using MASK_NULL;
create data mask policy test2 on internal.test.stu.name to 'jack' using MASK_HASH;
  1. show data mask policy
MySQL [test]> show data mask policy;
+------------+-------------+--------+-----------+------------+--------------+-----------------------+------+------+
| PolicyName | CatalogName | DbName | TableName | ColumnName | DataMaskType | DataMaskDef           | User | Role |
+------------+-------------+--------+-----------+------------+--------------+-----------------------+------+------+
| test       | internal    | test   | stu       | phone      | MASK_DEFAULT |                       | jack | NULL |
| test1      | internal    | test   | stu       | id         | MASK_NULL    | NULL                  | jack | NULL |
| test2      | internal    | test   | stu       | name       | MASK_HASH    | hex(sha2({col}, 256)) | jack | NULL |
+------------+-------------+--------+-----------+------------+--------------+-----------------------+------+------+
  1. drop data mask policy
MySQL [test]> drop data mask policy test;
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> show data mask policy;
+------------+-------------+--------+-----------+------------+--------------+-----------------------+------+------+
| PolicyName | CatalogName | DbName | TableName | ColumnName | DataMaskType | DataMaskDef           | User | Role |
+------------+-------------+--------+-----------+------------+--------------+-----------------------+------+------+
| test1      | internal    | test   | stu       | id         | MASK_NULL    | NULL                  | jack | NULL |
| test2      | internal    | test   | stu       | name       | MASK_HASH    | hex(sha2({col}, 256)) | jack | NULL |
+------------+-------------+--------+-----------+------------+--------------+-----------------------+------+------+
  1. explain
MySQL [test]> explain select * from stu;
+------------------------------------------------------------------------------+
| Explain String(Nereids Planner)                                              |
+------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                              |
|   OUTPUT EXPRS:                                                              |
|     id[#4]                                                                   |
|     name[#5]                                                                 |
|     phone[#6]                                                                |
|     birth[#7]                                                                |
|   PARTITION: UNPARTITIONED                                                   |
|                                                                              |
|   HAS_COLO_PLAN_NODE: false                                                  |
|                                                                              |
|   VRESULT SINK                                                               |
|      MYSQL_PROTOCAL                                                          |
|                                                                              |
|   1:VEXCHANGE                                                                |
|      offset: 0                                                               |
|      distribute expr lists:                                                  |
|                                                                              |
| PLAN FRAGMENT 1                                                              |
|                                                                              |
|   PARTITION: HASH_PARTITIONED: id[#0]                                        |
|                                                                              |
|   HAS_COLO_PLAN_NODE: false                                                  |
|                                                                              |
|   STREAM DATA SINK                                                           |
|     EXCHANGE ID: 01                                                          |
|     UNPARTITIONED                                                            |
|                                                                              |
|   0:VOlapScanNode(68)                                                        |
|      TABLE: test.stu(stu), PREAGGREGATION: ON                                |
|      partitions=1/1 (stu)                                                    |
|      tablets=1/1, tabletList=28797                                           |
|      cardinality=1, avgRowSize=0.0, numNodes=1                               |
|      pushAggOp=NONE                                                          |
|      final projections: NULL, hex(sha2(name[#1], 256)), phone[#2], birth[#3] |
|      final project output tuple id: 1                                        |
+------------------------------------------------------------------------------+

Issue Number: close #xxx

qzsee avatar Jul 24 '24 08:07 qzsee