«Розумний» SQL

Багато програмістів, які працюють з 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
as
begin
   select *
   from pubs..authors a
      inner join #params t on
      a.state = t.state
end
go

Тепер для вирішення нашої задачі залишилося написати наступний скрипт:

create table #params
   (state varchar(2))
insert into #params(state)
   values(“ca”)
insert into #params(state)
   values(“mi”)
insert into #params(state)
   values(“ks”)
exec dbo.testproc
   drop table #params

Просто, чи не так? До речі, команду drop table #params можна не виконувати. Тимчасова таблиця #params буде автоматично видалена при завершенні сесії.

Порівняння двох таблиць

Припустимо, потрібно порівняти дві таблиці за умовою "І". Наприклад – вибрати видавців, що випускають книги за темами "Психологія" ("psychology") і "Бізнес" ("business"). Елементарно? Звичайно, але що робити, якщо умов багато і їх кількість невідома? Можна оголосити змінну @count, В якій буде зберігатися кількість збігів (кількість унікальних записів з таблиці, з якою ми порівнюємо). Потім треба порівняти кількість неоднакових збігів цікавлять нас тим кожного видавця з @count. Потрібні нам запису – ті, які задовольняють останньому умові. Спробуємо написати скрипт:

/ * Створимо і заповнимо тимчасову таблицю # testtable, що містить умови пошуку. * /
create table #testtable
   (type varchar(15))
insert into #testtable
   values(“business”)
insert into #testtable
   values(“psychology”)
/ * Оголосимо змінну @ count. * /
declare
   @count int
/ * І збережемо в ній кількість неповторюваних записів у таблиці # test * /
select
   @count = count(distinct type) from #testtable
select
   distinct p.pub_name
from #testtable inner join
   titles t on
   t.type = #testtable.type inner join
   publishers p on t.pub_id = p.pub_id
group by
   p.pub_name
having
   count(distinct t.type) = @count

Цей прийом цілком сумісний з прийомом, наведених у попередньому пункті. Разом вони дуже корисні для процедур пошуку.

Виконання динамічного запиту при відсутності прав на вибірку даних з таблиці

Іноді бувають ситуації, коли простіше сформувати умова where в клієнтському додатку, а потім передати його як параметр в збережену процедуру. У випадку, коли існує дозвіл на вибірку даних з бажаної таблиці, рішення буде тривіальним. Але що робити, якщо користувач має право користуватися тільки збереженими процедурами?

Відповідь досить простий. Процедура працює з базою даний з "сумарними" правами власника і користувача, який її викликав. Так, будь-яка процедура, створена користувачем dbo, має всі права на базу даних. Але якщо у процедурі зустрічається оператор exec, То він виконується від імені користувача, що викликав процедуру. Якщо ми не дали достатньо повноважень для виконання запиту, присутнього в операторі exec – Відбудеться помилка. Вирішення цієї проблеми просте – вивантажити дані в тимчасову таблицю, а потім вивести їх у відповідності з нашими умовами:

create procedure dbo.testproc
   (@where varchar(8000) = null)
/ * Створимо процедуру dbo.testproc * /
as
begin
set nocount on
/ * Вона буде робити вибірку даних з таблиці dbo.authors
бази даних pubs, які відповідають умовам, що зберігається у змінній @ where * /
/ * Створимо таблицю # tmp. * /
create table #tmp (
   au_id varchar(11) not null ,
   au_lname varchar(40) not null ,
   au_fname varchar(20) not null ,
   phone char(12),
   address varchar(40) null ,
   city varchar(20) null ,
   state char(2) null ,
   zip char(5) null ,
   contract bit not null
)
/ * Вивантажити дані з таблиці dbo.authors бази даних pubs в # tmp … * /
insert into #tmp select * into #tmp from pubs..authors
/ * … І виконаємо вибірку, що задовольняє наших умов. * /
if (@while = “”)
   select * from #tmp
