Задачи, модели и способы проектирования баз данных

Дата: 2025-06-14; Просмотры: 1

Оценка:

0.00 из 5.00 — оценок

Скачиваний: 0

Скачать

Содержание

Задачи, модели и способы проектирования баз данных.. 3

Транзакции и блокировки.. 4

Понятие транзакций и блокировок. 4

Требования к набору команд, включенных в транзакцию.. 5

Проблемы одновременного доступа нескольких пользователей к данным. 6

Уровни блокирования данных. 8

Явные транзакции. 8

Неявные и автоматические транзакции. 9

Компоненты системы безопасности.. 11

Компоненты системы безопасности SQL Server. Учетные записи и группы.. 11

Компоненты системы безопасности SQL Server. Пользователи. 14

Компоненты системы безопасности SQL Server. Роли сервера и базы данных. 16

Роли приложения. 18

Управление правами доступа к объектам базы данных.. 20

Разрешения для объектов и разрешения для команд T-SQL. 20

Предоставление доступа. Команда GRANT. 21

Запрещение доступа. Команда DENY.. 23

Неявное отклонение доступа. Команда REVOKE. 24

Защита данных.. 26

Шифрование данных в SQL Server 26

Репликация данных.. 27

Понятие репликации данных. 27

Репликация данных. Издатель. 28

Репликация данных. Подписчик. 29

Репликация данных. Дистрибьютор. 30

Механизмы репликации. 31

Модели репликации.. 32

Репликация моментальных снимков. 32

Безотлагательное обновление при репликации моментальных снимков. 35

Репликация транзакций. 36

Безотлагательное обновление при репликации транзакций. 39

Задачи, модели и способы проектирования баз данных

Вопросы:

Основные понятия модели "сущность-связь".

Виды ключей.

Связи: направленность, мощность, обязательность, идентифицирующие и неидентифицирущие связи.

Преобразование ER-модели в реляционную (логическую схему базы данных).

См. метод. рекомендации к практическому занятию и СБОРНИК ИНДИВИД. ЗАДАНИЙ ПО ТЕХНОЛОГИЯМ БД.pdf (с. 6 – 15).

Язык реляционных баз данных SQL. Назначение и состав

Язык SQL (Structured Query Language – язык структурированных запросов) является непроцедурным языком и ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц. Формулируя запросы на языке SQL, можно создавать и модифицировать различные объекты БД и, оперируя группами строк, вставлять, выбирать, обновлять и удалять данные из таблиц. Кроме того, он позволяет управлять доступом к объектам БД и обеспечивать непротиворечивость и целостность данных, хранящихся в базе.

В архитектуре «клиент-сервер» язык SQL занимает очень важное место. Именно он используется как язык общения клиентского программного обеспечения с серверной СУБД, расположенной на удаленном компьютере. Так, клиент посылает серверу запрос на языке SQL, а сервер разбирает его, интерпретирует, выбирает план выполнения, выполняет запрос и отсылает клиенту результат.

Этот язык специально разработан для создания запросов. При использовании архитектуры «клиент-сервер» выделенный компьютер используется не только в качестве хранилища файлов, но и выполняет основной объем действий по обработке информации. Пользователь рабочей станции отправляет список операций обрабатываемых данных (запрос), которые необходимо выполнить центральному компьютеру, т. е. серверу. Сервер выполняет необходимые вычисления и выборку данных и отправляет готовый результат клиенту.

Архитектура «клиент-сервер»

В настоящее время широко распространенны следующие процедурные расширения SQL: Тип базы данных Процедурные расширения SQL Microsoft SQL Transact-SQL Microsoft Jet/Access Jet SQL MySQL SQL/PSM (SQL/Persistent Stored Module) Oracle PL/SQL (Procedural Language/SQL) IBM DB2 SQL PL (SQL Procedural Language) InterBase/Firebird PSQL (Procedural SQL)

T-SQL (Transact-SQL) – процедурное расширение языка SQL (диалект ) фирмы Microsoft для создания объектов базы данных, построения запросов к базе данных и обработки их результатов.

Язык SQL включает в себя несколько поименованных подмножеств операторов:

DDL (Data Definition Language – язык определения данных),

DDL – язык, предназначенный для создания, модификации и удаления объектов базы данных. Язык включает три оператора: CREATE – создать объект, ALTER – модифицировать объект, DROP – удалить объект базы данных. С помощью этих операторов создаются, изменяются и удаляются таблицы, индексы, представления, пользователи базы данных, процедуры и другие объекты базы данных.

DCL (Data Control Language – язык управления данными),

DСL – язык, предназначенный для управления доступом пользователей базы данных к ее объектам. Язык включает два оператора:

GRANT – назначить разрешение, REVOKE – отобрать разрешение на операции с объектом или группой объектов базы данных.

DML (Data Manipulation Language – язык манипулирования данными),

DML – язык, предназначенный для выполнения операций с реляционными таблицами. Язык включает четыре оператора: INSERT – добавить одну или несколько строк в таблицу, DELETE – удалить строки из таблицы, UPDATE – изменить строки таблицы, SELECT – выбрать строки таблицы

TCL (Transaction Control Language – язык управления транзакциями).

Язык включает два основных оператора: COMMIT – зафиксировать изменения в базе данных, ROLLBACK – отказаться от незафиксированных изменений в базе данных (откатить изменения).

Стандарт SQL является совместной разработкой ANSI (American National Standards Institute) и ISO (International Organization for Standardization), в 1986 году опубликовавших серию стандартов SQL/86. Первый международный стандарт языка SQL был принят в 1989 году (стандарт SQL/89) и устанавливал многие важные свойства языка как определяемые реализацией (что дало большой простор для расхождений между различными реализациями SQL, причем многие аспекты языка вообще не упоминались в SQL/89).

Каждая фирма, разработчик SQL. утверждает, что ее SQL наиболее близок к стандарту, и достаточно трудно определить, какой из диалектов SQL, например SQL-PLUS фирмы Oracle или TRANSACT-SQL [25] фирмы Microsoft более ему соответствует.

Достоинства SQL :

1. Наличие международных стандартов.

2. Независимость от конкретной СУБД. Несмотря на наличие диалектов и различий в синтаксисе, в большинстве своем тексты SQL-запросов, содержащие DDL и DML, могут быть достаточно

легко перенесены из одной СУБД в другую.

3. Поддержка архитектуры клиент-сервер.

4. Распространенность.

5. Быстрое обучение.

6. Декларативность. С помощью SQL программист описывает только то, какие данные нужно извлечь или модифицировать. Каким образом это сделать, решает СУБД непосредственно при обработке SQL-запроса. Однако программисту полезно представлять, как СУБД будет разбирать текст его запроса. Чем сложнее сконструирован запрос, тем больше он допускает вариантов написания, различных по скорости выполнения, но одинаковых по итоговому набору данных.

Недостатки SQL:

1. Неполное соответствие реляционной модели данных (наличие дубликатов, необязательность первичного ключа, возможность упорядочения результатов).

2. Недостаточно продуманный механизм неопределенных значений.

3. Сложность формулировок и громоздкость.

Транзакции и блокировки

Понятие транзакций и блокировок

Транзакция представляет собой последовательный набор команд Transact-SQL (одна или несколько команд), образующих логически завершенный блок, который выполняется как единое целое. В транзакцию может быть включено как несколько команд (или даже одна команда), так и несколько тысяч команд. Независимо от количества команд в транзакции либо все они будут выполнены, либо ни одна из них не выполнится. Если хотя бы одна из команд транзакции не выполнена, происходит откат транзакции. При откате транзакции система восстанавливается в состоянии, в котором она была до начала транзакции. Информация о первоначальном состоянии системы хранится в журнале тран­закций.

Большинство действий, выполняемых пользователями в SQL Server , про­изводятся в теле транзакций. По умолчанию каждая команда выполняется как самостоятельная транзакция. При необходимости пользователь может явно ука­зать начало и конец транзакции, чтобы иметь возможность включить в нее не­сколько команд.

Программист должен позаботиться о соблюдении логической целостности данных и бизнес-правил. На него возлагается разработка эффективных и логиче­ски верных алгоритмов обработки данных. Программист должен решить, какие команды должны выполняться как одна транзакция, а какие могут быть разбиты на несколько последовательно выполняемых транзакций. В первом случае (при выполнении множества команд в одной транзакции) на данные накладывается блокировка. Некоторые режимы блокирования запрещают обращение и измене­ние данных до тех пор, пока вся транзакция не будет завершена. Таким образом, другие пользователи не смогут обратиться к данным, с которыми работает тран­закция, до тех пор, пока эта транзакция не будет завершена. Если к базе данных одновременно обращаются множество пользователей, использующих некоррект­но написанную хранимую процедуру, возможны длительные простои в ожида­нии выполнения начатых транзакций. В этом случае необходимо написать хранимую процедуру таким образом, чтобы она по возможности выполняла изменение данных не как единую большую транзакцию, а как несколько, более мелких, быстрее выполняемых и блокирующих меньше данных. В этом случае множество пользователей одновременно смогут успешно работать с одной и той же хранимой процедурой.

