Stored procedures security

[singalen]

ru_sql пока меня не пускает, ru_case сдох из-за неправильного названия.
Вопрошу у себя: какие именно угрозы безопасности закрывает DAL на stored procedures, в отличие от динамического SQL, как, скажем, в Hibernate? (уточнение вопроса)
Обычно на них упирают в MS SQL Server, но это потому что оный крив исторически. Даже в версии 2000 не было приличного кэша распарсенных запросов и execution plan-ов (это написано в доке, английским по белому). В результате CRUD на SQL в нём работал раз в 20 медленнее, чем на процедурах. Я не преувеличиваю, а скорее преуменьшаю.
Это приводило даже к таким невероятно уродливым костылям: ODBC драйвера для CRUD каждой таблицы генерировали-компилировали "временные процедуры".
И это в системе, которая называется SQL server. Ещё раз: MS SQL Server 2000 не является полноценным SQL-сервером (поправка).
Даже сейчас мы работаем с ним через кодогенератор (читай - дублируем код в промышленных масштабах).
Не говоря о невероятной кривизне инсталляции 2005-го и затруднениях в администрировании.
И ведь этот вторичный продукт покупают.

Но я хотел поговорить о процедурах как о подходе, не о MS SQL. Какой, в ухо, sql injection, если всё общение - между application server и db server? Нет такой буквы.
Далее, fine-grained security, да. Наверняка более мелкозернистая, чем при распределении доступа к столбцам и строкам. Но зачем она нам? Ищу и не нахожу ответа. Обычно всё равно приходится реализовывать более-менее полнофункциональный API для каждой сущности - получить список, получить по ключу, записать, удалить. В результате security не более fine-grained, чем на таблицах/столбцах.
Не говорите мне о вынесении бизнес-логики на уровень сервера БД. Ни один из известных мне популярных языков хранимых процедур не приспособлен для более-менее приличного программирования, у них ужасный синтаксис и никакие отладчики. Исключение, может быть, составляют встраиваемые в PostgreSQL языки, и оракловая Джава. Но и тут я не уверен. Более-менее сложная логика превратит вашу БД в legacy code очень быстро.

Нарыл ссылок:
http://en.wikipedia.org/wiki/Stored_procedure
отличная дискуссия: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx "Stored procedures also will open up a maintenance problem. The reason for this is that they form an API by themselves. Changing an API is not that good, it will break a lot of code in some situations. Adding new functionality or new procedures is the "best" way to extend an existing API"
http://www.devx.com/Java/Article/29337
http://www.praetoriate.com/t_grid_rac_admin_security.htm
http://www.microsoft.com/technet/prodtechnol/mom/mom2005/Library/1405f97c-29c2-457f-b75a-e4800b085ae6.mspx?mfr=true

Comments

[beskov.livejournal.com]

ru_sql пока меня не пускает, ru_case сдох из-за неправильного названия.
Думаю, можно писать в ru_sysarchitect, там вас поймут.

Я щас как раз пишу статейку на тему "когда и зачем нужны ХП" ) Пока набрались следующие примеры:
# Аудит изменений в БД (как архитектурное требование)
# Задачи администрирования БД
# Кодогенерация БД
# Реализация уровня доступа к данным (DAL, Data Access Layer) в компонентной архитектуре
# Реализация бизнес-логики

[singalen]

Очень интересно будет почитать. Дайте пока пройдусь по пунктам.
- аудит изменений стоит совать в БД только если её меняют несколько разных систем. В противном случае это проще сделать на уровне DAL.
- про администрирование не понял.
- снова не понял. Кодогенерация БД и DAL из модели? Это не предназначение, а способ работы. Ничто не мешает сделать то же самое без процедур. И слово "кодогенерация" часто значит, что мы что-то делаем неправильно.
- не думаю, что ХП лучше приспособлены к компонентной архитектуре, скорее наоборот.
- хай меня лучше ранят, чем я буду писать бизнес-логику на языке ХП. Там же даже инкапсуляции нет на уровне объекта, а значит, мы будем иметь полный промискуитет в межмодульных связях.

Аудит изменений

[beskov.livejournal.com]

