[Из песочницы] Реализация Row Level Security на MySQL23.09.2016 17:33

Привет Хабр! Мне довелось реализовать бизнес-процесс, который предполагал безопасность на уровне строк (Row Level Security) на mysql и php.

image

Row Level Security или безопасность на уровне строк — механизм разграничения доступа к информации к БД, позволяющий ограничить доступ пользователей к отдельным строкам в таблицах.

Т.к. большую часть времени я программирую на Oracle, то решил, что наиболее оптимально реализовать это в БД.

Имеем MySQL 5.1.73 с триггерами, view, хранимыми функциями и процедурами на обычном виртуальном хостинге.

В приложении таблица 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='Авторизованные пользователи в текущий момент';

которая заполняется при авторизации в php
REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = :user_id

и очищается при завершении php-скрипта
	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'));

Пример схемы данных:
  • справочник организаций
    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)

  • настройка доступа:
    1. Кладовщик №1 user_id = 1, имеет доступ на просмотр документов «Склад Москва», на просмотр и редактирование документов «Склад Новосибирск»
    2. Кладовщик №2 user_id = 2, имеет доступ на просмотр документов «Склад Новосибирск», на просмотр и редактирование документов «Склад Москва»
    3. Директор user_id = 3, имеет доступ на просмотр документов «Склад Новосибирск» и «Склад Москва»
    4. Бухгалтер user_id = 4, имеет доступ на просмотр и редактирование документов «Склад Новосибирск» и «Склад Москва»
    5. Менеджер №1 user_id = 5, имеет доступ на просмотр документов «Склад Москва»
    6. Менеджер №2 user_id = 6, имеет доступ на просмотр документов «Склад Новосибирск»

    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)

  • таблица документы, содержит поле склад (по которому мы будем разграничивать доступ) и другие атрибуты документа
    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)

Итак, начнём настраивать RLS: для начала переименуем целевую таблицу docs → t_docs
ALTER TABLE docs RENAME t_docs;

и создадим одноимённый редактируемый VIEW
CREATE OR REPLACE VIEW docs AS
SELECT id, warehouse_org_id, sum
FROM t_docs
WITH CHECK OPTION;

Теперь все запросы из клиентских приложений обращаются не напрямую к таблице, а ко VIEW

Важно! Если в системе есть функции, процедуры, запросы, которым не надо ограничивать доступ к таблице, то там необходимо прописать непосредственно таблицу, т.е. t_docs. Например, это могут быть процедуры расчёта долгов/остатков по всей системе.

Теперь сделаем простую вещь, ограничим просмотр в соответствии с контролем доступа.

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;

Проверим как это сработало: 
SELECT * FROM docs;
Empty set (0.00 sec)

Ничего не вернулось. Действительно, ведь надо авторизоваться. Авторизуемся Менеджер №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)

Видит только документы «Склад Москва». Авторизуемся Директор 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)

Видит документы «Склад Москва» и «Склад Новосибирск»! Вроде всё работает как надо. Тогда переходим к более сложной задаче — ограничение на редактирование. Попробуем авторизоваться Менеджер №1 user_id = 5 и отредактировать документы: 
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

Обновились только строки которые видим.

Но как же нам добиться различных прав на просмотр и редактирование? Можно добавить ещё одно 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;

и все DML команды пустить через это VIEW, но это потребует переписать в приложении все DML-команды и у нас будет уже 3 объекта

t_docs — исходная таблица
docs — RLS-таблица для просмотра
e_docs — RLS-таблица для редактирования

Попробуем другой вариант, более гибкий.

  1. Создадим функцию get_db_mode для отображения текущего режима — просмотр/редактирование
    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. Модифицируем VIEW, чтобы выдавались разные строки в режиме просмотра/редактирования
    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. Теперь при DML в BEFORE триггере будем устанавливать переменную @db_mode в 'edit', а в AFTER триггере в '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 ;

Вуаля, проверяем как всё работает:

Авторизуемся Кладовщик №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'

Отлично, просматривать можем, редактировать не даёт. Но не всё так гладко: 
SELECT get_db_mode();
+---------------+
| get_db_mode() |
+---------------+
| edit          |
+---------------+
1 row in set (0.00 sec)

После ошибки не отработал AFTER триггер и не снял режим редактирования. Сейчас сделав SELECT мы увидим только те строки которые можем редактировать.
SELECT * FROM docs;
+----+------------------+--------+
| id | warehouse_org_id | sum    |
+----+------------------+--------+
|  2 |                2 | 105000 |
+----+------------------+--------+
1 row in set (0.00 sec)

Один из вариантов решения, это try… catch PDO в php и выполнять принудительно SET @db_mode = 'show' при любой ошибке
Скрипты для удаления тестовых объектов
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;

Теперь, всю логику по контролю доступа очень легко прописать в одном VIEW. По этой же схеме легко реализовать различный доступ на операции INSERT/UPDATE/DELETE

Комментарии (4)

  • 23 сентября 2016 в 17:01

    0

    ↑

    ↓

    , а какую нагрузку держат эти ваши пляски?

    • 23 сентября 2016 в 17:26

      0

      ↑

      ↓

      По нагрузке у него не такой уж и большой оверхед будет в общих случаях, если ключи нормально сделать. Ну и один фиг все это можно поверх закешить. Хотя конечно, в некоторых случаях и хитрых запросах, обязательно вылезет какое-то неочевидное поведение.

  • 23 сентября 2016 в 17:15

    0

    ↑

    ↓

    Имхо, на практике такой подход многим будет не очевиден и труден в отладке. Имхо, лучше, подобные вещи выносить в код, в слой получения данных. Также на практике же, как часто бывает, может появится необходимость в более сложной системе прав, которая в такую модель не ляжет и внезапно придется менять схемы данных, что обычно больно.

    • 23 сентября 2016 в 17:26

      0

      ↑

      ↓

      Согласен. Вполне может появиться ситуация, когда надо будет всем юзерам в группе склад_главный дать доступы на изменение, кроме Васи и Пети, и только те остатки, которые поступили со склада #2, а поступления от поставщиков менять может только Галина Ивановна из бухгалтерии.

© Habrahabr.ru