Блокировкой называется временно накладываемое ограничение на выполне­ние некоторых операций обработки данных. В SQL Server имеется множест­во видов блокировок, как говорится, «на все случаи жизни». Блокировка может быть наложена как на отдельную строку таблицы, так и на всю базу данных. Управлением блокировками занимается менеджер блокировок (Lock Manager), контролирующий их наложение и разрешение конфликтов. Транзакции и блоки­ровки тесно связаны друг с другом. Транзакции накладывают блокировки на данные, чтобы обеспечить изолированность изменений. Без использования бло­кировок несколько транзакций могли бы изменять одни и те же данные. SQL Server автоматически блокирует необходимые данные и при необходи­мости может расширить зону охвата для повышения производительности. Хотя механизмы управления блокировками в SQL Server были переработаны и весьма эффективны, все же для получения требуемой функциональности опыт­ные программисты могут реализовать собственные алгоритмы наложения бло­кировок.

Требования к набору команд, включенных в транзакцию

Набор команд, включенных в транзакцию, должен удовлетворять четырем требованиям, известным как требования ACID, гарантирующие правильность и надежность работы системы. ACID – это аббревиатура от Atomicity, Consistency. Isolation и Durability.

Atomicity – атомарность. Блок команд, включенных в транзакцию, выпол­няется или нe выполняется только целиком. Не может быть такого, что из тысячи строк данных окажутся изменены только три сотни, а остальные останутся в прежнем состоянии.

Consistency – согласованность (или постоянство). Все данные после вы­полнения транзакции должны находиться в согласованном состоянии, то есть все правила и ограничения целостности должны быть соблюдены. Все внешние структуры данных (например, индексы) после окончания тран­закции также должны находиться в корректном состоянии.

Isolation – изолированность. Изменения данных, выполняемые одной транзакцией, не должны зависеть от изменений, выполняемых другой транзакцией, то есть изменения данных различными транзакциями долж­ны быть изолированными. В противном случае возможны «мертвые» бло­кировки, в результате чего работа обеих транзакций будет блокирована. Транзакция видит данные либо в состоянии, которое было до начала рабо­ты другой транзакции, либо в состоянии после того, как работа второй транзакции была завершена. Одна транзакция не может просмотреть про­межуточное состояние данных, изменяемых другой транзакцией. Если транзакция читает несколько раз одни и те же данные, то она должна ви­деть их каждый раз в том состоянии, в котором они были при первом обра­щении. Например, если первая транзакция выбирает строки данных, соот­ветствующие определенному логическому условию, то другая транзакция не должна вставлять строки, соответствующие этому логическому усло­вию. Такое поведение известно как «упорядочиваемость» или «сериализуемость» (Serializability).

Durability – устойчивость (или долговечность). После того как транзак­ция завершена, она сохраняется в системе и ничто не может вернуть сис­тему в состояние, в котором она была до начала транзакции. Это утверж­дение верно и в случае неожиданного останова или краха системы.

Исполнение требований ACID берет на себя SQL Server, обеспечивая выпол­нение всех команд транзакции как единого целого. Кроме того, поддерживаются два различных режима проверки согласованности. Можно проверять целост­ность данных на каждом этапе выполнения команд транзакции или дождаться завершения транзакции и уже после этого проверить, удовлетворяют ли сделан­ные изменения данных наложенным ограничениям целостности. Начиная вы­полнение изменения данных, SQL Server накладывает на данные в таблице бло­кировку таким образом, что ни одна другая транзакция не сможет их прочитать или изменить. Только после того как транзакция будет завершена или отменена, другая транзакция сможет получить доступ к данным. После завершения тран­закции SQL Server гарантирует, что изменения данных будут зафиксированы, даже если сразу же после завершения транзакции произойдет сбой компьютера, операционной системы или самого SQL Server. При следующем старте сервер выполнит восстановление изменений данных, используя журнал транзакций.

Проблемы одновременного доступа нескольких пользователей к данным

Если в системе управления базами данных не реализованы механизмы блоки­рования, то при одновременном чтении и изменений одних и тех же данных не­сколькими пользователями возникает четыре проблемы одновременного дос­тупа:

Проблема последнего изменения (The Lost Update Problem). Когда не­сколько пользователей изменяют одну и ту же строку, основываясь на ее начальном значении, то часть данных будет потеряна, так как каждая последующая транзакция перезапишет изменения, сделанные предыдущей транзакцией. Для примера можно привести работу двух редакторов над одним документом. Первоначально каждый из редакторов копирует себе с сервера копию документа и вносит в нее какие-то изменения. После того как редактирование завершено, каждый из них сохраняет свою копию об­ратно на сервер. Естественно, изменения, внесенные редактором, который сохранит файл первым, будут потеряны, так как другой редактор переза­пишет файл, не подозревая, что кроме него кто-то еще работал с этим же документом. Выходом из этой ситуации будет последовательное внесение изменений. Это означает, что второй редактор не должен копировать файл и начинать вносить изменения, пока первый не закончит свою работу и не сохранит файл на сервер.

Проблема «грязного» чтения (The uncommitted dependency problem).

Предположим, что пользователь выполняет сложные операции обработки данных, требующие множественного изменения данных перед тем, как данные будут находиться в логически верном состоянии. Если во время изменения данных другой пользователь будет считывать данные, то может оказаться, что он получит логически неверную информацию. Для избежа­ния подобных проблем лучшим выходом было бы дождаться окончания изменения всех данных, а уже потом считывать их.

Проблема неповторяемого чтения (The inconsiste analysis problem).

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

Проблема фантомов (The phaom read problem). Эта проблема возника­ет, когда транзакция выбирает данные из таблицы, а другая транзакция вставляет новые строки до завершения первой транзакции. Если первая транзакция выполняет сложные многошаговые изменения данных, это мо­жет вызвать серьезные проблемы. Полученные на первом этапе значения будут некорректными, и их дальнейшее использование может привести к непредсказуемым результатам.

Уровни блокирования данных

Для решения перечисленных выше проблем институтом стандартизации был разработан специальный стандарт ANSI, определяющий четыре уровня блокиро­вания. Каждый последующий уровень поддерживает требования предыдущего уровня и налагает дополнительные ограничения:

Уровень 0 – запрещение «загрязнения» данных (No trashing of data). Этот уровень требует, чтобы изменять данные могла только одна транзакция. Если другая транзакция пытается изменить эти же данные, то она должна ожидать завершения первой транзакции.

Уровень 1 – запрещение «грязного" чтения (No dirty reads). Если тран­закция начала изменение данных, то никакая другая транзакция не сможет прочитать эти данные до тех пор, пока первая транзакция не завершится.

Уровень 2 – запрещение неповторяемого чтения (No nonrepeatable reads). Если транзакция считывает данные, то никакая другая транзакция не смо­жет их изменить. Таким образом, при повторном чтении данных они будут находиться в первоначальном состоянии.

Уровень 3 – запрещение фантомов (No phaom). Если транзакция обра­щается к данным, то никакая другая транзакция не сможет добавить новые или удалить имеющиеся строки, которые могут быть считаны при выпол­нении транзакции.

ПРИМЕЧАНИЕ

Microsoft SQL Server поддерживает все четыре уровня блокирования.

Явные транзакции

Явные транзакции (Explicit Transaction) требуют, чтобы пользователь явно ука­зал начало и конец транзакции, используя команды Transact-SQL. Для управле­ния явными транзакциями используются следующие команды:

BEGIN TRANSACTION. Эта команда определяет начало транзакции. В журнале транзакции фиксируются первоначальные значения изменяемых данных и указывается, что транзакция начата. Синтаксис этой команды следующий:

BEGIN TRANSACTION] [transaction_name | @tran_name_variable]

Аргумент transaction_name используется для указания имени транзакции. Имя транзакции обычно используется только для вложенных транзакций последнего уровня. Оно должно удовлетворять стандартным правилам именования объек­тов, но его длина не должна превышать 32 символов.

Аргумент @tran_name_variablе задает имя переменной типа char, varchar, nchar или nvarchar, в которой хранится имя транзакции. Использование переменной для указания имени транзакции позволяет нескольким пользователям создавать множество транзакций, используя один и тот же код (например, хранимую про­цедуру).

COMMIT TRANSACTION или COMMIT WORK. Эта команда определяет конец транзак­ции. Если в теле транзакции не было ошибок, то эта команда предписыва­ет серверу зафиксировать все изменения, сделанные в транзакции, после чего в журнале транзакции помечается, что изменения зафиксированы и транзакция завершена. Синтаксис этих команд следующий:

COMMIT [WORK]

COMMIT [TRAN[SACTION] [transaction_name | @tran_name_variable] ]

Назначение аргументов команды COMMIT TRANSACTION соответствует назначению аналогичных аргументов команды BEGIN TRANSACTION.

ROLLBACK TRANSACTION или ROLLBACK WORK. Эта команда используется, если пользователю необходимо прервать транзакцию, например, при выполне­нии определенного логического условия. Когда сервер встречает эту команду, происходит откат транзакции, восстанавливается первоначаль­ное состояние системы и в журнале транзакции отмечается, что транзак­ция была отменена. Синтаксис этих команд следующий:

ROLLBACK [WORK]

ROLLBACK [TRAN[SACTION] [transaction_name |

@tran_name_variable | savepoint_name | @savepoint_variable] ]

Назначение первых двух аргументов команды ROLLBACK TRANSACTION соответст­вует назначению аналогичных аргументов команды BEGIN TRANSACTION.

Аргумент savepoint_name указывает имя контрольной точки транзакции, соз­данной командой SAVE TRANSACTION, Откат транзакции до контрольной точки поз­воляет отменить только часть транзакции (изменения, сделанные после созда­ния контрольной точки).

Аргумент @savepoint_variable используется для указания имени контрольной точки транзакции через переменную. Эта переменная должна иметь тип char. varchar, nchar или nvarchar и содержать имя контрольной точки.

При использовании для доступа к данным механизмов OLE DB и ADO мож­но использовать явное определение транзакций, используя соответствующие ме­тоды. Если для доступа к данным используется технология ODBC, то явное определение транзакции невозможно, так как ODBC поддерживает только авто­матическое и неявное определения транзакций.

При использовании для доступа к данным различных механизмов следует ис­пользовать однотипные методы для управления транзакциями. В противном случае поведение системы может быть непредсказуемым. Например, если тран­закция была начата драйвером ODBC, а завершена с помощью команды ROLLBACK TRANSACTION, то драйвер ODBC не получит информации о завершении транзакции и произойдет ошибка, когда он попытается ее закрыть.

Неявные и автоматические транзакции

По умолчанию SQL Server работает в режиме автоматического начала тран­закций (Autocommit Transaction). В этом режиме каждая команда рассматрива­ется как отдельная транзакция. Пользователю не нужно явно указывать начало и конец транзакции, так как это за него делает SQL Server. Если команда выполне­на успешно, то сделанные ею изменения фиксируются. Если же при выполнении команды произошла ошибка, то сделанные изменения отменяются и система возвращается в первоначальное состояние.

Если пользователю необходимо создать транзакцию, включающую несколько команд, то он должен явно указать транзакцию. При этом используются те же команды, что были описаны в предыдущем разделе.

Вообще сервер работает только в одном из двух режимов определения тран­закции: автоматическом или подразумевающемся. Сервер не может находиться в режиме исключительно явного определения транзакций. Этот режим работает поверх двух других.

Для установки режима автоматического определения транзакций использует­ся следующая команда Transact-SQL:

SET IMPLICIT_TRANSACTION OFF

Неявные транзакции

При работе в режиме неявного (или подразумевающегося) начала транзакции (Implicit Transaction) SQL Server автоматически начинает новую транзакцию, как только завершена предыдущая. Пользователь ничего не должен делать, что­бы указать начало транзакции. Транзакция продолжается до тех пор, пока поль­зователь явно не укажет команду отката (ROLLBACK TRANSACTION) или конца (COMMIT TRANSACTION) транзакции, после чего сервер автоматически начинает новую тран­закцию. В итоге генерируется непрерывная цепь транзакций.

После того как для соединения установлен режим подразумевающегося нача­ла транзакции, сервер автоматически заканчивает текущую транзакцию и начи­нает новую, если встречается одна из следующих команд:

ALTER TABLE – изменение структуры таблицы; CREATE – создание объекта базы данных; DELETE – удаление строк данных из таблицы; DROP - удаление объектов базы данных;

FETCH – извлечение указанной колонки из курсора;

GRANT – разрешение доступа к объектам базы данных;

INSERT – добавление строк в таблицу;

OPEN – открытие курсора;

REVOKE – неявное отклонение доступа к объектам базы данных;

SELECT – выборка данных из таблиц;

TRUNCATE TABLE - усечение таблицы;

UPDATE – изменение данных в таблице.

Для установки режима автоматического определения транзакций использует­ся следующая команда Transact-SQL:

SET IMPLIСIT _TRANSACTION ON

Компоненты системы безопасности

Фундаментом системы безопасности SQL Server являются учетные записи (login), пользователи (user), роли (role) и группы (group). Пользователь, подклю­чающийся к SQL Server, должен идентифицировать себя, используя учетную за­пись. Учетная запись отображается в пользователя базы данных, которые могут объединяться в группы и роли для упрощения управления системой безопас­ности.

Компоненты системы безопасности SQL Server. Учетные записи и группы

SQL Server управляет работой пользователей с использованием идентификатора учетной записи – login. Для получения доступа к серверу необходимо зарегистрироваться на SQL Server либо в домене Windows в зависимости от выбранного режима аутентификации. С каждым пользователем, установившим соединение с сервером, ассоциируется идентификатор учетной записи – login ID.

При использовании режима аутентификации SQL Server член стандартной роли сервера sysadmin должен создать для каждого пользователя его login, введя пользовательское имя и пароль для доступа к серверу. Создаваемая учетная запись не имеет отношения к учетным записям Windows . Вы можете создать учетную запись пользователя Windows с любым именем, включая его имя в домене. При подключении к серверу необходимо указать как имя, так и пароль пользователя. Данные об учетных записях пользователей хранятся в таблице syslogins системной базы данных Master.

Мы ограничимся рассмотрением средств Transact-SQL, которые можно использовать для управления системой безопасности. К таковым относится хранимая процедура sp_addlogin, с помощью которой можно создать новую учетную запись SQL Server:

sp_addlogin [@loginname =] ‘login'