Под аудитом изменений могут пониматься разные вещи:
а) Common History Logging - ведение общего журнала с информацией о том, кто когда и что менял. Это в принципе лучше делать на уровне DAL, но не исключены и System-Wide Triggers.
б) Personal History Table - ведение журналов изменений каждого объекта с возможностью отката в определённое состояние - на потабличных триггерах.
в) ведение журналов изменений каждого объекта с возможностью получения его состояния на определённый момент - то же самое. Подробнее см. здесь

[singalen]

Я преисполнился. Думал, что знаю о БД довольно много, но тут почувствовал себя сынком %)

Второй и третий пункт отнесем к вопросу о версионности. Это довольно сложная бизнес-логика, которую лучше реализовывать на более структурируемых языках. Работал с паттерном "исторические данные", сейчас он мне кажется самым простым (KISS) решением для этой задачи.

Администрирование

[beskov.livejournal.com]

Примеры административных задач:
1. Сбор статистики о кардинальности таблиц.
2. Генерация скрипта различий для двух БД.

[singalen]

1. select справится не хуже. А если нужны данные в квази-рилтайме - то триггер.
2. Очень узкая задача. Фактически, одна процедура. Хотя, да, полезная.

Кодогенерация

[beskov.livejournal.com]

При поддержке в СУБД схемы метаданных (Information_Schema в стандарте SQL99?) и динамического SQL с помощью ХП могут очень эффективно решаться задачи кодогенерации, например:

а) Создание таблиц-дубликатов для ведения истории изменений для задачи аудита изменений в БД и соответствующих триггеров (см.выше).

б) Приведение имён объектов (унаследованной) БД к стандарту, например создание явных семантически значимых имён внешних ограничений, ограничений типа NOT NULL, первичных ключей, индексов и т.д. См. пример для NOT NULL и Oracle.

Если что-то тут неправильно, готовы выслушать.

Re: Кодогенерация

[singalen]

а) Испугался задачи и молчу. Не понял, но кажется, что это что-то страшное :)
б) Это тот же select+DDL, ХП здесь - только форма. К тому же, на DDL это было бы более прозрачно и отлаживаемо.

Компонентная архитектура

[beskov.livejournal.com]

Ну вообще статья предназначается для не очень опытных веб-разработчиков (в ru_mysql), которые привыкли смешивать SQL-запросы и HTML в одном коде. Типа Пиара компонентной архитектуры:

"...Изолированные друг от друга слои системы реализуют таким образом инкапсуляцию и позволяют вносить изменения в каждый уровень (например, с целью рефакторинга) с минимальным воздействием на прочие. Кроме того, такой "многослойный пирог" позволяет распределять задачи каждого уровня по разным физическим узлам, оптимизируя характеристики оборудования под решаемые задачи и вводя при необходимости распределение нагрузки, динамически перенаправляя поток вызовов на дублирующие узлы одного уровня (фермы, репликация).

Компонентная архитектура также позволяет добиться более эффективной совместной разработки, когда каждый разработчик или группа отвечает за определённый слой, являясь как бы центром компетенции по технологиям данного слоя, что особенно важно при создании больших и/или высокопроизводительных систем. Например, разработчик веб-сервисов и бизнес-логики может быть экспертом в технологиях java или c#, но ему не обязательно быть знатоком баз данных и SQL - все необходимые методы доступа к данным и структуры для их хранения создаст разработчик БД (сокрытие сложности БД).

В такой архитектуре хранимые процедуры могут эффективно реализовывать уровни доступа к данным (прежде всего) и, возможно, бизнес-логики."

[singalen]

Как показывает практика, CALL {} мешать с прочим кодом ничуть не сложнее, чем SELECT/UPDATE.

Неопытные разработчики и размер проекта

[nponeccop.livejournal.com]

Ну вообще статья предназначается для не очень опытных веб-разработчиков (в ru_mysql), которые привыкли смешивать SQL-запросы и HTML в одном коде.

Это не "не очень опытные", а "совсем нулевые" веб-разработчики, в 2006-то году. Я вижу такую эволюцию веб-программиста (и веб-проектов, которые он может потянуть):

1) Нулевой веб-программист. Смешивается SQL, PHP и HTML.

Если мало слоя логики (я называю такие проекты "базками"), то этот подход начинает давать сбои уже при 10 таблицах. Если логики много - то даже раньше.

2) Не очень опытный веб-программист. HTML выносится в презентационный слой (шаблоны, ASP.NET controls и иже с ними), но SQL мешается с PHP. Максимум - создаются абстракции таблицы и формы редактирования записи для простых случаев.

Такой подход работает в случае проекта размером 50 таблиц прекрасно, даже при наличии логики обитающей целиком в PHP или во внешних компонентах (в моем случае было что-то типа RPC на Perl поверх SSH).

Ваш подход необходим на гораздо более высоких уровнях сложности проектов и компетенции разработчиков - когда второй подход перестанет хорошо масштабироваться. Например, в случае колоссальной бизнес-логики, когда мало страниц просто отображают и редактируют профильтрованные записи в таблице. Или в случае 400 таблиц, когда в данных чёрт ногу сломит и бывают случаи, когда изменения в БД не затрагивают логики страниц. В малых проектах (скажем, 3 человекогода) такие случаи крайне редки и слой абстракции не приводит к изоляции изменений.

Бизнес-логика

[beskov.livejournal.com]

Насколько я знаю, есть ряд систем, например, банковские, в которых вся БЛ лежит в ХП, вроде как из соображений безопасности. Пока у меня мало фактов и доводов по поводу того, когда стоит использовать ХП для БЛ.

[singalen]

Вот безопасность меня и заинтересовала.
У меня самого нет никакого опыта в системах, в которых реально ради безопасности стоит делать ХП, не повторяющие операции CRUD.
А для CRUD SQL всяко лучше.

Недавно показывали систему на Smalltalk, в которой нет РБД. Есть только RTE для объектов.
Вот это да, это я понимаю :)

[cotyary.livejournal.com]

"Не говорите мне о вынесении бизнес-логики на уровень сервера БД. Ни один из известных мне популярных языков хранимых процедур не приспособлен для более-менее приличного программирования, у них ужасный синтаксис и никакие отладчики. Исключение, может быть, составляют встраиваемые в PostgreSQL языки, и оракловая Джава. Но и тут я не уверен."

Ну хочешь-не хочешь, а иногда приходится выносить. Делали проэктик с больно дурным поском в базе (куча критериев с весами, исключениями и фонетическим соответствием). Пришлось таки совать в базу. Заюзали "оракловую Джаву". Что могу сказать - прикольно. Взоимодействие с базой всё-одно идёт через JDBC, со всеми вытекающими. Тока чтоб на сервак задеплоить приходилось всё время админа дёргать - нам правов на это не давали. Но в общем (за редким исключением) код можно отладить и на стороне (спасибо коннекту через JDBC) а потом залить на сервер.
И трейсить, опять-же из-за прав, не очень удобно.

"Более-менее сложная логика превратит вашу БД в legacy code очень быстро" - т.к у Джавы для коннекта используется JDBC, то доступ к таблицам, в общем, зависит от того, какой (и с какими правами в результате) коннекшн стринг ему будет отдан

[singalen]

Вах, какие люди! Круто! 8)

По пунктам. Первый - это оптимизация, которая всегда делается после архитектуры и реализации, и, как правило, ломает оные. Но тут ничего не попишешь. Это то исключение, которое подтверждает правило.

По второму - я имел в виду не подключение, а внутренности БД. Представь себе хотя бы пару метров кода бизнес-логики на PL/SQL, при том, что все таблицы - в глобальном неймспейсе. Мне уже становится плохо.

[cotyary.livejournal.com]

Угу - жуть как круто Ж8-))))

1. Не всегда. В случае, о котором я говорю это была часть архитектуры. Прирост скорости ответа на порядок трудно назвать просто оптимизацией Ж;-). А на счёт ломает - эт как спроектировать Ж;о)

2. Согласен - жуть. Но именно такой интерпретации в твоём посте не было Ж;-))). И абсолютно согласен, что языки типа ПЛ/Сиквела просто не для того. Я просто подкорректировал твой пост для случая СиквелДжея.

[singalen]

1. Это типичнейшая оптимизация, причём начальный этап - когда находят bottleneck, производительность прыгает где-то так. Бывает и несколько порядков.
2. Было, правда, немного нечитабельно: Не говорите мне о вынесении бизнес-логики на уровень сервера БД. Ни один из известных мне популярных языков хранимых процедур не приспособлен для более-менее приличного программирования, у них ужасный синтаксис и никакие отладчики.
Про high coupling, да, не сказал. Но это же очевидно :)

MS SQL Server 2000 не является полноценным SQL-сервером

[cotyary.livejournal.com]

Вить, можно поподробней про сабж. Что ты под этим понимаешь?

Re: MS SQL Server 2000 не является полноценным SQL-сервером

[singalen]

Похоже, что я приврал. С версии 2000 (8.0) появился execution plan cache.
В доке 2000-го есть страничка "Execution Plan Caching and Reuse".
В доке на 7-й такой странички нет, более того, там есть прямые рекомендации - пользоваться ХП, потому что SQL выполняется медленнее (в СофтВебе меряли - для 6.5 разница была в 40 раз).
Быстрый поиск сказал вот что: http://www.webmasterworld.com/forum47/1823.htm
In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan.

Re: MS SQL Server 2000 не является полноценным SQL-сервером

[cotyary.livejournal.com]

Ааа .. никогда об этом не задумывался - с Оракла сразу на 2000й перешел.

Кстати, никогда толком не понимал, когда народ в одной процедуре на клиенте из 5 строчек ставит первой строкой PrepareStatement с обычным селектом (часто даже без всяких джойнов), во 2-4й заполняет параметры, последней - execute. А процедура выполняется раз в полчаса. На что расчет? На то, что ДБ сервер прикомпилит стэйтмент, а потом при последующих вызовах будет его сначала искать в кеше компилиных стейтментов, используя текст стейтмента в качестве ключа. И типа это быстрее, чем скомпилить заново, что-ли? Так-ли велика разница?

[singalen]

Prepare никогда не будет лишним.
Паттерн :) Скажи спасибо, что они его выучили :)

[(Anonymous)]

По поводу изначального вопроса подход с реализацией DAL на ХР состоит не только в том что мы все делаем с помощью ХР но и в том что мы заприщаем прямое обращение с помощью SQL к базе (у всех пользователей отсутствуют права на select update insert таблиц а есть права только на необходимые хранимые процедуры) таким образом не возможно выполнить произвольный запрос к базе воспользовавшись например sqlplus или чем-то подобным. Понятно что такой подход выглядит более секьюрным чем использование непосредственно SQL запросов. Так же при использование например row-level security в Оракале без ХР вообще обойтись не удасться :(. Да еще наверное какой-то выиграш в производительности можно получить за счет уже скомпилированости запросов в ХР хотя тут видемо все зависит от производителя БД и самой структуры приложения. Так же можно сделать аудит на выборку данных.
А по поводу сложности написания БЛ на сервере вынужден с вами не согласться наоборот те разработчики которые относятся к БД просто как к месту для хранения объектов обычно имеют большие проблеммы с производительностью (причем не только кода но и разработки). По собственному опыту могу сказать что написание БЛ на pl-sql довольно удобное занятие (кстати отладчики с среды разработки не хуже чем для java) а вот что касается t-sql то да действительно это не очень предназначенное для большого количества кода средство хотя есть как минимум одна комерческая ERP система написанная с логикой на сервере под MS SQL интересно чего это стоило разработчикам :) зато внедренцам и купившим систему хорошо они могут вносить изменения в систему не обращаясь к разработчика вот такой вот почти open source получается :)

[singalen]

То что обращение по SQL закрывается, мне казалось, очевидно. Уточню.
Мне, собственно, было интересно, действительно ли этот подход более секьюрен, как кажется на первый взгляд. А аудит достуап можно сделать и по SQL-запросам.

Второй. Не могли бы Вы поподробнее описать системы, в которых были проблемы с производительностью из-за "места для хранения объектов", и сами проблемы?
А если у вас есть сравнительные оценки трудозатрат на системы с бизнес-логикой в БД, в domain model и с размазанной по GUI, то это будет вообще волшебно. Хотя бы навскидку.

написание БЛ на pl-sql довольно удобное занятие (кстати отладчики с среды разработки не хуже чем для java)
О, а как у PL-SQL с юнит тестами? С рефакторингами, есть extract method? С инкапсуляцией на уровне объектов (про пакеты знаю)? С записью в лог из транзакции (в последний раз я смотрел API для 8i, было плохо)? С метриками кода? С инструментами автоматического анализа кода, вроде FindBugs?

