[MXS-1255] Reveals masked column with 'concat' function Created: 2017-05-01  Updated: 2017-05-02  Resolved: 2017-05-02

Status: Closed
Project: MariaDB MaxScale
Component/s: masking
Affects Version/s: 2.1.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Atsushi KANAYA Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None

Attachments: File masking_rules.json     File maxscale.cnf     File test.sql    

 Description   

Masked column values are revealed with sql function CONCAT.

For example, masking table with masked column `lastname`,
select statement returns masked value for lastname.
But using CONCAT function, values are revealed.

mysql> SELECT id, firstname, lastname FROM masking;
 
+----+-----------+----------+
 
| id | firstname | lastname |
 
+----+-----------+----------+
 
|  1 | test1     | XXXXX    |
 
|  2 | test2     | XXXXX    |
 
+----+-----------+----------+
 
 
 
mysql> SELECT id, firstname, CONCAT(lastname) FROM masking;
 
+----+-----------+------------------+
 
| id | firstname | CONCAT(lastname) |
 
+----+-----------+------------------+
 
|  1 | test1     | mask1            |
 
|  2 | test2     | mask2            |
 
+----+-----------+------------------+
 
2 rows in set (0.00 sec)



 Comments   
Comment by Johan Wikman [ 2017-05-02 ]

That observation is correct and is actually documented behaviour.

From the documentation

Note that he masking filter alone is not sufficient for preventing access to a particular column. As the masking filter works on the column name alone a query like

> SELECT name, concat(ssn) FROM person;
will reveal the value. Also, executing a query like

> SELECT name FROM person WHERE ssn = ...;
a sufficient number of times with different ssn values, will, eventually, reveal the social security number of all persons in the database.

For a secure solution, the masking filter must be combined with the firewall filter to prevent the use of functions and the use of particular columns in where-clauses.

However, we are aware that it is quite inconvenient currently to do that, so I just created the task https://jira.mariadb.org/browse/MXS-1256 for providing something more convenient.

Comment by Johan Wikman [ 2017-05-02 ]

Currently this is not a bug, but documented behaviour.

Will be further address in MXS-1256

Generated at Thu Feb 08 04:05:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.