else
   exec(“select * from #tmp where ” + @where)
set nocount off
end

Тепер можна перевірити роботу процедури. Припустимо, у нас існує користувач з ім'ям manager. Заборонимо йому вибирати дані з таблиці dbo.authors:
deny select on dbo.authors to manager

І дозволимо виконання процедури dbo.testproc.
grant execute on dbo.testproc to manager

Змінимо поточного користувача на manager.
setuser “manager”

І виконаємо процедуру dbo.testproc
exec dbo.testproc "au_lname like" "gree%" "and state in (" "ca" "," "mi" ")"

Використання бази даних tempdb

База даних tempdb існує для зберігання тимчасових об'єктів і створюється заново при кожному запуску sql server "а. У ній же зберігаються результати виконання складних запитів і процедур. Тут мені хотілося б поговорити про використання тимчасових таблиць і тимчасових процедур.

Досить часто програмісти припускаються помилки, користуючись конструкцією select into в складних запитах з вивантаженням даних в тимчасову таблицю. Проблема в тому, що під час виконання такого запиту база даних tempdb блокується. Це розумно тільки у випадку, коли точно відомо, що вибірка даних буде короткочасною. Треба враховувати і те, що деякі таблиці, з яких відбувається вибірка даних, можуть бути заблоковані іншими процесами. Якщо ж кількість полів тимчасової таблиці велике або невідомо, то для її створення краще скористатися таким запитом:
select top 0 * into #temptable from …

Причому запит не повинен включати в себе умов відбору і сортування, а таблиці повинні бути перераховані через кому.

Перейдемо до тимчасових процедур. На жаль, microsoft тільки до середини 2000 року помітила величезне упущення в роботі з тимчасовими збереженими процедурами – вони дозволяють будь-якому користувачеві виконати будь-яку (Навіть заборонену для нього) збережену процедуру. Давайте розглянемо цю ситуацію на прикладі з попередньої глави. Заборонимо користувачеві з ім'ям manager виконання процедури dbo.testproc:
deny execute on dbo.testproc to manager

Змінимо поточного користувача на manager.
setuser “manager”
І виконаємо процедуру dbo.testproc.
exec dbo.testproc "au_lname like" "gree%" "and state in (" "ca" "," "mi" ")"

У відповідь ми отримаємо:
server: msg 229, level 14, state 5, procedure testproc, line 1
execute permission denied on object "testproc", database "pubs", owner "dbo".

Тепер створимо тимчасову процедуру #testproc:

create procedure #testproc
   (@where varchar(8000) = null)
as
begin
   exec dbo.testproc @where
end

І виконаємо її:
exec # testproc "au_lname like" "gree%" "and state in (" "ca" "," "mi" ")"

Всі чудово працює!

Як же убезпечити дані від таких ситуацій? Процедурі доведеться самій перевірити, хто ж її запустив, і чи має він на це право. Припустимо, у нас є група користувачів managers. Додамо до неї користувача manager. Тепер за допомогою функції is_member() дізнаємося, чи належить користувач до цієї групи. Додамо в початок процедури dbo.testproc рядки:
if (is_member(“managers”) != 1)
begin
raiserror ("Тільки користувачі, що входять до групи" "managers" "можуть викликати цю процедуру", 16, 1) end

Таку перевірку слід включати в початок кожної процедури.

Офіційне рішення цієї проблеми див на сервері microsoft за адресою:
http://www.microsoft.com/downloads/release.asp?releaseid=22470 (для платформи intel) і http://www.microsoft.com/downloads/release.asp?releaseid=22469 (для alpha).

Обробка результату роботи збереженої процедури

Часто бувають ситуації, коли одну велику процедуру хочеться розділити на кілька маленьких; або в декількох процедурах використовується однаковий код, який краще винести в окрему процедуру. Але постає питання: як отримати від неї дані про результат її роботи? Одного коду повернення найчастіше буває мало. Тим більше цікаве питання передачі повертаються процедурою даних. У books online, на жаль, цей важливий момент зовсім не розглянутий. Тим не менш, ця можливість активно використовується при створенні бази даних master. Синтаксис такого запиту наступний: insert into таблиця exec процедура.
Для прикладу створимо процедуру dbo.testproc, Вибирає з таблиці dbo.authors поля au_id, au_lname і au_fname і тимчасову таблицю #testtable з полями au_id, au_lname і au_fname.

/ * Створимо процедуру dbo.testproc * /
create procedure dbo.testproc
as
begin
   select au_id, au_lname, au_fname from dbo.authors
end
go

/ * Створимо тимчасову таблицю # testtable * /
create table #testtable
   (au_id varchar(20) not null,
   au_lname varchar(40) not null,
   au_fname varchar(20) not null)
go

/ * І виконаємо вставку значень з збереженої процедури dbo.testproc * /

insert into #testtable exec dbo.testproc
go

/ * Тепер можна подивитися на результат * /
select * from #testtable


Шифрування даних

У додатках, що працюють з 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, в якій будуть зберігатися імена та паролі користувачів * /
create table dbo.testtable
   ( username varchar(30),
   password varbinary(16))

/ * Додамо користувача "user1" з паролем "user1", зашифрованим функцією encrypt () * /
insert into dbo.testtable
   (username, password)
values(“user1”, encrypt(“user1”))

/ * … Користувача "user1_1" з паролем "user1", функція encrypt () * /
insert into dbo.testtable
   (username, password)
values(“user1_1”, encrypt(“user1”))

/ * "User1_2", пароль "user1", функція encrypt () * /
insert into dbo.testtable
   (username, password)
values(“user1_2”, encrypt(“user1”))

/ * "User2", пароль "user2", pwdencrypt () * /
insert into dbo.testtable
   (username, password)
values(“user2”, convert( varbinary, pwdencrypt(“user2”)))

/ * "User2_1", пароль "user2", pwdencrypt () * /
insert into dbo.testtable
   (username, password)
values(“user2_1”, convert( varbinary, pwdencrypt(“user2”)))

/ * "User2_2", пароль "user2", pwdencrypt () * /
insert into dbo.testtable
   (username, password)
values(“user2_2”, convert( varbinary, pwdencrypt(“user2”)))

/ * А тепер – подивимося на результат. * /
select * from dbo.testtable

 – 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

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*