Implementing Data Privacy and Row-Level Security Policy in MariaDB

Implementing Data Privacy and Row-Level Security Policy in MariaDB

To implement row-level security, you need two things:

  1. Some way to label the data. This might be the name of the owner of the data, or a classification level (CONFIDENTIAL, SECRET, TOP SECRET), or it might be something else entirely.
  2. Some rules or policies that outline which users can see data labelled with each security label.

In this example, we will use a very simple labelling system. Data will be labelled using colors. For a user to access data labelled with the red security label, the user needs to be granted access to the red security label. For the user to access data labelled blue, the user needs to be granted access to the blue security label. The labels of each color work exactly the same way.

Now, let’s start creating the database objects.

First, let’s create a database to store access information.

CREATE DATABASE accesses;

Second, let’s store the possible security labels. Bitstrings can be a good way to efficiently store a lot of security labels. Each label is assigned a bit field, and then bitwise operations can be used to get/set individual labels from the bitstring.

We will use bitstrings to store the labels that a user can access, so let’s also store the bit field of the label in a BIT column.

  1. CREATE TABLE accesses.security_labels (
  2. id INT AUTO_INCREMENT PRIMARY KEY,
  3. security_label VARCHAR(50),
  4. label_value BIT(5)
  5. );

  7. INSERT INTO accesses.security_labels (security_label, label_value) VALUES
  8. ('red', b'00001'),
  9. ('blue', b'00010'),
  10. ('green', b'00100'),
  11. ('yellow', b'01000'),
  12. ('purple', b'10000');


Third, let’s store the actual access levels for the user accounts.

  1. CREATE TABLE accesses.user_accesses (
  2. id INT AUTO_INCREMENT PRIMARY KEY,
  3. user VARCHAR(50),
  4. access_label_values BIT(5)
  5. );

  7. INSERT INTO accesses.user_accesses (user, access_label_values) VALUES
  8. ('root@localhost', b'11111'),
  9. ('alice@localhost', b'00011'),
  10. ('bob@localhost', b'11100'),
  11. ('trudy@localhost', b'00000');

Fourth, let’s create a stored function to represent our row-level security policy.

The function takes user name X and security label Y, and it returns true if the user is allowed to access the label. Notice that the function uses the bitwise AND (&) operator to get the individual label’s bit field from the bitstring column.

  1. DELIMITER //

  3. CREATE FUNCTION accesses.access_check (v_user VARCHAR(50), v_security_label VARCHAR(50)) 
  4. RETURNS BOOLEAN
  5. NOT DETERMINISTIC
  6. READS SQL DATA
  7. SQL SECURITY INVOKER
  8. BEGIN
  9. SELECT label_value INTO @v_label_value
  10. FROM accesses.security_labels
  11. WHERE security_label = v_security_label;

  13. SELECT @v_label_value & access_label_values INTO @v_label_check
  14. FROM accesses.user_accesses 
  15. WHERE user = v_user;

  17. IF @v_label_check = @v_label_value THEN
  18. RETURN true;
  19. ELSE
  20. RETURN false;
  21. END IF;
  22. END
  23. //

  25. DELIMITER ;


Now, let’s test out the function with a few user and label combinations.
  1. MariaDB [(none)]> SELECT accesses.access_check('alice@localhost', 'red');
  2. +-------------------------------------------------+
  3. | accesses.access_check('alice@localhost', 'red') |
  4. +-------------------------------------------------+
  5. |                                               1          |
  6. +-------------------------------------------------+
  7. 1 row in set (0.00 sec)
  8. MariaDB [(none)]> SELECT accesses.access_check('alice@localhost', 'blue');
  9. +--------------------------------------------------+
  10. | accesses.access_check('alice@localhost', 'blue') |
  11. +--------------------------------------------------+
  12. |                                                1          |
  13. +--------------------------------------------------+
  14. 1 row in set (0.00 sec)
  15. MariaDB [(none)]> SELECT accesses.access_check('alice@localhost', 'green');
  16. +---------------------------------------------------+
  17. | accesses.access_check('alice@localhost', 'green') |
  18. +---------------------------------------------------+
  19. |                                                 0         |
  20. +---------------------------------------------------+
  21. 1 row in set (0.00 sec)
  22. MariaDB [(none)]> SELECT accesses.access_check('bob@localhost', 'red');
  23. +-----------------------------------------------+
  24. | accesses.access_check('bob@localhost', 'red') |
  25. +-----------------------------------------------+
  26. |                                             0         |
  27. +-----------------------------------------------+
  28. 1 row in set (0.00 sec)
  29. MariaDB [(none)]> SELECT accesses.access_check('bob@localhost', 'blue');
  30. +------------------------------------------------+
  31. | accesses.access_check('bob@localhost', 'blue') |
  32. +------------------------------------------------+
  33. |                                              0         |
  34. +------------------------------------------------+
  35. 1 row in set (0.00 sec)
  36. MariaDB [(none)]> SELECT accesses.access_check('bob@localhost', 'green');
  37. +-------------------------------------------------+
  38. | accesses.access_check('bob@localhost', 'green') |
  39. +-------------------------------------------------+
  40. |                                               1          |
  41. +-------------------------------------------------+
  42. 1 row in set (0.00 sec)


PROTECTING THE DATA

