Прямий доступ, MS SQL Server, Бази даних, статті

Келен Діланом, Журнал «SQL Magazine OnLine»

Якщо жоден спосіб легального доступу до метаданих, що зберігаються в системних таблицях, не допомагає, залишається спробувати напряму звернутися до цих таблиць.

У попередніх статтях були розглянуті способи вилучення метаданих із системних таблиць за допомогою інструментарію, що поставляється корпорацією Microsoft: системних збережених процедур і системних функцій, уявлень INFORMATION_SCHEMA і функцій властивостей. Більшу частину необхідної інформації можна отримати з системних таблиць запропонованими способами. Але не всю потрібну інформацію можна отримати за допомогою цих засобів. Тому іноді доведеться вдатися до прямого доступу до системних таблиць. (Зазначимо, що кількість ситуацій, коли доводиться застосовувати прямий доступ до системних таблиць в SQL Server 7.0 значно менше, ніж у більш ранніх версіях.)

Нові трюки

Щоб навести приклад ситуації, коли необхідно задіяти прямий доступ до системних таблиць, розглянемо додаткові можливості, введені Microsoft в SQL Server 7.0. У попередніх версіях не було коштів, за допомогою яких можна було подивитися, чи приєднається до таблиці будь-які тригери. Отримати список тригерів, підключених до певної таблиці, буває дуже корисно, особливо у тих випадках, коли програма починає поводитися дивно. Але як це зробити, якщо ви працюєте не на SQL Server 7.0? В офіційних навчальних матеріалах Microsoft помилково зазначено, що у версії SQL Server 6.5 для цього слід запустити процедуру sp_depends. Однак дана процедура показує тільки, які процедури або тригери пов'язаних з даною таблицю в тілі коду. Але в багатьох випадках тригери посилаються на таблицю, до якої вони належать, тільки в обороті ON, а не в тілі тригера, наступного за ключовим словом AS.

У SQL Server 7.0 до складу поставляються Microsoft процедур була включена процедура sp_helptrigger. Але якщо ви все ще працюєте на одній з попередніх версій SQL Server, вам допоможе приклад створення такої процедури, наведений у лістингу 1. Системна таблиця Sysobjects зберігає інформацію про те, які тригери існують для кожної таблиці. Ідентифікатор тригера може перебувати в одному з трьох стовпців цієї таблиці – deltrig, instrig і upgtrig. Ці стовпці містять осмислену інформацію тільки для тих рядків, які описують таблиці або подання. Наявність в цьому стовпці нуля означає відсутність тригера відповідного типу для даної таблиці або подання. Відмінне від нуля значення є ідентифікатором тригера зазначеного типу. Ім'я тригера можна отримати за допомогою системної функції object_name (). Якщо рядок у таблиці Sysobjects описує тригер, то в стовпці deltrig знаходиться назва таблиці, до якої він ставиться незалежно від типу тригера. Стовпці instrig і upgtrig не використовуються. (В SQL Server 7.0 в цю таблицю доданий новий стовпець parent_obj. Для тригерів в ньому також міститься ідентифікатор базової таблиці.)