Да, если внедренцы могут быстрее исправить баг, это уменьшит его стоимость в десяток раз, так что это удобно. И даже если при этом исправить этот баг в 5 раз дороже, чем в коде domain model, мы выигрываем в сопровождении - но проигрываем в разработке.
За внедренцев я, конечно, рад, но разработка меня интересует больше.
Примерно такой же выигрыш даст скриптование domain layer-а или DSL, и саппортить их будет ещё дешевле. Но это ещё более сложно, чем создать domain model, проще свалить всё в ХП.

[(Anonymous)]

Если очевидно что доступ по SQL запрещаеться то я честно говоря не совсем понимаю ваши сомнения по поводу безопасности. Понятно что гипотетическая возможность выполнить произвольный запрос (изменение данных) всегда более опасна чем выполнение контролируемого кода. А по поводу аудита доступа по запросам можно его сделать (без DAL на ХП) или нет очень сильно зависит от поставщика БД и по любому будет делаться на стороне БД (я считаю что любой аудит данных должен быть не зависим от способа доступа к ним).

Подробнее по поводу "места для хранения объектов" написанно у Кайта (хотя он конечно лицо заинтересованное :) на я с его доводами по большей части согласен). Из того что приходилось видеть самому проблеммы начинаються как только на клиенте (сервер приложений) пытаються что либо проджойнить а это случаеться сплошь и рядом либо возникает какая-либо операция с множеством объектов, либо выборка объектов по сложному критерию если объект сложно составной. Еще что интересно такой подход часто влечет за собой переписывание функционала имеющегося в базе (блокировки, транакционый механизм, кэши, ограничение целостности) вы сами в данном обсуждении высказывали мысль самим писать кэш запросов :).
По поводу трудозатрат тут на мой взгляд все зависит от объема того что требуеться написать а так же от необходимости сопровождения (расширения функциональности) написанного понятно что при большом объеме кода или при необходимости сопровождения код размазаный по UI не катит совершенно как говориться легче переписать чем дороботать, что же касаеться domain model (если я под этим понимаю то же что и вы) и БЛ в БД то я не вижу особых противоречий по написанию системы с использованием этих двух подходов совместно все видемо зависит от конкретной системы (требований к ней).

По поводу удобства средств для PL-SQL. С unit тестами на PL-SQL все нормально :). Есть правда проблеммы (решаемые но не так легко и красиво как хотелось бы) с тестированием клиент-серверных программ вообще..., но они к наличию либо отсутствию кода в БД не относяться. Рефакторинг во всяком случае extract method есть. По поводу записи в лог из транзакции то же все хорошо причем в 8i необходимые средства уже были.(dbms_application_info, dbms_aq, dbms_pipe или dbms_alert что из этих средств не требует фиксации транзации не вскидку не помню, использование автономных транзакции, использование вызова внешних процедур). Как видите средств много нужно только выбрать более удобное... Насчет метрики кода и анализа честно говоря не знаю как то оно не нужно было принципиальных причин для отсутствия таких инструментов не видно так что наверное если поискать то что-то найдеться. Насчет скрытия методов на уровне объектов в том же глубоко всеми любимом лиспе живут же как то люди со скрытием методов на уровне пакетов и ничего не жалуются... А если серьезно то ИМХО не смотря на наличие объектов PL-SQL для истино объектно-ореентированного програмирования не слишком подходит, но опять же это видь только одна из многих парадигм программирования и на ней свет клином не сошелся... можно хорошо писать большие системы и без объектов так же как можно ужасно спроектировать систему с объектами.

[beskov.livejournal.com]

О, а как у PL-SQL с юнит тестами?
http://www.apollo-pro.com/help/pl_unit.htm

С записью в лог из транзакции (в последний раз я смотрел API для 8i, было плохо)
http://log4plsql.sourceforge.net/ Не уверен, что там используются автономные транзакции, но в 9-ке они появились именно для таких задач.

С рефакторингами, есть extract method? С инкапсуляцией на уровне объектов (про пакеты знаю)?
Я щас копаю в эту сторону, обчитавшись Фаулера - PL/SQL немного жмёт. Нет полиморфизма, сокрытие только через методы. Пакеты можно считать singleton, но на одних их много не построишь.