Protect Your Data: Row-level Security in MariaDB 10.0

Tue, 2015-06-02 19:07geoff_montee_gMost MariaDB users are probably aware of the privilege system available in MariaDB and MySQL. Privileges control what databases, tables, columns, procedures, and functions a particular user account can access. For example, if an application stored credit card data in the database, this data should probably be protected from most users. To make that happen, the DBA might disallow access to the table or column storing this sensitive data.
However, sometimes the privilege system isn’t sufficient to secure data. Sometimes data needs to be secured beyond tables and columns. In those cases, row-level security (sometimes abbreviated RLS) may be necessary. Possible use cases for row-level security are:
A government agency might only allow a user to see a row based on classification (CONFIDENTIAL, SECRET, TOP SECRET) and other factors.
An e-commerce site storing credit card information might only allow users to see the credit cards tied to their account.
A hospital or clinic might only allow staff to see records for patients that they are authorized to see.
A regional store manager might only be able to see employment records for employees and inventory records for stores in their region.
MariaDB’s privilege system does not support row-level privileges, so developers and DBAs need to find another way to implement row-level security.
Sometimes, the row-level security logic is taken care of by the application. Other times, it can be more effective or better design to put the row-level security logic into the database. For example, if multiple applications use the same database, it might be better for the database to handle security. That way, the security functionality only has to be designed once, and it works the same for every application.
In this blog post, I will show a very simple way to implement row-level security in MariaDB 10.0 using the following features:
Views
Stored functions
Privileges
Anonymous accounts
Of course, this is just a simple example. This is not the only way to implement row-level security in MariaDB.
Security Labels and Policies
To implement row-level security, you need two things:
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.
Some rules or policies that outline which users can see data labelled with each security label.
Real world security labels and policies can be very complicated. There might be a hierarchical system of labels, or there might be several groups of labels that contribute different authorization information to the policy.
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.
CREATE TABLE accesses.security_labels (
id INT AUTO_INCREMENT PRIMARY KEY,
security_label VARCHAR(50),
label_value BIT(5)
);
INSERT INTO accesses.security_labels (security_label, label_value) VALUES
(‘red’, b’00001′),
(‘blue’, b’00010′),
(‘green’, b’00100′),
(‘yellow’, b’01000′),
(‘purple’, b’10000′);
Third, let’s store the actual access levels for the user accounts.
CREATE TABLE accesses.user_accesses (
id INT AUTO_INCREMENT PRIMARY KEY,
user VARCHAR(50),
access_label_values BIT(5)
);
INSERT INTO accesses.user_accesses (user, access_label_values) VALUES
(‘root@localhost’, b’11111′),
(‘alice@localhost’, b’00011′),
(‘bob@localhost’, b’11100′),
(‘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.
DELIMITER //
CREATE FUNCTION accesses.access_check (v_user VARCHAR(50), v_security_label VARCHAR(50)) RETURNS BOOLEAN
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
SELECT label_value INTO @v_label_value
FROM accesses.security_labels
WHERE security_label = v_security_label;
SELECT @v_label_value & access_label_values INTO @v_label_check
FROM accesses.user_accesses WHERE user = v_user;
IF @v_label_check = @v_label_value THEN
RETURN true;
ELSE
RETURN false;
END IF;
END
//
DELIMITER ;
Now, let’s test out the function with a few user and label combinations.
MariaDB [(none)]> SELECT accesses.access_check(‘alice@localhost’, ‘red’);
+————————————————-+
| accesses.access_check(‘alice@localhost’, ‘red’) |
+————————————————-+
| 1 |
+————————————————-+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT accesses.access_check(‘alice@localhost’, ‘blue’);
+————————————————–+
| accesses.access_check(‘alice@localhost’, ‘blue’) |
+————————————————–+
| 1 |
+————————————————–+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT accesses.access_check(‘alice@localhost’, ‘green’);
+—————————————————+
| accesses.access_check(‘alice@localhost’, ‘green’) |
+—————————————————+
| 0 |
+—————————————————+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT accesses.access_check(‘bob@localhost’, ‘red’);
+———————————————–+
| accesses.access_check(‘bob@localhost’, ‘red’) |
+———————————————–+
| 0 |
+———————————————–+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT accesses.access_check(‘bob@localhost’, ‘blue’);
+————————————————+
| accesses.access_check(‘bob@localhost’, ‘blue’) |
+————————————————+
| 0 |
+————————————————+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT accesses.access_check(‘bob@localhost’, ‘green’);
+————————————————-+
| accesses.access_check(‘bob@localhost’, ‘green’) |
+————————————————-+
| 1 |
+————————————————-+
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.
CREATE DATABASE unprotected;
CREATE TABLE unprotected.important_data (
id INT AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(50),
security_label VARCHAR(50)
);
INSERT INTO unprotected.important_data (data, security_label) VALUES
(‘correct’, ‘red’),
(‘horse’, ‘blue’),
(‘battery’, ‘green’),
(‘stapler’, ‘yellow’),
(‘correcter’, ‘purple’);
Second, let’s create a view that queries the unprotected table in a secure manner.
CREATE DATABASE protected;
CREATE SQL SECURITY DEFINER
VIEW protected.important_data
AS
SELECT *
FROM unprotected.important_data uid
WHERE accesses.access_check(SESSION_USER(), uid.security_label)
WITH CHECK OPTION;
Some things to note here:
The protected.important_data view queries the unprotected.important_data table.
The view adds a WHERE clause that filters the results based on the accesses of SESSION_USER().
SESSION_USER() has to be used, rather than CURRENT_USER(), since the view is defined with SQL SECURITY DEFINER.
SQL SECURITY DEFINER has to be used, since the view’s invoker (i.e. a normal user) usually won’t have privileges to directly access the unprotected.important_data table or the accesses.access_check function. (Giving regular users direct access to these objects may allow ways to bypass the security mechanisms.)
The WITH CHECK OPTION makes it so that users can only insert and update data that they are authorized to see. Depending on the type of data, if a user is inserting data that they aren’t authorized to see, it could mean that a security incident of some kind (potentially outside the database) has already occurred, which allowed the user to receive that data.
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.
CREATE USER ”@’localhost’;
GRANT SELECT, INSERT, UPDATE, DELETE ON protected.* TO ”@’localhost’;
Now we can log in as any user to this database.
[gmontee@localhost ~]$ mysql -u alice –execute="SELECT SESSION_USER(), CURRENT_USER();"
+—————–+—————-+
| SESSION_USER() | CURRENT_USER() |
+—————–+—————-+
| alice@localhost | @localhost |
+—————–+—————-+
[gmontee@localhost ~]$ mysql -u bob –execute="SELECT SESSION_USER(), CURRENT_USER();"
+—————-+—————-+
| SESSION_USER() | CURRENT_USER() |
+—————-+—————-+
| bob@localhost | @localhost |
+—————-+—————-+
Now let’s test out some queries using different user accounts.
[gmontee@localhost ~]$ mysql -u root –execute="SELECT * FROM protected.important_data"
+—-+———–+—————-+
| id | data | security_label |
+—-+———–+—————-+
| 1 | correct | red |
| 2 | horse | blue |
| 3 | battery | green |
| 4 | stapler | yellow |
| 5 | correcter | purple |
+—-+———–+—————-+
[gmontee@localhost ~]$ mysql -u alice –execute="SELECT * FROM protected.important_data"
+—-+———+—————-+
| id | data | security_label |
+—-+———+—————-+
| 1 | correct | red |
| 2 | horse | blue |
+—-+———+—————-+
[gmontee@localhost ~]$ mysql -u bob –execute="SELECT * FROM protected.important_data"
+—-+———–+—————-+
| id | data | security_label |
+—-+———–+—————-+
| 3 | battery | green |
| 4 | stapler | yellow |
| 5 | correcter | purple |
+—-+———–+—————-+
[gmontee@localhost ~]$ mysql -u trudy –execute="SELECT * FROM protected.important_data"
[gmontee@localhost ~]$ mysql -u alice –execute="SELECT * FROM protected.important_data WHERE security_label=’purple’"
[gmontee@localhost ~]$ mysql -u alice –execute="SELECT * FROM protected.important_data WHERE security_label=’red’"
+—-+———+—————-+
| id | data | security_label |
+—-+———+—————-+
| 1 | correct | red |
+—-+———+—————-+
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?
[gmontee@localhost ~]$ mysql -u root –execute="SELECT * FROM unprotected.important_data"
+—-+———–+—————-+
| id | data | security_label |
+—-+———–+—————-+
| 1 | correct | red |
| 2 | horse | blue |
| 3 | battery | green |
| 4 | stapler | yellow |
| 5 | correcter | purple |
+—-+———–+—————-+
[gmontee@localhost ~]$ mysql -u alice –execute="SELECT * FROM unprotected.important_data"
ERROR 1142 (42000) at line 1: SELECT command denied to user ”@’localhost’ for table ‘important_data’
[gmontee@localhost ~]$ mysql -u bob –execute="SELECT * FROM unprotected.important_data"
ERROR 1142 (42000) at line 1: SELECT command denied to user ”@’localhost’ for table ‘important_data’
[gmontee@localhost ~]$ mysql -u trudy –execute="SELECT * FROM unprotected.important_data"
ERROR 1142 (42000) at line 1: SELECT command denied to user ”@’localhost’ for table ‘important_data’
The root account can query the original table, but our other accounts don’t have sufficient privileges.
Conclusion
Although MariaDB 10.0 doesn’t have a built-in row-level security mechanism, it is still fairly easy to implement row-level security with built-in features.
Has anyone been using row-level security implementations in MariaDB? Do you have any suggestions on how to improve MariaDB to make this better?
Tags: DBADeveloperHowtoSecurity
About the Author
Geoff Montee is a Support Engineer with MariaDB. He has previous experience as a Database Administrator/Software Engineer with the U.S. Government, and as a System Administrator and Software Developer at Florida State University.
via Planet MySQL
Protect Your Data: Row-level Security in MariaDB 10.0