Іншою поширеною операцією, не підтримується більш ранніми версіями, є зміна власника об'єкта. У SQL Server 7.0 з'явилася нова процедура sp_changeobjectowner. У попередніх випусках SQL Server для вирішення проблеми зміни власників при догляді співробітника з компанії рекомендувалося робити власником всіх об'єктів Database Owner (DBO). Звичайно, таке рішення є неприйнятним в тих випадках, коли об'єкт вже був створений, тому багатьом системним адміністраторам доводилося самим писати процедури, які дозволяли змінювати власників об'єктів. Нагадаємо, що перш ніж створювати процедуру, яка міняє власника, необхідно встановити таку конфігурацію, при якій дозволено прямий доступ до системних таблиць. Після введення такої можливості можна змінити власника об'єкта, замінивши значення в стовпці uid. (Uid являє собою ідентифікатор користувача, що є власником об'єкта.) Якщо у вашій процедурі передбачені два параметри, @ new_user_name і @ object_name, то основний текст коду буде виглядати приблизно так:


UPDATE sysobjects
SET uid = user_id(@new_user_name)
WHERE id = object_id(@object_name)


При модифікації системних таблиць слід бути особливо уважним. Якщо хтось спробує отримати доступ до таблиці в той час, як ви міняєте її власника, можна отримати непередбачуваний результат.

Перш ніж приступити до розповіді про написання процедур доступу до системних таблиць, має сенс проаналізувати, як працюють деякі існуючі процедури. Поставляються системні процедури написані на T-SQL. Такий код можна переглянути двома способами (якщо, звичайно, процедура не є розширеною). По-перше, з бази даних Master можна запустити процедуру sp_helptext для отримання ухвали будь-якої процедури. Крім того, у підкаталозі \ install каталогу, де встановлений SQL Server, знаходиться текстовий файл procsyst.sql, що містить визначення всіх системних процедур.

Прародителька кодифікатор

Читаючи визначення системних процедур, можна звернути увагу на те, що дуже часто згадується таблиця spt_values. Вона не є системною, але поставляється разом з SQL Server. Це велика довідкова таблиця. Основна частина інформації системних таблиць зберігається у вигляді закодованих значень, або поміщених в окреме поле, або як частина цифрового значення. Процедура, що інтерпретує ці коди і переводить їх на відносно зрозумілу англійську мову, формує результат, під'єднуючи таблицю spt_values ​​до розглянутої системної таблиці з закодованими значеннями.

Наприклад, процедура sp_dboption формує звіт про те, які опції встановлені для бази даних. Ця інформація зберігається в трьох стовпцях таблиці sysdatabases: status, status2 і category. Наявність 1 в четвертому бите стовпця status означає, наприклад, що встановлена ​​опція SELECT / BULKCOPY. Код процедури sp_dboption спочатку повинен перевірити, чи знаходиться в цьому бите 1, а потім знайти відповідну розшифровку у таблиці spt_values. Процедура не може просто перевірити, чи дорівнює значення sysdatabases.status чотирьом, оскільки в інших бітах також можуть бути 1. Можна використовувати бітову арифметику. Бітовим еквівалентом AND є амперсант (&). Наступна умова буде істинним, якщо четвертий біт дорівнює 1, незалежно від значень інших бітів:


sysdatabases.status & 4 <> 0


Стовпець type в таблиці spt_values ​​позначає тип представленої інформації. У документації інформація про це відсутня, але неважко обчислити значення більшості типів, аналізуючи таблицю. Приміром, тип С відповідає значенню конфігурації (configuration), L означає режим блокування (locking), DBR показує дозвіл на роль в базі даних (database role). Стосовно інформації в таблиці sysdatabases, D вказує на опцію, що зберігається в стовпці status; D2 означає величину, вміщену в стовпець status2, а DC відповідає значенню, що знаходиться в стовпці category, який показує різні опції реплікації для бази даних. В лістингу 2 наведено фрагмент процедури sp_dboption, який формує список всіх опцій, встановлених для бази даних.

Процедура sp_helpdb перераховує деякі (але не всі) опції, встановлені для бази даних. Якщо перевірити код T-SQL процедури sp_helpdb, з'ясується, що вона заглядає тільки в стовпець status, ігноруючи стовпці status2 і category. Щоб визначати, які опції встановлені для бази даних, краще використовувати процедуру sp_dboption.

Нова процедура

Тепер розглянемо деякі завдання, для виконання яких необхідно звернутися до системних таблиць. Ці завдання неможливо виконати за допомогою тих методів, про які розповідалося в попередніх статтях (Див. номери 2 і 3 нашого журналу). SQL Server дозволяє вводити в таблиці обчислювані стовпці. Визначення стовпця базується на формулі, за якою значення обчислюється заново кожен раз, коли який-небудь запит звертається до цього стовпцю. Приміром, в базi даних Pubs таблиця titles містить відомості про ціну та кількість проданих примірників кожної книги. Якщо знадобиться зберегти цю інформацію в окремій таблиці разом з інформацією про сумарну виручку від продажу книг, це можна зробити за допомогою коду, наведеного в лістингу 3. Тепер при проведенні вибірки з таблиці priceinfo значення в стовпці gross обчислюються як добуток відповідних значень, що містяться у стовпцях price і sales. Запуск збереженої процедури sp_help для таблиці priceinfo покаже, що стовпець gross є обчислюваним, проте за якою формулою виробляються обчислення дізнатися не можна.

Визначення обчислюваних стовпців зберігаються в таблиці syscomments. У стовпці ID цій таблиці зберігаються ідентифікатори об'єктів, для яких обчислюються значення. Стовпець number зазвичай містить посилання на збережену процедуру або тригер, але якщо об'єкт є таблицею, то в цьому стовпці знаходиться ідентифікатор стовпця, значення якого обчислюються. Таблиця syscolumns включає назви та ідентифікатори всіх наявних стовпців, а також стовпець ID, куди поміщається ідентифікатор таблиці, частиною якої є стовпець. Значення стовпця IsComputed таблиці syscolumns дорівнює 1 для обчислюваних стовпців і 0 для всіх інших.

Наступний код дозволяє переглянути визначення всіх обчислюваних стовпців у базі даних:


SELECT ‘table’ = object_name(cl.id),
'Column name' = name, definition = text
FROM syscolumns cl JOIN syscomments cm
ON cl.id = cm.id
AND cm.number = cl.colid
WHERE iscomputed = 1


Запуск цього запиту показує, що стовпець syscolumns.IsComputed також є обчислюваним. Його формула показує, що SQL Server обчислює значення для цього стовпця, аналізуючи біт у стовпці syscolumns.colstat:


(convert(int, sign(([colstat] & 4))))


Цей вираз поверне 1, якщо четвертий біт стовпця colstat дорівнює 1. У всіх інших випадках результатом обчислення цього виразу буде 0. Запропонований код для перегляду формул обчислюваних стовпців можна оформити у вигляді збереженої процедури, параметрами якої будуть назви таблиці та стовпця. Така процедура буде виглядати приблизно так, як показано в лістингу 4. Але не спокушайтеся: це тільки початок. Щоб процедура набула цілком закінчений вигляд, в неї необхідно ввести цілий ряд перевірок.

Оновлення системних таблиць

У якийсь момент знадобиться оновити інформацію, що зберігається в системних таблицях. Одна із слухачок курсу моїх лекцій захотіла змінити логічне ім'я файлу бази даних, оскільки їй доводилося часто робити безліч копій баз даних. У тих випадках, коли проводиться створення резервної копії бази даних та її подальше відновлення, або коли застосовуються процедури sp_detach_db, а потім sp_attach_db, з'являється можливість перемістити фізичні файли і змінити їх імена. Але логічні імена при цьому залишаються незмінними. При повному зміну імені бази даних логічно відповідним чином змінити і ім'я файлу. У SQL Server Books Online (BOL) не пояснюється, як це зробити. Системна таблиця sysfiles, в якій знаходяться імена файлів, реально не існує, це віртуальна таблиця. Але недокументіруемая таблиця sysfiles1 існує насправді. Я спробувала внести в неї зміни, замінивши назву таблиці бази даних безпосередньо у стовпці name таблиці sysfiles1. Схоже, що даний підхід призводить до успішного результату. Для такої зміни був використаний запит приблизно наступного вигляду:


UPDATE sysfiles1
SET name = ‘new name’
WHERE name = ‘old name’


При запуску процедури sp_helpfile повертається нове ім'я файлу, як і при зупинці і старті SQL Server. При створенні резервної копії бази даних та її завантаженні система також показує нове ім'я файлу (звичайно, якщо тільки відновлюється не стара копія бази даних, яку робили до зміни імені в таблицю sysfiles1). Але у виробничому середовищі я ще не протестувала пропонований підхід. Безумовно, внесення змін безпосередньо в системні таблиці не підтримується, так що необхідно попередньо забезпечити конфігурацію allow updates. І тим не менше, при необхідності замінити логічне ім'я файлу можна вдатися до цього способу.

На жаль, інформація про вміст системних таблиць ніде не документується. Її можна отримати лише шляхом скрупульозного вивчення текстів системних процедур і уважного аналізу процесу пошуку інформації за запитами системними процедурами.

Келен Ділана (kalen_delaney@compuserve.com, www.InsideSQLServer.com) Має сертифікати MCT і MCSE, працює незалежним консультантом і викладачем на північно-заході тихоокеанського узбережжя США. З SQL Server співпрацює з 1987 року. Келен написала книгу «Inside SQL Server 7.0 ", випущену видавництвом Microsoft Press, вона також є співавтором книг« SQL Server 6.5 Unleashed "і« Teach yourself SQL Server in 21 days ", виданих в Sams Publishing.

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


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

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

Ваш отзыв

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

*

*