|
|
|||||||||||||||||||||||||||||
|
«Умный» SQLИсточник: lsoft Павел Леченко
Многие программисты, работающие с Microsoft SQL Server, предпочитают писать логику приложения в коде своих программ, используя sql server только как хранилище данных. Одна из причин такого подхода - обычная лень (например, при переносе базы данных с Access на SQL Server), другая же причина - мнение, что язык SQL не столь гибок, как Delphi, C++ и другие языки программирования. Но ведь он и не предназначен для работы с конечным пользователем! На самом деле - SQL предоставляет необходимый минимум возможностей для работы с данными; надо только ими правильно воспользоваться. В этой статье я хочу поделиться некоторыми хитростями работы с Microsoft SQL Server, которые я использую в своих проектах. Сразу оговорюсь, что все скрипты написаны для MS SQL Server 7.0. Передача таблицы значений в хранимую процедуру Нередко возникает вопрос: "Как передать в хранимую процедуру массив (таблицу) значений?". Стандартные решения - вызов процедуры для каждого значения по отдельности, либо передача массива в виде строки с разделителями, которая потом разбирается хранимой процедурой. Очевидно, что в случае, когда размер массива значений велик, первый подход очень сложен для сервера (и его выполнение занимает очень много времени), второй же ограничен максимальной длиной типа varchar (8000 байт). Однако, есть решение, свободное от этих недостатков. Можно создать временную таблицу, занести в нее массив значений - и выполнить хранимую процедуру, которая будет брать данные из созданной таблицы. Все команды выполняются в одной сессии. Единственный недостаток такого подхода очевиден - процедура должна точно знать названия таблицы и полей. Допустим, нам надо выбрать все записи из таблицы authors базы pubs, у которых поле state равно одному из значений ['ca', 'mi', 'ks']. Создадим процедуру dbo.testproc: create procedure dbo.testproc Теперь для решения нашей задачи осталось написать следующий скрипт: create table #params Просто, не правда ли? Кстати, команду drop table #params можно не выполнять. Временная таблица #params будет автоматически удалена при завершении сессии. Сравнение двух таблиц Допустим, нужно сравнить две таблицы по условию 'И'. Например - выбрать издателей, выпускающих книги по темам "Психология"("psychology") и "Бизнес"("business"). Элементарно? Конечно; но что делать, если условий много и их количество неизвестно? Можно объявить переменную @count, в которой будет храниться количество совпадений (количество уникальных записей из таблицы, с которой мы сравниваем). Затем надо сравнить количество неодинаковых совпадений интересующих нас тем каждого издателя с @count. Нужные нам записи - те, которые удовлетворяют последнему условию. Попробуем написать скрипт: /* Создадим и заполним временную таблицу #testtable, содержащую условия поиска. */ Этот прием вполне совместим с приемом, описанным в предыдущем пункте. Вместе они очень полезны для процедур поиска. Выполнение динамического запроса при отсутствии прав на выборку данных из таблицы Иногда бывают ситуации, когда проще сформировать условие where в клиентском приложении, а затем передать его как параметр в хранимую процедуру. В случае, когда существует разрешение на выборку данных из желаемой таблицы, решение будет тривиальным. Но что делать, если пользователь имеет право пользоваться только хранимыми процедурами? Ответ достаточно прост. Процедура работает с базой данный с "суммарными" правами владельца и пользователя, который ее вызвал. Так, любая процедура, созданная пользователем dbo, имеет все права на базу данных. Но если в процедуре встречается оператор exec, то он выполняется от имени пользователя, вызвавшего процедуру. Если мы не дали достаточно прав на выполнение запроса, присутствующего в операторе exec - произойдет ошибка. Решение этой проблемы простое - выгрузить данные во временную таблицу, а затем вывести их в соответствии с нашими условиями: create procedure dbo.testproc Теперь можно проверить работу процедуры. Допустим, у нас существует пользователь с именем manager. Запретим ему выбирать данные из таблицы dbo.authors: И разрешим выполнение процедуры dbo.testproc. Изменим текущего пользователя на manager. И выполним процедуру dbo.testproc Использование базы данных tempdb База данных tempdb существует для хранения временных объектов и создается заново при каждом запуске sql server'а. В ней же хранятся результаты выполнения сложных запросов и процедур. Здесь мне хотелось бы поговорить об использовании временных таблиц и временных процедур. Достаточно часто программисты допускают ошибку, пользуясь конструкцией select into в сложных запросах с выгрузкой данных во временную таблицу. Проблема в том, что во время выполнения такого запроса база данных tempdb блокируется. Это разумно только в случае, когда точно известно, что выборка данных будет кратковременной. Надо учитывать и то, что некоторые таблицы, из которых происходит выборка данных, могут быть заблокированы другими процессами. Если же количество полей временной таблицы велико или неизвестно, то для ее создания лучше воспользоваться таким запросом: Причем запрос не должен включать в себя условий отбора и сортировки, а таблицы должны быть перечислены через запятую. Перейдем к временным процедурам. К сожалению, microsoft только к середине 2000 года заметила огромное упущение в работе с временными хранимыми процедурами - они позволяют любому пользователю выполнить любую (даже запрещенную для него) хранимую процедуру. Давайте рассмотрим эту ситуацию на примере из предыдущей главы. Запретим пользователю с именем manager выполнение процедуры dbo.testproc: Изменим текущего пользователя на manager. В ответ мы получим: Теперь создадим временную процедуру #testproc: create procedure #testproc И выполним ее: Все прекрасно работает! Как же обезопасить данные от таких ситуаций? Процедуре придется самой проверить, кто же ее запустил, и имеет ли он на это право. Допустим, у нас есть группа пользователей managers. Добавим в нее пользователя manager. Теперь с помощью функции is_member() узнаем, принадлежит ли пользователь к этой группе. Добавим в начало процедуры dbo.testproc строки: Такую проверку следует включать в начало каждой процедуры. Официальное решение этой проблемы см. на сервере microsoft по адресу: Обработка результата работы хранимой процедуры Часто бывают ситуации, когда одну большую процедуру хочется разделить на несколько маленьких; или в нескольких процедурах используется одинаковый код, который лучше вынести в отдельную процедуру. Но встает вопрос: как получить от нее данные о результате ее работы? Одного кода возврата зачастую бывает мало. Тем более интересен вопрос передачи возвращаемых процедурой данных. В books online, к сожалению, этот важный момент совершенно не рассмотрен. Тем не менее, эта возможность активно используется при создании базы данных master. Синтаксис такого запроса следующий: insert into таблица exec процедура. /* Создадим процедуру dbo.testproc */ /* Создадим временную таблицу #testtable */ /* И выполним вставку значений из хранимой процедуры dbo.testproc */ insert into #testtable exec dbo.testproc /* Теперь можно посмотреть на результат */
Шифрование данных В приложениях, работающих с sql server, часто требуется провести дополнительную идентификацию пользователя. Например, для выполнения какой-то ответственной операции надо проверить, может ли пользователь ее выполнить. Можно, конечно, попросить его ввести пароль. Но как этот пароль хранить? Прописывать его в коде самого приложения - по крайней мере глупо; хранить как открытый текст в какой-то таблице базы данных - тоже небезопасно. Остается один выход - шифровать. К сожалению, корпорация microsoft не захотела документировать функции, которые позволяют шифровать данные. Этими функциями являются: encrypt(), pwdencrypt() и pwdcompare(). Функция encrypt(значение) возвращает тип varbinary(6). Функция pwdencrypt(значение) возвращает тип nvarchar(32). Функция pwdcompare({ проверяемое значение }, { зашифрованное значение }) сравнивает введенное значение с зашифрованным, где проверяемое значение - строка типа nvarchar, зашифрованное значение имеет тип varbinary(16). Возвращаемые значения - 0 (если не совпадает), 1 (если совпадает), null (если произошла ошибка). Различия функций encrypt() и pwdencrypt() в том, что encrypt() кодирует значения по единому алгоритму, а pwdencrypt() - по меняющемуся. То есть, выполнив несколько раз select encrypt('test') мы будем получать одно и то же значение, а выполнение select convert(varbinary(16), pwdencrypt('test')) дает все время разные значения. Значение, зашифрованное с помощью pwdencrypt() можно сравнить с проверяемым только с помощью функции pwdcompare(), а зашифрованное с помощью encrypt() - сравнивая его с зашифрованным проверяемым значением. Но у функции pwdcompare() есть один большой минус - если на сервере установлена регистро-независимая кодовая страница, то проверка также будет регистро-независимой. То есть пароли 'test' и 'test' будут признаны идентичными. Приходится выбирать - либо пользоваться мощным алгоритмом, предоставляемым функцией pwdencrypt(), либо пользоваться менее защищенной encrypt(). Приведу примеры использования этих функций: /* Создадим таблицу dbo.testtable, в которой будут храниться имена и пароли пользователей */ /* Добавим пользователя 'user1' с паролем 'user1', зашифрованным функцией encrypt() */ /* …пользователя 'user1_1' с паролем 'user1', функция encrypt() */ /* 'user1_2', пароль 'user1', функция encrypt() */ /* 'user2', пароль 'user2', pwdencrypt() */ /* 'user2_1', пароль 'user2', pwdencrypt() */ /* 'user2_2', пароль 'user2', pwdencrypt() */ /* А теперь - посмотрим на результат. */ -- username password -- ------------------------------ ---------------------------------- -- user1 0x7aba56bb8e350ac328af -- user1_1 0x7aba56bb8e350ac328af -- user1_2 0x5afac964b710d95ac836 -- user2 0x2131235f272d2c375058555332214c2f -- user2_1 0x213123253a58355621592d4634295358 -- user2_2 0x213124372c2b4c2228563c454f363d56 Видно, что user1 и user1_1 имеют одинаковые пароли, а user2 и user2_1 - разные. Теперь поэкспериментируем с функцией pwdcompare() и тоже посмотрим на результаты: select username from dbo.testtable where pwdcompare('user2', password)=1 -- username -- ---------------------------- -- user2 -- user2_1 -- user2_2 select username from dbo.testtable where pwdcompare('user2', password)=1 -- username -- ---------------------------- -- user2 -- user2_1 -- user2_2 select username from dbo.testtable where pwdcompare('user1', password)=1 -- username -- ---------------------------- Аналогично для функции encrypt(): select username from dbo.testtable where encrypt('user1') = password -- username -- ---------------------------- -- user1 -- user1_1 select username from dbo.testtable where encrypt('user1') = password -- username -- ---------------------------- -- user1_2
|
|