Implementing Row Level Security on MySQL

Hi Habr! I happened to implement a business process that assumed row-level security ( Row Level Security ) on mysql and php.

Row Level Security or row level security is a mechanism for restricting access to information to a database, which allows users to restrict access to individual rows in tables.

Since most of the time I program in Oracle, I decided that it would be best to implement this in a database.
')
We have MySQL 5.1.73 with triggers, view, stored functions and procedures on a regular virtual hosting.

Attached table auth_users

CREATE TABLE `auth_users`

CREATE TABLE `auth_users` ( `conn_id` bigint(20) NOT NULL, `user_id` int(11) NOT NULL, `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`conn_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='    ';

which is filled in when logging in to php

 REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = :user_id

and cleared at the completion of the php script

  public static function user_logout(){ //   auth_users app()->db->query("DELETE FROM auth_users WHERE conn_id = CONNECTION_ID()"); } ... register_shutdown_function(array('Auth', 'user_logout'));

Example data schema:

directory of organizations

CREATE TABLE `organizations`

 CREATE TABLE `organizations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `type` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''; INSERT INTO organizations (id, name, type) VALUES (1, ' ', ''), (2, ' ', '');

 SELECT * FROM organizations; +----+-----------------------------------+------------+ | id | name | type | +----+-----------------------------------+------------+ | 1 |   |  | | 2 |   |  | +----+-----------------------------------+------------+ 2 rows in set (0.00 sec)

access setting:

Storekeeper number 1 user_id = 1, has access to view documents "Warehouse Moscow", to view and edit documents "Warehouse Novosibirsk"
Storekeeper number 2 user_id = 2, has access to view documents "Warehouse Novosibirsk", to view and edit documents "Warehouse Moscow"
Director user_id = 3, has access to view documents "Warehouse Novosibirsk" and "Warehouse Moscow"
Accountant user_id = 4, has access to view and edit documents "Warehouse Novosibirsk" and "Warehouse Moscow"
Manager number 1 user_id = 5, has access to view documents "Warehouse Moscow"
Manager number 2 user_id = 6, has access to view documents "Warehouse Novosibirsk"

CREATE TABLE `user_access`

 CREATE TABLE `user_access` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `warehouse_org_id` int(11) NOT NULL, `edit` tinyint(1), PRIMARY KEY (`id`), CONSTRAINT `user_access_ibfk_1` FOREIGN KEY (`warehouse_org_id`) REFERENCES `organizations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' '; INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (1, 1, NULL), (1, 2, 1); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (2, 1, 1), (2, 2, NULL); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (3, 1, NULL), (3, 2, NULL); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (4, 1, 1), (4, 2, 1); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (5, 1, NULL); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (6, 2, NULL);

 SELECT * FROM user_access; +----+---------+------------------+------+ | id | user_id | warehouse_org_id | edit | +----+---------+------------------+------+ | 1 | 1 | 1 | NULL | | 2 | 1 | 2 | 1 | | 3 | 2 | 1 | 1 | | 4 | 2 | 2 | NULL | | 5 | 3 | 1 | NULL | | 6 | 3 | 2 | NULL | | 7 | 4 | 1 | 1 | | 8 | 4 | 2 | 1 | | 9 | 5 | 1 | NULL | | 10 | 6 | 2 | NULL | +----+---------+------------------+------+ 10 rows in set (0.00 sec)

the table of documents contains a field warehouse (by which we will delimit access) and other document attributes

CREATE TABLE `docs`

 CREATE TABLE `docs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `warehouse_org_id` int(11) NOT NULL, `sum` int(11), PRIMARY KEY (`id`), CONSTRAINT `docs_ibfk_1` FOREIGN KEY (`warehouse_org_id`) REFERENCES `organizations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' '; DELETE FROM docs; INSERT INTO docs (id, warehouse_org_id, sum) VALUES (1, 1, 10000), (2, 2, 5000);

 SELECT * FROM docs; +----+------------------+-------+ | id | warehouse_org_id | sum | +----+------------------+-------+ | 1 | 1 | 10000 | | 2 | 2 | 5000 | +----+------------------+-------+ 2 rows in set (0.00 sec)

So, let's start configuring RLS: first, let's rename the target table docs -> t_docs

 ALTER TABLE docs RENAME t_docs;

and create the same editable VIEW

 CREATE OR REPLACE VIEW docs AS SELECT id, warehouse_org_id, sum FROM t_docs WITH CHECK OPTION;

Now all requests from client applications are not addressed directly to the table, but to VIEW

Important! If the system has functions, procedures, queries that do not need to restrict access to the table, then it is necessary to register the table directly, i.e. t_docs For example, these may be procedures for calculating debts / balances throughout the system.

Now we will do a simple thing, we will limit the view in accordance with the access control.

 CREATE OR REPLACE VIEW docs AS SELECT id, warehouse_org_id, sum FROM t_docs d WHERE EXISTS ( SELECT NULL FROM auth_users INNER JOIN user_access ON user_access.user_id = auth_users.user_id AND auth_users.conn_id = CONNECTION_ID() WHERE d.warehouse_org_id = user_access.warehouse_org_id ) WITH CHECK OPTION;

Check out how it worked:

 SELECT * FROM docs; Empty set (0.00 sec)

Nothing has returned. Indeed, you need to log in. Sign in Manager №1 user_id = 5

 DELETE FROM auth_users; REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 5; SELECT * FROM docs; +----+------------------+-------+ | id | warehouse_org_id | sum | +----+------------------+-------+ | 1 | 1 | 10000 | +----+------------------+-------+ 1 row in set (0.00 sec)

Sees only the documents "Warehouse Moscow". Log in Director user_id = 3

 DELETE FROM auth_users; REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 3; SELECT * FROM docs; +----+------------------+-------+ | id | warehouse_org_id | sum | +----+------------------+-------+ | 1 | 1 | 10000 | | 2 | 2 | 5000 | +----+------------------+-------+ 2 rows in set (0.00 sec)

Sees the documents "Warehouse Moscow" and "Warehouse Novosibirsk"! It seems everything works as it should. Then go to a more difficult task - the restriction on editing. Let's try to log in Manager number 1 user_id = 5 and edit the documents:

 DELETE FROM auth_users; REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 5; UPDATE docs SET sum = 20000 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 UPDATE docs SET sum = 15000 WHERE id = 2; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0

Only the lines that are visible were updated.

But how do we achieve different rights to view and edit? You can add another VIEW e_docs

 CREATE OR REPLACE VIEW e_docs AS SELECT id, warehouse_org_id, sum FROM t_docs d WHERE EXISTS ( SELECT NULL FROM auth_users INNER JOIN user_access ON user_access.user_id = auth_users.user_id AND auth_users.conn_id = CONNECTION_ID() WHERE d.warehouse_org_id = user_access.warehouse_org_id AND user_access.edit = 1 ) WITH CHECK OPTION;

and let all DML commands run through this VIEW, but this will require rewriting all DML commands in the application and we will have 3 objects

t_docs - source table
docs - RLS table for viewing
e_docs - RLS table for editing

Let's try another option, more flexible.

Create a function get_db_mode to display the current mode - view / edit

 DELIMITER $ CREATE FUNCTION get_db_mode() RETURNS VARCHAR(20) BEGIN IF @db_mode = 'edit' THEN RETURN 'edit'; ELSE RETURN 'show'; END IF; END $ DELIMITER ;

Modify VIEW to display different lines in view / edit mode.

 CREATE OR REPLACE VIEW docs AS SELECT id, warehouse_org_id, sum FROM t_docs d WHERE EXISTS ( SELECT NULL FROM auth_users INNER JOIN user_access ON user_access.user_id = auth_users.user_id AND auth_users.conn_id = CONNECTION_ID() WHERE d.warehouse_org_id = user_access.warehouse_org_id AND (get_db_mode() = 'show' OR user_access.edit = 1 AND get_db_mode() = 'edit') ) WITH CHECK OPTION;

Now with DML in BEFORE the trigger we will set the @db_mode variable to 'edit' , and in AFTER the trigger to 'show'

CREATE TRIGGERS

 DELIMITER $ CREATE TRIGGER `docs_bef_ins_trg` BEFORE INSERT ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'edit'; END $ CREATE TRIGGER `docs_bef_upd_trg` BEFORE UPDATE ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'edit'; END $ CREATE TRIGGER `docs_bef_del_trg` BEFORE DELETE ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'edit'; END $ CREATE TRIGGER `docs_aft_ins_trg` AFTER INSERT ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'show'; END $ CREATE TRIGGER `docs_aft_upd_trg` AFTER UPDATE ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'show'; END $ CREATE TRIGGER `docs_aft_del_trg` AFTER DELETE ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'show'; END $ DELIMITER ;

Voila, check how everything works:

Log in Warehouse number 1 user_id = 1

 DELETE FROM auth_users; REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 1; SELECT get_db_mode(); +---------------+ | get_db_mode() | +---------------+ | show | +---------------+ 1 row in set (0.00 sec) SELECT * FROM docs; +----+------------------+-------+ | id | warehouse_org_id | sum | +----+------------------+-------+ | 1 | 1 | 20000 | | 2 | 2 | 5000 | +----+------------------+-------+ 2 rows in set (0.00 sec) UPDATE docs SET sum = 105000 WHERE id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 SELECT get_db_mode(); +---------------+ | get_db_mode() | +---------------+ | show | +---------------+ 1 row in set (0.00 sec) SELECT * FROM docs; +----+------------------+--------+ | id | warehouse_org_id | sum | +----+------------------+--------+ | 1 | 1 | 20000 | | 2 | 2 | 105000 | +----+------------------+--------+ 2 rows in set (0.01 sec) UPDATE docs SET sum = 205000 WHERE id = 1; ERROR 1369 (HY000): CHECK OPTION failed '3006309-habr.docs'

Great, we can view, edit does not. But not everything is so smooth:

 SELECT get_db_mode(); +---------------+ | get_db_mode() | +---------------+ | edit | +---------------+ 1 row in set (0.00 sec)

After the error, the AFTER trigger did not work and did not remove the edit mode. Now having made SELECT we will see only those lines which we can edit.

 SELECT * FROM docs; +----+------------------+--------+ | id | warehouse_org_id | sum | +----+------------------+--------+ | 2 | 2 | 105000 | +----+------------------+--------+ 1 row in set (0.00 sec)

One solution is try ... catch PDO in php and force SET @db_mode = 'show' on any error

Scripts for deleting test objects

 DROP TABLE IF EXISTS auth_users; DROP TABLE IF EXISTS organizations; DROP TABLE IF EXISTS user_access; DROP TABLE IF EXISTS docs; DROP TABLE IF EXISTS t_docs; DROP VIEW IF EXISTS docs; DROP FUNCTION IF EXISTS get_db_mode;

Now, all the access control logic is very easy to put in one VIEW. According to the same scheme, it is easy to implement various access on INSERT / UPDATE / DELETE operations.

Source: https://habr.com/ru/post/310832/

All Articles

Implementing Row Level Security on MySQL

More articles: