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`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
Комментарии (60)
jrip
23.09.2016 17:15+2Имхо, на практике такой подход многим будет не очевиден и труден в отладке. Имхо, лучше, подобные вещи выносить в код, в слой получения данных. Также на практике же, как часто бывает, может появится необходимость в более сложной системе прав, которая в такую модель не ляжет и внезапно придется менять схемы данных, что обычно больно.
TimsTims
23.09.2016 17:26Согласен. Вполне может появиться ситуация, когда надо будет всем юзерам в группе склад_главный дать доступы на изменение, кроме Васи и Пети, и только те остатки, которые поступили со склада #2, а поступления от поставщиков менять может только Галина Ивановна из бухгалтерии.
asmm
24.09.2016 22:36В отладке трудностей никаких нет. Единственное, иногда надо бывает авторизоваться, чтобы эмулировать поведение пользователя. Как раз отладка проходит на ура, т.к. не зависим от кода получения данных, открываем
PL/SQL Developermysql и разбираемся в запросе, вся логика написана тут, ничего не размазано по коду.
Про более сложную систему тоже непонятно, SQL-запросами можно реализовать все самые причудливые хотелки разграничения прав.
unabl4
23.09.2016 17:43+2Очень заморочено, сложно поддерживать, сложно тестировать и сам профит неочевиден, имхо.
amaksr
23.09.2016 17:57+1Приходилось сталкиваться с таким подходом в одной системе учета персонала. Столкнулись с тем, что при проектировании более-менее сложного отчета результирующий запрос легко выходил на сотни таблиц, так как каждое VIEW с правами было продуктом основной таблицы + нескольких таблиц с правами и группами. В результате оракл загибался, хотя таблицы были не особо большие.
Для отчетов пришлось в итоге отказаться от VIEW, и заменить на обычные таблицы.asmm
24.09.2016 23:01Действительно такая проблема существует. В Оракле я практически не замечал сильного падения производительности от этого, а 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% случаев.msts2017
24.09.2016 23:15Вам надо перейти на следующий уровень — от таблиц спутников с правами размером — объекты*пользователи, к спискам доступа, таблица с правами будет одна размер ее не будет зависеть от количества объектов, только от «разнообразия» прав, роли легко добавить, проверка прав будет один к одному, правда поле с идентификатором списка надо добавить в таблицу с объектами
msts2017
24.09.2016 10:03Не, так не пойдет, какая тут RLS, RLS это когда у Вас права задаются не только на Склады, которых от силы штук 50, но и индивидуально на Документы которых, эдак, 500 000, плюс, видов документов штук 20 (лежат в разных таблицах), да еще и у документа есть поле Контрагент и на элементы справочника контрагентов тоже права есть, и т.д. и т.п. кароче, штук 50 таблиц и все RLS.
и чтоб все не тормозило (ну почти) как сделать? (нужно все по взрослому — дескрипторы и т.п. )
насчет редактирование, имхо надо ошибку выдавать (в триггере) если редактируют запись которая на чтение только, а не скрывать её.asmm
24.09.2016 23:34+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>)msts2017
24.09.2016 23:57с такими конструкциями это у вас даже на оракле умрет, ну или в трое более производительный железо потребуется.
у меня вопрос — в MySQL в триггере райзить эксепшен можно?asmm
25.09.2016 00:11В том то и дело что ничего не умрёт, это естественная проверка, работает гармонично и быстро.
Скажите, как бы вы проверили всё это по другому?
Это точно будет более накладно по производительности!
В 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; } } }msts2017
25.09.2016 00:33-1охохо, вот это ад.
а селекты в триггере можно? или только хранимые процедуры?
asmm
25.09.2016 00:46На самом деле никакого ада нет, light тюнинг
Селекты в триггере практически без ограничений.
Ещё очень развязывает руки (по сравнению с Ораклом) тот факт, что CREATE TEMPORARY TABLE не DDL команда.msts2017
25.09.2016 01:02я бы для начала заменил бы условие на
AND user_access.edit > get_db_mode()
где user_access.edit 0 — нет доступа, 1 — чтение записи, 2 — чтение и редактирование
get_db_mode() возвращает, если читать то 0, если надо и редактировать то 1
ибо ORы зло, да и короче
хотя оптимизация по константам есть в MySQL?
допускается ли добавление поля в organizations (и в другие таблицы с RLS) ?, типа a_list_idasmm
25.09.2016 01:18В ORах нет никакого зла когда они к месту.
Я намерено пишу ORы через UNION ALL, тогда они заведомо работают как написано в логике и оптимизаторы их именно в таком порядке и воспринимают, а OR статического выражения к константе не надо боятся вообще.
> допускается ли добавление поля в organizations (и в другие таблицы с RLS) ?, типа a_list_id
не понял вопроса, пояснитеmsts2017
25.09.2016 01:38|В ORах нет никакого зла когда они к месту
ну если нет разницы на 10млн записях то да
|поясните
ну я развиваю — чтобы сделал. для того чтобы избавится от таблиц спутников с правами, надо добавить поле в таблицы на которые настраивается RLS, вот и спрашиваю если какие у вас ограничение на этоasmm
25.09.2016 09:34Что-то типа метки безопасности для строки? Нет никаких ограничений, откуда им взяться
oxidmod
а какую нагрузку держат эти ваши пляски?
jrip
По нагрузке у него не такой уж и большой оверхед будет в общих случаях, если ключи нормально сделать. Ну и один фиг все это можно поверх закешить. Хотя конечно, в некоторых случаях и хитрых запросах, обязательно вылезет какое-то неочевидное поведение.
oxidmod
да и вообще БЛ в СУБД не место, имхо
prolis
Это скорее RBAC, чем БЛ, поэтому нормальное применение.
asmm
Реализация на уровне БД заведомо менее ресурсоёмкая чем проверки на уровне приложения. Как верно заметил jrip, при корректной настройке ключей всё работает идеально и на вставку, и на извлечение.
У меня большой опыт таких систем на другой БД, когда навигационные данные летят с ~5000 источников и запрашиваются десятками процессами, и всё это надо разграничивать по правам.
> да и вообще БЛ в СУБД не место, имхо
придерживаюсь противополжного мнения — надо максимально выносить БЛ в БД.
oxidmod
небось приложуха у вас только готовые хранимки дергает?
asmm
нет, в приложении обычные INSERT/UPDATE/DELETE. Логика в триггерах.
MySQL (в отличие от того же Oracle) не позволяет создать 1 триггер на все типы модификаций (INSERT/UPDATE/DELETE) что довольно неудобно и логика размазывается в 6 подпрограммах.
Я нашёл довольно элегантный способ это обойти, пожалуй напишу об этом статью как-нибудь.
Общий принцип такой:
— генератор триггеров: создаёт MEMORY TEMPORARY TABLE, инсертит в неё все значения
— вызывается процедура <table_name>_trg_proc
— в процедуре <table_name>_trg_proc прописывается вся БЛ
Например:
Получается даже в чём-то удобнее чем в Oracle
oxidmod
интересно, а что вы делаете, когда меняется структура бд и нужно некоотрое время поддерживать работу и со старой структурой и с новой?
или если внезапно вот эти вот все >>Юрлицо должно входить в ГО Юрлица нужно локализировать?
asmm
> интересно, а что вы делаете, когда меняется структура бд и нужно некоотрое время поддерживать работу и со старой структурой и с новой?
не вижу проблемы, обычно миграция БД идёт таким образом, чтобы БД из одного консистентного состояния переходила в другое консистентное состояние.
Т.е. если есть скрипт миграции
То обязательно будет применена миграция на recreate триггеров и обновлённая процедура
CREATE PROCEDURE docs_trg_proc()
Если же надо по каким-то причинам поддерживать какое-то время и новое, и старое поле, то тут просто дублирование кода в docs_trg_proc.
Вообщем-то проблема решается идентично любого другого клиентского кода.
> или если внезапно вот эти вот все >>Юрлицо должно входить в ГО Юрлица нужно локализировать?
2 варианта и один лучше другого:
И второй, более хороший способ:
oxidmod
и сколько же будет альтерится табла скажем в 500кк строк?
asmm
Столько же сколько и таблица без триггеров, т.к. смена имени поля триггер не рейзит.
Если же всё-таки будет рейзится триггер, например, в случае изменения NOT NULL DEFAULT ..., то просто необходимо перенести ALTER после DROP триггеров, т.е. схема миграции
1. DROP ALL TRIGGERS
2. ALTER TABLE
3. RECREATE ALL TRIGGERS
oxidmod
вы же понимаете, что без даунтайма это не произойдет? или вы рискуете словить неприятности в процессе
asmm
Любой ALTER на 500кк строк не пройдёт незамеченным.
Тут вопрос даунтайма не в наличии триггера, а в наличии ALTER
ALTER из всех этих команд, это 99.9% времени выполнения, а в это время вся система висит в любом случае.
Как вариант сглаживания этого процесса ещё альтернатива:
oxidmod
буквально недавно «менял структуру» таблы на 350кк± строк без даунтайма. в случае когда все лежит в коде — это не проблема. а что делать в приведенном выше примере я както не придумаю.
asmm
Что «всё» лежит в коде? База? Структура? Логика? Данные?
Я же объяснил: рекомпиляция триггеров и процедур занимает не более 100мс, время ALTER зависит (в mysql'е) от кол-ва данных, но не зависит от наличия триггеров и того места где лежит БЛ
oxidmod
«все в коде» это естественно логика и ограничения доступа. это дает вам возможность делать изменения без дайнтаймов, вообще. без выключения тригеров на какоето время (что в это время происходит с запросами, которые контролировались этими тригерами???)
asmm
Т.е. ALTER TABLE в даунтайм не идёт?
CREATE TRIGGER ожидает разблокировки таблицы для которой триггер создаётся
oxidmod
окей давайте по другому. я могу сделать так, чтобы структура таблицы изменилась без простоя системы.
зы. обратите внимание, что про альтер сказали вы. я же спросил что вы будете делать, когда придется поменять структуру. есть и другие способы помимо альтера.
asmm
Про ALTER спросили Вы
> и сколько же будет альтерится табла скажем в 500кк строк?
Я пояснил что триггеры не несут дополнительных накладных расходов.
>есть и другие способы помимо альтера.
Ес-но есть, можно данные вообще не в БД хранить, можно хранить в EAV-таблицах, в Oracle ALTER ADD… не блокирует таблицу и т.д.
Но это немного выходит за рамки текущей дискуссии.
oxidmod
можно в самом обычном мускуле без простоя. мой посыл лишь в том, что если не навешивать на бд лишнего, то эти проблемы решаются легко и непринужденно
asmm
Ещё раз: триггеры не несут дополнительных накладных расходов.
oxidmod
еще раз. когда вы держите контроль доступа в самой бд, то у вас возникают описанные выше проблемы. предложите способ менять структуру/навешивать или удалять индексы без простоя системы и тогда я признаю, что неправ
asmm
Когда вы держите хоть что-то в БД, то у Вас могут возникать описанные выше проблемы
oxidmod
мой опыт говорит обратное.
asmm
Вот Вам в помощь ссылки:
ALTER очень больших таблиц в MySQL
там же есть про опыт в Oracle и MSSQL, но с ними проще.
Если у Вас версия MySQL 5.7, то там появился ALTER ONLINE ..., который позволяет многие DDL-команды без блокировки таблицы
oxidmod
все гораздо проще. без создания тригеров как описан ов статье… не пойму почему у вас такая нелюбовь к приложению.
да, действительно создаем новую пустую таблу с нужно структурой.
модифицируем приложение чтобы новые записи писались и в новую и в старую таблу.
пишем команду которая в фоне небольшими порциями не нагружая особо сервер переливает старые записи из старой таблы в новую.
меняем код, чтобы он не только писал в новую таблу, но и итал с неё
через пару дней можно просто удалить/переместить в архив уже ненужную таблу, если все ок и подчистить код.
плюсы:
— без простоя приложения
— вся история изменений в гите
— без необратимых потерь данных
минусы:
— делается в несколько этапов
asmm
Я только не понимаю чем Вам в этой схеме триггеры мешают?
oxidmod
да не триггеры, а тот факт невозможно одновременно засинкать фиксы в коде и изменения бд. не говоря уже о том, что размазывание БЛ по приложению и БД затрудняет понимание того что происходит вообще.
asmm
> тот факт невозможно одновременно засинкать фиксы в коде и изменения бд
Поэтому я и сторонник держать БЛ в БД, а не в коде
> размазывание БЛ по приложению и БД затрудняет понимание того что происходит вообще
Чтобы логика по коду не размазывалась я предпочитаю её единожды прописать в БД.
Например, при изменении позиций документа считаются остатки и пересчитывается сумма и статус документа
В триггере я пропишу это 1 раз и всё. Потом откуда бы в коде я не изменял позиции документа, целостность БД нарушена не будет, логика будет отработана всегда.
Если же БЛ держать в коде, то каждом месте при изменении позиций документа мне надо не забыть дёрнуть процедуры пересчёта остатков и изменения статуса и суммы документа.
oxidmod
возможности бд по описанию БЛ не сравнятся с нормальным языком.
то что вы описываете как проблемы всего лишь следствия говенной архитектуры приложения. в нормальном приложении никто не обновляет данные в бд напрямую. делается это через соотвествующий сервис, который инкапсулирует в себе всю БЛ связанную с этими данными. потому, октуда бы вы не обратились к сервису с просьбой изменить позиции в документе остатки будут пересчитаны.
идем дальше. все хорошо, пока у вас одна бд. что вы будете делать, когда баз становится больше одной? как ваша БЛ в базе в одном ДЦ будет пересчитывать остатки в БД в другом ДЦ?
asmm
Возможности «нормального» языка не сравняться с эффективностью SQL в плане манипулирования данными.
Нормальное приложение пишет данные напрямую минуя излишние сущности.
Сервисы это расход ресурсов для написания, поддержки и плюс просадка по производительности.
Из примера выше:
Вы напишите сервис с БЛ для позиций документа в коде. Все INSERT/UPDATE/DELETE будете делать через этот сервис. Плюс Вам будут нужны интерфейсы вызова этого сервиса как на нативном языке, так и внешние, потому что я могу, например, захотеть дёргать код из Python'а и php
Чувствуйте как 3 лаконичные команды INSERT/UPDATE/DELETE обросли тоннами кода, который нужно писать и поддерживать.
Идём дальше:
Например, понадобился сервис копирования документов за период.
Вы, следуя Вашей логике, дёргайте сервис (опять сервис!) получения документов + позиций за период и начинаете вызывать сначала сервис вставки документа, потом построчно сервис вставки каждой позиции. Получаете просадку в производительности.
Либо опять пишите пару сервисов, которые умеют оперировать документом и всеми его позициями.
Чувствуйте всю сложность?!
Я же просто написал бы 3-4 SQL команды вида
Заметьте, что в моём случае вся логика доступа к данным уже инкапсулирована во VIEW, БЛ в триггерах!
Я просто пишу что я хочу сделать и база этим занимается.
asmm
По поводу другой БД, в Oracle если линки и гетерогенные сервисы
oxidmod
у меня почемуто чувство, что вы очень далеки от разработки действительно больших проектов. посему оставайтесь на своем. всего хорошего
зы. действительно. давайте выбирать субд исходя из прихотей разработчика по хранению БЛ в субд, а не исходя из потребностей проекта/бизнеса
asmm
Если мои примеры и рассуждения не убедили Вас, в том, что кроме Вашего мнения и неправильного есть другие, то рекомендую подтянуть уровень прочтением парой книг Тома Кайта
— Oracle для профессионалов
— Эффективное проектирование приложений Oracle
Автор очень доходчиво, на множестве жизненных примерах показывает почему не надо использовать БД как чёрный ящик и почему логика в БД это быстро эффективно, и дёшево
oxidmod
я вот как раз не считаю, что мое мнение единственно правильное. но вы не смогли сказать, что вы будете делать, если у вас больше одной бд и это не оракл?
asmm
Я же ответил, в Oracle есть линки и гетерогенные сервисы которые призваны решить эту проблему. На крайний случай, если уж задача такая из ряда вон выходящая, то никто у меня не забирает возможности написать код на «нормальном» языке.
Я лишь предпочитаю дрелью сверлить отверстия, а молотком забивать гвозди.
Вы же говорите, раз дрелью нельзя забивать гвозди, то давайте всё делать молотком, а дрель выкинем и будем использовать от неё только коробку для хранения гвоздей.
Процитирую Кайта:
oxidmod
кроме того, что нужно разрабатывать еще нужно поддерживать. это очень круто, когда при очередной гениальной идее приходится делать переход между вашими «если».
а еще вот это утверждение: «если можно, сделай это с помощью одного оператора SQL;» крайне спорно. нескоько простых выборок гораздо ефективней одной с джойнами
asmm
Считаю что гораздо проще переключится между «если», чем сверлить отверстия молоком
Судя по Вашим утверждениям, у Вас ясно нет опыта в разработке больших проектов.
Запустите скрипт без join'а вида:
А потом с join'ом
И почувствуйте разницу!
oxidmod
судя по вашим ответам, вы слишите и видите только то что хотите. я не предлагал выборки в цикле.
досвиданья
asmm
Да Вы вообще ничего не предлагали!
Предложите альтернативу, не используя JOIN и выборку в цикле.
И Вам всего хорошего.
michael_vostrikov
Если именно
$conn->query('SELECT * FROM docs'), то$sql = 'SELECT * FROM doc_pos', так как, судя по названию таблицы,doc_id != NULL.Но вообще это делается как-то так:
Если записей совсем много, но надо все их
вывестиобработать, и именно без JOIN-ов, то можно разбить на блоки по N штук. Будет total/N запросов в базу, и логика в программном коде, с возможностью использовать OOP, VCS, и другие полезные аббревиатуры.PS: В качестве примера:
https://habrahabr.ru/post/282844/#comment_8888240
asmm
Вы, по сути, хотите написать заново фунционал БД, т.е. сначала извлечь ключи, а потом по ключам запросить данные следующей таблицы + накладные расходы на переключение контекста клиент-сервер.
Это заведомо более медленный подход.
Запрос курильщика:
И запрос нормального человека:
В среднем запрос с соединением у меня отрабатывает в 1.5-3 раза быстрее.
В 90-99% случаев 1 оператор SQL работает гораздо быстрее чем несколько выполняющих ту же функцию.
Т.е. если все ключи настроены верно, то два запроса
заведомо медленнее одного
Правда бывают исключения. Например, mysql может умирать при соединении большого числа таблиц (более 100), т.к у него есть проблемы с оптимизатором, в таких случаях помогает «материализовать» запрос через
CREATE TEMPORARY TABLE tmp SELECT… FROM t1, t2, t3…
Возможно даже с индексами.
А потом с ней соединить
Но ни в коем случае, не передавать управление на клиента, ничего не извлекать.