Привет Хабр! Мне довелось реализовать бизнес-процесс, который предполагал безопасность на уровне строк (Row Level Security) на mysql и php.
![image](https://habrastorage.org/r/w1560/getpro/habr/post_images/c5d/134/394/c5d1343945fffa30fe736bf4b977c560.png)
Row Level Security или безопасность на уровне строк — механизм разграничения доступа к информации к БД, позволяющий ограничить доступ пользователей к отдельным строкам в таблицах.
Т.к. большую часть времени я программирую на Oracle, то решил, что наиболее оптимально реализовать это в БД.
Имеем MySQL 5.1.73 с триггерами, view, хранимыми функциями и процедурами на обычном виртуальном хостинге.
В приложении таблица auth_users
которая заполняется при авторизации в php
и очищается при завершении php-скрипта
Пример схемы данных:
Итак, начнём настраивать RLS: для начала переименуем целевую таблицу docs -> t_docs
и создадим одноимённый редактируемый VIEW
Теперь все запросы из клиентских приложений обращаются не напрямую к таблице, а ко VIEW
Важно! Если в системе есть функции, процедуры, запросы, которым не надо ограничивать доступ к таблице, то там необходимо прописать непосредственно таблицу, т.е. t_docs. Например, это могут быть процедуры расчёта долгов/остатков по всей системе.
Теперь сделаем простую вещь, ограничим просмотр в соответствии с контролем доступа.
Проверим как это сработало:
Ничего не вернулось. Действительно, ведь надо авторизоваться. Авторизуемся Менеджер №1 user_id = 5
Видит только документы «Склад Москва». Авторизуемся Директор user_id = 3
Видит документы «Склад Москва» и «Склад Новосибирск»! Вроде всё работает как надо. Тогда переходим к более сложной задаче — ограничение на редактирование. Попробуем авторизоваться Менеджер №1 user_id = 5 и отредактировать документы:
Обновились только строки которые видим.
Но как же нам добиться различных прав на просмотр и редактирование? Можно добавить ещё одно VIEW e_docs
и все DML команды пустить через это VIEW, но это потребует переписать в приложении все DML-команды и у нас будет уже 3 объекта
t_docs — исходная таблица
docs — RLS-таблица для просмотра
e_docs — RLS-таблица для редактирования
Попробуем другой вариант, более гибкий.
Вуаля, проверяем как всё работает:
Авторизуемся Кладовщик №1 user_id = 1
Отлично, просматривать можем, редактировать не даёт. Но не всё так гладко:
После ошибки не отработал AFTER триггер и не снял режим редактирования. Сейчас сделав SELECT мы увидим только те строки которые можем редактировать.
Один из вариантов решения, это try… catch PDO в php и выполнять принудительно SET @db_mode = 'show' при любой ошибке
Теперь, всю логику по контролю доступа очень легко прописать в одном VIEW. По этой же схеме легко реализовать различный доступ на операции INSERT/UPDATE/DELETE
![image](https://habrastorage.org/getpro/habr/post_images/c5d/134/394/c5d1343945fffa30fe736bf4b977c560.png)
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 user_id = 1, имеет доступ на просмотр документов «Склад Москва», на просмотр и редактирование документов «Склад Новосибирск»
- Кладовщик №2 user_id = 2, имеет доступ на просмотр документов «Склад Новосибирск», на просмотр и редактирование документов «Склад Москва»
- Директор user_id = 3, имеет доступ на просмотр документов «Склад Новосибирск» и «Склад Москва»
- Бухгалтер user_id = 4, имеет доступ на просмотр и редактирование документов «Склад Новосибирск» и «Склад Москва»
- Менеджер №1 user_id = 5, имеет доступ на просмотр документов «Склад Москва»
- Менеджер №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-таблица для редактирования
Попробуем другой вариант, более гибкий.
- Создадим функцию 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 ;
- Модифицируем 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;
- Теперь при DML в BEFORE триггере будем устанавливать переменную @db_mode в 'edit', а в AFTER триггере в 'show'
CREATE TRIGGERSDELIMITER $ 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