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

Содержание

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

Вопросы:

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

Виды ключей.

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

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

 

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

 

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

 

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

 

Транзакция представляет собой последовательный набор команд 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 не могут быть добавлены в эту роль. Роль активизируется, когда приложение ус

Поделиться:

Воспользуйтесь поиском по сайту: