pingcap / pingcap/tidb

after change role, masking policy does not take efffect immediately

Open
#67,136 6 comments 0 reactions 0 assignees View on GitHub
affects-8.5 feature/developing may-affects-7.1 may-affects-7.5 may-affects-8.1 severity/major sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table payment_details;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| payment_details | CREATE TABLE `payment_details` (
`id` int NOT NULL,
`customer_id` int DEFAULT NULL,
`card_no` varchar(20) DEFAULT NULL /* MASKING POLICY `p_pan_mask` ENABLED */,
`expiry_date` date DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show masking policies for payment_details;
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+---------+--------------+-------------+
| Policy_name | Column_name | Expression | Status | Masking_type | Restrict_on |
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+---------+--------------+-------------+
| p_pan_mask | card_no | CASE WHEN CURRENT_ROLE() NOT IN (_UTF8MB4'`xxx`@`%`') THEN MASK_FULL(`card_no`, _UTF8MB4'*') ELSE `card_no` END | ENABLED | CUSTOM | NONE |
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+---------+--------------+-------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| yyy@% |
+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+-----------------------------------------------+
| Grants for User |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'yyy'@'%' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'yyy'@'%' |
| GRANT 'xxx'@'%' TO 'yyy'@'%' |
+-----------------------------------------------+
3 rows in set (0.00 sec)

mysql> set role xxx;
Query OK, 0 rows affected (0.00 sec)

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `xxx`@`%` |
+----------------+
1 row in set (0.00 sec)

mysql> select * from payment_details;
+----+-------------+----------------+-------------+
| id | customer_id | card_no | expiry_date |
+----+-------------+----------------+-------------+
| 1 | 1 | 23233438477283 | 2030-05-06 |
+----+-------------+----------------+-------------+
1 row in set (0.00 sec)

mysql> set role none;
Query OK, 0 rows affected (0.00 sec)

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)

mysql> select * from payment_details;
+----+-------------+----------------+-------------+
| id | customer_id | card_no | expiry_date |
+----+-------------+----------------+-------------+
| 1 | 1 | 23233438477283 | 2030-05-06 |
+----+-------------+----------------+-------------+
1 row in set (0.01 sec)
```

### 2. What did you expect to see? (Required)

In the last two step, current role is set to none, but `select * from payment_details` can still see the unmasked data!

### 3. What did you see instead (Required)

After set role to none, it should be:

```
mysql> select * from payment_details;
+----+-------------+----------------+-------------+
| id | customer_id | card_no | expiry_date |
+----+-------------+----------------+-------------+
| 1 | 1 | ************** | 2030-05-06 |
+----+-------------+----------------+-------------+
1 row in set (0.00 sec)
```

### 4. What is your TiDB version? (Required)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.