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

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 никогда не будет лишним.
Паттерн :) Скажи спасибо, что они его выучили :)

[cotyary.livejournal.com]

С месяц назад возился с ATLным OLE DB под плюсы.
Там они к преперу относились очень демократично. В некоторых конструкциях его вызывать необязательно, в некоторых его просто нет Ж;-)

В общем ясно. Похоже, как я и думал, расчёт таки на внутренний кеш компилид стейтментов.
Интересно, видел-ли кто реальный эффект от этого ... надо Берлянда спросить Ж;-)))

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

[beskov.livejournal.com]

А процедура выполняется раз в полчаса..
Системы имеют свойство расти, и то, что сегодня кажется Overkill, завтра окажется подстеленной соломкой.

"Скомпилить заново" (запрос на лету) - значит:
1. Поискать запрос в кэше, если он отличается хотя бы символом ("order=1", а не "order=2"), значит в кэше его нет.
2. Распарсить запрос синтаксически.
3. Проверить права доступа пользователя к использованным в запросе таблицам.
4. Построить план выполнения.
5. И только теперь выполнить.

Прекомпилированные же ХП, запущенные хотя бы один раз, сидят в кэше.

[singalen]

Этого overkill можно избежать, если:
- не склеивать явно строку запорса со значениями. Или не использовать MS SQL :) В Oracle, и даже, кажется, даже в MySQL, есть опция - подменять явные значения параметрами, и кэшировать планы запросов в таком виде.
- кэширвать препарированные запросы на уровне драйвера или, на худой конец, фасада к драйверу.
И сохранить клиентский код простым.