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]

1. Какой SELECT? В базе 400 таблиц, нужно понять, какие перегружены, какие не используются.
2. В смысле узкая? Систематически у многих возникает. Процессы разработки не идеальны + есть разные ситуации.

[singalen]

1. Тогда это сильно зависит от СУБД. Какие-то могут не дать таого API и для ХП, какие-то дадут информацию select-ом из метаданных.
2. Так решается-то один раз. Или даже - скачать готовую утилиту.

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

[beskov.livejournal.com]

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

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

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

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

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

[singalen]

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

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

[beskov.livejournal.com]

б) не понял, что значит SELECT + DDL? Причём тут DDL? Есть готовая база, именование ограничений которой задавалось системно - через генерацию самой СУБД. Теперь нужно привести всё это в читаемый вид.

[singalen]

У меня то же выглядело бы примерно так:
sqlplus -f "SELECT constraint_name name, table_name tab, search_condition cond
FROM user_constraints
WHERE constraint_type = 'C'
AND constraint_name LIKE 'SYS%'" > constraints.txt
perl gen_con_names.pl < constraints.txt > rename_constraints.sql
далее выполнить rename_constraints.sql.
При этом rename_constraints.sql можно предварительно просмотреть, подправить, отладить на тексте, а не на реальной базе.

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

[beskov.livejournal.com]

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

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

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

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

[singalen]

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

[beskov.livejournal.com]

Я имел в виду, что все уровни слеплены в 1. CALL вводит уже как минимум 2 уровня.

CALL оставляет возможность разработчику БД менять структуры хранения данных (рефакторить), не затрагивая внешнего программиста.

[singalen]

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

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

[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 человекогода) такие случаи крайне редки и слой абстракции не приводит к изоляции изменений.

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

[beskov.livejournal.com]

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

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

[nponeccop.livejournal.com]

Согласен.

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

[singalen]

Давайте вырабатывать общий язык. Фаулер в "корпоративных информсистемах" называет первый случай паттерном "transaction script".
Да и второй, в общем, тоже...

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

[nponeccop.livejournal.com]

Первый и второй случай отличаются только вынесением представления в отдельный слой, так что с точки зрения управления данными они одинаковы.

Определение Фаулера для паттерна Transaction Script (http://www.martinfowler.com/eaaCatalog/transactionScript.html) выглядит довольно убого. Оно касается только того как организован слой данных в подобных системах? Или все приложения-"базки" целиком (включая код, перекачивающий данные как есть из слоя данных в слой представления) вы хотите представить в виде набора сценариев транзакций?

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

[beskov.livejournal.com]

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

[singalen]

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

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

Просветите неуча

[nponeccop.livejournal.com]

Как умные люди передают в SQL параметры? Конструирование SQL строковыми функциями (применяемое сплошь и рядом на низких уровнях зрелости) это ж нехорошо?

В Perl DBI есть "вопросики". В мире серьезных RDBMS как делают? Так?

my $recordSet = executeXp(
    'xp_name', 
    {
        'param1' => $foo, 
        'param2' => $bar
    }
}

Или то же самое в более императивно-stateful стиле:

var xp = new Request("xp_name")
xp.addParam('param1', foo)
xp.addParam('param2', bar)
var recordSet = xp.Execute();

Re: Просветите неуча

[singalen]

Везде есть вопросики. Это фича сервера и драйвера, это не perl подставляет значения.