[. [@passwd =] 'password'

[. [@defdb =] 'database'

[. [@deflanguAgent =] 'languAgent’

[. [@sid =] ‘SID’

[. [@encryptopt =] 'encryption_option’

Параметры хранимой процедуры имеют следующий смысл:

- login – в качестве данного параметра вы указываете имя учетной записи (login) SQL Server, которое будет использоваться для аутентификации пользователя;

- password – пароль, который ассоциируется с данной учетной записью. При создании учетной записи можно указать любой пароль, даже пустой. Единственное, что необходимо сделать, – это проинструктировать пользователя, чтобы он поменял пароль при первом входе в систему. Это можно сделать при помощи хранимой процедуры sp_ password;

- database – база данных по умолчанию. Именно к этой базе данных пользователь получает доступ после подключения к SQL Server. Например, работники отдела кадров после подключения к серверу могут быть сразу соединены с базой данных о сотрудниках компании;

- languAgent – этот параметр определяет язык, который будет выбран для данного пользователя при соединении с SQL Server;

- SID – этот параметр позволяет создавать учетные записи на различных серверах с одинаковым Security ID;

- encryption_option – определяет, будет ли шифроваться пароль данной учетной записи.

СОВЕТ. Хотя параметр database можно опустить, старайтесь всегда ассоциировать учетную запись пользователя с определенной базой данных. В противном случае пользователь подключен к системной базе данных Master, что может привести к нежелательным последствиям.

При использовании режима аутентификации Windows член стандартной роли сервера sysadmin должен указать SQL Server, какие группы и пользователи Windows имеют доступ к серверу. При использовании аутентификации Windows вам не нужно указывать имя и пароль пользователя. Проверка правильности имени пользователя и пароля возлагается на контроллер домена Windows . При подключении клиента к серверу SQL Server использует доверительное соединение, которое устанавливается операционной системой только при успешной регистрации пользователя в домене. Поскольку невозможно установись доверительное соединение без предварительной регистрации в домене, SQL Server не выполняет никаких действий по аутентификации пользователей, а просто считывает информацию о пользователе из атрибутов доверительного соединения. Сервер ищет соответствие имени пользователя, пытающегося установить соединение, с именами в системной таблице syslogins, для которых разрешен доступ. Если соответствие найдено, то доступ предоставляется, в противном же случае поиск соответствия продолжается для групп, к которым этот пользователь принадлежит. Если соответствие все равно не найдено, доступ к SQL Server не разрешается и пользователь получает сообщение об ошибке.

Хранимая процедура sp_GRANTlogin позволяет разрешить доступ к SQL Server для пользователя или группы Windows :

sp_GRANTlogin [@loginname =] 'login'

В параметре login указывается полное имя пользователя или группы Windows , которым необходимо предоставить доступ к SQL Server. Имя должно содержать указание на домен, в котором оно определено. Так, например, чтобы предоставить пользователю Lex домена MIS право подключиться к SQL Server, необходимо выполнить следующую команду:

EXEC sp_GRANTlogin ‘MIS\Lex’

СОВЕТ. Помните, что учетная запись Windows имеет приоритет перед учетными записями SQL Server. Если пользователь, зарегистрированный в домене Windows , попытается установить соединение с сервером, использующим смешанный режим аутентификации, используя имя и пароль учетной записи SQL Server, для предоставления доступа все же будет использоваться учетная запись Windows .

В большинстве случаев учетная запись в SQL Server создастся с целью предоставления доступа. Но бывают ситуации, когда необходимо запретить доступ пользователю или группе. Например, при наличии сложной системы безопасности Windows доступ обычно предоставляется группе пользователей. Однако если в группе имеется человек, которому нельзя разрешать доступ к SQL Server, его необходимо было бы убрать из этой группы. Но такой подход неудовлетворителен, если группа предназначена не только для объединения пользователей, имеющих доступ к SQL Server, но имеет какие-то дополнительные функции. SQL Server разрешает создать учетную запись с целью запрещения доступа. Это гарантирует, что пользователь никаким образом не сможет установить соединение с сервером. Создав грушу Windows и запретив ей доступ к SQL Server, вы можете включать в нее пользователей, которым необходимо отказать в доступе. Такой подход позволяет решить проблемы отслеживания членства пользователей в группах, имеющих доступ к SQL Server.

После установки SQL Server создаются две стандартные учетные записи: BUILTIN\Administrators и sa.

BUILTIN\Adminstrators – это учетная запись, обеспечивающая автоматический доступ всем членам группы Administrators доступ к SQL Server. Учетная запись BUILTIN\Administrators по умолчанию является членом встроенной роли сервера sysadmin. Таким образом, системные администраторы получают полный доступ ко всем базам данных. В ситуации, когда функции системного администратора и администратора баз данных выполняют разные люди, скорее всего, следует исключить эту учетную запись из роли sysadmin.

sa - это специальная учетная запись администратора. По умолчанию она присвоена встроенной системной роли сервера sysadmin и не может быть изменена. Эта учетная запись сохранена к версии для сохранения совместимости с приложениями, написанными для предыдущих версий SQL Server. Хотя sa и имеет административные права, ее не рекомендуется использовать в SQ.L Server . Вместо этого следует создать новых пользователей и включить их в административную группу sysadmin. Использование sa оставьте на крайний случай, когда системные администраторы окажется недоступными либо вы забудете пароль.

СОВЕТ. По умолчанию sa имеет пустой пароль. Поэтому сразу же после установки SQL Server измените пароль для этой учетной записи.

Компоненты системы безопасности SQL Server. Пользователи

После того, как пользователь прошел аутентификацию и получил идентификатор учетной записи (login ID), он считается зарегистрированным и ему предоставляется доступ к серверу. Для каждой базы данных, к объектам которой пользователю необходимо получить доступ, необходимо ассоциировать учетную запись (login) с пользователем (user) конкретной базы данных. Пользователи выступают в качестве специальных объектов SQL Server, при помощи которых определяются все разрешения доступа и владения объектами в базе данных. Имя пользователя может использоваться для предоставления доступа как конкретному человеку, так и целой группе людей (в зависимости от типа учетной записи). При создании базы данных определяются два стандартных пользователя: dbo и guest.

Если login не связывается явно с user, пользователю предоставляется неявный доступ с использованием гостевого имени guest. Если вы удалите имя guest, то пользователи, не имеющие явного отображения login в имя пользователя, не смогут получить доступа к базе данных. Тем не менее, guest не имеет автоматического доступа к объектам. Владелец объекта должен сам решать, разрешать пользователю guest доступ или нет. Обычно пользователю guest предоставляется минимальный доступ в режиме «только чтение».

СОВЕТ. Для обеспечения максимальной безопасности можно удалить пользователя guest из любой базы данных, кроме системных баз данных master и Tempdb, в которых guest используется для выполнения системных хранимых процедур обычными пользователями.

Владелец базы данных (DataBase Owner – DBO) – специальный пользователь, обладающий максимальными правами в базе данных. Любой член роли sysadmin отображается в пользователя dbo. Если пользователь, являющийся членом роли sysadmin, создает какой-нибудь объект, то владельцем этого объекта назначается не данный пользователь, a dbo. Например, если Liliya, член административной группы, создает таблицу TableА, то полное имя таблицы будет не Liliуа.TableА, a dbo.TableА. В то же время, если Liliya, не будучи участником роли сервера sysadmin, состоит в роли базы данных db_owner, то имя таблицы будет Liliya.TableА. Пользователя dbo нельзя удалить.

Для связывания учетной записи login с определенным именем пользователя (user) можно воспользоваться следующей хранимой процедурой:

sp_adduser [@loginame =] 'login’

[. [@name_in_db =] 'user']

[. [@grpname=] role']

Ниже приводим пояснения используемых параметров:

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

user – имя пользователя базы данных, с которым ассоциируется данная учетная запись,

role – этот параметр определяет роль, в которую данный пользователь будет включен.

Хранимая процедура sp_GRANTdbaccess позволяет отобразить учетную запись Windows в имя пользователя;

sp_GRANTdbaccess [@loginame =] 'login'

[. [@name_in_db =] 'user']

Параметры означают следующее;

login – имя учетной записи пользователя или группы пользователей Windows , которым необходимо предоставлять доступ к базе данных. Имя должно снабжаться ссылкой на домен, в котором учетная запись определена.

user – имя пользователя базы данных, с которым ассоциируется учетная запись.

Пользователь, который создает объект в базе данных, например таблицу, хранимую процедуру или представление, становится владельцем объекта. Владелец объекта (database object owner) имеет все права доступа к созданному им объекту. Чтобы пользователь мог создать объект, владелец базы данных (dbo) должен предоставить пользователю соответствующие права. Полное имя создаваемого объекта включает в себя имя создавшего его пользователя. Если пользователь хочет обратиться к таблице, используя только ее имя и не указывая владельца, SQL Server применяет следующий алгоритм поиска:

- ищется таблица, созданная пользователем, выполняющим запрос;

- ищется таблица, созданная, владельцем базы данных (dbo).

Допустим, пользователь пытается вызвать таблицу Liliya.TableA, просто используя ими TableA. Поскольку таблица, созданная Liliya, не соответствует ни первому, ни второму критерию поиска, то таблица TableA не будет найдена и пользователь получит сообщение об ошибке. Для получения доступа к таблице необходимо ввести имя, включающее владельца объекта, то есть Liliya.TableA.

Владелец объекта не имеет никакого специального пароля или особых прав доступа. Он неявно имеет полный доступ, но должен явно предоставить доступ другим пользователям. SQL Server позволяет передавать права владения от одного пользователя другому. Чтобы удалить владельца объекта из базы данных, сначала необходимо удалить все объекты, которые он создал, или передать права на их владение другому пользователю. Для этого можно использовать хранимую процедуру sp_changeobjectowner.

Компоненты системы безопасности SQL Server. Роли сервера и базы данных

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

Существует 8 постоянных ролей сервера, которые предоставляют административные привилегии на уровне сервера, вне зависимости от базы данных:

· sysadmin – может выполнять любые действия на MS SQL Server. По умолчанию сюда входит учетная запись sa и все члены группы администраторов Windows;

· setupadmin – управляет связанными серверами (linked servers) и процедурами, которые выполняются вместе с запуском сервера;

· securityadmin – может создавать и управлять логинами, читать журнал ошибок и создавать БД;

· processadmin – обладает правами управления процессами внутри MS SQL Server, например, член этой роли может завершать задачи, которые выполняются слишком долго;

· dbcreator – разрешено создание и изменение баз данных;

· diskadmin – управляет файлами баз данных: назначает файлы в группы, присоединяет/отсоединяет базы данных и т.д.;

· bulkadmin – позволяет выполнять команду BULK INSERT для вставки сразу большого количества записей в таблицу;

Для просмотра информации о встроенных ролях используются хранимые процедуры:

· sp_helpsrvrole – возвращает список ролей сервера и описание каждой роли;

· sp_helpsrvrolemember [‘имя роли’] – возвращает список ролей и учетных записей, которым присвоены эти роли;

· sp_srvrolepermission [‘имя роли’] – возвращает список разрешений, присвоенных этим ролям.

ЗАМЕЧАНИЕ. Если указан необязательный параметр [‘имя роли’], то выводится информация, относящаяся только к указанной роли.

Роли баз данных предоставляют наборы административных привилегий на уровне базы данных. При использовании ролей базы данных каждая учетная запись сервера будет иметь разные полномочия в зависимости от того, с какой базой данных осуществляется работа. Существует 10 встроенных ролей базы данных:

· db_owner – включает в себя права все других ролей базы данных. Пользователь получает права владельца базы.

· db_accessadmin – похожа на серверную роль securityadmin, за исключением того, что ограничена одной базой данных. Она не позволяет создавать новые логины MS SQL Server, но разрешает добавлять новых пользователей в базу данных.

· db_datareader – разрешает выполнение оператора SELECT для всех таблиц базы данных.

· db_datawriter – разрешает выполнять INSERT, UPDATE и DELETE для всех таблиц базы данных.

· db_ddladmin – позволяет добавлять, удалять и изменять объекты в базе данных.

· db_securityadmin – еще одна роль похожая на серверную роль securityadmin. В отличие от db_accessadmin, она не разрешает создавать новых пользователей в базе, но позволяет управлять ролями и членством в ролях, а также правами на доступ к объектам базы данных.

· db_backupoperator – позволяет создавать резервные копии базы данных.

· db_denydatareader – запрещает выполнение SELECT для всех таблиц базы данных.

· db_denydatawriter – запрещает выполнение INSERT, UPDATE и DELETE для всех таблиц базы данных.

Просмотр информации о ролях баз данных (как встроенных, так и определенных пользователем) осуществляется с помощью процедуры sp_helprole, просмотр членов ролей баз данных – sp_helprolemember.

ЗАМЕЧАНИЕ. В каждой базе данных есть специальная роль Public. Она не может быть удалена. Каждый пользователь базы данных обязательно член этой роли. Обычно эту роль используют для предоставления некоторых разрешений всем пользователям данного сервера.

Роли приложения

Система безопасности SQL Server реализована на самом низком уровне – уровне базы данных. Это наилучший, наиболее действенный метод деятельности пользователей независимо от приложений, используемых ими для подключения к SQL Server. Тем не менее, встречаются ситуации, когда необходимо использовать постоянный набор прав для доступа к базе данных из приложения. Особенно это касается работы с большими базами данных, имеющих множество сложных взаимосвязанных таблиц с тысячами или миллионами записей. Чаще всего для работы с такими базами данных создают специальные приложения.

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

SQL Server решает перечисленные проблемы путем использования роли приложения, создаваемой на уровне базы данных. Отличия между стандартными ролями и ролью приложения фундаментальны. Роль приложения не имеет членов. Пользователи SQL Server или Windows не могут быть добавлены в эту роль. Роль активизируется, когда приложение устанавливает соединение. Пользователь, работающий в это время с приложением, не является членом роли. Он лишь использует установленное приложением соединение.

Перед использованием роли приложения необходимо сначала создать ее. При создании роли укажите ее имя и пароль для доступа. Приложение может быть спроектировано так, чтобы пользователь, работающий с ним, должен был вводить пароль для активизации роли приложения. Однако чаще всего пароль вводится самим приложением незаметно для пользователя. Дополнительно, для обеспечения максимальной безопасности, пароль может быть зашифрован перед своей отправкой в SQL Server по сети.

В процессе подключения приложение должно активизировать роль, передав пароль, ассоциированный с данной ролью. Для этого приложение использует следующую процедуру:

sp_setapprole [@rolename =] ‘role’.

[@password =] {Encrypt N ‘passsword’} | ‘password’

[. [@encrypt =] ‘encrypt_style’]

Рассмотрим параметры подробнее:

- rale – имя роли приложения, которое определено в базе данных;

- password – пароль, который приложение должно передать серверу для активизации роли приложения;

- encrypt_style – применяемая схема шифрования паролей. Данный параметр может иметь два значения: none (шифрование не применяется) и odbc (шифрование с применением функции ODBC Encrypt).

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

Поскольку роль приложения имеет права только в той базе данных, в которой она создана, а все разрешения для учетных записей, групп и ролей, к которым принадлежит пользователь, теряются, то доступ к другим базам данных возможен только под гостевым именем guest. Следовательно, если имя guest в базе данных не существует, то соединение не сможет получить доступ к данным. Если же имя guest не удалено из базы данных, соединение сможет получить доступ к объектам базы только в том случае, если разрешения явно выданы для пользователя guest.

Перед установлением соединения с использованием роли приложения пользователю сначала нужно получить доступ к SQL Server. Для этого можно использовать оба режима аутентификации пользователей.

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

Управление правами доступа к объектам базы данных

Разрешения для объектов и разрешения для команд T-SQL

Разрешения для объектов.

Работа с данными и выполнение хранимых процедур требуют наличия класса доступа, называемого разрешениями для объектов. Разрешения для объектов контролируют возможность выполнения команд SELECT, INSERT, UPDATE и DELETE для таблиц и представлений. Действия с хранимыми процедурами контролируется разрешением либо запрещением их выполнения, предоставлением или запрещением права EXECUTE. Например, если пользователю необходимо добавить новые данные в таблицу, ему следует предоставить право INSERT (вставка записей в таблицу).

Для различных объектов применяются разные наборы разрешений:

- SELECT, INSERT, UPDATE и DELETE – эти разрешения могут быть применены для таблицы или представления;

- SELECT и UPDATE – эти разрешения могут быть применены к конкретной колонке таблицы или представления;

- INSERT и DELETE – эти разрешения применяются для таблицы или представления;

- EXECUTE – это разрешение применяется только к хранимым процедурам.

Разрешения для команд T - SQL .

Этот класс разрешений контролирует возможность создания объектов в базе данных, создания самой базы данных и выполнения процедуры резервного копирования. Например, если пользователю необходимо создать представление, администратор базы данных должен предоставить пользователю право на выполнение команды CREATE VIEW.

Разрешения для команд приведены в таблице 1.

Таблица 1. Разрешения для команд Transact-SQL

Предоставление доступа. Команда GRANT

Перед тем как пользователь сможет выполнять любые действия, администратор должен предоставить ему соответствующие права (allowing access). После создания пользователь не имеет никаких прав доступа кроме тех, которые разрешены для специальной роли базы данных public. Права, предоставленные этой роли, доступны для всех пользователей в базе данных.

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

Используйте всe возможности SQL Server, контролируя права доступа не только на уровне таблицы, но и на уровне колонки. Указывая права доступа к конкретной колонке, вы можете более гибко управлять системой безопасности.

Аналогичные рекомендации касаются разрешений на выполнение команд Transact-SQL. Можно спроектировать базу данных таким образом, что определенные пользователи будут выполнять конкретные действия: создание таблиц, представлений, правил, резервных копий и т. д.

Используйте команду GRANT для управления разрешениями пользователя на доступ к объектам базы данных:

GRANT

{ALL [PRIVILEGES] | permission[….n]}

{

[(co1umn[...n])] ON {table | view}

| ON {table | view} [(column [...n])]

| ON {stored_procedure | extended_procedure}

}

TO security_account[….n]

[WITH GRANT OPTION]

[AS {group | role}]

и для разрешения выполнения команд Transact-SQL:

GRANT {ALL | statement[...n]}

TO security_account[….n]

Назначение параметров команды GRANT следующее:

ALL – использование данного параметра означает, что пользователю будут предоставлены все доступные разрешения. Этот параметр могут использовать только участники роли sysadmin;

permission – список доступных операций, которые предоставляются пользователю (SELECT, IMSERT, UPDATE, DELETE, EXECUTE). Вы можете одновременно предоставлять несколько разрешений, в этом случае их нужно разделять запятыми;

statement – при помощи этого параметра можно предоставлять разрешения на выполнение команд Transact-SQL, которые перечислены в таблице 1.

security_account – здесь указывается имя того объекта системы безопасности, который необходимо включить в роль. В качестве таких объектов могут выступать как учетные записи SQL Server, так и пользователи и группы пользователей Windows , которым предоставлен доступ к серверу баз данных;

table, view, column, stored_procedure, extended_procedure – в качестве данных параметров выступают имена объектов в текущей базе данных, для которых необходимо предоставить доступ;

WITH GRANT OPTION – использование данною параметра позволяет пользователю, которому вы предоставляете права, назначить права на доступ к объекту для других пользователей;

AS {group | role} – этот необязательный параметр позволяет указать участие пользователя в роли, которой предоставлена возможность предоставлять права другим пользователям.

В качестве примера использования команды рассмотрим следующую ситуацию. Вам необходимо предоставить права на использование команд INSERT и SELECT группе Engineer и таблица Materials. При этом нужно, чтобы в дальнейшем пользователи этой группы могли сами предоставлять аналогичные права. Для этого следует выполнить следующую команду:

GRANT SELECT. INSERT

ON Materials

ТО Engineer

WITH GRANT OPTION

Впоследствии пользователь Valeine, являющийся членим группы Engineer, может предоставить аналогичные права другому пользователю Lex:

GRANT SELECT, INSERT

ON Materials

TO Lex

AS Engineer

В данном случае необходимо подтвердить, на каком основании Valeine предоставляет права, поэтому применяется параметр AS.

СОВЕТ. Будьте осторожны при использования параметра WITH GRANT OPTION, поскольку при этом вы теряете контроль над предоставлением прав на доступ другим пользователям. Лучше всего ограничить круг людей, обладающих возможностью управлять назначением прав.

Запрещение доступа. Команда DENY

Система безопасности SQL Server имеет иерархическую структуру. Это позволяет ролям базы данных включать в себя учетные записи и группы Windows , пользователей и роли SQL Server. Пользователь же, в свою очередь, может участвовать в нескольких ролях. Следствием иерархической структуры системы безопасности является то, что пользователь может одновременно иметь разные права доступа для разных ролей. Если одна из ролей, в которых состоит пользователь, имеет разрешение на доступ к данным, то пользователь автоматически имеет аналогичные права. Тем не менее, может потребоваться запретить возможность доступа к данным. Когда вы запрещаете пользователю доступ к данным или командам Transact-SQL (deny access), тем самым аннулируются все разрешения на доступ, полученные пользователем на любом уровне иерархии. При этом гарантируется, что доступ останется запрещенным независимо от разрешений, предоставленных на более высоком уровне.

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

Используйте команду DENY дли запрещения пользователю доступа к объектам базы данных:

DENY

{ALL [PRIVILEGES] | permission[....n]}

{

[(column[....n])] ON {table | view}

| ON {table | view}[(column[....n])]

| ON {stored_procedure | extended_procedure}

}

TO security_account[….n]

Для запрещения выполнения команд Transact-SQL применяется другая команда:

DENV {ALL | statement[….n]}

TO security_account[….n]

Параметры данной команды аналогичны параметрам команды GRANT. Использование параметра CASCADE позволяет отзывать права не только у данного пользователя, но также и у всех тек пользователей, кому он предоставил данные права. Поясним смысл сказанного на примере. Пусть вы предоставили пользователю Sheridan определенные права:

GRANT CREATE TABLE

ТО Sheridan

WITH GRANT OPTION

Допустим, Sheridan предоставляет аналогичные права некоторым пользователям. Если впоследствии вам потребуется отозвать разрешения у Sheridan, вы выполните команду

DENY CREATE TABLE

ТО Sheridan

CASCADE

При этом будут отозваны и все разрешения, которые Sheridan предоставил другим пользователям.

Неявное отклонение доступа. Команда REVOKE

Помимо уже рассмотренных операций предоставления и запрещения доступа, в системе безопасности SQL Server используется неявное отклонение доступа (revoke access). Неявное отклонение подобно запрещению доступа с тем отличием, что оно действует только на том уровне, на котором определено. Если пользователю на определенном уровне неявно отклонен доступ, он все же может получить его на другом уровне иерархии через членство в роли, имеющей право просмотра. По умолчанию доступ пользователя к данным неявно отклонен. Это можно рассматривать как «среднее состояние» между предоставлением и запрещением доступа или как отсутствие каким-либо разрешений.

Используйте команду REVOKE для неявного отклонения доступа к объектам балы данных:

REVOKE [GRANT OPTION FOR]

{ALL [PRIVILEGES] | permission[….n]}

{

[(column[....n])] ON {table | view}

| ON {table | view}[(column[....n])]

| {stored_procedure | extended_procedure}

}

{TO | FROM} security_account[....n]

[CASCADE]

[AS {group | role}]

Для неявного отклонения разрешения на выполнение команд Transact-SQL используется следующая команда:

REVOKE: {ALL | statement[....n]}

FROM security_account[….n]

Параметры имеют смысл, аналогичный параметрам команд GRANT и DENY. Опция GRANT OPTION FOR используется, когда необходимо отозвать право, предоставляемое параметром WITH GRANT OPTION команды GRANT. Пользователь при этом сохраняет разрешение на доступ к объекту, но теряет возможность предоставлять это разрешение другим пользователям.

В качестве примера рассмотрим такую ситуацию. Пользователю Malari, который является участником роли Ceaurs, предоставлены права на доступ к таблице Materials. Если при помощи команды REVOKE мы отклоняем доступ к таблице для роли Ceaurs, пользователь Malari все равно сможет обращаться к данной таблице, поскольку права для него определены явно. И только когда мы применим команду REVOKE персонально для него, он потеряет право доступа к таблице.

Неявное отклонение доступа позволяет более гибко конфигурировать систему безопасности.

Защита данных

Шифрование данных в SQL Server

Шифрование – это метод, используемый SQL Server для изменения данных до нечитаемой формы. Использование шифрования гарантирует, что ценная конфиденциальная информация не будет просмотрена кем бы то ни было. Вы можете скопировать данные, но ничего не сможете с ними сделать. Для просмотра данных авторизированными пользователями используется дешифрование.

SQL Server позволяет шифровать следующие данные:

- любые данные, передаваемые между сервером и клиентом по сети;

- пароли учетных записей SQL Server или ролей приложения;

- код хранимых процедур;

- код представлений;

- код триггеров.

Пароли учетных записей и ролей приложения всегда сохраняются в системных таблицах SQL Server в зашифрованной форме. Это предохраняет их от просмотра любым пользователем, включая администратора. Кроме того, пароль роли приложения может быть зашифрован перед отправкой его на сервер.

Если код триггера, представления или хранимой процедуры содержит данные или алгоритм, которые необходимо сохранить в тайне, используйте шифрование этих объектов.

Использование шифрования данных при передаче их по сети гарантирует, что никакое приложение или устройство не сможет их прочитать, даже если и перехватит. Использование шифрованного соединения позволяет также предотвратить перехват паролей пользователей.

Если вы хотите использовать шифрование данных при передаче их по сети, необходимо использовать сетевую библиотеку Multiprotocol Net-Library.

Репликация данных

Понятие репликации данных

Репликация – это копирование данных, расположенных на одном сервере, на один или несколько других серверов. SQL Server поддерживает репликацию данных не только на другие SQL Server , но и на серверы предыдущих версий, а также на СУБД сторонних производителей. Однако все имеющиеся возможности могут быть реализованы только в том случае, если все участники репликации являются серверами SQL Server .

Модель репликации SQL Server базируется на метафоре «опубликуйте и подпишитесь» (publish and subscribe), введенной в SQL Server 6.0.

В терминологии репликации для серверов – участников процесса тиражирования данных используются термины «издатель» (Publisher), «дистрибьютор» (Distributor) и «подписчик» (Subscriber).

ПРИМЕЧАНИЕ. Предполагается, что издатель, подписчик и дистрибьютор запускаются на разных серверах SQL Server. В принципе, можно сконфигурировать репликацию таким образом, чтобы данные копировались между базами данных одного сервера. В этом случае издатель, подписчик и дистрибьютор будут запускаться на одном компьютере.

Помимо перечисленных терминов для обозначения участников процесса тиражирования используются термины «публикация» (Publication) и «статья» (Article) для обозначения копируемых данных. Статья представляет собой таблицу или ее часть, выбранную для тиражирования. Когда выбирается часть таблицы, используется либо вертикальное разделение (using a vertical filler), либо горизонтальное разделение (using a horizontal filter). Вертикальное разделение ограничивает количество колонок таблицы, включенных в репликацию. Горизонтальное разделение накладывает условие на выбираемые для тиражирования строки. Публикация представляет собой набор статей, копируемых как одно целое между сервером-издателем и сервером-подписчиком. Подписчик может подписаться только на всю публикацию. Подписка на отдельную статью в публикации не поддерживается.

ПРИМЕЧАНИЕ. В SQL Server 6.x подписчик мог подписываться на отдельную статью в публикации. Для сохранения обратной совместимости SQL Server все еще поддерживает для приложений SQL Server 6.х возможность подписки на отдельную статью. Тем не менее, пользовательский интерфейс уже не позволяет этого делать. Если необходимо скопировать одну-единственную статью, придется создать для нее отдельную публикацию. Microsoft рекомендует изменить созданные ранее приложения и как наименьший объект репликации вместо статьи использовать публикацию.

Репликация данных. Издатель

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

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

Издатель поддерживает всю информацию обо всех сконфигурированных на нем статьях и публикациях.

Репликация данных. Подписчик

Подписчиком называется сервер, копирующий предоставляемые издателем данные. В качестве подписчика может выступать не только сервер SQL Server , но и SQL Server предыдущих версий, а также Microsoft Jet 4.0 Database (Microsoft Access), ODBC data source или OLE DB data source. Подписчиком может быть сконфигурирован в качестве издателя для других подписчиков. В отличие от предыдущих версий SQL Server, разрешавших изменение данных только на издателе, механизмы репликации SQL Server разрешают, помимо издателя, вносить изменения в данные и подписчикам. Механизмы, используемые при изменении данных подписчиком, иные, чем при выполнении изменений данных издателем,

В SQL Server реализовано два различных механизма выполнения изменений данных подписчиком:

– использование репликации сведением (Merge Replication);

– использование подписчиков незамедлительного обновления (Immediate Updating Subscribers).

Существует два метода обновления подписчиков:

– Pull subscription (Репликация по запросу). Подписчик периодически подключается к дистрибьютору и требует у него все изменения, сделанные со времени последнего подключения. При наличии большого количества подписчиков использование репликации по запросу позволяет снизить на­грузку на дистрибьютора. Кроме того, для мобильных пользователей также рекомендуется использовать репликацию по запросу. В этом случае они могут начать процесс репликации немедленно после подключения к сети, а не дожидаться, пока дистрибьютор скопирует им данные. Использование репликации по запросу позволяет упростить процесс рассылки данных через глобальные сети, например через Интернет. При этом подписчик инициирует начало репликации, а наиболее удобное для него время.

– Push subscription (Принудительная репликация). Дистрибьютор сам устанавливает соединение с подписчиками и копирует им все необходимые данные. Использование этого метода репликации рекомендуется для серверов, с которыми постоянно установлено соединение. Изменения могут копироваться постоянно, немедленно после того, как они произошли, или периодически на основе установленного расписания. Администратор может централизованно управлять расписанием выполнения обновлений на всех подписчиках.

ПРИМЕЧАНИЕ. Одна и та же публикация может поддерживать как репликацию по запросу, так и принудительную репликацию.

Репликация данных. Дистрибьютор

Дистрибьютором называется сервер, поддерживающий базу данных распределения. Этот сервер исполняет роль посредника между издателем и подписчиком. Дистрибьютор копирует себе все публикации, подготовленные издателем, и тиражирует их подписчикам. Использование дистрибьютора позволяет разгрузить основной сервер, переложив заботу об отслеживании процессов репликации на сервер, специально выделенный для этих целей. Кроме того, в некоторых случаях дистрибьютор может значительно увеличить скорость выполнения операций тиражирования данных за счет снижения сетевого трафика. Представьте, например, что центральный офис компании находится в Москве, а в Новосибирске расположены три филиала. Каждый из филиалов участвует в репликации и копирует информацию из центральной базы данных. Если дистрибьютор установлен в Москве, то сервер каждого филиала должен соединяться по медленному каналу связи с дистрибьютором в Москве и выкачивать необходимые данные.

Для увеличения скорости копирования можно сконфигурировать один из серверов в Новосибирске в качестве дистрибьютора. В этом случае филиалы могут копировать данные с дистрибьютора в Новосибирске, используя высокоскоростные и недорогие местные линии связи. Такой же подход можно использовать, если рядом с Новосибирском находятся несколько других филиалов, которым быстрее и дешевле связываться с Новосибирском, а не с Москвой.

В качестве дистрибьютора можно использовать как выделенный сервер, так и сервер, сконфигурированный в качестве издателя или подписчика. Конкретные функции, выполняемые дистрибьютором, зависят от используемого метода репликации.

Механизмы репликации

Механизмы репликации в SQL Server реализованы в виде специализированных агентов, запускаемых в зависимости от выполняемых ими функций на подписчике, издателе или дистрибьюторе. Эти агенты устанавливают соединения с серверами и выполняют копирование данных и отображение произведенных операций в системных таблицах и специальных колонках пользовательских таблиц. В процессе репликации могут принимать участие два или более агентов из следующего списка:

- Snapshot Agent;

- Log Reader Agent;

- Distribution Agent;

- Merge Agent,

ВНИМАНИЕ. Программа инсталляции не инициализирует механизм репликации автоматически. Для разрешения поддержки репликации воспользуйтесь одним из мастеров, обеспечивающих работу механизмов репликации. При первом вызове мастер определит, что поддержка репликации не установлена, и выполнит ее инициализацию. После этого на сервере добавится база данных Distribution, а в консоли Enterprise Manager появится новая папка – Replication Monitor.

Агенты репликации работают как часть службы SQL Server Agent. Поэтому для успешного выполнения копирования данных следует убедиться, что служба SQL Server Agent запущена. Рекомендуется установить автоматический запуск этой службы при запуске операционной системы.

Помимо перечисленных агентов, при создании базы данных распределении создается несколько вспомогательных автоматически запускаемых задач:

- Agent checkup;

- Transaction cleanup;

- History cleanup;

- Subscription cleanup at the Publisher;

- History cleanup at the Distributor.

Эти задачи помогают репликации эффективно функционировать долгое время. Задачи очистки (cleanup task) удаляют из базы данных Distribution устаревшие строки, тем самым предохраняя ее от переполнения. Кроме того, эти задачи удаляют с диска файлы моментальных снимков (snapshot) и сценариев после того, как все подписчики получат нужные данные. Вспомогательные задачи работают в фоновом режиме и не требуют администрирования.

Модели репликации

Репликация моментальных снимков

Репликация моментальных снимков – это самый простой вид репликации. Моментальный снимок (snapshot) представляет собой полную копию данных, выбранных для репликации. При использовании репликации моментальных снимков полная копия тиражируемых данных рассылается всем подписчикам. Этот тип репликации характеризуется малой загрузкой процессора, так как нет необходимости в анализе таблиц для отбора новых или измененных данных. Недостатком является значительная загрузка сети при работе с большими базами данных. При внесении даже небольшого количества изменений будут передаваться все данные, включенные в репликацию.

Репликация моментальных снимков гарантирует согласованность данных между издателем и подписчиком. Так как этот тип репликации требует наличия мощного канала для передачи данных, Snapshot Replication часто используется для копирования редко обновляемых данных, «свежесть» которых не актуальна. Например, при использовании систем принятия решений подписчики могут использовать репликацию моментальные снимков для периодического копирования нужной информации. Данные в системе принятия решений изменяются редко или вообще не изменяются, поэтому не имеет смысла использовать репликацию транзакций или репликацию сведением. Аналогичная ситуация складывается при копировании прайс-листов из основного офиса множеством филиалов или мобильных пользователей. Если данные на издателе не обновляются, то после выполнения репликации подписчики могут полностью отсоединиться от сети и работать самостоятельно.

Рисунок – Репликация моментальных снимков

Как показано на рис., репликация моментальных снимков выполняется двумя агентами – Snapshot Agent и Distribution Agent.

Каждый раз, когда запускается Snapshot Agent, он подготавливает файл данных моментального снимка (snapshot file), который впоследствии будет копироваться подписчикам. Эти действия выполняются в несколько шагов;

1. Snapshot Agent запускается на дистрибьюторе и устанавливает соединение с издателем. Затем агент устанавливает коллективную блокировку (share-lock) на все таблицы, для которых определены статьи публикации. Блокировка гарантирует, что скопированные данные будут последовательны, то есть пользователь не сможет удалить уже скопированную агентом строку, а затем снова вставить ее в конец таблицы. Это могло бы вызнать нарушение целостности данных. В то же время пользователи не смогут вносить изменения в заблокированные таблицы. Если создание моментального снимка занимает много времени, планируйте запуск Snapshot Agent на то время, когда активность пользователей минимальна, например, ночью или в выходные.

2. Snapshot Agent подключается с издателя к дистрибьютору и записывает копию структуры (схемы) каждой статьи в файл .sch на дистрибьюторе. Файл сохраняется в подкаталоге того каталога, в котором расположена база данных Distribution. Если статья на издателе была индексирована, агент создает скрипт для создания индекса и сохраняет его в файле .idx на дистрибьюторе.

3. Агент делает копию данных из публикуемой издателем таблицы и сохраняет ее в файл на дистрибьюторе. Как и файлы схемы, файлы моментальных снимков копируются в подкаталог каталога базы данных Distribution. Для создания моментальных снимков используется технология массивного копирования с помощью утилиты bcp.exe. Если все участники репликации являются серверами SQL Server , то файлы моментальных снимков создаются в «исконном» (native) формате утилиты bcp.exe и имеют расширение .bcp. Если же один или несколько участников репликации – не SQL Server , то файлы сохраняются в текстовом формате с расширением .txt. Файлы .sch и .bcp синхронизированы для конкретной статьи, то есть количество колонок, их тип, размер и другие свойства в файле моментального снимка соответствуют данным в файле схемы.

4. Snapshot Agent добавляет строки в таблицы Msrepl_commands и Msrepl_transactions базы данных распределения Distribution. В таблице Msrepl_commands сохраняется информация о местонахождении файлов .sch и .bср, а также всех скриптов, сохраненных в фактах .idx, которые должны быть выполнены на подписчике перед началом копирования данных. В таблице Msrepl_transactions описываются задания синхронизации подписчиков.

5. Агент снимает блокировку с таблиц издателя и записывает информацию о проделанных действиях в журнал событий (log history file).

После того как агент Snapshot Agent завершит свою работу, на дистрибьюторе будет находиться вся информация, необходимая для создания на подписчиках копий статей издателя. Перед Distribution Agent стоит задача доставить подписчикам данные, подготовленные Snapshot Agent. Это выполняется в несколько шагов:

1. Distribution Agent устанавливает соединение с сервера, на котором он запущен, с дистрибьютором. Если используется репликация по запросу, то агент запускается на подписчике. Если же используется принудительная репликация, то агент запускается на дистрибьюторе.

2. Агент просматривает базу данных Distribution и анализирует содержимое таблиц Msrepl_commands и Msrepl_transactions. Если агент обнаруживает новые записи в таблицах, он считывает информацию о расположении файлов схемы, моментального снимка и скриптов.

3. Агент выполняет содержащийся в файле схемы скрипт для создания на подписчике таблицы с нужной структурой. Если подписчик – не SQL Server , то Distribution Agent предварительно выполняет необходимые преобразования кода. Затем агент начинает копирование данных из файла моментального снимка в созданные таблицы. При этом используются механизмы массированного копирования, предоставляемые утилитой bcp.exe и командой BULK INSERT,

Когда в сети имеется множество подписчиков, использование репликации по запросу может снизить нагрузку на дистрибьютора и тем самым повысить производительность. Distribution Agent в таком случае запускается на подписчике. Если для периодического выполнения репликации моментальных снимков установлено расписание, то используется дата и время репликации, заданные на подписчике. Если же используется принудительная репликация, то значения времени и даты берутся с дистрибьютора.

Когда используется репликация моментальных снимков, на дистрибьюторе периодически запускаются следующие вспомогательные задачи:

- Agent checkup;

- Transaction cleanup;

- History cleanup.

Безотлагательное обновление при репликации моментальных снимков

Самая простая схема репликации моментальных снимков имеет однонаправленное действие – от издателя к подписчику. Все изменения должны выполняться на издателе, а затем копироваться подписчикам. В SQL Server была выедена новая технология – подписчики незамедлительного обновления. Это позволило расширить возможности репликации моментальных снимков, разрешив подписчикам изменять данные. Использование подписчиков незамедлительного обновления обеспечивает скрытую согласованность транзакций, выполняемых одним из подписчиков, между всеми участниками репликации. Когда подписчик изменяет локальные данные, вносимые нм изменения немедленно отображаются на издателе и затем копируются всем остальным подписчикам. Для этого используется двухфазный протокол изменений (two-phase commit protocol – 2РС), управляемый координатором распределенных транзакций (Microsoft Distributed Transactional Coordinator – MS DTC). Когда подписчик пытается изменить данные, с помощью протокола 2РС создается распределенная транзакция, в которой данные одновременно изменяются на подписчике и издателе. Если по каким-то причинам данные на издателе не могут быть изменены, вся транзакция отменяется, и данные на подписчике остаются неизмененными. Если изменения с использованием протокола 2РС были успешно выполнены, измененные данные распространяются с помощью дистрибьютора всем остальным подписчикам по установленному расписанию или по требованию. Подписчик, который выполнял изменения, может успешно работать, не дожидаясь, пока новые данные будут ему скопированы. Целостность данных в этом случае не нару­шается.

Репликация транзакций

Репликация транзакций (Transactional Replication) может быть использована для копирования объектов двух различных типов; таблиц или хранимых процедур. Вся таблица или ее часть может быть включена в публикацию как статья. Кроме того, в эту же или другую публикацию в качестве статьи может быть также включена одна или несколько хранимых процедур.

Репликация транзакций использует журнал транзакций базы данных, из которого собирается информация о выполняемых в выбранной для публикации таблице командах UPDATE, INSERT, DELETE и других изменениях. Выбранные транзакции копируются в базу данных дистрибьютора с сохранением информации о последовательности их выполнения. Затем эти транзакции рассылаются подписчикам и применяются из них в том же порядке, в котором они происходили на издателе.

Сделанные на издателе изменения передаются подписчикам постоянно или с определенными интервалами. Обычно изменения отображаются на всех участниках репликации в реальном времени или с задержкой в несколько секунд. При выполнении репликации транзакций исключаются конфликты обновления, так как все изменения выполняются на издателе.

Репликацию транзакций рекомендуется использовать в локальных сетях. Серверы SQL Server могут постоянно поддерживать соединение друг с другом, и изменения будут отображаться на все серверы практически мгновенно. Кроме того, репликация транзакций часто используется в больших базах данных c небольшим количеством изменений. Издатель и подписчик могут располагаться на разных материках, и репликация моментальных снимков потребовала бы неоправданно больших затрат. При использовании репликации транзакций подписчик может периодически (например, два раза в сутки) связываться с дистрибьютором и забирать у него выполненные на издателе транзакции. При таком подходе резко снижается объем передаваемых данных и, как следствие, повышается производительность репликации.

При использовании репликации транзакций задействованы три агента (рис):

- Snapshot Agent;

- Log Reader Agent;

- Distribution Agent.

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

Если подписчик уже имеет таблицы с правильной структурой и данные уже были синхронизированы, при создании репликации транзакций можно указать, что выполнение первоначальной синхронизации не нужно, тогда Snapshot Agent не будет запускаться. Тем не менее, применение транзакций начнется только после того, как SQL Server убедится, что структура и содержание таблиц подписчика соответствуют структуре и содержанию таблиц издателя.

Рисунок – Репликация транзакций

Если база данных издателя слишком большая, то для копирования моментального снимка базы данных подписчику можно использовать ручную синхронизацию. Например, если размер базы данных составляет примерно 40 Гбайт, то быстрее и дешевле будет сделать архив базы данных на нескольких компакт-дисках и отправить их почтой иди с экспресс-курьером. В таком случае запуск Snapshot Agent не требуется. SQL Server начинает копирование транзакций незамедлительно, как только убедится в соответствии данных на подписчике и издателе.

СОВЕТ. Выполнение ручной синхронизации рекомендуется выполнять только опытным пользователям. Они должны гарантировать, что структура таблиц на подписчике и издателе, а также их содержание идентичны.

После того как первоначальная синхронизация выполнена, начинается копирование транзакций. Log Reader Agent запускается на дистрибьюторе и устанавливает соединение с издателем. Когда таблица участвует в репликации, все выполняемые в ней транзакции специальным образом маркируются для ускорения поиска нужных записей в журнале транзакций. Агент анализирует журнал транзакций и выбирает из него все команды INSERT, UPDATE, DELETE и другие модификации данных, которые изменяли определенные для публикации таблицы. SQL Server имеет набор системных хранимых процедур, обеспечивающих работу механизмов репликации. Log Reader Agent использует хранимую процедуру sp_replcmds для получения транзакций, помеченных для репликации. Агент считывает только завершенные транзакции и сохраняет их в таблице MSrepl_transactions базы данных распределения Distribution. В итоге получается однозначное сопоставление транзакций в издателе и в дистрибьюторе. Каждая транзакция может включать множество команд, а каждая команда – иметь до 500 символов Unicode.

Транзакция, помеченная для репликации, не удаляется из журнала транзакций до res пор, пока Log Reader Agent не скопирует ее. После этого транзакция будет удалена при следующем урезании журнала. Для разрешения удаления транзакции агент вызывает хранимую процедуру sp_repldone.

Когда одна или несколько транзакций скопированы в базу данных распределения, Distribution Agent может начинать тиражировать их подписчикам. Агент последовательно считывает транзакции из таблицы MSrepl_transactions и применяет их на подписчиках в том же порядке, в котором они происходили на издателе Teм самым гарантируется, что все подписчики будут иметь одинаковые копии данных. Действия, выполняемые Distribution Agent при репликации транзакций, практически ничем не отличаются от действий, выполняемых этим агентом при репликации моментальных снимков.

При использовании репликации транзакций автоматически запускаются несколько специальных заданий, обеспечивающих продолжительное функционирование дистрибьютора и базы данных распределения:

- Agent checkup;

- Transaction cleanup;

- History cleanup.

Безотлагательное обновление при репликации транзакций

Наиболее простая форма репликации транзакций предлагает однонаправленное выполнение изменений – изменения, сделанные на издателе, тиражируются всем подписчикам. Сами подписчики в этом случае не могут изменять данные. В репликации SQL Server реализован протокол 2РС, с помощью которого пользователи, работающие с серверами-подписчиками, могут обновлять и изменять локальные данные. Приложение, успешно работавшее с базой данных, размещенной на сервере-издателе, может в этом случае безо всяких изменений устойчиво работать с базой данных подписчика.

ВНИМАНИЕ. Поддержка подписчиков незамедлительного обновления поддерживается только для серверов SQL Server .

Если при создании публикации для нее была разрешена поддержка подписчиков незамедлительного обновления (Immediate-update Subscribers), то в таблицах на подписчике создаются специальные триггеры (рис). Кроме того, в публикуемых таблицах как издателя, так и подписчиков автоматически добавляется колонка с типом данных timestamp. Каждый раз при изменении строки в таблице значение в этой колонке изменяется. Когда пользователь пытается изменить данные на подписчике, триггеры UPDATE, DELETE и INSERT перехватывают инициируемые пользователем транзакции и передают их издателю с помощью протокола 2РС, управляемого координатором распределенных транзакций (MS DTC).

Рисунок – Подписчики незамедлительного обновления в репликации транзакций

ПРИМЕЧАНИЕ. Если пользователь в одной транзакции пытается одновременно обновить несколько строк в таблице, необходимо, чтобы соответствующие строки имели одинаковые значения на издателе и подписчике.

Триггер создает распределенную транзакцию и одновременно пытается обновить данные на издателе и подписчике. Он сравнивает значения колонок times-tamp издателя и подписчика для изменяемой строки. Если значения совпадают, значит, данные в издателе не были изменены со времени последней репликации и подписчик может их изменять. Для колонки timestamp устанавливается новое значение, так что другие подписчики незамедлительного обновления не смогут изменить данные до тех пор, пока измененные данные не будут им реплицированы.

ПРИМЕЧАНИЕ. Данные в солонке timestamp не имеют ничего общего с хронологическим временем. Знамения в этой колонке монотонно возрастают и уникальны в пределах базы данных.

Изменение как локальных данных, так и данных на издателе выполняется как одна распределенная транзакция. Поэтому если по каким-то причинам внести изменения в данные на издателе невозможно, то изменить локальные данные гоже будет невозможно. Этим гарантируется целостность данных. Если издатель недоступен, подписчик не сможет изменить локальные данные.

Если изменения на подписчике были выполнены успешно, это означает, что данные на издателе также были изменены. Log Reader Agent обнаруживает сделанные изменения и сохраняет их в базе распределения. Затем Distribution Agent тиражирует транзакции всем подписчикам. Подписчик, инициировавший изменения, уже имеет обновленные данные, поэтому считается, что данные на него уже были реплицированы и повторное копирование не выполняется.

Для избежания повторного копирования в тело транзакции вставляются специальные метки, которые информируют Distribution Agent о ненужности копирования этой транзакции конкретному подписчику. Этот подписчик может продолжать работать сразу же после того, как сделал изменения. Спустя непродолжительное время все участники репликации будут иметь одинаковые копии данных.

ВНИМАНИЕ. Если с помощью одной транзакции выполняется изменение значений в нескольких таблицах различных баз данных на подписчике незамедлительного обновления, то информация о выполненных распределенных транзакциях может быть потеряна. Репликация транзакций в таком случае может потерпеть неудачу.