Реализация Row Level Security на MySQL

Привет Хабр! Мне довелось реализовать бизнес-процесс, который предполагал безопасность на уровне строк (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'));

Пример схемы данных:


Итак, начнём настраивать 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
@asmm
23.09.2016 18:54 UTC
Первоисточник