Now that the user accounts’ accesses are set up, let’s set up some data to protect.
First, let’s create a regular table with some labeled data.
  1. CREATE DATABASE unprotected;

  3. CREATE TABLE unprotected.important_data (
  4. id INT AUTO_INCREMENT PRIMARY KEY,
  5. data VARCHAR(50),
  6. security_label VARCHAR(50)
  7. );

  9. INSERT INTO unprotected.important_data (data, security_label) VALUES
  10. ('correct', 'red'),
  11. ('horse', 'blue'),
  12. ('battery', 'green'),
  13. ('stapler', 'yellow'),
  14. ('correcter', 'purple');

Second, let’s create a view that queries the unprotected table in a secure manner.

  1. CREATE DATABASE protected;

  3. CREATE 
  4. SQL SECURITY DEFINER
  5. VIEW protected.important_data
  6. AS
  7. SELECT *
  8. FROM unprotected.important_data uid
  9. WHERE accesses.access_check(SESSION_USER(), uid.security_label)
  10. WITH CHECK OPTION;

TESTING THE INTERFACE

Now that everything is set up, let’s create some user accounts and test it out.
First, create an anonymous account and grant it access to the protected database.

  1. CREATE USER ''@'localhost';
  2. GRANT SELECT, INSERT, UPDATE, DELETE ON protected.* TO ''@'localhost';
Now we can log in as any user to this database.

  1. [minervadb@localhost ~]$ mysql -u alice --execute="SELECT SESSION_USER(), CURRENT_USER();"
  2. +-----------------+----------------+
  3. | SESSION_USER()  | CURRENT_USER() |
  4. +-----------------+----------------+
  5. | alice@localhost | @localhost     |
  6. +-----------------+----------------+
  7. [minervadb@localhost ~]$ mysql -u bob --execute="SELECT SESSION_USER(), CURRENT_USER();"
  8. +----------------+----------------+
  9. | SESSION_USER() | CURRENT_USER() |
  10. +----------------+----------------+
  11. | bob@localhost  | @localhost     |
  12. +----------------+----------------+

Now let’s test out some queries using different user accounts.

  1. [minervadb@localhost ~]$ mysql -u root --execute="SELECT * FROM protected.important_data"
  2. +----+-----------+----------------+
  3. | id | data      | security_label |
  4. +----+-----------+----------------+
  5. |  1 | correct   | red            |
  6. |  2 | horse     | blue           |
  7. |  3 | battery   | green          |
  8. |  4 | stapler   | yellow         |
  9. |  5 | correcter | purple         |
  10. +----+-----------+----------------+
  11. [minervadb@localhost ~]$ mysql -u alice --execute="SELECT * FROM protected.important_data"
  12. +----+---------+----------------+
  13. | id | data    | security_label |
  14. +----+---------+----------------+
  15. |  1 | correct | red            |
  16. |  2 | horse   | blue           |
  17. +----+---------+----------------+
  18. [minervadb@localhost ~]$ mysql -u bob --execute="SELECT * FROM protected.important_data"
  19. +----+-----------+----------------+
  20. | id | data      | security_label |
  21. +----+-----------+----------------+
  22. |  3 | battery   | green          |
  23. |  4 | stapler   | yellow         |
  24. |  5 | correcter | purple         |
  25. +----+-----------+----------------+
  26. [minervadb@localhost ~]$ mysql -u trudy --execute="SELECT * FROM protected.important_data"
  27. [minervadb@localhost ~]$ mysql -u alice --execute="SELECT * FROM protected.important_data WHERE security_label='purple'"
  28. [minervadb@localhost ~]$ mysql -u alice --execute="SELECT * FROM protected.important_data WHERE security_label='red'"
  29. +----+---------+----------------+
  30. | id | data    | security_label |
  31. +----+---------+----------------+
  32. |  1 | correct | red            |
  33. +----+---------+----------------+

The row-level security mechanism built into the view appears to work great. But what happens if these users try to query the actual table, rather than the view?

  1. [minervadb@localhost ~]$ mysql -u root --execute="SELECT * FROM protected.important_data"
  2. +----+-----------+----------------+
  3. | id | data      | security_label |
  4. +----+-----------+----------------+
  5. |  1 | correct   | red            |
  6. |  2 | horse     | blue           |
  7. |  3 | battery   | green          |
  8. |  4 | stapler   | yellow         |
  9. |  5 | correcter | purple         |
  10. +----+-----------+----------------+
  11. [minervadb@localhost ~]$ mysql -u alice --execute="SELECT * FROM protected.important_data"
  12. +----+---------+----------------+
  13. | id | data    | security_label |
  14. +----+---------+----------------+
  15. |  1 | correct | red            |
  16. |  2 | horse   | blue           |
  17. +----+---------+----------------+
  18. [minervadb@localhost ~]$ mysql -u bob --execute="SELECT * FROM protected.important_data"
  19. +----+-----------+----------------+
  20. | id | data      | security_label |
  21. +----+-----------+----------------+
  22. |  3 | battery   | green          |
  23. |  4 | stapler   | yellow         |
  24. |  5 | correcter | purple         |
  25. +----+-----------+----------------+
  26. [minervadb@localhost ~]$ mysql -u trudy --execute="SELECT * FROM protected.important_data"
  27. [minervadb@localhost ~]$ mysql -u alice --execute="SELECT * FROM protected.important_data WHERE security_label='purple'"
  28. [minervadb@localhost ~]$ mysql -u alice --execute="SELECT * FROM protected.important_data WHERE security_label='red'"
  29. +----+---------+----------------+
  30. | id | data    | security_label |
  31. +----+---------+----------------+
  32. |  1 | correct | red            |
  33. +----+---------+----------------+

The root account can query the original table, but our other accounts don’t have sufficient privileges.