Реалізація 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'));

Приклад схеми даних:

  • довідник організацій

    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 AS docs
SELECT id, warehouse_org_id, sum
FROM t_docs
WITH CHECK OPTION;

Тепер всі запити з клієнтських додатків звертаються безпосередньо до таблиці, а до VIEW

Важливо! Якщо в системі є функції, процедури, запити, яких не треба обмежувати доступ до таблиці, то там необхідно прописати безпосередньо таблицю, тобто t_docs. Наприклад, це можуть бути процедури розрахунку боргів/залишків по всій системі.

Тепер зробимо просту річ, обмежимо перегляд у відповідності з контролем доступу.

CREATE OR REPLACE VIEW AS docs
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 AS e_docs
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 AS docs
    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` INSERT BEFORE 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 ON DELETE `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 ON DELETE `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
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.