September 23, 2016 at 16:54

Implement Row Level Security on MySQL

Hi Habr! I happened to implement a business process that involved row-level security in mysql and php.

image

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

Because Most of the time I program on Oracle, I decided that it would be most optimal to implement this in the database.

We have MySQL 5.1.73 with triggers, view, stored functions and procedures on a shared hosting.

In the application 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 during authorization in php

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

and cleared when the php script ends

	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:

    1. Storekeeper №1 user_id = 1, has access to view documents “Warehouse Moscow”, to view and edit documents “Warehouse Novosibirsk”
    2. Storekeeper №2 user_id = 2, has access to view documents "Warehouse Novosibirsk", to view and edit documents "Warehouse Moscow"
    3. Director user_id = 3, has access to view the documents “Warehouse Novosibirsk” and “Warehouse Moscow”
    4. Accountant user_id = 4, has access to view and edit documents “Warehouse Novosibirsk” and “Warehouse Moscow”
    5. Manager No. 1 user_id = 5, has access to view the documents “Warehouse Moscow”
    6. Manager No. 2 user_id = 6, has access to view the 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)

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

    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, rename the target table docs -> t_docs

ALTER TABLE docs RENAME t_docs;

and create the editable VIEW of the same name

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

Now all queries from client applications do not access the table directly, but VIEW

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

Now let's do a simple thing, restrict the viewing in accordance with 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;

Let's see how it worked:

SELECT * FROM docs;
Empty set (0.00 sec)

Nothing came back. Indeed, you must log in. We are authorized Manager No. 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)

He 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)

He sees the documents “Warehouse Moscow” and “Warehouse Novosibirsk”! It seems that everything works as it should. Then we pass to a more difficult task - the restriction on editing. Let's try to log in to Manager # 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

Updated only the lines that we see.

But how do we achieve various 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 the DML commands through VIEW, but this will require rewriting all the DML commands in the application and we will already have 3

t_docs objects - the original
docs table - RLS table for viewing
e_docs - RLS table for editing

Let's try another option, more flexible .

  1. Let's create the get_db_mode function 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 ;

  2. We modify VIEW so that different lines are displayed in the 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;

  3. Now with DML in the BEFORE trigger, we will set the @db_mode variable to 'edit' , and in the AFTER trigger in '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, we check how everything works: We are

authorized as the Storekeeper No. 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'

Excellent, we can browse, does not give editing. 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 editing mode. Now, having done SELECT, we will see only those lines that 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' for 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 access control logic is very easy to write in one VIEW. Using the same scheme, it is easy to implement various access to INSERT / UPDATE / DELETE operations
Tags:

Also popular now: