Protect Your Data #2: A Row-level Security Walkthrough in MariaDB 10.0

My last row-level security blog post got a few questions, so I decided that it would be good to follow up with more detail. The last blog post described some basic information about row-level security, but row-level security policies are highly dependent on an application’s or organization’s security requirements. In this blog post, I’m going to walk through an example row-level security implementation in MariaDB 10.0 in a little more detail.

In this blog post, I’ll do the following:

  1. Describe a fictional application.
  2. Outline a set of row-level security requirements for the application.
  3. Show how to meet the security requirements by securing the relevant parts of the application’s schema.

The Application

In this example, my fictional application is an e-commerce site that has a blog and an an online storefront. There’s nothing unordinary about this application: this description could match countless others, including the web sites of many small businesses.

What would an application like this gain from having row-level security built in to the database? Some potential benefits are:

  • Having row-level security in the databse provides extra protection against SQL injection.
  • If the application has multiple front-ends (e.g. web, mobile, desktop), it can simplify development of each front-end if the row-level security functionality is in the database rather than the application.
  • If the database itself has row-level security, you can provide some users (such as developers) direct access to the database (e.g. via mysql client, MySQL Workbench, phpMyAdmin, SQLyog, etc.) without letting the user see every single row.

The Row-level Security Requirements

My fictional application does not have a lot of sensitive data, so there are not many row-level security requirements. The requirements are:

  1. I want to protect unpublished proprietary information, so for the table that stores blog posts, only company employees should see unpublished blog posts.
  2. I want to protect customers’ orders, so for the table that stores orders, a customer should only see their orders.
  3. I want to protect customers’ credit cards, so for the table that stores credit cards, a customer should only see their credit cards.

The Existing Schema

An e-commerse site like this would most likely have many tables that won’t have to be changed to implement these few row-level security requirements. For this blog post, I will ignore those. Let’s say that the parts of our schema that we care about look like this:

CREATE DATABASE webapp;
USE webapp;
CREATE TABLE user ( 
user_id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
username varchar(100), 
password varchar(100), -- this should be a hash, not a clear-text 
password type ENUM('CUSTOMER', 'EMPLOYEE') DEFAULT 'CUSTOMER' 
);
CREATE TABLE post ( 
post_id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
title varchar(200), 
body longtext, 
is_published boolean, 
user_id int REFERENCES user (user_id) 
);
CREATE TABLE credit_card ( 
credit_card_id int NOT NULL AUTO_INCREMENT PRIMARY KEY, cc_number varchar(20), exp_month int, exp_year int, security_code int, user_id int REFERENCES user (user_id) );
CREATE TABLE customer_order ( 
order_id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
time_placed datetime, 
amount decimal(65,2), 
shipping_address varchar(500), 
billing_address varchar(500), 
user_id int REFERENCES user (user_id), 
credit_card_id int REFERENCES credit_card (credit_card_id) 
); 

The Row-level Security Design

Who’s the user?

In order to secure data from unauthorized users, we need to know who is using the database at a particular moment. In my last blog post, I assumed that every user would have a unique database account, so I used SESSION_USER() directly in the row-level security policies. However, this is impractical in this case. Most e-commerce sites use a connection pool authenticated with a single database user account for the whole application. That means that before we can design our row-level security policies, we need a way to keep track of which user is using a particular MariaDB connection at a particular time.

Let’s say our application does this by updating a session table when it grabs a MariaDB connection on behalf of a user:

CREATE TABLE session ( 
connection_id int NOT NULL PRIMARY KEY, 
username varchar(100) 
);

However, if regular users have direct access to this session table, they could potentially alter their connection’s record (e.g. via SQL injection). To prevent this, we are only going to allow the application to change this table through function calls that will require a security token. e.g.:

CREATE TABLE security_token ( 
security_token_id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
username varchar(100), 
security_token varchar(100) 
);
DELIMITER //
CREATE FUNCTION set_connection_auth (v_user VARCHAR(100), v_security_token VARCHAR(100)) 
RETURNS BOOLEAN 
NOT DETERMINISTIC 
MODIFIES SQL DATA 
SQL SECURITY DEFINER 
BEGIN 
SELECT COUNT(*) INTO @v_token_count 
FROM security_token 
WHERE username = SESSION_USER() AND security_token = v_security_token;
IF @v_token_count < 1 THEN
RETURN false; 
END IF;
INSERT INTO session (connection_id, username) VALUES (CONNECTION_ID(), v_user);
RETURN true; 
END //
CREATE FUNCTION unset_connection_auth (v_security_token VARCHAR(100)) 
RETURNS BOOLEAN 
NOT DETERMINISTIC 
MODIFIES SQL DATA 
SQL SECURITY DEFINER 
BEGIN 
DELETE FROM session WHERE connection_id = CONNECTION_ID();
RETURN true; 
END //
DELIMITER ;

