Реализация 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 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

Similar posts

Ads
AdBlock has stolen the banner, but banners are not teeth — they will be back

More

Comments 62

    0
    а какую нагрузку держат эти ваши пляски?
      +1
      По нагрузке у него не такой уж и большой оверхед будет в общих случаях, если ключи нормально сделать. Ну и один фиг все это можно поверх закешить. Хотя конечно, в некоторых случаях и хитрых запросах, обязательно вылезет какое-то неочевидное поведение.
        0
        да и вообще БЛ в СУБД не место, имхо
          0
          Это скорее RBAC, чем БЛ, поэтому нормальное применение.
        +1
        Реализация на уровне БД заведомо менее ресурсоёмкая чем проверки на уровне приложения. Как верно заметил jrip, при корректной настройке ключей всё работает идеально и на вставку, и на извлечение.
        У меня большой опыт таких систем на другой БД, когда навигационные данные летят с ~5000 источников и запрашиваются десятками процессами, и всё это надо разграничивать по правам.

        > да и вообще БЛ в СУБД не место, имхо
        придерживаюсь противополжного мнения — надо максимально выносить БЛ в БД.
          0
          небось приложуха у вас только готовые хранимки дергает?
            0
            нет, в приложении обычные INSERT/UPDATE/DELETE. Логика в триггерах.
            MySQL (в отличие от того же Oracle) не позволяет создать 1 триггер на все типы модификаций (INSERT/UPDATE/DELETE) что довольно неудобно и логика размазывается в 6 подпрограммах.
            Я нашёл довольно элегантный способ это обойти, пожалуй напишу об этом статью как-нибудь.
            Общий принцип такой:
            — генератор триггеров: создаёт MEMORY TEMPORARY TABLE, инсертит в неё все значения
            — вызывается процедура <table_name>_trg_proc
            — в процедуре <table_name>_trg_proc прописывается вся БЛ
            Например:
            -- Триггер для docs
            DROP PROCEDURE IF EXISTS docs_trg_proc$
            CREATE PROCEDURE docs_trg_proc()
            BEGIN
            	/* версия 00002 */
            	-- Проверки
            	DECLARE msg TEXT;
            	-- Документ закрыт. Изменения запрещены.
            	SET msg := (SELECT raise_error('Документ закрыт. Изменения запрещены.')
            	FROM docs_tmp_trg d
            	WHERE COALESCE(d.new_closed, d.old_closed) = 1 AND d.time = 'B'
            	AND (
            		d.type IN ('I', 'D')
            		OR (d.type = 'U'
            			AND d.new_closed = 1
            			AND d.old_closed = 1
            			-- Если изменился только статус, то проверку не делаем
            			AND only_col_changed(d.col_changed, 'status_id') IS NULL
            		)
            	)
            	);
            	-- ...
            	-- ...
            	-- ...
            	-- Заполним Юрлицо из склада, если оно пустое
            	UPDATE docs_tmp_trg
            	INNER JOIN organizations ON docs_tmp_trg.new_org_id_addr = organizations.id
            	SET docs_tmp_trg.new_org_id_ur = organizations.org_id_ur
            	WHERE docs_tmp_trg.type IN ('I', 'U') AND docs_tmp_trg.time = 'B' AND docs_tmp_trg.new_org_id_ur IS NULL;
            	-- И проверим что Юрлицо входит в ГО Юрлица
            	SET msg := (SELECT raise_error('Юрлицо должно входить в ГО Юрлица')
            	FROM docs_tmp_trg d
            	WHERE d.time = 'B' AND d.type IN ('I', 'U')
            	AND d.new_org_id_ur NOT IN (SELECT gr.org_id
            	 FROM pos_org_gr gr
            	 WHERE gr.gr_org_id = (SELECT value FROM sys_vars WHERE name = 'ГО_ЮРЛИЦА')
            	));
            	-- Заполним номер документа если он пустой
            	UPDATE docs_tmp_trg SET new_num = get_auto_num()
            	WHERE time = 'B' AND type IN ('I') AND new_num IS NULL;
            	-- ...
            	-- ...
            	-- ...
            	-- Закрытие документа
            	SELECT close_doc(d.new_id, IF(d.new_closed = 1 AND d.old_closed IS NULL, 1, -1))
            	INTO msg
            	FROM docs_tmp_trg d
            	WHERE d.time = 'A' AND d.type = 'U' AND (d.new_closed = 1 AND d.old_closed IS NULL OR d.new_closed IS NULL AND d.old_closed = 1)
            	;
            END$
            

            Получается даже в чём-то удобнее чем в Oracle
              0
              интересно, а что вы делаете, когда меняется структура бд и нужно некоотрое время поддерживать работу и со старой структурой и с новой?

              или если внезапно вот эти вот все >>Юрлицо должно входить в ГО Юрлица нужно локализировать?
                0
                > интересно, а что вы делаете, когда меняется структура бд и нужно некоотрое время поддерживать работу и со старой структурой и с новой?
                не вижу проблемы, обычно миграция БД идёт таким образом, чтобы БД из одного консистентного состояния переходила в другое консистентное состояние.
                Т.е. если есть скрипт миграции
                ALTER TABLE docs CHANGE closed doc_closed TINYINT(1)
                

                То обязательно будет применена миграция на recreate триггеров и обновлённая процедура
                CREATE PROCEDURE docs_trg_proc()
                Если же надо по каким-то причинам поддерживать какое-то время и новое, и старое поле, то тут просто дублирование кода в docs_trg_proc.
                Вообщем-то проблема решается идентично любого другого клиентского кода.

                > или если внезапно вот эти вот все >>Юрлицо должно входить в ГО Юрлица нужно локализировать?
                2 варианта и один лучше другого:
                	...
                	SET msg := (SELECT raise_error((SELECT IFNULL(l.local_msg, l.msg)
                		FROM localizations l
                		WHERE l.msg = 'Документ закрыт. Изменения запрещены.'
                		 AND local = @user_local)
                		)
                	FROM docs_tmp_trg d
                	WHERE COALESCE(d.new_closed, d.old_closed) = 1 AND d.time = 'B'
                	...
                

                И второй, более хороший способ:
                CREATE PROCEDURE raise_error(p_msg TEXT)
                BEGIN
                  SET @raise_error_msg := COALESCE(p_msg
                  , (SELECT l.local_msg)
                     FROM localizations l
                     WHERE l.msg = p_msg AND local = @user_local
                    )
                  , '');
                  DROP TEMPORARY TABLE IF EXISTS mysql_error_generator;
                  CREATE TEMPORARY TABLE mysql_error_generator(raise_error VARCHAR(255) unique) engine=MEMORY;
                  INSERT INTO mysql_error_generator VALUES (IFNULL(p_msg, '')), (IFNULL(p_msg, ''));
                END$
                
                  0
                  и сколько же будет альтерится табла скажем в 500кк строк?
                    0
                    Столько же сколько и таблица без триггеров, т.к. смена имени поля триггер не рейзит.
                    DROP TABLE IF EXISTS tmp$
                    CREATE TABLE IF NOT EXISTS tmp (id int(11))$
                    INSERT tmp VALUES (1), (NULL)$
                    CREATE TRIGGER `tmp_bef_ins_trg` BEFORE INSERT ON `tmp` FOR EACH ROW BEGIN CALL raise_error('Ошибка'); END$
                    CREATE TRIGGER `tmp_bef_upd_trg` BEFORE UPDATE ON `tmp` FOR EACH ROW BEGIN CALL raise_error('Ошибка'); END$
                    ALTER TABLE tmp CHANGE id id2 int(11)$
                    SELECT * FROM tmp$
                    DROP TABLE tmp$
                    

                    Если же всё-таки будет рейзится триггер, например, в случае изменения NOT NULL DEFAULT ..., то просто необходимо перенести ALTER после DROP триггеров, т.е. схема миграции
                    1. DROP ALL TRIGGERS
                    2. ALTER TABLE
                    3. RECREATE ALL TRIGGERS
                      0
                      вы же понимаете, что без даунтайма это не произойдет? или вы рискуете словить неприятности в процессе
                        0
                        Любой ALTER на 500кк строк не пройдёт незамеченным.
                        Тут вопрос даунтайма не в наличии триггера, а в наличии ALTER
                        ALTER из всех этих команд, это 99.9% времени выполнения, а в это время вся система висит в любом случае.
                        Как вариант сглаживания этого процесса ещё альтернатива:
                        SET @disable_docs_trg := 1;
                        ALTER TABLE docs ...
                        DROP ALL TRIGGERS
                        RECREATE ALL TRIGGERS
                        
                          –1
                          буквально недавно «менял структуру» таблы на 350кк± строк без даунтайма. в случае когда все лежит в коде — это не проблема. а что делать в приведенном выше примере я както не придумаю.
                            0
                            Что «всё» лежит в коде? База? Структура? Логика? Данные?
                            Я же объяснил: рекомпиляция триггеров и процедур занимает не более 100мс, время ALTER зависит (в mysql'е) от кол-ва данных, но не зависит от наличия триггеров и того места где лежит БЛ
                              0
                              «все в коде» это естественно логика и ограничения доступа. это дает вам возможность делать изменения без дайнтаймов, вообще. без выключения тригеров на какоето время (что в это время происходит с запросами, которые контролировались этими тригерами???)
                                0
                                Т.е. ALTER TABLE в даунтайм не идёт?

                                CREATE TRIGGER ожидает разблокировки таблицы для которой триггер создаётся
                                  0
                                  окей давайте по другому. я могу сделать так, чтобы структура таблицы изменилась без простоя системы.
                                  зы. обратите внимание, что про альтер сказали вы. я же спросил что вы будете делать, когда придется поменять структуру. есть и другие способы помимо альтера.
                                    0
                                    Про ALTER спросили Вы
                                    > и сколько же будет альтерится табла скажем в 500кк строк?
                                    Я пояснил что триггеры не несут дополнительных накладных расходов.

                                    >есть и другие способы помимо альтера.
                                    Ес-но есть, можно данные вообще не в БД хранить, можно хранить в EAV-таблицах, в Oracle ALTER ADD… не блокирует таблицу и т.д.
                                    Но это немного выходит за рамки текущей дискуссии.
                                      0
                                      можно в самом обычном мускуле без простоя. мой посыл лишь в том, что если не навешивать на бд лишнего, то эти проблемы решаются легко и непринужденно
                                        0
                                        Ещё раз: триггеры не несут дополнительных накладных расходов.
                                          0
                                          еще раз. когда вы держите контроль доступа в самой бд, то у вас возникают описанные выше проблемы. предложите способ менять структуру/навешивать или удалять индексы без простоя системы и тогда я признаю, что неправ
                                            0
                                            Когда вы держите хоть что-то в БД, то у Вас могут возникать описанные выше проблемы
                                              0
                                              мой опыт говорит обратное.
                                                0
                                                Вот Вам в помощь ссылки:
                                                ALTER очень больших таблиц в MySQL
                                                там же есть про опыт в Oracle и MSSQL, но с ними проще.

                                                Если у Вас версия MySQL 5.7, то там появился ALTER ONLINE ..., который позволяет многие DDL-команды без блокировки таблицы
                                                  0
                                                  все гораздо проще. без создания тригеров как описан ов статье… не пойму почему у вас такая нелюбовь к приложению.

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

                                                  плюсы:
                                                  — без простоя приложения
                                                  — вся история изменений в гите
                                                  — без необратимых потерь данных
                                                  минусы:
                                                  — делается в несколько этапов
                                                    0
                                                    Я только не понимаю чем Вам в этой схеме триггеры мешают?
                                                      0
                                                      да не триггеры, а тот факт невозможно одновременно засинкать фиксы в коде и изменения бд. не говоря уже о том, что размазывание БЛ по приложению и БД затрудняет понимание того что происходит вообще.
                                                        0
                                                        > тот факт невозможно одновременно засинкать фиксы в коде и изменения бд
                                                        Поэтому я и сторонник держать БЛ в БД, а не в коде

                                                        > размазывание БЛ по приложению и БД затрудняет понимание того что происходит вообще
                                                        Чтобы логика по коду не размазывалась я предпочитаю её единожды прописать в БД.

                                                        Например, при изменении позиций документа считаются остатки и пересчитывается сумма и статус документа
                                                        В триггере я пропишу это 1 раз и всё. Потом откуда бы в коде я не изменял позиции документа, целостность БД нарушена не будет, логика будет отработана всегда.
                                                        Если же БЛ держать в коде, то каждом месте при изменении позиций документа мне надо не забыть дёрнуть процедуры пересчёта остатков и изменения статуса и суммы документа.
                                                          0
                                                          возможности бд по описанию БЛ не сравнятся с нормальным языком.
                                                          то что вы описываете как проблемы всего лишь следствия говенной архитектуры приложения. в нормальном приложении никто не обновляет данные в бд напрямую. делается это через соотвествующий сервис, который инкапсулирует в себе всю БЛ связанную с этими данными. потому, октуда бы вы не обратились к сервису с просьбой изменить позиции в документе остатки будут пересчитаны.
                                                          идем дальше. все хорошо, пока у вас одна бд. что вы будете делать, когда баз становится больше одной? как ваша БЛ в базе в одном ДЦ будет пересчитывать остатки в БД в другом ДЦ?
                                                            0
                                                            Возможности «нормального» языка не сравняться с эффективностью SQL в плане манипулирования данными.
                                                            Нормальное приложение пишет данные напрямую минуя излишние сущности.
                                                            Сервисы это расход ресурсов для написания, поддержки и плюс просадка по производительности.

                                                            Из примера выше:
                                                            Вы напишите сервис с БЛ для позиций документа в коде. Все INSERT/UPDATE/DELETE будете делать через этот сервис. Плюс Вам будут нужны интерфейсы вызова этого сервиса как на нативном языке, так и внешние, потому что я могу, например, захотеть дёргать код из Python'а и php
                                                            Чувствуйте как 3 лаконичные команды INSERT/UPDATE/DELETE обросли тоннами кода, который нужно писать и поддерживать.

                                                            Идём дальше:
                                                            Например, понадобился сервис копирования документов за период.
                                                            Вы, следуя Вашей логике, дёргайте сервис (опять сервис!) получения документов + позиций за период и начинаете вызывать сначала сервис вставки документа, потом построчно сервис вставки каждой позиции. Получаете просадку в производительности.
                                                            Либо опять пишите пару сервисов, которые умеют оперировать документом и всеми его позициями.
                                                            Чувствуйте всю сложность?!
                                                            Я же просто написал бы 3-4 SQL команды вида
                                                            INSERT INTO docs ... SELECT .. FROM docs d ... WHERE d.date BETWEEN ...
                                                            INSERT INTO doc_pos ... SELECT .. FROM doc_pos .. docs ... WHERE d.date BETWEEN ...
                                                            


                                                            Заметьте, что в моём случае вся логика доступа к данным уже инкапсулирована во VIEW, БЛ в триггерах!
                                                            Я просто пишу что я хочу сделать и база этим занимается.
                                                              0
                                                              По поводу другой БД, в Oracle если линки и гетерогенные сервисы
                                                                0
                                                                у меня почемуто чувство, что вы очень далеки от разработки действительно больших проектов. посему оставайтесь на своем. всего хорошего

                                                                зы. действительно. давайте выбирать субд исходя из прихотей разработчика по хранению БЛ в субд, а не исходя из потребностей проекта/бизнеса
                                                                  0
                                                                  Если мои примеры и рассуждения не убедили Вас, в том, что кроме Вашего мнения и неправильного есть другие, то рекомендую подтянуть уровень прочтением парой книг Тома Кайта
                                                                  — Oracle для профессионалов
                                                                  — Эффективное проектирование приложений Oracle
                                                                  Автор очень доходчиво, на множестве жизненных примерах показывает почему не надо использовать БД как чёрный ящик и почему логика в БД это быстро эффективно, и дёшево
                                                                    0
                                                                    я вот как раз не считаю, что мое мнение единственно правильное. но вы не смогли сказать, что вы будете делать, если у вас больше одной бд и это не оракл?
                                                                      0
                                                                      Я же ответил, в Oracle есть линки и гетерогенные сервисы которые призваны решить эту проблему. На крайний случай, если уж задача такая из ряда вон выходящая, то никто у меня не забирает возможности написать код на «нормальном» языке.
                                                                      Я лишь предпочитаю дрелью сверлить отверстия, а молотком забивать гвозди.
                                                                      Вы же говорите, раз дрелью нельзя забивать гвозди, то давайте всё делать молотком, а дрель выкинем и будем использовать от неё только коробку для хранения гвоздей.
                                                                      Процитирую Кайта:
                                                                      При разработке приложений баз данных я использую очень простую мантру:
                                                                      • если можно, сделай это с помощью одного оператора SQL;
                                                                      • если это нельзя сделать с помощью одного оператора SQL, сделай это в PL/SQL;
                                                                      • если это нельзя сделать в PL/SQL, попытайся использовать хранимую процедуру на языке Java;
                                                                      • если это нельзя сделать в Java, сделай это в виде внешней процедуры на языке C;
                                                                      • если это нельзя реализовать в виде внешней процедуры на языке C, надо серьезно подумать, зачем это вообще делать...

                                                                        0
                                                                        кроме того, что нужно разрабатывать еще нужно поддерживать. это очень круто, когда при очередной гениальной идее приходится делать переход между вашими «если».
                                                                        а еще вот это утверждение: «если можно, сделай это с помощью одного оператора SQL;» крайне спорно. нескоько простых выборок гораздо ефективней одной с джойнами
                                                                          0
                                                                          кроме того, что нужно разрабатывать еще нужно поддерживать. это очень круто, когда при очередной гениальной идее приходится делать переход между вашими «если».

                                                                          Считаю что гораздо проще переключится между «если», чем сверлить отверстия молоком

                                                                          нескоько простых выборок гораздо ефективней одной с джойнами

                                                                          Судя по Вашим утверждениям, у Вас ясно нет опыта в разработке больших проектов.

                                                                          Запустите скрипт без join'а вида:
                                                                          $sql = 'SELECT * FROM doc_pos WHERE doc_id = :doc_id';
                                                                          $sth = $dbh->prepare($sql);
                                                                          foreach ($conn->query('SELECT * FROM docs') as $row) {
                                                                              $sth->execute(array(':doc_id' => $row['id']));
                                                                              while ($r = $sth->fetch()) {
                                                                                  print_r($r);
                                                                              }
                                                                          }
                                                                          


                                                                          А потом с join'ом
                                                                          $sql = 'SELECT p.*
                                                                          FROM doc_pos p
                                                                          INNER JOIN docs d ON p.doc_id = d.id';
                                                                          foreach ($conn->query($sql) as $row) {
                                                                              print_r($row);
                                                                          }
                                                                          


                                                                          И почувствуйте разницу!
                                                                            0
                                                                            судя по вашим ответам, вы слишите и видите только то что хотите. я не предлагал выборки в цикле.
                                                                            досвиданья
                                                                              0
                                                                              Да Вы вообще ничего не предлагали!
                                                                              Предложите альтернативу, не используя JOIN и выборку в цикле.
                                                                              И Вам всего хорошего.
                                                                                0
                                                                                Если именно $conn->query('SELECT * FROM docs'), то $sql = 'SELECT * FROM doc_pos', так как, судя по названию таблицы, doc_id != NULL.

                                                                                Но вообще это делается как-то так:
                                                                                $sql = 'SELECT * FROM doc_pos WHERE doc_id IN (:doc_id_array)';
                                                                                

                                                                                Если записей совсем много, но надо все их вывести обработать, и именно без JOIN-ов, то можно разбить на блоки по N штук. Будет total/N запросов в базу, и логика в программном коде, с возможностью использовать OOP, VCS, и другие полезные аббревиатуры.

                                                                                PS: В качестве примера:
                                                                                https://habrahabr.ru/post/282844/#comment_8888240
                                                                                  0
                                                                                  Вы, по сути, хотите написать заново фунционал БД, т.е. сначала извлечь ключи, а потом по ключам запросить данные следующей таблицы + накладные расходы на переключение контекста клиент-сервер.
                                                                                  Это заведомо более медленный подход.

                                                                                  Запрос курильщика:
                                                                                  SELECT COUNT(*) doc_cn, GROUP_CONCAT(id SEPARATOR ', ')
                                                                                  FROM docs d
                                                                                  WHERE d.date > '2015-05-01';
                                                                                  +--------+
                                                                                  | doc_cn |
                                                                                  +--------+
                                                                                  |   6191 |
                                                                                  +--------+
                                                                                  1 row in set (0.02 sec)
                                                                                  
                                                                                  SELECT COUNT(*) pos_doc_cn
                                                                                  FROM doc_pos
                                                                                  WHERE doc_id IN (40465,40651,/* ... 6191 элементов ... */40440,40574)
                                                                                  +------------+
                                                                                  | pos_doc_cn |
                                                                                  +------------+
                                                                                  |      94370 |
                                                                                  +------------+
                                                                                  1 row in set (0.14 sec)
                                                                                  

                                                                                  И запрос нормального человека:
                                                                                  mysql> SELECT COUNT(*) doc_pos_cn FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id WHERE d.date > '2015-05-01';
                                                                                  +------------+
                                                                                  | doc_pos_cn |
                                                                                  +------------+
                                                                                  |      94370 |
                                                                                  +------------+
                                                                                  1 row in set (0.06 sec)
                                                                                  

                                                                                  В среднем запрос с соединением у меня отрабатывает в 1.5-3 раза быстрее.

                                                                                  В 90-99% случаев 1 оператор SQL работает гораздо быстрее чем несколько выполняющих ту же функцию.
                                                                                  Т.е. если все ключи настроены верно, то два запроса
                                                                                  SET @doc_id := (SELECT id FROM docs WHERE unique_field = :unique_field);
                                                                                  SELECT *
                                                                                  FROM doc_pos
                                                                                  WHERE doc_id = @doc_id
                                                                                  

                                                                                  заведомо медленнее одного
                                                                                  SELECT p.*
                                                                                  FROM doc_pos p
                                                                                  INNER JOIN docs d ON p.doc_id = d.id
                                                                                  WHERE d.unique_field = :unique_field
                                                                                  


                                                                                  Правда бывают исключения. Например, mysql может умирать при соединении большого числа таблиц (более 100), т.к у него есть проблемы с оптимизатором, в таких случаях помогает «материализовать» запрос через
                                                                                  CREATE TEMPORARY TABLE tmp SELECT… FROM t1, t2, t3…
                                                                                  Возможно даже с индексами.
                                                                                  А потом с ней соединить
                                                                                  Но ни в коем случае, не передавать управление на клиента, ничего не извлекать.
                                                                                    0
                                                                                    как я говорил. вы не хотите видеть и слышать.
                                                                                    бывает, что фулскан быстреё индекса (когда данных очень мало)
                                                                                    бывает, что джойны нагружают сильней чем ряд простых выборок по первичному ключу (да, какието неуки придумали elastic search, ведь можно все в TEMPORARY TABLE поджойнить, пофильтровать и выбрать, ага)

                                                                                    вы же понимаете, что ресурсы сервера не бесконечны. что эти мс, которые вы экономите оборачиваются лишними тактами проца, лишними мегабайтами ОЗУ (а на действительно больших таблах даже десятками и сотнями мегабайт). и эти секономленные пара мс ничто по сравнению с сетевыми задержками для юзера.
                                                                                      0
                                                                                      не приписывайте мне Ваши домыслы.
                                                                                      Я как раз рекомендую Вам разобраться в устройстве и архитектуре используемой СУБД.
                                                                                      Не использовать СУБД как хранилище-«чёрный ящик», а перекладывать на неё тот функционал для которого она предназначена и делает это многократно эффективнее, чем разработка того же функционала на коленке
                                                                                      Не надо изобретать новые велосипеды, тогда приложения будут легко масштабируемы и велосипеды не будут съедать все ресурсы сервера.
          +2
          Имхо, на практике такой подход многим будет не очевиден и труден в отладке. Имхо, лучше, подобные вещи выносить в код, в слой получения данных. Также на практике же, как часто бывает, может появится необходимость в более сложной системе прав, которая в такую модель не ляжет и внезапно придется менять схемы данных, что обычно больно.
            0
            Согласен. Вполне может появиться ситуация, когда надо будет всем юзерам в группе склад_главный дать доступы на изменение, кроме Васи и Пети, и только те остатки, которые поступили со склада #2, а поступления от поставщиков менять может только Галина Ивановна из бухгалтерии.
              0
              + поддерживаю
                0
                В отладке трудностей никаких нет. Единственное, иногда надо бывает авторизоваться, чтобы эмулировать поведение пользователя. Как раз отладка проходит на ура, т.к. не зависим от кода получения данных, открываем PL/SQL Developer mysql и разбираемся в запросе, вся логика написана тут, ничего не размазано по коду.
                Про более сложную систему тоже непонятно, SQL-запросами можно реализовать все самые причудливые хотелки разграничения прав.
                +2
                Очень заморочено, сложно поддерживать, сложно тестировать и сам профит неочевиден, имхо.
                  +1
                  Приходилось сталкиваться с таким подходом в одной системе учета персонала. Столкнулись с тем, что при проектировании более-менее сложного отчета результирующий запрос легко выходил на сотни таблиц, так как каждое VIEW с правами было продуктом основной таблицы + нескольких таблиц с правами и группами. В результате оракл загибался, хотя таблицы были не особо большие.
                  Для отчетов пришлось в итоге отказаться от VIEW, и заменить на обычные таблицы.
                    0
                    Действительно такая проблема существует. В Оракле я практически не замечал сильного падения производительности от этого, а mysql реально начинает подумирать.

                    Решается довольно просто: в INNER JOIN используем только одну RLS-таблицу, с самым жёстким ограничением, остальные таблицы напрямую.
                    Например, есть RLS-VIEW docs и есть RLS-VIEW позиции документа doc_pos
                    SELECT * FROM doc_pos
                    WHERE EXISTS (SELECT NULL
                      FROM docs
                      WHERE doc_pos.doc_id = docs.id
                    )
                    

                    В отчётах стоит писать
                    SELECT ...
                    FROM t_doc_pos p
                    INNER JOIN docs d ON p.doc_id = d.id
                    ...
                    

                    В этом случае в mysql всё будет работать идеальнейшим образом.
                    А в Оракле, даже при соединении множества RLS-таблиц провального падения производительности не наблюдается в 80% случаев.
                      0
                      Вам надо перейти на следующий уровень — от таблиц спутников с правами размером — объекты*пользователи, к спискам доступа, таблица с правами будет одна размер ее не будет зависеть от количества объектов, только от «разнообразия» прав, роли легко добавить, проверка прав будет один к одному, правда поле с идентификатором списка надо добавить в таблицу с объектами
                    0
                    Не, так не пойдет, какая тут RLS, RLS это когда у Вас права задаются не только на Склады, которых от силы штук 50, но и индивидуально на Документы которых, эдак, 500 000, плюс, видов документов штук 20 (лежат в разных таблицах), да еще и у документа есть поле Контрагент и на элементы справочника контрагентов тоже права есть, и т.д. и т.п. кароче, штук 50 таблиц и все RLS.
                    и чтоб все не тормозило (ну почти) как сделать? (нужно все по взрослому — дескрипторы и т.п. )
                    насчет редактирование, имхо надо ошибку выдавать (в триггере) если редактируют запись которая на чтение только, а не скрывать её.
                      +1
                      > какая тут RLS
                      Я же описал базовый принцип, дальше всё зависит от Вашей фантазии и умения писать SQL-запросы

                      > но и индивидуально на Документы которых, эдак, 500 000
                      добавляем всего 7 строк кода:
                      ...
                      WHERE EXISTS (
                        ...
                        UNION ALL
                        SELECT NULL
                        FROM auth_users
                        INNER JOIN user_docs_access ON user_docs_access.user_id = auth_users.user_id
                         AND auth_users.conn_id = CONNECTION_ID()
                        WHERE d.id = user_docs_access.doc_id
                         AND (get_db_mode() = 'show' OR user_docs_access.edit = 1 AND get_db_mode() = 'edit')</b>
                      )
                      


                      > видов документов штук 20
                      ещё 7 строк кода:
                      ...
                      WHERE EXISTS (
                        ...
                        UNION ALL
                        SELECT NULL
                        FROM auth_users
                        INNER JOIN user_doc_type_access ON user_doc_type_access.user_id = auth_users.user_id
                         AND auth_users.conn_id = CONNECTION_ID()
                        WHERE d.doc_type_id = user_doc_type_access.doc_type_id
                         AND (get_db_mode() = 'show' OR user_doc_type_access.edit = 1 AND get_db_mode() = 'edit')</b>
                      )
                      


                      > (лежат в разных таблицах)
                      хорошо ещё 7xN строк кода:
                      ...
                      WHERE EXISTS (
                        ...
                        UNION ALL
                        SELECT NULL
                        FROM auth_users
                        INNER JOIN user_doc_type_n_access ON user_doc_type_n_access.user_id = auth_users.user_id
                         AND auth_users.conn_id = CONNECTION_ID()
                        WHERE d.doc_type_id = user_doc_type_n_access.doc_type_id
                         AND (get_db_mode() = 'show' OR user_doc_type_n_access.edit = 1 AND get_db_mode() = 'edit')</b>
                      )
                      


                      > да еще и у документа есть поле Контрагент и на элементы справочника контрагентов тоже права есть

                      Заметьте как изящно и лаконично реализуется фраза
                      > и на элементы справочника контрагентов тоже права есть
                      INNER JOIN organizations cl ON cl.id = d.org_id_client — Соединение с RLS-таблицей
                      ...
                      WHERE EXISTS (
                        ...
                        UNION ALL
                        SELECT NULL
                        FROM auth_users
                        INNER JOIN user_client_access ON user_client_access.user_id = auth_users.user_id
                         AND auth_users.conn_id = CONNECTION_ID()
                        INNER JOIN organizations cl ON cl.id = d.org_id_client -- Соединение с RLS-таблицей
                        WHERE d.org_id_client = user_client_access.org_id_client
                         AND (get_db_mode() = 'show' OR user_client_access.edit = 1 AND get_db_mode() = 'edit')</b>\
                      )
                      
                        0
                        с такими конструкциями это у вас даже на оракле умрет, ну или в трое более производительный железо потребуется.
                        у меня вопрос — в MySQL в триггере райзить эксепшен можно?
                          0
                          В том то и дело что ничего не умрёт, это естественная проверка, работает гармонично и быстро.
                          Скажите, как бы вы проверили всё это по другому?
                          Это точно будет более накладно по производительности!

                          В MySQL версии 5.1.73 в триггере можно рейзить эксепшены не нативным методом:
                          DELIMITER $
                          CREATE PROCEDURE raise_error(msg TEXT)
                          BEGIN
                            SET @raise_error_msg := IFNULL(msg, '');
                            DROP TEMPORARY TABLE IF EXISTS mysql_error_generator;
                            CREATE TEMPORARY TABLE mysql_error_generator(raise_error VARCHAR(255) unique) engine=MEMORY;
                            INSERT INTO mysql_error_generator VALUES (IFNULL(msg, '')), (IFNULL(msg, ''));
                          END$
                          

                          И в php для более красивой обработки ошибок пишем:
                          class ExPDOException extends PDOException {
                          	public function __construct(PDOException $e, PDO $connection) {
                          		parent::__construct($e->getMessage(), 0, $e->getPrevious());
                          		$this->code = $e->getCode();
                          		$this->errorInfo = $e->errorInfo;
                          		// Пользовательская ошибка
                          		if ($e->getCode() == 23000 && strstr($e->getMessage(), "for key 'raise_error'")) {
                          			$this->code = -20000;
                          			$this->errorInfo[0] = -20000;
                          			$this->errorInfo[1] = -20000;
                          			$sql = 'SELECT @raise_error_msg msg';
                          			$q = $connection->query($sql);
                          			$msg = $q->fetchColumn();
                          			$this->message = $msg;
                          			$this->errorInfo[2] = $msg;
                          		}
                          	}
                          }
                          
                            –1
                            охохо, вот это ад.
                            а селекты в триггере можно? или только хранимые процедуры?
                      0
                      На самом деле никакого ада нет, light тюнинг

                      Селекты в триггере практически без ограничений.
                      Ещё очень развязывает руки (по сравнению с Ораклом) тот факт, что CREATE TEMPORARY TABLE не DDL команда.
                        0
                        я бы для начала заменил бы условие на
                        AND user_access.edit > get_db_mode()
                        где user_access.edit 0 — нет доступа, 1 — чтение записи, 2 — чтение и редактирование
                        get_db_mode() возвращает, если читать то 0, если надо и редактировать то 1

                        ибо ORы зло, да и короче

                        хотя оптимизация по константам есть в MySQL?

                        допускается ли добавление поля в organizations (и в другие таблицы с RLS) ?, типа a_list_id
                          0
                          В ORах нет никакого зла когда они к месту.
                          Я намерено пишу ORы через UNION ALL, тогда они заведомо работают как написано в логике и оптимизаторы их именно в таком порядке и воспринимают, а OR статического выражения к константе не надо боятся вообще.

                          > допускается ли добавление поля в organizations (и в другие таблицы с RLS) ?, типа a_list_id
                          не понял вопроса, поясните
                            0
                            |В ORах нет никакого зла когда они к месту
                            ну если нет разницы на 10млн записях то да

                            |поясните
                            ну я развиваю — чтобы сделал. для того чтобы избавится от таблиц спутников с правами, надо добавить поле в таблицы на которые настраивается RLS, вот и спрашиваю если какие у вас ограничение на это
                              0
                              Что-то типа метки безопасности для строки? Нет никаких ограничений, откуда им взяться

                      Only users with full accounts can post comments. Log in, please.