- PVSM.RU - https://www.pvsm.ru -
Привет! Мне довелось реализовать бизнес-процесс, который предполагал безопасность на уровне строк (Row Level Security) на mysql и php.
Row Level Security или безопасность на уровне строк — механизм разграничения доступа к информации к БД, позволяющий ограничить доступ пользователей к отдельным строкам в таблицах.
Т.к. большую часть времени я программирую на Oracle, то решил, что наиболее оптимально реализовать это в БД.
Имеем MySQL 5.1.73 с триггерами, view, хранимыми функциями и процедурами на обычном виртуальном
В приложении таблица 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` (
`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)
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` (
`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-таблица для редактирования
Попробуем другой вариант, более гибкий.
DELIMITER $
CREATE FUNCTION get_db_mode()
RETURNS VARCHAR(20)
BEGIN
IF @db_mode = 'edit' THEN
RETURN 'edit';
ELSE
RETURN 'show';
END IF;
END
$
DELIMITER ;
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;
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
Источник [2]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/mysql/191481
Ссылки в тексте:
[1] хостинге: https://www.reg.ru/?rlink=reflink-717
[2] Источник: https://habrahabr.ru/post/310832/?utm_source=habrahabr&utm_medium=rss&utm_campaign=sandbox
Нажмите здесь для печати.