Our functions above use the CONNECTION_ID() function to fill in the connection_id field for the session table. We will use this function again later.

Protecting Unpublished Blog Posts

Our first row-level security requirement is: how do we prevent non-employees from gaining access to unpublished posts? To carry out this policy, the data we need is: whether a user is an employee (the type field from the user table) and whether a blog is published (the is_published field from the post table). We can wrap this policy in a simple view:

CREATE 
SQL SECURITY DEFINER 
VIEW protected_post AS 
SELECT p.* FROM post p -- 1.) Get row from session table for current connection 
JOIN session s ON s.connection_id = CONNECTION_ID() -- 2.) Get row from user table for user using current connection 
JOIN user u ON u.username = s.username 
WHERE 
-- 3.) If user is an employee, allow all blog posts 
((u.type = 'EMPLOYEE') 
-- 4.) If user is a customer, only allow published blog posts 
OR (u.type = 'CUSTOMER' AND p.is_published = true)) WITH CHECK OPTION; 

The first JOIN grabs the row in the session table for the current connection. Like we previously did in our functions, we can use the CONNECTION_ID() function to do this. The second JOIN grabs the row in the user table for the user using the current connection. Then in the WHERE clause, we use the user’s type to determine if the user should or should not see unpublished blogs.

We could also put the row-level security logic into a function if we wanted to do so, like I did in the last blog post. But since this policy will only be used for the single post table, we would not need to use the function more than once anyway, so I won’t do that here.

Protecting Customer Orders and Credit Cards

Our other row-level security requirements are almost identical: users should only be able to see their own orders and credit cards. Since these policies are so similar, there is more incentive to wrap the logic in a function that can be re-used. To demonstrate, I’ll wrap this policy in a function called owner_check. To carry out this policy, the only data the function will need is: who owns the data? Let’s create a function and some views for these policies:

DELIMITER //
CREATE FUNCTION owner_check (v_owner_user_id int) 
RETURNS BOOLEAN 
NOT DETERMINISTIC 
READS SQL DATA 
SQL SECURITY INVOKER 
BEGIN 
SELECT u.user_id INTO @v_current_user_id 
FROM session s 
JOIN user u 
ON u.username = s.username 
WHERE s.connection_id = CONNECTION_ID();
IF @v_current_user_id = v_owner_user_id THEN 
RETURN true; 
ELSE 
RETURN false; 
END IF; 
END 
DELIMITER ;
CREATE 
SQL SECURITY DEFINER 
VIEW protected_credit_card AS 
SELECT cc.* FROM credit_card cc WHERE owner_check(cc.user_id) 
WITH CHECK OPTION;
CREATE 
SQL SECURITY DEFINER 
VIEW protected_customer_order AS 
SELECT co.* FROM customer_order co WHERE owner_check(co.user_id) 
WITH CHECK OPTION; 

User Privileges

The last piece of the design are the user account privileges. We want to make sure that users can access our protected interfaces that contain row-level security policies, and that they can’t access the underlying restricted tables.

CREATE USER 'webapp_user'@'%' IDENTIFIED BY 'password';
-- Revoke commands below are not needed, since the new user account should not have pre-existing privileges. 
-- These commands are included and commented out to show that privileges for the objects were intentionally left out. 
-- REVOKE ALL PRIVILEGES ON TABLE webapp.post FROM 'webapp_user'@'%'; 
-- REVOKE ALL PRIVILEGES ON TABLE webapp.credit_card FROM 'webapp_user'@'%'; 
-- REVOKE ALL PRIVILEGES ON TABLE webapp.customer_order FROM 'webapp_user'@'%'; 
-- REVOKE ALL PRIVILEGES ON TABLE webapp.session FROM 'webapp_user'@'%'; 
-- REVOKE ALL PRIVILEGES ON TABLE webapp.security_token FROM 'webapp_user'@'%'; 
-- REVOKE ALL PRIVILEGES ON FUNCTION webapp.owner_check FROM 'webapp_user'@'%';
GRANT EXECUTE ON FUNCTION webapp.set_connection_auth TO 'webapp_user'@'%'; 
GRANT EXECUTE ON FUNCTION webapp.unset_connection_auth TO 'webapp_user'@'%'; 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE webapp.protected_post TO 'webapp_user'@'%'; 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE webapp.protected_credit_card TO 'webapp_user'@'%'; 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE webapp.protected_customer_order TO 'webapp_user'@'%'; 

Test Data

In order to test this out, let’s insert some test data:

INSERT INTO security_token (username, security_token) VALUES ('webapp_user@localhost', 'some_security_token');
INSERT INTO user (user_id, username, password, type) VALUES (1, 'alice', '5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', 'EMPLOYEE'); 
INSERT INTO user (user_id, username, password, type) VALUES (2, 'bob', 'e38ad214943daad1d64c102faec29de4afe9da3d', 'CUSTOMER'); 
INSERT INTO user (user_id, username, password, type) VALUES (3, 'eve', '21298df8a3277357ee55b01df9530b535cf08ec1', 'CUSTOMER');
INSERT INTO post (title, body, is_published, user_id) VALUES ('unpublished blog', 'only alice should see this', false, 1); 
INSERT INTO post (title, body, is_published, user_id) VALUES ('published blog', 'everyone should see this', true, 1);
INSERT INTO credit_card (credit_card_id, cc_number, exp_month, exp_year, security_code, user_id) VALUES (1, '4916395911460334', 1, 2014, 111, 2); 
INSERT INTO credit_card (credit_card_id, cc_number, exp_month, exp_year, security_code, user_id) VALUES (2, '4929478351399512', 1, 2015, 222, 3);
INSERT INTO customer_order (time_placed, amount, user_id, credit_card_id) VALUES ('2013-01-01 00:00:00', 1.00, 2, 1); 
INSERT INTO customer_order (time_placed, amount, user_id, credit_card_id) VALUES ('2014-01-01 00:00:00', 2.00, 3, 2); 

Test it out

Now to test it out, let’s try running the following script as the `webapp` user:

SELECT set_connection_auth('alice', 'some_security_token'); 
SELECT 'Looking at blogs as alice' AS current_step; 
SELECT * FROM protected_post; 
SELECT 'Looking at credit cards as alice' AS current_step; 
SELECT * FROM protected_credit_card; 
SELECT 'Looking at customer orders as alice' AS current_step; 
SELECT * FROM protected_customer_order; SELECT unset_connection_auth('some_security_token');
SELECT set_connection_auth('bob', 'some_security_token'); 
SELECT 'Looking at blogs as bob' AS current_step; 
SELECT * FROM protected_post; 
SELECT 'Looking at credit cards as bob' AS current_step; 
SELECT * FROM protected_credit_card; 
SELECT 'Looking at customer orders as bob' AS current_step; 
SELECT * FROM protected_customer_order; 
SELECT unset_connection_auth('some_security_token');
SELECT set_connection_auth('eve', 'some_security_token'); 
SELECT 'Looking at blogs as eve' AS current_step; 
SELECT * FROM protected_post; 
SELECT 'Looking at credit cards as eve' AS current_step; 
SELECT * FROM protected_credit_card; 
SELECT 'Looking at customer orders as eve' AS current_step; 
SELECT * FROM protected_customer_order; 
SELECT unset_connection_auth('some_security_token'); 

Example output:

$ mysql -D webapp -u webapp_user -p < test_script.sql 
Enter password: 
set_connection_auth('alice', 'some_security_token') 
1 
current_step 
Looking at blogs as alice 
post_id title body is_published user_id 
1 unpublished blog only alice should see this 0 1 
2 published blog everyone should see this 1 1 
current_step 
Looking at credit cards as alice 
current_step 
Looking at customer orders as alice 
unset_connection_auth('some_security_token') 
1 
set_connection_auth('bob', 'some_security_token') 
1 
current_step 
Looking at blogs as bob 
post_id title body is_published user_id 
2 published blog everyone should see this 1 1 
current_step 
Looking at credit cards as bob 
credit_card_id cc_number exp_month exp_year security_code user_id 
1 4916395911460334 1 2014 111 2 
current_step 
Looking at customer orders as bob 
order_id time_placed amount shipping_address billing_address user_id credit_card_id 
1 2013-01-01 00:00:00 1.00 NULL NULL 2 1 
unset_connection_auth('some_security_token') 
1 
set_connection_auth('eve', 'some_security_token') 
1 
current_step 
Looking at blogs as eve 
post_id title body is_published user_id 
2 published blog everyone should see this 1 1 
current_step 
Looking at credit cards as eve 
credit_card_id cc_number exp_month exp_year security_code user_id 
2 4929478351399512 1 2015 222 3 
current_step 
Looking at customer orders as eve 
order_id time_placed amount shipping_address billing_address user_id credit_card_id 
2 2014-01-01 00:00:00 2.00 NULL NULL 3 2 
unset_connection_auth('some_security_token') 
1 

Conclusion

As you can see, this row-level security implementation is a little different than the one in my last blog post.

Do you have any tips of your own for implementing row-level security in MariaDB?