Что такое код fbsql_username

Содержание

Получение имя пользователя

Подскажите, как получить имя пользователя, под которым был произведен вход на сервер SQL? То есть если я зашла с именем «mariya\sqlexpress», то мне нужно получить «mariya». Этот пользователь должен выполнить скрипт, в котором в том числе его имя заносится в одну из таблиц созданной с помощью этого скрипта базы данных.

Заранее благодарю за ответы!

06.03.2013, 12:54

Имя пользователя в MDB — файле
Как узнать имя пользователя с правами администратора в MDB- файле

Вставка ID пользователя, вводя его имя
Добрый день. Есть 2 таблицы: Студенты и Аккаунты_Студентов. Таблицы эти, конечно же, связаны по ID.

Имя пользователя принадлежит недоверенному домену Windows
Здравствуйте. Устанавливаю удаленное соединения с SQL сервером. SqlConnectionString выглядит так.

Получение имени пользователя, вводящего данные в базу
Здравствуйте! Есть люди знающие которые могут ответить на следующий вопрос: Базу заполнять будут.

Функция, которая возвращает имя пользователя, который вызвал UPDATE/DELETE/INSERT
Не могу нагуглить. Нужна функция которая возвращает имя пользователя который вызвал.

Внедрение SQL-кода

Внедрение SQL-кода — (англ. SQL injection) — один из распространённых способов взлома сайтов и программ, работающих с базами данных, основанный на внедрении в запрос произвольного SQL-кода.

Внедрение SQL, в зависимости от типа используемой СУБД и условий внедрения, может дать возможность атакующему выполнить произвольный запрос к базе данных (например, прочитать содержимое любых таблиц, удалить, изменить или добавить данные), получить возможность чтения и/или записи локальных файлов и выполнения произвольных команд на атакуемом сервере.

Атака типа внедрения SQL может быть возможна из-за некорректной обработки входящих данных, используемых в SQL-запросах.

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

Принцип атаки внедрения SQL

Если на сервер передан параметр >

SELECT * FROM news WHERE >

Но если злоумышленник передаст в качестве параметра >

SELECT * FROM news WHERE >

Таким образом, изменение входных параметров путём добавления в них конструкций языка SQL вызывает изменение в логике выполнения SQL-запроса (в данном примере вместо новости с заданным идентификатором будут выбраны все имеющиеся в базе новости, поскольку выражение 1=1 всегда истинно).

Внедрение в строковые параметры

Сделав запрос вида http://example.org/script.php?search_text=Test мы получим выполнение следующего SQL-запроса:

Но, внедрив в параметр search_text символ кавычки (который используется в запросе), мы можем кардинально изменить поведение SQL-запроса. Например, передав в качестве параметра search_text значение ‘)+and+(news_ >

Язык SQL позволяет объединять результаты нескольких запросов при помощи оператора UNION. Это предоставляет злоумышленнику возможность получить несанкционированный доступ к данным.

Рассмотрим скрипт отображения новости (идентификатор новости, которую необходимо отобразить, передается в параметре id):

Если злоумышленник передаст в качестве параметра id конструкцию —1 UNION SELECT 1,username, password,1 FROM admin, это вызовет выполнение SQL-запроса:

Так как новости с идентификатором −1 заведомо не существует, из таблицы news не будет выбрано ни одной записи, однако в результат попадут записи, несанкционированно отобранные из таблицы admin в результате инъекции SQL.

Использование UNION + group_concat()

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

которая объединяет несколько колонок в одну. Например, для примера данного выше вызов функции будет таким:

Экранирование хвоста запроса

Зачастую, SQL-запрос, подверженный данной уязвимости, имеет структуру, усложняющую или препятствующую использованию union. Например скрипт:

отображает имя автора новости по передаваемому идентификатору id только при условии, что имя начинается с буквы а, и внедрение кода с использованием оператора UNION затруднительно.

В таких случаях, злоумышленниками используется метод экранирования части запроса при помощи символов комментария(/* или — в зависимости от типа СУБД).

В данном примере, злоумышленник может передать в скрипт параметр id со значением —1 UNION SELECT password FROM admin/*, выполнив таким образом запрос:

в котором часть запроса ( AND author LIKE (‘a%’)) помечена как комментарий и не влияет на выполнение.

Для разделения команд в языке SQL используется символ ; (точка с запятой), внедряя этот символ в запрос, злоумышленник получает возможность выполнить несколько команд в одном запросе, однако не все диалекты SQL поддерживают такую возможность.

Например, если в параметры скрипта:

$ ];
$res = mysql_query(«SELECT * FROM news WHERE >
злоумышленником передается конструкция, содержащая точку с запятой, например 12;INSERT INTO admin (username, password) VALUES (‘HaCkEr’, ‘foo’); то в одном запросе будут выполнены 2 команды:

и в таблицу admin будет несанкционированно добавлена запись HaCkEr.

Методика атак типа внедрение SQL-кода

Поиск скриптов, уязвимых для атаки

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

# Данными передаваемыми через методы POST и GET
# Значениями [HTTP-Cookie]
# HTTP_REFERER (для скриптов)
# AUTH_USER и AUTH_PASSWORD (при использовании аутентификации)

Как правило, манипуляция сводится к подстановке в параметры символа одинарной (реже двойной или обратной) кавычки.

Аномальным поведением считается любое поведение, при котором страницы, получаемые до и после подстановки кавычек, различаются (и при этом не выведена страница о неверном формате параметров).

Наиболее частые примеры аномального поведения:

# выводится сообщение о различных ошибках;
# * при запросе данных (например, новости или списка продукции) запрашиваемые данные не выводятся вообще, хотя страница отображается
и т. д. Следует учитывать, что известны случаи, когда сообщения об ошибках, в силу специфики разметки страницы, не отображаются в браузере, хотя и присутствуют в её HTML-коде.

Комментирование остатка строки:
MySQL /* .
MS SQL — .
Oracle — . или /* .
MS Access Внедрение в запрос NULL‑байта: %00.
PostgreSQL — .
Sybase — .
IBM DB2 — .
Ingres — .

Получение версии:
MySQL version()
MS SQL @@version
Oracle select banner
from v$version
PostgreSQL version()
Sybase @@version
IBM DB2 select versionnumber from sysibm.sysversions
Ingres dbmsinfo(‘_version’)

Конкатенация строк:
MySQL concat (string1, string2)
MS SQL string1 + string2
Oracle string1 || string2
или concat (string1, string2)
PostgreSQL string1 || string2
Sybase string1 + string2
IBM DB2 string1 || string2 или string1 concat string2
Ingres string1 || string2

Защита от атак типа внедрение SQL-кода

Для защиты от данного типа атак необходимо тщательно фильтровать входные параметры, значения которых будут использованы для построения SQL-запроса.

Фильтрация строковых параметров:

Предположим, что код, генерирующий запрос (на языке программирования Паскаль), выглядит так:

Чтобы внедрение кода было невозможно, для некоторых СУБД, в том числе, для MySQL, требуется брать в кавычки все строковые параметры. В само́м параметре заменяют кавычки на \», апостроф на \’, обратную косую черту на \\ (это называется «экранировать спецсимволы»).

Это можно делать таким кодом:

Для PHP фильтрация может быть такой:

Фильтрация целочисленных параметров

Возьмём другой запрос:

В данном случае поле id имеет числовой тип, и его нельзя брать в кавычки. Поэтому «закавычивание» и замена спецсимволов на escape-последовательности не проходит. В таком случае помогает проверка типа; если переменная id не является числом, запрос вообще не должен выполняться.

Например, на Delphi для противодействия таким инъекциям помогает код:

В случае ошибки функция StrToInt вызовет исключение EConvertError, и в его обработчике можно будет вывести сообщение об ошибке. Двойное преобразование обеспечивает корректную реакцию на числа в формате $132AB (шестнадцатеричная система счисления). На стандартном Паскале, не умеющем обрабатывать исключения, код несколько сложнее.

Для PHP этот метод будет выглядеть так:

$query = ‘SELECT * FROM users WHERE >

Усечение входных параметров

Для внесения изменений в логику выполнения SQL-запроса требуется внедрение достаточно длинных строк. Так, минимальная длина внедряемой строки в вышеприведённых примерах составляет 8 символов («1 OR 1=1»). Если максимальная длина корректного значения параметра невелика, то одним из методов защиты может быть максимальное усечение значений входных параметров.

Например, если известно, что поле id в вышеприведённых примерах может принимать значения не более 9999, можно «отрезать лишние» символы, оставив не более четырёх:

Использование параметризованных запросов

Connecting to SQL Server with a username and password

I am writing a C# application to be deployed on another machine. The deploy machine is a clean Windows 7 install.

On my development machine (A Windows 7 32 bit Virtual Machine) I have installed SQL Server 2008 R2 and Visual Studio Express 2010. I would like to create a new database and have it require authentication from an application to connect. All the tutorials that I find mention breeze over the connection string part, I cannot find this explained anywhere. In fact, I cannot even figure out how to add a new user and password to SQL Server in SQL Server Management Studio.

How do I add a new user and password to SQL Management Studio 2008 R2 such that I can easily install another SQL Server instance on another machine and move the database to it?

Что такое код fbsql_username

Допустим, серверное ПО, получив входной параметр id, использует его для создания SQL-запроса. Рассмотрим следующий PHP-скрипт:

Если на сервер передан параметр >

Но если злоумышленник передаст в качестве параметра >

Таким образом, изменение входных параметров путём добавления в них конструкций языка SQL вызывает изменение в логике выполнения SQL-запроса (в данном примере вместо новости с заданным идентификатором будут выбраны все имеющиеся в базе новости, поскольку выражение 1=1 всегда истинно).

Внедрение в строковые параметры

Предположим, серверное ПО, получив запрос на поиск данных в новостях параметром search_text, использует его в следующем SQL-запросе (здесь параметры экранируются кавычками):

Сделав запрос вида http://example.org/script.php?search_text=Test мы получим выполнение следующего SQL-запроса:

Но, внедрив в параметр search_text символ кавычки (который используется в запросе), мы можем кардинально изменить поведение SQL-запроса. Например, передав в качестве параметра search_text значение ‘)+and+(news_ >

Использование UNION

Язык SQL позволяет объединять результаты нескольких запросов при помощи оператора UNION. Это предоставляет злоумышленнику возможность получить несанкционированный доступ к данным.

Рассмотрим скрипт отображения новости (идентификатор новости, которую необходимо отобразить, передается в параметре id):

Если злоумышленник передаст в качестве параметра id конструкцию -1 UNION SELECT 1,username, password,1 FROM admin, это вызовет выполнение SQL-запроса

Так как новости с идентификатором −1 заведомо не существует, из таблицы news не будет выбрано ни одной записи, однако в результат попадут записи, несанкционированно отобранные из таблицы admin в результате инъекции SQL.

Использование UNION + group_concat()

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

которая объединяет несколько колонок в одну. Например, для примера данного выше вызов функции будет таким:

Экранирование хвоста запроса

Зачастую, SQL-запрос, подверженный данной уязвимости, имеет структуру, усложняющую или препятствующую использованию union. Например скрипт

отображает имя автора новости по передаваемому идентификатору id только при условии, что имя начинается с буквы а, и внедрение кода с использованием оператора UNION затруднительно.

В таких случаях, злоумышленниками используется метод экранирования части запроса при помощи символов комментария(/* или в зависимости от типа СУБД).

В данном примере, злоумышленник может передать в скрипт параметр id со значением -1 UNION SELECT password FROM admin/*, выполнив таким образом запрос

в котором часть запроса ( AND author LIKE (‘a%’)) помечена как комментарий и не влияет на выполнение.

Расщепление SQL-запроса

Для разделения команд в языке SQL используется символ ; (точка с запятой), внедряя этот символ в запрос, злоумышленник получает возможность выполнить несколько команд в одном запросе, однако не все диалекты SQL поддерживают такую возможность. MySQL, например, не поддерживает.

Например, если в параметры скрипта

злоумышленником передается конструкция, содержащая точку с запятой, например 12;INSERT INTO admin (username, password) VALUES (‘HaCkEr’, ‘foo’); то в одном запросе будут выполнены 2 команды

и в таблицу admin будет несанкционированно добавлена запись HaCkEr.

Методика атак типа внедрение SQL-кода

Поиск скриптов, уязвимых для атаки

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

  • Данными, передаваемыми через методы POST и GET
  • Значениями [HTTP-Cookie]
  • HTTP_REFERER (для скриптов)
  • AUTH_USER и AUTH_PASSWORD (при использовании аутентификации)

Как правило, манипуляция сводится к подстановке в параметры символа одинарной (реже двойной или обратной) кавычки.

Аномальным поведением считается любое поведение, при котором страницы, получаемые до и после подстановки кавычек, различаются (и при этом не выведена страница о неверном формате параметров).

Наиболее частые примеры аномального поведения:

  • выводится сообщение о различных ошибках;
  • при запросе данных (например, новости или списка продукции) запрашиваемые данные не выводятся вообще, хотя страница отображается

и т. д. Следует учитывать, что известны случаи, когда сообщения об ошибках, в силу специфики разметки страницы, не видны в браузере, хотя и присутствуют в её HTML-коде.

Конструкция Комментирование остатка строки Получение версии Конкатенация строк
MySQL . или /* . version() concat (string1, string2)
MS SQL . @@version string1 + string2
Oracle . или /* . select banner
from v$version
string1 || string2
или concat (string1, string2)
MS Access Внедрение в запрос NULL‑байта: %00.
PostgreSQL . version() string1 || string2
Sybase . @@version string1 + string2
IBM DB2 . select versionnumber from sysibm.sysversions string1 || string2 или string1 concat string2
Ingres . dbmsinfo(‘_version’) string1 || string2

Защита от атак типа внедрение SQL-кода

Для защиты от данного типа атак необходимо тщательно фильтровать входные параметры, значения которых будут использованы для построения SQL-запроса.

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

Предположим, что код, генерирующий запрос (на языке программирования Паскаль), выглядит так:

Чтобы внедрение кода было невозможно, для некоторых СУБД, в том числе, для MySQL, требуется брать в кавычки все строковые параметры. В само́м параметре заменяют кавычки на \», апостроф на \’, обратную косую черту на \\ (это называется «экранировать спецсимволы»). Это можно делать таким кодом:

Роли SQL: пользователи и безопасность в InterBase

Brett Bandy, Markus Kemper, BorCon, 1998.

Что такое пользователь InterBase?

Безопасность InterBase основана на концепции «пользователя» (user). Безопасность всей базы данных, в сущности, зависит от проверки подлинности идентификатора пользователя. Информация о пользователях , зарегистрированных для конкретного сервера InterBase, хранится в особой базе данных безопасности (security database) – ISC4.GDB (из-за проблем с резервированием файлов с расширением gdb в Windows XP в IB7 этот файл переименован в ADMIN.IB, в FB1.5 – в SECURITY.FDB. В обоих случаях для сервера можно указать иное имя). Для каждого сервера InterBase существует своя собственная база данных безопасности, таким образом, пользователь «привязан» к конкретному серверу. Пользователь с таким же именем может существовать на нескольких серверах, но для этого информация о нем должна быть заведена на каждом из серверов. В базе данных безопасности также для каждого пользователя хранится зашифрованное значение пароля. Пользователь, авторизованный на сервере, имеет доступ ко всем базам данных этого сервера.

Имя пользователя может состоять максимум из 31 символа, при этом их регистр не учитывается. Пароль может состоять максимум из 8 символов (если ввести больше, лишние символы игнорируются), регистр – учитывается.

KDV: механизм логина на сервере следующий – клиентская часть, принимая пароль пользователя, шифрует его алгоритмом DES с потерей данных (см. раздел www.ibase.ru/d_security.htmLINK), после чего передает зашифрованный пароль на сервер. Сервер, приняв пароль, шифрует его еще раз тем же алгоритмом, и сравнивает со строкой, хранящейся в таблице users базы isc4.gdb. Т. е. пароль никогда не дешифруется в исходный вид, и это сделать невозможно. Даже перехватив «полузашифрованный» пароль на этапе его пересылки с клиента на сервер единственным способом «взлома» остается только прямой подбор пароля.

Распространенная ошибка: Большинство новых пользователей InterBase определяют пользователя и пароль, но когда указывают их при подключении, получают сообщение об ошибке (неверно введенное имя пользователя или пароль). Будьте, уверены, что вы ввели пароль в точности таким же, как и при создании данного пользователя.

Поле Обязательно? Тип Описание
User Name Yes String Имя пользователя, указываемое при подключении
Password Yes String Пароль пользователя
UID No Integer Необязательный UserID. В данный момент поле не используется
GID No Integer Необязательный GroupID. В данный момент поле не используется
Full Name No String Полное имя пользователя

Расширение списка пользователей InterBase пользователями ОС

Все версии InterBase используют базу данных безопасности для аутентификации пользователей, при этом некоторые версии позволяют организовать разграничение доступа на основе системы разграничения доступа операционной системы. Версии InterBase, работающие под управлением Unix, позволяют при подключении к серверу InterBase или его базам использовать либо учетные записи операционной системы, либо учетные записи InterBase. Версии, работающие под Win95 или NT, не позволяют использовать систему разграничения доступа этих операционных систем, соответственно, пользователи Win95 или NT не являются легальными пользователями InterBase. В данном случае,для аутентификации пользователей, InterBase полностью полагается на свою базу данных безопасности.

В случае с Unix, InterBase будет трактовать пользователя Unix точно так же как и пользователя, хранящегося в собственной базе данных InterBase, до тех пор, пока сервер видит клиента в качестве доверенного хоста. Учетные записи пользователя должны существовать как на клиенте, так и на сервере. Для того чтобы установить доверительные отношения с хостом клиента, необходимо на сервере занести соответствующую запись в файл /etc/hosts.equiv или /etc/gds_hosts.equiv. В файле hosts.equiv прописываются доверительные отношения на уровне операционных систем, которые, соответственно, распространяются на все сервисы (например, rlogin, rsh, rcp). В файле gds_hosts.equiv устанавливаются доверительные отношения между хостами, только для InterBase. Формат записи идентичен для обоих файлов, и выглядит следующим образом:

Нельзя устанавливать доверительные отношения между хостами под управлением Unix и Windows. Для того чтобы использовать учетные записи Unix, необходимо, чтобы и клиент и сервер функционировали под управлением Unix.

Системные учетные записи Unix используются если при установлении соединения с сервером InterBase не указывается имя пользователя.

Пользователь SYSDBA

KDV: 10 января 2001 в исходных текстах IB 6.0 была обнаружена дыра в безопасности – вкомпилированный в код сервера username/password (politically/correct). Однако, пользователь зайдя под таким account ничего не может делать (только просматривать сист. таблицы). Кроме того, username должен быть введен в нижнем регистре, а подавляющее количество инструментария (99%) автоматически при вводе конвертируют username в верхний регистр. Для 5.6, 6.0, и FB 0.9.3 был выпущен патч (IBPhoenix, Embarcadero). В версии 6.0.1 и выше эта дыра закрыта.

Управление пользователями

KDV: сейчас ServerManager уже нет, и можно пользоваться IBConsole, IBExpert, GrantManager и т. п. – www.ibase.ru/d_security.htmLINK). Кроме этого добавился еще один способ – через Services API (который пока не полностью работает в версиях > Команда Описание di[splay] Показывает информацию обо всех пользователях из базы ISC4.GDB di[splay] name Показывает информацию о пользователе name a[dd] name -pw passwd [option argument option argument . ] Добавляет пользователя с именем name, паролем passwd и дополнительной информацией mo[dify] name [options] Изменяет атрибуты пользователя de[lete] name Удаляет информацию о пользователе с именем name из ISC4.GDB h[elp] Показывает синтаксис команд GSEC q[uit] Завершает сеанс

Просмотр информации о пользователях

Добавление пользователя

Чтобы добавить нового пользователя в базу данных безопасности необходимо использовать команду ADD.

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

Опция Описание
-pw Пароль пользователя
-u[id] ID пользователя
-g[id] ID группы
-f[name] Имя пользователя (first name)
-mn[ame] Отчество пользователя (middle name)
-l[name] Фамилия пользователя (last name)

В следующем примере заводится пользователь BJONES с паролем «blah» и указываются его имя и фамилия:

Изменение информации о пользователе

Удаление пользователя

Использование InterBase API для управления пользователями

KDV: далее описание функций isc_add_user, isc_modify_user, isc_delete_user и примеры удалены для сокращения объема статьи. Примеры на C см. в API Guide, примеры на Delphi – ibusers.zip. Пример использования всех InterBase Admin (services api) компонент – AdminDemo.
KDV: при использовании функций user api есть одна проблема – вы должны передавать в вызове username/password SYSDBA. Для приложений, которые дают возможность пользователям самим модифицировать свой пароль, это неприемлемо. Проблема решается только через модификацию ISC4.GDB: дается GRANT UPDATE ON USERS TO PUBLIC после чего на таблице users создается exception и триггер before update: if USER <> ‘new.USER’ then exception NOTALLOWED; Таким образом пользователю разрешается менять только свой, а не чужой пароль. Подробнее этот способ и другие модификации isc4.gdb описан в документе.

Логин по умолчанию

Существует возможность определить username/password, который будет использоваться клиентской частью (gds32.dll) по умолчанию. Это делается при помощи переменных среды ISC_USER/ISC_PASSWORD. Если вы укажете в этих переменных имя пользователя и пароль, то при «пустой» информации при логине к серверу будут использоваться именно эти имя и пароль.
Однако, не все инструменты или программы позволяют ввести пустой username.

Данными переменными среды удобно пользоваться для административных целей, например, при ремонте БД или других операциях, однако нужно учитывать интересную особенность – если эти переменные среды определены на серверной стороне, то клиент также может осуществить «логин по умолчанию» не указывая username/password! Поэтому, если вы и используете данную возможность, то определяйте переменные среды только для контекста пользователя, но не для всей системы (см. закладки User и System в MyComputer/Properties/Advanced/Environment variables).

Привилегии SQL: Второй уровень безопасности

Как уже отмечалось, в InterBase реализована двухуровневая модель безопасности. На первом уровне осуществляется аутентификация пользователя в момент подключения к базе данных, при этом используется база данных безопасности (более подробно см. предыдущий раздел). Второй уровень реализуется уже на уровне самой базы данных. Все привилегии по доступу к объектам базы данных хранятся в самой базе. Авторизованный пользователь не имеет никаких привилегий по доступу к данным, хранящимся в базе, пока какие-либо права не будут ему предоставлены явным образом. Контроль привилегий осуществляется на уровне таблиц. Каждому пользователю сопоставлен список операций, которые допускается произвести над данной таблицей или представлением. Этот список и составляет привилегии пользователя. Возможность использования хранимых процедур в InterBase регулируется отдельной привилегией. Право на доступ к любому объекту базы данных после его создания имеют только SYSDBA и владелец этого объекта. Владельцем объекта является пользователь создавший этот объект. SYSDBA или владелец объекта могут выдавать привилегии другим пользователям, в том числе и привилегии на право выдачи привилегий другим пользователям. Собственно сам процесс раздачи привилегий на уровне SQL реализуется двумя операторами: GRANT и REVOKE. Оператор GRANT выдает привилегии авторизованным пользователям на доступ к таблицам или представлениям, а оператор REVOKE, соответственно, изымает ранее выданные привилегии.

Распространенная ошибка: Очень часто разработчики создают объекты в базе данных и забывают назначить привилегии каким-либо пользователям. Учитывая, что сам разработчик является владельцем этих объектов, у него не возникает каких-либо проблем с привилегиями на доступ к ним. Когда же приходит время внедрять приложение, пользователи получают отказ в доступе к объектам, в связи с отсутствием назначенных привилегий.

Для всех операций, связанных с управлением безопасностью, SYSDBA всегда имеет права назначать и отменять привилегии пользователям. SYSDBA – это суперпользователь, которому невозможно отменить доступ к какому-либо объекту базы данных. Соответственно, операторы GRANT и REVOKE не имеют смысла в отношении пользователя SYSDBA.

Оператор GRANT

Оператор GRANT предоставляет привилегии на объект или привилегии указанному пользователю. В таблице представлен список привилегий, которые могут быть выданы пользователю.

Привилегия Пользователь получает возможность.
Insert Добавлять новые строки в таблицу
Update Изменять данные в таблице
Delete Удалять строки из таблицы
Select Извлекать содержимое таблицы
Execute Выполнить хранимую процедуру ( в том числе и как select)
References Сервер может проверять внешние ссылки на другие таблицы
All Делать все выше обозначенные, за исключением Execute
KDV: существование пользователя, которому выдаются права, не проверяются при выполнении оператора grant. Помните, что пользователи существуют в isc4.gdb. Права же в базе данных могут быть выданы каким угодно, в том числе несуществующим (пока или по ошибке) пользователям.

Назначение привилегий пользователю PUBLIC

Назначение привилегий на выполнение хранимых процедур

KDV: начинающие пользователи часто опускают слово procedure думая что to name самостоятельно определит, что name – это имя процедуры. Это не так. При выдаче прав to name означает выдачу права именно пользователю, а не объекту вообще. Кроме того, существование таких пользователей не проверяется (они находятся в isc4.gdb).

KDV: при модификации процедур (alter) права, выданные им, сбрасываются.

KDV: права, выданные процедуре, проверяются сразу при ее загрузке в память для выполнения, а не по ходу выполнения процедуры. Это значит, что если в коде процедуры (или триггера) есть обращение к объекту при каком то условии (if . then), то права на этот объект должны быть даны процедуре обязательно. В противном случае при выполнении процедуры сразу будет выдана ошибка о нехватке прав, даже если по всем условиям данная ветка if не выполняется.

Назначение привилегий для представлений

В основном, процесс назначения привилегий для представлений соответствует аналогичному процессу для таблиц. Но, имеются некоторые особенности, которые хотелось бы отметить. Необходимо помнить, что представление это своего рода «виртуальная» таблица, представляющая собой запрос в базовую таблицу. Данные, извлекаемые по этому запросу, не хранятся в представлении. В самом представлении храниться только описание запроса, соответственно, любой запрос типа INSERT, UPDATE или DELETE предполагает модификацию базовой таблицы. Поэтому, привилегии типа INSERT, UPDATE или DELETE имеет смысл выдавать только для обновляемых представлений. В общем случае, обновляемое представление – это представление, в запросе которого не используются агрегатные функции и нет соединений (более подробно, например, см. [2]). Хотя и разрешается выдавать привилегии INSERT, UPDATE, или DELETE на представления только для чтения без каких-либо сообщений об ошибке, любая попытка изменения данных через такое представление успеха не достигнет. Привилегию SELECT для доступных только на чтение представлений можно использовать, например, с целью контроля доступа за тем, какие конкретно пользователи могут извлекать из него данные.

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

Например, представим себе таблицу TEST_SCORES, содержащую поля LASTNAME, FIRSTNAME, TESTNAME, SCORE. Столбцы LASTNAME, FIRSTNAME и TESTNAME таблицы содержат информацию, доступную для всех пользователей – в них хранится информация о том, кто проходил какие тесты. Но есть также информация, которую не следовало бы делать доступной для всех категорий пользователей – поле SCORE (результаты). Соответственно, необходимо обеспечить всем пользователям доступ к определенному набору полей таблицы TEST_SCORES, и только некоторым избранным ко всем полям, включая и поле SCORE. Это можно реализовать, создав представление, на основе запроса, выбирающего необходимый набор полей, доступный для каждого пользователя, и выдав привилегии всем пользователям на доступ только к этому представлению, а не к таблице:

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

Примеры использования оператора GRANT

Предоставление прав с GRANT OPTION

Оператор REVOKE

Только тот пользователь, который выдавал ранее привилегию, может удалить её. Например, если пользователь А предоставил какую-либо привилегию пользователю Б, то пользователь В не сможет отнять её у пользователя Б. Только у пользователя А имеется такое право. Пользователь SYSDBA всегда может предоставить и/или отменить любую привилегию любому пользователю.

Привилегия ALL объединяет привилегии SELECT, INSERT, UPDATE, DELETE и может использоваться для упрощения отмены множества привилегий пользователя. Для того, чтобы использовать аргумент ALL в выражении отмены прав, необязательно, чтобы пользователь обладал всеми привилегиями, включаемыми в ALL. При отмене привилегии ALL, он потеряет подмножество привилегий ALL. Например, предположим, что пользователь имеет привилегии INSERT и SELECT, и если SYSDBA выполнит команду REVOKE ALL для данной таблицы, то пользователь больше не будет иметь какие-либо права для этой таблицы.

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

  1. Предоставление привилегий пользователю USERA с GRANT OPTION
  2. Пользователь USERA передает права пользователю USERB
  3. Отмена привилегий пользователя USERA
  4. Ни пользователь USERA, ни USERB не имеют привилегий

Последовательность команд GRANT и REVOKE демонстрирующих рассмотренный сценарий:

Отмена привилегий пользователя PUBLIC

Примеры использования оператора Revoke

Отмена привилегии, назначенной с GRANT OPTION

Низкая эффективность средств безопасности SQL

Стандартные средства разграничения доступа SQL (в данном документе под стандартом понимается стандарт SQL-92) позволяют ограничить доступ к данным, однако они недостаточно эффективны с точки зрения управления доступом к данным в целом. SQL ориентирован на работу с индивидуальным пользователем. За исключением пользователя PUBLIC, не имеется возможностей по управлению пользователями на групповом уровне. Более того, механизм привилегий требует, чтобы права назначались непосредственно для каждой таблицы базы данных.

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

Трудности управления пользователями средствами безопасности SQL

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

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

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

Слабая гибкость средств безопасности SQL

Что такое роли (SQL Roles)?

В InterBase 5.0 было введено расширение стандартных средств SQL – роли, реализующее концепцию управления безопасностью на групповом уровне. Роли служат своего рода шаблонами для предустанавливаемых наборов привилегий. К обычному механизму привилегий SQL привилегии добавляют преимущество группового управления безопасностью, позволяя определять набор привилегий для нескольких таблиц в базе данных.

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

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

Идентификация ролей

Аддитивность ролей

Какие экземпляры InterBase поддерживают роли?

Роли доступны в InterBase 5.0 и выше. При переходе с предыдущих версий (4.x) недостаточно просто перенести базу данных на новый сервер, необходимо пройти процедуру backup/restore под новым сервером.

Далее перечислены основные требования и условия применения ролей:

  • Роль связана с базой данных, в которой она создается.
  • Имя роли должно быть уникальным с учетом имен других ролей и имен пользователей.
  • Роли должны указываться при подключении. Пользователь не может сменить роль, без переподключения к базе данных.
  • Для того, чтобы использовать роли, база данных от IB 4.x должна быть восстановлена из резервной копии (backup) на сервере InterBase версии 5.0 или выше. Возможно использование баз данных, созданных сервером версии 4.0, на сервере версии 5.0, при этом роли не будут доступны.

Преимущества ролей

Модель безопасности SQL

Давайте пересмотрим пример с настройкой прав доступа для 100 таблиц 100 пользователям. Мы уже пришли к выводу, что стандартными средствами SQL для полной настройки привилегий 100 пользователям относительно 100 таблиц придется выполнить 10000 команд. При использовании ролей, в лучшем случае – все пользователи получают одинаковые права для всех таблиц, потребуется только 200 команд.

В данном случае, администратор может настроить одну роль, для этого потребуется 100 команд, для назначения роли пользователям потребуется еще 100 команд, итого 200. В общем случае, потребуется 100*n + 100 команд, где n – количество необходимых различных ролей. Например, для 5 ролей необходимо выполнить 600 команд. Т.е. понятно, что использование ролей значительно упрощает процесс настройки безопасности базы данных.

Преимущества ролей при управлении пользователями

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

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

KDV: бывает и еще хуже – права не разграничивают, и все пользователи логинятся как SYSDBA. В результате невозможно нормально сделать базе shutdown, т. к. SYSDBA может работать в таком режиме (и перевод в shutdown ничего не меняет).

Такой подход упрощает настройку и поддержание привилегий. Основной минус, учитывая, что при подключении все пользователи указывают одно и то же имя – отсутствие возможности как-то различать их на уровне СУБД. Использование ролей в качестве шаблонов позволяет устранить этот недостаток. Администратор, по-прежнему, единовременно настраивает привилегии, на этот раз для роли, а не общего пользователя. При подключении пользователи, как и в случае с одним общим именем, указывают свои регистрационные имена, на этот раз уникальные, и, дополнительно, используемую роль, что позволяет InterBase, поддерживая механизм привилегий, дифференцировать пользователей. Между использованием общего пользователя и роли имеется два различия. Во-первых, пользователь должен указывать роль при подключении. Во-вторых, администратору необходимо всем пользователям выдать право на использование этой роли.

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

KDV: роли не являются группами в том смысле, что нельзя роли дать права другой роли (включить в роль). Так сделано для того, чтобы исключить возможность зацикливания (роль A включена в роль Б, роль Б включена в роль А и т.п.) и упростить механизм проверки прав при загрузке объектов в память сервера. Также пользователь не может одновременно получить права более чем одной роли (которая указывается при коннекте).

Гибкость ролей

KDV: не следует перебарщивать с раздачей прав, осуществляя это для группы разработчиков из 5-10 человек. Может оказаться, что вы только зря потратите время и усложните жизнь отдельным разработчикам. Лучше потратить это время на планирование и организацию прав доступа для пользователей (приложений).

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

Суммарный привилегии = Привилегии, непосредственно выданные пользователю + Привилегии, назначенные используемой роли.

Продолжая рассматривать пример с жизненным циклом программного продукта, предположим, что пользователь BJONES получил право использовать роль QA. BJONES несёт ответственность за ежемесячный подсчет количества изменений, внесенных разработчиками в исходный код. Учитывая, что ему выдано право на использование только роли QA, он не может получить доступ к таблицам, используемым в разработке. Администратор, в свою очередь, не хотел бы назначать BJONES роль DEVELOPMENT, потому что необходимые права являются лишь подмножеством привилегий, имеющихся у роли DEVELOPMENT. В этом случае, администратор может выдать требуемый набор привилегий непосредственно пользователю. Тогда при подключении с указанием роли QA, BJONES будет обладать привилегиями как по доступу к подмножеству таблиц, используемым в разработке, так и к таблица, используемым в тестировании продукта. Исходя из «соединительной» природы ролей, BJONES получает привилегии, назначенные используемой роли, плюс привилегии, непосредственно выданные ему администратором.

Применение ролей

Создание роли

KDV: одним из «хакерских» способов защиты БД от доступа SYSDBA (при распространении приложений с базами данных) является создание роли SYSDBA, после чего пользователь SYSDBA к базе логиниться не может. Однако, нельзя гарантировать что такое поведение останется во всех будущих версиях InterBase и его клонах. Например, в Firebird 1.5 RC5 для создания роли SYSDBA база должна быть создана не от имени SYSDBA, а также не должна иметь объектов, созданных SYSDBA.

Предоставление привилегий роли

Назначение роли пользователю

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

Оператор предоставления пользователю права использования роли имеет следующий синтаксис:

Использование роли

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

Распространенная ошибка: Часто встречается неправильное представление о механизме ролей, что после того как пользователю назначили роль, он сразу же получает все привилегии, выданные этой роли. Это неверно. Чтобы пользователь приобрел все привилегии, назначенные роли, он должен указать эту роль при подключении к базе данных. Если же пользователь при подключении не указал имя роли, то ему не будут доступны ни одна из привилегий этой роли.

Ниже приведен пример подключения пользователя BJONES к базе данных с указанием роли FULL_ACCESS. В примере используется ISQL, утилита InterBase интерактивного выполнения команд SQL:

Внедрение SQL-кода

Внедрение SQL-кода (англ. SQL injection ) — один из распространённых способов взлома сайтов и программ, работающих с базами данных, основанный на внедрении в запрос произвольного SQL-кода.

Внедрение SQL, в зависимости от типа используемой СУБД и условий внедрения, может дать возможность атакующему выполнить произвольный запрос к базе данных (например, прочитать содержимое любых таблиц, удалить, изменить или добавить данные), получить возможность чтения и/или записи локальных файлов и выполнения произвольных команд на атакуемом сервере.

Атака типа внедрения SQL может быть возможна из-за некорректной обработки входящих данных, используемых в SQL-запросах.

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

Принцип атаки внедрения SQL Править

Допустим, серверное ПО, получив входной параметр id, использует его для создания SQL-запроса. Рассмотрим следующий PHP-скрипт:

Если на сервер передан параметр >

Но если злоумышленник передаст в качестве параметра >

Таким образом, изменение входных параметров путём добавления в них конструкций языка SQL вызывает изменение в логике выполнения SQL-запроса (в данном примере вместо новости с заданным идентификатором будут выбраны все имеющиеся в базе новости, поскольку выражение 1=1 всегда истинно).

Внедрение в строковые параметры Править

Предположим, серверное ПО, получив запрос на поиск данных в новостях параметром search_text, использует его в следующем SQL-запросе (здесь параметры экранируются кавычками):

Сделав запрос вида http://example.org/script.php?search_text=Test мы получим выполнение следующего SQL-запроса:

Но, внедрив в параметр search_text символ кавычки (который используется в запросе), мы можем кардинально изменить поведение SQL-запроса. Например, передав в качестве параметра search_text значение ‘)+and+(news_ >

Использование UNION Править

Язык SQL позволяет объединять результаты нескольких запросов при помощи оператора UNION. Это предоставляет злоумышленнику возможность получить несанкционированный доступ к данным.

Рассмотрим скрипт отображения новости (идентификатор новости, которую необходимо отобразить, передается в параметре id):

Если злоумышленник передаст в качестве параметра id конструкцию -1 UNION SELECT 1,username,password,1 FROM admin, это вызовет выполнение SQL-запроса

Так как новости с идентификатором -1 заведомо не существует, из таблицы news не будет выбрано ни одной записи, однако в результат попадут записи, несанкционированно отобранные из таблицы admin в результате инъекции SQL.

Экранирование хвоста запроса Править

Зачастую, SQL-запрос, подверженный данной уязвимости, имеет структуру, усложняющую или препятствующую использованию union. Например скрипт

отображает имя автора новости по передаваемому идентификатору id только при условии, что имя начинается с буквы а, и внедрение кода с использованием оператора UNION затруднительна.

В таких случаях, злоумышленниками используется метод экранирования части запроса при помощи символов комментария(/* или в зависимости от типа СУБД).

В данном примере, злоумышленник может передать в скрипт параметр id со значением -1 UNION SELECT password FROM admin/*, выполнив таким образом запрос

в котором часть запроса ( AND author LIKE (‘a%’)) помечена как комментарий и не влияет на выполнение.

Расщепление SQL-запроса Править

Для разделения команд в языке SQL используется символ ; (точка с запятой), внедряя этот символ в запрос, злоумышленник получает возможность выполнить несколько команд в одном запросе, однако не все диалекты SQL поддерживают такую возможность.

Например, если в параметры скрипта

злоумышленником передается конструкция, содержащая точку с запятой, например 12;INSERT INTO admin (username, password) VALUES (‘HaCkEr’, ‘foo’); то в одном запросе будут выполнены 2 команды

и в таблицу admin будет несанкционированно добавлена запись HaCkEr.

Методика атак типа внедрение SQL-кода Править

Поиск скриптов, уязвимых для атаки Править

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

  • Данными передаваемыми через методы POST и GET
  • Значениями [HTTP-Cookie]
  • HTTP_REFERER (для скриптов )
  • AUTH_USER и AUTH_PASSWORD (при использовании аутентификации)

Как правило, манипуляция сводится к подстановке в параметры символа одинарной (реже двойной или обратной) кавычки.

Аномальным поведением считается любое поведение, при котором страницы, получаемые до и после подстановки кавычек, различаются (и при этом не выведена страница о неверном формате параметров).

Наиболее частые примеры аномального поведения:

  • выводится сообщение о различных ошибках;
  • при запросе данных (например, новости или списка продукции) запрашиваемые данные не выводятся вообще, хотя страница отображается

и т. д. Следует учитывать, что известны случаи, когда сообщения об ошибках, в силу специфики разметки страницы, не отображаются в браузере, хотя и присутствуют в её HTML-коде.

Анализ внедрения Править

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

  • примерный вид SQL-запроса, в котором происходит внедрение кода;
  • тип используемой СУБД, а также, возможно, версия СУБД.

Для определения типа СУБД во внедряемый код подставляются SQL-конструкции, специфические для разных СУБД.

Конструкция MySQL MS SQL Oracle MS Access PostgreSQL Sybase IBM DB2 Ingres
Комментирование остатка строки /* . — . — . или /* . Коментирование возможно лишь внедрением в запрос NULL-байта: %00. — . — . — . — .
Получение версии version() @@version select banner from v$version @@version select versionnumber from sysibm.sysversions dbmsinfo(‘_version’)
Конкатенация строк concat(string1,string2) string1+string2 string1ΙΙstring2 или string1+string2 или concat(string1,string2) string1IIstring2 string1+string2 string1ΙΙstring2 или string1 concat string2 string1ΙΙstring2

Защита от атак типа внедрение SQL-кода Править

Для защиты от данного типа атак необходимо тщательно фильтровать входные параметры, значения которых будут использованы для построения SQL-запроса.

Фильтрация строковых параметров Править

Предположим, что код, генерирующий запрос (на языке программирования Паскаль), выглядит так:

Чтобы внедрение кода было невозможно, требуется брать в кавычки все строковые параметры. В само́м параметре заменяют кавычки на \», апостроф на \’, обратную косую черту на \\ (это называется «экранировать спецсимволы»). Это можно делать таким кодом:

Фильтрация целочисленных параметров Править

Возьмём другой запрос:

В данном случае поле id имеет числовой тип, и его нельзя брать в кавычки. Поэтому «закавычивание» и замена спецсимволов на escape-последовательности не проходит. В таком случае помогает проверка типа; если переменная id не является числом, запрос вообще не должен выполняться.

Например, на Delphi для противодействия таким инъекциям помогает код:

В случае ошибки функция StrToInt вызовет исключение EConvertError , и в его обработчике можно будет вывести сообщение об ошибке. Двойное преобразование обеспечивает корректную реакцию на числа в формате $132AB (шестнадцатеричная система счисления). На стандартном Паскале, не умеющем обрабатывать исключения, код несколько сложнее.

Усечение входных параметров Править

Для внесения изменений в логику выполнения SQL-запроса требуется внедрение достаточно длинных строк. Так, минимальная длина внедряемой строки в вышеприведённых примерах составляет 8 символов («1 OR 1=1«). Если максимальная длина корректного значения параметра невелика, то одним из методов защиты может быть максимальное усечение значений входных параметров.

Например, если известно, что поле id в вышеприведённых примерах может принимать значения не более 9999, можно «отрезать лишние» символы, оставив не более четырёх:

Использование параметризованных запросов Править

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

Управление безопасностью SQL сервера средствами Microsoft Access (документация)

Эта статья рассказывает о том, как управлять безопасностью SQL сервера, используя Access совместно с библиотекой SQL Distributed Management Objects (SQL-DMO) в Visual Basic for Applications (VBA)

Вступление

Эта статья в основном ориентирована на разработчиков ADP проектов. Возможно, Вы знакомы с 2-мя вещами относительно безопасности SQL сервера. Во-первых, безопасность — это не опция, как это было в Jet. Во-вторых, модель безопасности SQL сервера отличается от модели безопасности Access. Даже если у Вас имеются приблизительные понятия о безопасности SQL сервера, эта статья научит Вас, как настроить ее для защиты ресурсов вашего проекта.

Эта статья также может быть полезна администраторам SQL серверов. Решения, предлагаемые здесь, могут помочь им в управлении безопасностью сервера средствами Access. Из содержания этой статьи администраторы могут узнать об управлении безопасностью сервера без использования графических средств Enterprise Manager или запуска Transact SQL (T-SQL) скриптов из Query Analyzer. Изучив, как управлять безопасностью сервера, используя Access, администраторы получат альтернативу создания более быстрого по исполнению решения.

Cтатью можно условно разделить на две части о безопасности SQL Server 7.0 и SQL Server 2000. В начале рассматривается концепция безопасности SQL сервера, акцентируя внимание на том, как управлять безопасностью, используя Access. Этих сведений достаточно для разработки многопользовательских приложений с разными группами пользователей, имеющих четко определенные права на использование объектов сервера. Вторая часть статьи демонстрирует программные решения, основанные на управлении объектами SQL-DMO (SQL Distributed Management Objects) средствами Microsoft Visual Basic® for Applications (VBA). Поскольку SQL-DMO имеет иерархическую модель построения объектов, очень похожую на модель построения объектов Microsoft Office, то все, что необходимо, это больше узнать об объектах, свойствах, методах, и событиях SQL-DMO относительно безопасности. Ко всему прочему, программистам Access 2002 не обойтись без программирования функций настройки безопасности в VBA, поскольку они недоступны из меню Безопасность базы данных (Database Security).

Концепция безопасности SQL сервера

Этот раздел состоит из 4-х основных частей. Ознакомление начинается с описания аутентификации — процесса проверки пользователя на право доступа к базе данных сервера. Используя Enterprise Manager, SQL сервер предоставляет два вида аутентификации. Первая часть раздела показывает различия и сходства между ними.

Далее говорится о понятии авторизованного пользователя, служащего для осуществления соединения с сервером. Авторизованные пользователи называются логинами (logins). Два типа логинов соответствуют двум моделям аутентификации SQL сервера. Логины отвечают за права доступа к серверу.

Третья часть знакомит с понятием пользователей (users). SQL сервер резервирует понятие users, как понятие, характеризующее пользователей баз данных. Обычно на сервере существует несколько логинов, каждый логин ассоциируется с несколькими пользователями — одним на каждую базу данных, доступ к которой разрешен для данного логина. Логины и пользователи могут принадлежать фиксированным ролям, которые описывают набор разрешений. Вы можете управлять поведением логинов и пользователей, управляя их принадлежностью фиксированным ролям сервера и фиксированным ролям базы данных соответственно.

В четвертой части говорится об определяемых пользователем (user-defined) ролях баз данных.

Аутентификация

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

SQL сервер поддерживает два вида аутентификации: SQL Server и Microsoft Windows® (или Windows NT® с SQL Server 7.0). Эти два вида определяют, кто осуществляет проверку логина: SQL сервер или Windows. При Windows аутентификации пользователи имеют доверительные учетные записи для доступа к SQL серверу. Эти учетные записи проверяет Windows, но SQL сервер «знает» имя учетной записи. Windows аутентификация дает пользователям возможность авторизоваться в Windows и на SQL сервере вводом только одного пароля. При аутентификации SQL Server SQL сервер сам осуществляет авторизацию. При любом типе аутентификации SQL сервер должен знать авторизованные логины.

Есть плюсы и минусы и у первого и у второго типа, однако предпочтительней Windows аутентификация. В Windows осуществляется более сложная процедура авторизации, и она освобождает администратора SQL сервера от необходимости управлять учетными записями. С другой стороны, не все операционные системы, поддерживающие SQL сервер, поддерживают Windows авторизацию. Например, Microsoft Data Engine (MSDE), установленный с Microsoft Office 2000 и Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) установленный с Office XP: и тот и другой вариант могут работать в системе Windows 98, которая не поддерживает Windows аутентификацию. Или SQL Server 7.0 и MSDE, работающие в системе Windows 95. Некоторые организации могут предпочесть возможность контролировать доступ к ресурсам базы данных на уровне администратора БД вместо более глобального уровня администратора Windows.

Любой экземпляр SQL сервера может иметь любой из двух типов аутентификации, которые можно установить в Enterprise Manager (я расскажу о третьем типе далее в этой статье). Документация SQL сервера определяет термин смешанного (mixed) режима подключения к SQL серверу, который поддерживает и Windows, и SQL Server аутентификацию. Если сервер поддерживает только Windows аутентификацию, тогда сервер имеет Windows (или Windows NT) тип аутентификации. По умолчанию MSDE устанавливается со смешанным типом аутентификации. MSDE 2000, напротив, по умолчанию устанавливается с Windows аутентификацией. Разработчики и администраторы, использующие Enterprise Manager, могут изменять тип аутентификации, используя графические средства. Так же любое приложение может использовать библиотеку SQL-DMO для изменения типа аутентификации сервера.

Логины и фиксированные серверные роли

Проект ADP содержит окно базы данных (Database window) похожее на традиционное для Jet решений. Однако проект ADP соединяется с сервером через OLE DB соединение в отличие от базы данных Jet. В проекте ADP тип логина указывается в диалоговом окне Параметры соединения (Data Link Properties). Для вызова этого диалога: из окна базы данных выберите меню Файл (File), в меню выберите Соединение (Connection).

Диалоговое окно Параметры соединения позволяет выбрать сервер баз данных, логин, и базу данных, с которой проект соединится через OLE DB. Подключаться к серверу можно двумя способами. При первом выбирается использование внутренней проверки безопасности Windows NT (use Windows NT integrated security). Это называется Windows аутентификацией. Выбрав эту опцию, не нужно указывать логин и пароль. Это потому, что Windows авторизует пользователя во время запуска Windows. Когда выбрана опция использования внутренней безопасности, проект ADP посылает логин Windows на SQL сервер при попытке создать с ним OLE DB соединение. Выбрав вторую опцию (use a specific user name and password), необходимо указать логин SQL сервера. В поле имени пользователя (user name) должно быть введено стандартное имя логина SQL сервера — логина, поддерживаемого данным SQL сервером. Пароль опционален, однако строго рекомендуется использовать пароль, поскольку совместно с логином, пароль предоставляет дополнительный уровень безопасности.

Хотя ввод логина является ключевым моментом при создании OLE DB соединения в проекте ADP, логин сам по себе не является достаточным для предоставления прав на выполнение каких-либо задач на сервере или с базой данных. Существует понятие членства логина в фиксированных серверных ролях, которое позволяет проекту ADP выполнять некоторые серверные функции, такие как, создание новых баз данных и управление логинами. В зависимости от версии SQL сервера, к которому подключается ADP проект, существует семь или восемь фиксированных серверных ролей. SQL Server 7.0 и MSDE предоставляют семь фиксированных серверных ролей, а SQL Server 2000 и MSDE 2000 — восемь. SQL Server Books Online (BOL) содержит исчерпывающую документацию о фиксированных серверных ролях, включая T-SQL выражения для назначения и удаления логинов из членства в ролях. К примеру, раздел Roles в BOL, рассказывает о фиксированных серверных полях и соответствующем наборе разрешений для доступа к базам данных, таких как просмотр и запись в таблицы.

Следующая таблица содержит краткий обзор имен фиксированных серверных ролей и краткое их описание. Выполнив системную хранимую процедуру sp_helpsrvrole можно получить список имен фиксированных серверных ролей. Исполнение системной хранимой процедуры sp_srvrolepermission выводит подробный перечень функций для каждой фиксированной серверной роли. Есть различия между фиксированными серверными ролями в 7.0 и 2000 версиях SQL сервера. Например, bulkadmin является новой ролью в SQL Server 2000. Дополнительно, выражение DROP DATABASE было доступно только для роли sysadmin в SQL Server 7.0, а SQL Server 2000 дает право на выполнение этой процедуры также и членам роли dbcreator.

Имя фиксированной серверной роли

Описание фиксированной серверной роли

sysadmin Выполнение любого выражения сервера или базы данных serveradmin Администрирование сервера, конфигурация, запуск, остановка. setupadmin Администрирование присоединенных (linked) серверов и право запуска хранимых процедур на этапе запуска сервера. securityadmin Управление логинами, паролями. Может давать право на создание новых баз данных. processadmin Выполнение команды KILL. dbcreator Создание, изменение, переименование и удаление баз данных. diskadmin Управление файлами на диске. bulkadmin Выполнение выражений BULK INSERT.

Логин sa является специальным логином SQL сервера. Этот логин входит в группу sysadmin и предоставляет право на выполнение любых функций на сервере. SQL сервер создает этот логин на этапе установки и его нельзя удалить. Сразу после окончания установки логин sa не имеет пароля. Необходимо задать пароль для sa для обеспечения безопасности Вашего сервера баз данных, особенно для серверов со смешанным типом аутентификации. Помните: сервера с Windows аутентификацией не принимают и не обрабатывают логины SQL сервера.

При установке SQL сервера на Windows 98 или Windows ME сервер всегда устанавливается со смешанным типом аутентификации, потому он может принимать логины SQL сервера. Типы аутентификации по умолчанию различаются для SQL Server 7.0 и MSDE от SQL Server 2000 и MSDE 2000, устанавливаемых на Windows 2000 и Windows NT. Для SQL Server 7.0 и MSDE процесс установки по умолчанию устанавливает сервер со смешанным типом аутентификации. Напротив, SQL Server 2000 и MSDE 2000 по умолчанию устанавливаются с Windows аутентификацией. Кроме того, процесс установки версии 2000 назначает членов группы администраторов Windows членами фиксированной серверной роли sysadmin. Потому эти логины подобны логину sa, который имеет полный контроль над сервером.

Пользователи и фиксированные роли баз данных

Обычно пользователи получают доступ к SQL Server, чтобы работать с одной или несколькими базами данных. Для этой цели SQL сервер имеет механизм приведения в соответствие логинов с базами данных. Так же как SQL сервер может иметь один или больше логинов, каждый логин может иметь одну или больше учетных записей, с ним ассоциированных. Однако одна учетная запись соответствует только одному логину (за двумя исключениями). Документация SQL сервера определяет учетные записи как пользователей баз данных (или пользователей — users). Каждый пользователь базы данных имеет доступ к этой базе данных.

SQL сервер может предоставить пользователям баз данных право владения базой данных и разрешения на использование объектов базы данных. SQL сервер обеспечивает назначение разрешений внутри базы данных на двух независимых уровнях. На первом уровне Вы можете назначить один или несколько кластеров разрешений пользователю. Документация SQL сервера определяет эти кластеры как фиксированные роли базы данных. Я назвал их кластерами разрешений, поскольку они могут объединять в себе разрешения на многие объекты базы данных. Например, одна фиксированная роль (db_datareader) позволяет Вам читать данные из любой таблицы базы данных-не только из какой-то одной или двух определенных таблиц. На втором уровне Вы можете назначать разрешения на уровне конкретных объектов базы данных. SQL сервер позволяет сочетать оба способа: пользователь может быть членом одной или нескольких фиксированных ролей базы данных а также иметь специфические разрешения на конкретные объекты базы данных. Я расскажу об управлении разрешениями на конкретные объекты базы данных в следующем разделе.

Два специальных пользователя могут ассоциироваться с более чем одним логином. Это пользователи dbo и guest. Пользователь dbo является членом фиксированной серверной роли sysadmin, и может создавать объекты на сервере, такие как базы данных или таблицы базы данных. Пользователь, логин которого не принадлежит фиксированной серверной роли sysadmin, также может создавать объекты баз данных, такие, как таблицы.

Видимость объектов в Окне базы данных проекта ADP зависит от того, принадлежит ли логин проекта фиксированной серверной роли sysadmin. Если логин проекта ADP является членом роли sysadmin, в Окне базы данных показываются все объекты, владельцем которых является пользователь dbo, без круглых скобок после их имен. Для пользователей dbo все объекты, владельцами которых являются другие пользователи, кроме dbo, показываются с именем пользователя, заключенного в круглые скобки после имени объекта базы данных. В случае если логин проекта ADP не принадлежит роли sysadmin, имена объектов, владельцем которых является этот пользователь, показываются в окне базы данных без круглых скобок. Имена объектов, владелец которых пользователь dbo показываются с суффиксом (dbo). Объекты, владельцы которых не являются пользователями логина проекта ADP или пользователя dbo, не показываются в Окне базы данных.

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

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

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

Имя фиксированной роли базы данных

Описание фиксированной роли базы данных

db_owner Неограниченные полномочия в базе данных. db_accessadmin Для добавления и удаления пользователей базы данных. db_datareader Для чтения из таблиц и представлений (views) базы данных. db_datawriter Для добавления (insert), редактирования (update) и удаления (delete) записей таблиц и представлений базы данных. db_ddladmin Для выполнения любого оператора SQL Data Definition Language (или выполнения этих функций с помощью графического интерфейса) в базе данных. db_securityadmin Для управления членством пользователей в ролях, разрешением доступа к объектам и владением базой данных. db_backupoperator Для создания резервных копий (backing) и восстановления из них (restoring) базы данных. db_denydatareader Для запрета (или отмены) разрешения на любую выборку (SELECT) из конкретного объекта базы данных. db_denydatawriter Для запрета (или отмены) разрешения на любой оператор INSERT, UPDATE или DELETE, выполняемый с конкретным объектом базы данных.

Назначение пользователям фиксированных ролей базы данных сказывается на функциях, которые пользователи смогут выполнять. Есть возможность изменять влияние членства пользователя в фиксированной роли баз данных назначением разрешений пользователю на конкретные объекты базы данных. Для простоты, в этом разделе об этом не говорится, однако следующий раздел посвящен назначению разрешений на конкретные объекты и определенным пользователем (user-defined) ролям базы данных. В Окне базы данных не показываются никакие таблицы, пока пользователь для логина проекта ADP не является членом фиксированной роли базы данных db_datareader. Пользователь без членства в роли db_backupoperator не может выполнять команды меню: Архивировать (Backup) или Восстановить (Restore) (меню Сервис (Tools), подменю Служебные программы (Database Utilities)). Соответственно, не могут создавать таблицы или другие объекты в базе данных пользователи без членства в фиксированной роли базы данных db_ddladmin. Средствами Окна базы данных, пользователь без членства в роли db_ddladmin может редактировать объекты сервера, такие как таблицы и представления, или создавать новые, однако, отредактированные и созданные объекты не будут сохранены в базе данных.

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

Определяемые пользователем (User-defined) роли базы данных и назначение разрешений

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

Шесть имен разрешений на объекты и их описание показано в следующей таблице. Вы можете назначать эти разрешения конкретным объектам базы данных. Если Вы хотите раздать разрешения на все объекты, Вам придется назначать разрешение для каждого объекта базы данных. Однако Вам лучше использовать фиксированные роли базы данных в случае, если необходимо дать разрешение на все объекты базы данных. Например, если Вы хотите дать разрешение на SELECT для всех таблиц и представлений (включая те, что еще не созданы), проще включить пользователя в фиксированную роль базы данных db_datareader. Если Ваша цель — ограничить разрешение на SELECT только для одного или двух существующих представлений, тогда создайте определяемую пользователем роль базы данных, дайте разрешение на SELECT для этих представлений, и включите в эту роль тех пользователей, которым нужно дать специальные разрешения.

SELECT Просмотр записей в таблице или представлении. INSERT Добавление новых записей в таблицу или представление. UPDATE Изменение содержимого записей таблицы или представления. DELETE Удаление записей из таблиц или представлений. REFERENCES Позволяет создавать внешние ключи к первичному ключу или уникальному индексу таблицы или определяемой пользователем табличной функции (row-returning user-defined function). EXECUTE Выполнение хранимой процедуры или определяемой пользователем функции.

Разрешения на конкретные объекты базы данных могут иметь три состояния (статуса): разрешено (granted), запрещено (denied), и отменено (revoked). Если Вы даете доступ к объекту, значение разрешения будет иметь статус granted. Соответственно, если Вы запрещаете доступ, разрешение будет denied. Если вы отменяете разрешение, поменяйте разрешения с granted или denied на revoked. Отмененные разрешения не разрешают и не запрещают доступ к объекту.

Поскольку допускается включать пользователя в несколько фиксированных и определяемых пользователем ролей базы данных, возможен конфликт разрешений между ролями. Вы можете использовать эти конфликты для тонкой настройки Вашей системы безопасности. Например, пользователь включен в фиксированную роль базы данных db_datareader, дающую ему право выполнять запросы SELECT из всех таблиц и представлений базы данных. Однако Вы можете запретить чтение из таблицы продаж пользователю, имеющему разрешение чтения данных из всех таблиц, чтобы скрыть от него конфиденциальную информацию в таблице продаж. Соответственно, члены фиксированной роли базы данных db_denydatareader не могут адресовать запросы SELECT к любой таблицы базы данных, даже если пользователи включены в определяемую пользователем роль базы данных, которая дает разрешение на чтение из некоторых конкретных таблиц. Разрешение denied всегда переопределяет разрешение granted.

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

Программирование безопасности SQL Server

SQL-DMO является контейнерным приложением (Automation application), назначение которого — администрирование SQL Server. Поскольку SQL-DMO является API для SQL Server Enterprise Manager, Вы можете программировать на SQL-DMO все, что могут предоставить Вам графические средства Enterprise Manager, включая управление всеми аспектами безопасности SQL Server. Этот аспект SQL-DMO особенно полезен для проектов, использующих MSDE и MSDE 2000, потому что в их состав не входит Enterprise Manager. Дополнительно, программистам Access 2002 необходимо изучить некоторые программные решения по управлению безопасностью, поскольку Access 2002 не имеет возможности управлять безопасностью с помощью меню (как это было в Access 2000).

В Access Вы можете программировать, используя SQL-DMO так же, как используются любые другие COM объекты. Ваш VBA проект должен при этом использовать библиотеку SQL-DMO. Microsoft SQLDMO Object Library — имя этой объектной библиотеки. Добавьте ссылку на библиотеку с помощью команды References (меню Tools). Файл DLL с библиотекой включен в MSDE и MSDE 2000. Другие версии SQL Server содержат DLL и файл справки, который Вы можете открыть прямо из окна Visual Basic Editor (VBE). Использующие Microsoft Office XP Developer Edition, могут установить версию SQL сервера, которая включает в себя файл справки по SQL-DMO.

На рисунке представлен фрагмент иерархической модели SQL-DMO с объектами для примеров кода, расположенных далее по тексту. Обратите внимание, что объект SQLServer расположен во главе иерархии. Многие приложения могут соединяться с SQL Server посредством объекта SQLServer. Наследники объекта SQLServer являются коллекциями объектов для логинов и баз данных, а также для индивидуальных объектов, таких как, IntegratedSecurity, которые предоставляют возможность управления безопасностью SQL Server. Последующий пример использует свойство SecurityMode объекта IntegratedSecurity для демонстрации того, как установить режим аутентификации SQL Server. Членами коллекции Databases являются индивидуальные базы данных, каждая из которых имеет собственные коллекции и объекты. Одна из этих коллекций — коллекция Users. Другая коллекция используется для фиксированных и определяемых пользователем ролей базы данных (DatabaseRoles). Членство отдельных пользователей в коллекции DatabaseRoles определяет способность пользователей читать и модифицировать объекты коллекций Tables и Views.

Перед началом рассказа об SQL-DMO важно знать, что DLL для SQL-DMO отличается для SQL Server 7 и SQL Server 2000. Две версии SQL-DMO различаются как минимум в двух аспектах. Во-первых, версия для SQL Server 2000 включает в себя новые объекты, появившиеся в SQL Server 2000, а также улучшенные традиционные. Также версия SQL-DMO для SQL Server 2000 дополнительно содержит традиционные объекты для управления SQL Server 7.0. Имена объектов для 2000 обычно оканчиваются 2 (например, SQLServer2 для новых объектов, вместо SQLServer для традиционных). Во-вторых, программами, использующими SQL-DMO для SQL Server 2000 нельзя управлять SQL Server 7.0 (даже если программы используют традиционные имена объектов SQL-DMO). Однако, используя библиотеку SQL-DMO для SQL Server 7.0 можно управлять SQL Server 2000. Это ограничение является результатом изменения файловых форматов библиотек при переходе к новой версии; синтаксис скрипта SQL-DMO не претерпел изменений.

Различие между версиями SQL-DMO требует внимательного отношения к тому, какую версию библиотеки Вы будете использовать в Вашем проекте. Если необходимо, чтобы приложение SQL-DMO работало с серверами SQL Server 7.0 и SQL Server 2000, необходимо писать программы, используя SQL-DMO для SQL Server 7.0. С другой стороны, если необходимо использовать преимущество SQL Server 2000, тогда Вам необходимо разрабатывать Ваше приложение, используя библиотеку для SQL Server 2000. Поскольку статья демонстрирует технику программирования безопасности, используя SQL-DMO, я использовал версию SQL-DMO для SQL Server 7.0. В любом случае, код выглядит одинаковым для разных версий, кроме случаев, когда Вы используете новые объекты, представленные в SQL Server 2000.

Подключение к SQL серверу

Подобно тому, как вы можете подключиться к SQL Server двумя способами, используя диалог Свойства соединения в проекте ADP, Вы можете подключиться к SQL Server двумя способами через SQL-DMO. Один способ соответствует аутентификации средствами SQL Server. Используя этот способ, Ваш код должен посылать имя сервера, логин, и пароль через SQL-DMO на сервер. Вы можете использовать параметр «имя сервера» для указания различных экземпляров SQL Server на локальной рабочей станции или на другой рабочей станции в сети. SQL-DMO также позволяет Вам соединяться, указав только имя сервера. В этом случае, SQL-DMO посылает идентификатор авторизованного в Windows пользователя на экземпляр SQL сервера. Для того чтобы использовать этот способ, следует установить свойство LoginSecure сервера в значение True.

На следующем листинге показана пара процедур, демонстрирующих синтаксис подключения к экземпляру SQL Server, используя логин SQL Server. Первая процедура определяет три строковых параметра для имени сервера (srvname), логина (suid) и пароля (pwd). Далее она посылает их второй процедуре, которая начинается инициализацией объекта SQLServer. Этот объект представляет экземпляр сервера. Затем вторая процедура вызывает метод Connect объекта SQLServer. Этот метод принимает на вход три параметра. Демонстрируется синтаксис передачи переменных имени сервера, логина и пароля.

Изменение режима аутентификации

Одним из основных преимуществ, которое дает SQL-DMO программистам, работающим с MSDE и MSDE 2000, является реализация этой возможности, которая иначе была бы им недоступна. Вызвано это тем, что Enterprise Manager не включен в установочный пакет MSDE или MSDE 2000. К примеру, клиентский компонент SQL Server Enterprise Manager дает возможность администраторам графическими средствами изменить режим аутентификации сервера:Windows аутентификация или смешанный режим. Проект ADP не предоставляет такой возможности. Однако следующая пара процедур позволит Вам изменить режим аутентификации сервера, даже не имея возможности использовать Enterprise Manager.

Первая процедура определяет значение одного параметра и посылает его второй процедуре. Этот параметр определяет один из двух режимов аутентификации. В комментариях к процедуре показаны имена двух предопределенных констант в соответствии с режимом аутентификации. Смешанный режим аутентификации является режимом по умолчанию. SQL-DMO позволяет установить режим аутентификации, который недоступен даже в Enterprise Manager. Другими словами, Вы можете использовать SQL-DMO для указания серверу принимать только логины SQL Server.

Вторая процедура выполняет подключение к серверу с использованием идентификатора пользователя Windows. Затем она устанавливает свойство SecurityMode объекта IntegratedSecurity для сервера в значение параметра, переданного ей из первой процедуры. Если значение этого свойства меняет режим аутентификации, режим не сменится, пока Вы не остановите и не перезапустите сервер. Однако вызов метода Stop объекта сервера не может моментально остановить сервер. Вы должны подождать, пока сервер не остановится. При помощи свойства Status объекта сервера Ваша программа может следить за сообщением, что сервер остановился. Процедура использует цикл, ожидая смены значения Status на SQLDMOSvc_Stopped. Далее процедура выполняет метод Start объекта SQLServer. Вызов этого метода установит новый режим аутентификации для сервера.

Открытие проекта ADP

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

Следующий пример кода демонстрирует, как открыть существующий ADP проект. В коде, представленном ниже, именем проекта ADP является msdn_test_security.adp. Пример кода позволяет открыть проект ADP в режиме аутентификации Windows или в режиме аутентификации SQL Server. Снова используется две процедуры. Список параметров в первой процедуре — CallOpenADPWindowsOrSQLServer — относительно большой, что связано с необходимостью указать имя сервера, базу данных, путь и название файла ADP проекта, логин SQL Server и пароль, а также логическую переменную. Логическая переменная определяет открытие проекта с Windows или с SQL Server аутентификацией. При указании Windows аутентификации нет необходимости в логине и пароле, поскольку проект ADP автоматически ссылается на идентификатор пользователя Windows. Вторая процедура использует эти параметры для открытия ADP проекта.

Перед описанием второй процедуры было бы полезным объяснить некоторые особенности кода. Во-первых, процедура, открывающая проект ADP уже работает в сессии Access. Поэтому необходимо создать новый экземпляр Access для проекта, который мы намерены открыть. Для создания сессии Access используется OLE класс.

Во-вторых, любой существующий проект ADP всегда открывается с сохраненными во время предыдущего открытия параметрами соединения с сервером. Поэтому программа должна открыть проект, используя старые параметры подключения, а потом эти параметры сменить. Новые параметры подключения можно назначить только открытому проекту. После закрытия проекта новые параметры соединения сохраняются, пока пользователь не изменит их в диалоге Параметры соединения. (Однако объектная модель офиса позволяет Вам скрыть и сделать недоступной команду меню Соединение в меню Файл).

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

Public appAccess as Access.Application

Вторая процедура — OpenADPWindowsOrSQLServer — начинается выводом сообщения, спрашивающим оставить ли сессию открытой после завершения процедуры. Если пользователь ответит «нет», процедура установит параметры соединения для проекта, а затем закроет сессию без шанса посмотреть изменения. Далее проект запускает новую сессию Access при помощи функции CreateObject. Аргументы этой функции различаются в зависимости от версии Access, которую Вы используете (в коде описаны особенности использования версий Access 2000 или Access 2002). После создания сессии для проекта процедура вызывает метод OpenAccessProject для добавления проекта ADP в сессию. Аргументом для этого метода служит путь вместе с именем файла проекта. Расширение .adp опционально. После открытия проекта процедура переопределяет его параметры соединения при помощи метода OpenConnection объекта CurrentProject. Процедура переопределяет соединения, используя две различные строки соединения в зависимости от значения переменной bolWindowsLogin. Эта Логическая переменная устанавливается первой процедурой для указания того, какой тип авторизации требуется установить для проекта. На последнем шаге процедуры сессия закрывается, если пользователь ответил положительно на вопрос в окне сообщения перед открытием сессии.

Добавление и удаление логинов

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

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

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

Вторая процедура после соединения с сервером инициализирует объект login. Этот объект — член коллекции Logins, показанной на рисунке. Далее процедура устанавливает три параметра для логина: его имя (login_name), базу данных по умолчанию (defaulf_db_name) и пароль (password). Операции по простому присвоению работают с двумя первыми аргументами. Для установки пароля для логина SQL сервера вызывается метод SetPassword объекта login. При установке пароля для нового логина используйте пустую строку в качестве первого аргумента метода; укажите пароль для нового логина вторым аргументом. В нашем примере пароль для логина login_name определен как «password» (без кавычек). Добавление логина login_name завершается вызовом метода Add коллекции Logins.

Также Вы можете использовать метод SetPassword для переопределения пароля существующего логина. Первый аргумент метода SetPassword в этом случае — текущий пароль. Однако если Вы не знаете текущего пароля, Вы можете указывать первым аргументом пустую строку-так же, как при создании пароля для нового логина. Создание или изменение логина возможно в том случае, если Ваш текущий логин (под которым Вы осуществили соединение с сервером) входит в одну из фиксированных ролей сервера: sysadmin или securityadmin. Неважно, каким способом Вы меняете пароль: программно или другими средствами. В примере используется логин sa, входящий в фиксированную роль сервера sysadmin.

В нескольких следующих строках второй процедуры создается логин для группы Windows group с именем msdn_OS_users. В моем случае эта группа Windows из трех идентификаторов пользователей Windows, в действительности группа Windows могла быть как группой пользователей локального компьютера, так и домена сервера Windows. Процесс создания логина начинается с инициализации нового объекта login (lgn1). При попытке повторного использования lgn1 без переинициализации (обнуления), возникнет ошибка выполнения, напоминающая о том, что требуется новый объект. Процедура использует название группы Windows, состоящее из двух частей. Первая часть — имя сервера Windows, вторая — имя группы сервера Windows. Обратный слэш служит разделителем. Базой данных по умолчанию для второго логина снова указывается база данных your_db_name. Поскольку второй логин создается для аутентификации Windows, нет необходимости устанавливать пароль. Однако необходимо установить свойство Type в SQLDMOLogin_NTGroup. Это значение соответствует внутренней константе, соответствующей логину группы Windows. При добавлении логина SQL Server не нужно устанавливать значение свойства Type, поскольку оно задано по умолчанию для логинов SQL сервера. Заканчивается создание второго логина вызовом метода Add коллекции Logins.

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

После печати членов коллекции Logins процедура удаляет два вновь созданных логина поименно: login_name и YOUR_SERVER_NAME\msdn_OS_users. Этот пример демонстрирует использование коллекции Logins ее метода Remove и свойства Name логина, которого Вы хотите удалить. Для подтверждения удаления двух логинов процедура выводит список логинов повторно из коллекции Logins. Откройте окно Immediate и сравните два списка логинов: после добавления и удаления двух логинов.

Создание логина с пользователем — участником роли db_datareader

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

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

После соединения с сервером и создания нового логина login_name, код создает нового пользователя. Далее назначаются свойства Name и Login для пользователя. Свойство Login означает логин, под которым пользователь соединяется — login_name в нашем примере. После этого пользователь готов к добавлению в коллекцию Users базы данных. Отметим, что синтаксис добавления нового пользователя соответствует синтаксису иерархии объектов. Метод Add для нового пользователя применяется к коллекции Users принадлежащей в свою очередь объекту database. Database — член коллекции Databases принадлежащей объекту сервера (SQLserver). После создания пользователя требуется одна или несколько строчек для внесения пользователя в фиксированную роль базы данных db_datareader. Метод по добавлению пользователя в роль называется AddMember. В нашем случае метод AddMember элемента коллекции DatabaseRoles добавляет пользователя в роль базы данных. Этот метод берет в качестве параметра строковое выражение свойства Name объекта нового пользователя.

Как только настройка безопасности нового логина и пользователя завершены, вызывается процедура OpenADPWindowsOrSQLServer. В предыдущих листингах есть пример использования этой процедуры. В нашем случае открывается файл msdn_test_security.adp, основываясь на логине login_name. Для проверки работоспособности логина откройте таблицы базы данных your_db_name. Дополнительно, работоспособность нового логина можно проверить в диалоговом окне Параметры соединения, указав логин your_login.

Если попытаться повторно запустить процедуру MakeLoginWithDatareaderUser попытка закончится ошибкой. Это потому, что логин your_login уже существует. Ошибка возникнет при попытке повторно его добавить. Попытка запустить процедуру может закончиться неудачей и по другой причине: Вы не закрыли проект, подключенный к БД с этим логином. Вы можете написать стандартную обработку ошибок VBA для обработки ошибок времени выполнения. Подобные ошибки могут сопровождать выполнение предыдущих примеров. Я предпочел не углубляться в обсуждение обнаружения ошибок времени выполнения, акцентируя внимание на вопросах безопасности SQL сервера.

Заключение

Безопасность SQL Server построена на принципах, отличных от тех, что использовали программисты в базах данных Jet. Тем не менее, SQL Server Books Online тщательно документирует правила по безопасности SQL Server. Эта статья переводит основные правила BOL в специфические правила, цель которых использовать проект Access с SQL Server. Примеры кода, приведенные в этой статье, демонстрируют, как управлять безопасностью SQL Server с помощью Access.

Основные команды SQL, которые должен знать каждый программист

Язык SQL или S tructured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных (RDBMS). В этой статье будет рассказано о часто используемых командах SQL, с которыми должен быть знаком каждый программист. Этот материал идеально подойдёт для тех, кто хочет освежить свои знания об SQL перед собеседованием на работу. Для этого разберите приведённые в статье примеры и вспомните, что проходили на парах по базам данных.

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

Настройка базы данных для примеров

Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):

Затем введите пароль.

Выполните следующую команду. Назовём базу данных «university»:

Команды для работы с базами данных

1. Просмотр доступных баз данных

2. Создание новой базы данных

3. Выбор базы данных для использования

4. Импорт SQL-команд из файла .sql

5. Удаление базы данных

Работа с таблицами

6. Просмотр таблиц, доступных в базе данных

7. Создание новой таблицы

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

  • ячейка таблицы не может иметь значение NULL;
  • первичный ключ — PRIMARY KEY (col_name1, col_name2, …) ;
  • внешний ключ — FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

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

Пример

Создайте таблицу «instructor»:

8. Сведения о таблице

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

9. Добавление данных в таблицу

При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

10. Обновление данных таблицы

11. Удаление всех данных из таблицы

12. Удаление таблицы

Команды для создания запросов

13. SELECT

SELECT используется для получения данных из определённой таблицы:

Следующей командой можно вывести все данные из таблицы:

14. SELECT DISTINCT

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

15. WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

В запросе можно задавать следующие условия:

  • сравнение текста;
  • сравнение численных значений;
  • логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE :

16. GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.

Пример

Выведем количество курсов для каждого факультета:

17. HAVING

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

Пример

Выведем список факультетов, у которых более одного курса:

18. ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .

Пример

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

19. BETWEEN

BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

20. LIKE

Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.

«КРОК», Москва, Санкт-Петербург, Троицк, Челябинск, Воронеж, Иркутск, Краснодар, Нижний Новгород, Самара, Пермь, от 120 000 до 240 000 ₽

Есть два свободных оператора, которые используются в LIKE :

  • % (ни одного, один или несколько символов);
  • _ (один символ).

Пример

Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-» :

21. IN

С помощью IN можно указать несколько значений для оператора WHERE :

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

22. JOIN

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

Пример 1

Выведем список всех курсов и соответствующую информацию о факультетах:

Пример 2

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

Пример 3

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

23. View

View — это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. View всегда показывает самую свежую информацию из базы данных.

Создание

Удаление

Пример

Создадим view , состоящую из курсов с 3 кредитами:

24. Агрегатные функции

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

  • COUNT (col_name) — возвращает количество строк;
  • SUM (col_name) — возвращает сумму значений в данном столбце;
  • AVG (col_name) — возвращает среднее значение данного столбца;
  • MIN (col_name) — возвращает наименьшее значение данного столбца;
  • MAX (col_name) — возвращает наибольшее значение данного столбца.

25. Вложенные подзапросы

Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос.

Пример

Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:

Sql код текущего пользователя

Я не знаю, почему этот код не работает. Может кто-нибудь, пожалуйста, помогите мне? Я не так много знаю о кодах SQL, но, пожалуйста, помогите, он написан на php, потому что я хочу, чтобы мои пользователи заходили на страницу с этим файлом.

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

Решение

пропущена точка с запятой в этой строке $ >

Основы безопасности SQL Server

Что должен знать о защите администратор базы данных: объяснение терминов и общий обзор объектов, применяемых на практике

Базовое понимание безопасности SQL Server невозможно без осознания различий между учетными записями, пользователями, схемами и ролями. Кроме того, необходимо различать безопасность SQL Server и доверенную проверку подлинности Trusted Authentication. В настоящей статье я планирую разъяснить эти вопросы и поделиться основными знаниями, необходимыми начинающему администратору базы данных для успешной работы.

Безопасность SQL Server и доверенная проверка подлинности

Существует два вида схем безопасности в Microsoft SQL Server: безопасность SQL Server и доверенная проверка подлинности (также известная как проверка подлинности Windows). Безопасность SQL Server — стандартная комбинация имени пользователя для регистрации и пароля, а доверенная проверка подлинности предполагает, что устройство, которое пытается подключиться к экземпляру SQL Server, одобрено процедурой проверки подлинности домена, и результаты этой проверки переданы экземпляру SQL Server: считается, что домен, в котором размещен экземпляр SQL Server, доверяет учетной записи пользователя — проверка выполнена ранее.

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

Имена и пользователи

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

Есть два способа создать эти учетные записи пользователя: с помощью Transact-SQL (https://msdn.microsoft.com/en-us/library/ms189751.aspx? f=255&MSPPError=-2147217396) или через графический интерфейс. Процедура использования T-SQL для создания учетных записей хорошо документирована, и лучше всего воспользоваться ссылкой на официальную документацию по Microsoft SQL Server. А пока рассмотрим способ создания учетной записи в графическом интерфейсе. Чтобы запустить диалоговое окно для создания учетных записей пользователей, подключитесь к экземпляру SQL Server в среде SQL Server Management Studio (SSMS) в обозревателе объектов, а затем разверните узел Security\Logins («Безопасность\Имена пользователя»). Щелкните правой кнопкой мыши на пункте Logins и выберите в контекстном меню пункт New Login («Создать учетную запись») (см. экран 1).

Экран 1. Создание учетной записи пользователя SQL Server

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

Экран 2. Настройка учетной записи пользователя SQL Server

Это вкладка General («Общие») для создания (и изменения) параметров учетной записи. Она отличается от двух ранее описанных схем безопасности. На вкладке General можно задать:

  • Login name («Имя пользователя»). Используется при проверке подлинности. В случае Windows, или доверенной проверки подлинности, необходимо задать имя в формате DOMAIN\LOGIN, где LOGIN — имя пользователя внутри домена, из которого пользователь выполняет проверку подлинности. Если экземпляр SQL Server расположен в другом домене, то необходимы отношения доверия между этим доменом и доменом SQL Server.
  • Password («Пароль»). При проверке подлинности SQL Server текстовое поле пароля включено, и вы вводите как имя пользователя, так и связанный с ним пароль.
  • Password Policy («Настройки политики паролей») и Expiration («Срок действия»). Флажки для политики пароля и срока действия также установлены в режиме проверки подлинности SQL Server, и применяются те политики, которые действуют в Active Directory в домене, где размещается SQL Server. Назначая имя пользователя SQL Server, вы можете разрешить пользователям менять свои пароли после регистрации. В результате администратор базы данных лишается доступа к имени учетной записи конечного пользователя.
  • Certificates («Сертификаты»), Keys («Ключи»), Credentials («Учетные данные»). В этой статье, предназначенной для начинающих, мы не будем рассматривать сертификаты, ключи и учетные данные.
  • Default Database («База данных по умолчанию»).Когда подключение к SQL Server установлено, выполняются два шага: проверка подлинности (должно существовать имя пользователя для учетных данных домена пользователя, если используется Windows или доверенная проверка подлинности либо необходимо передать комбинацию имени пользователя и пароля в экземпляр SQL Server). Это первый барьер. Второй заключается в том, что у проверенного имени пользователя имеется связанный объект пользователя в базе данных по умолчанию — базе данных, первоначально настроенной как контекст имени пользователя после проверки удостоверения. Даже если первое препятствие преодолено, при отсутствии соответствующего пользователя базы данных в базе данных по умолчанию подключение не будет установлено, и соответствующая запись будет внесена в журнал ошибок SQL. Но есть исключения: если серверная роль пользователя настолько важна, что нужно установить для него по умолчанию неявные права в каждой базе данных, то необязательно наличие соответствующего пользователя в базе данных по умолчанию. Однако я забегаю вперед, так как мы еще не рассматривали пользователей базы данных или роли сервера. Достаточно отметить, что, когда вы выбираете базу данных по умолчанию в графическом интерфейсе, связанный пользователь базы данных не создается. Вы просто указываете, какой должна быть база данных по умолчанию. При этом вы используете вкладку User Mapping («Сопоставление пользователей») диалогового окна Create Login («Создание учетной записи»), чтобы создать связанного пользователя базы данных.

Перейдем к следующей вкладке Server Roles («Роли сервера»), показанной на экране 3. На этой странице можно выбрать любые роли на уровне SQL Server (экземпляра) для нового пользователя. Роли сервера представляют собой коллекции прав, также известные как защищаемые объекты, которые упаковываются в коллекцию, чтобы вам не приходилось назначать права каждому защищаемому объекту отдельно. По умолчанию каждая учетная запись является членом общедоступной роли, что позволяет установить основное подключение к экземпляру SQL Server. Далее в статье будет рассмотрена каждая роль сервера в составе Microsoft SQL Server.

Экран 3. Вкладка Server Roles

Следующая страница диалогового окна Create Login в среде SQL Server Management Studio предназначена для сопоставления учетных записей пользователей. Каждая учетная запись может иметь пользователя в одной или нескольких базах данных. На этой странице можно создать пользователей базы данных, связанных с новой учетной записью. Для этого нужно предоставить следующую информацию.

  • Database («База данных»).Установите флажок рядом с базой данных, в которой нужно создать связанного пользователя для учетной записи.
  • User Name («Имя пользователя»). Имя объекта пользователя не обязательно соответствует имени учетной записи, и далее будет показано, как это можно изменить.
  • Default Schema («Схема по умолчанию»). Каждый пользователь базы данных должен быть назначен схеме по умолчанию. Схема представляет собой коллекцию объектов базы данных, отделенных логически (но не обязательно физически) от других объектов в базе данных. Можно предоставить пользователю или группе пользователей права для всех объектов данной схемы, например предоставить всем пользователям из бухгалтерии (или учетной записи службы бухгалтерского приложения) определенные права для всех объектов в схеме Billing, но не давать доступ к этим объектам другим пользователям. При назначении схемы по умолчанию для пользователя базы данных нет необходимости включать имя схемы в вызовы T-SQL к базе данных при адресации объектов в этой схеме. Это также означает, что если пользователю предоставлены права на создание объектов, то по умолчанию они будут созданы в этой схеме, если только не указать имя схемы при создании объектов. Далее в статье мы еще коснемся концепции схем.
  • Database Role Membership («Членство в роли базы данных»).Точно так же, как на уровне экземпляра или сервера, каждая база данных располагает заранее определенной коллекцией прав, упакованных в ролях. Чуть позже мы рассмотрим роли базы данных, поставляемые с Microsoft SQL Server.

Обратимся к примеру диалогового окна для учетной записи пользователя SQLCRUISE\skipper (см. экран 4).

Экран 4. Пример настроек учетной записи пользователя

В этом примере пользователю SQLCRUISE\skipper предоставляются права для базы данных по умолчанию (lifeboat), где связанное имя пользователя — просто skipper. Схема по умолчанию — skipper_only. В двух других базах данных, в которых будут созданы пользователи для этой учетной записи, применяется то же имя пользователя, что и в имени пользователя (обычно ради упрощения идентификации), а схема по умолчанию — dbo, которая применяется по умолчанию в Microsoft SQL Server для всех определяемых пользователем объектов. Дополнительные сведения об этом будут приведены в следующем разделе. В случае с базой данных lifeboat мы предоставляем только членство в общедоступной роли базы данных, что предусматривает подключение к базе данных без дополнительных разрешений.

На следующей странице, Securables, представлены защищаемые объекты на уровне сервера или экземпляра. Как отмечалось выше, защищаемые объекты — это разрешения, предоставленные объектам. Защищаемые объекты обычно предоставляются в следующих случаях:

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

В нашем примере я предоставил SQLCRUISE\skipper членство в общедоступной роли сервера и разрешил просматривать любые определения объектов, существующие на уровне сервера (см. экран 5).

Экран 5. Назначение дополнительных прав

Наконец, переходим к странице Status («Состояние»). На этой странице можно разрешить или отменить доступ для пользователя (по умолчанию выбирается Grant — разрешить). Поэтому можно создать учетную запись, предоставить права, создать связанных пользователей, а затем отменить доступ. Вы можете вернуться в это окно для существующего пользователя и отменить доступ к экземпляру SQL Server. Аналогично происходит включение и отключение учетной записи (см. экран 6). Наконец, мы можем просмотреть состояние учетной записи пользователя и узнать, была ли учетная запись заблокирована из-за слишком большого числа неудачных попыток регистрации с неверным паролем.

Экран 6. Вкладка Status

Вы можете изучить код T-SQL в листинге 1, который формируется и выполняется при выполнении этих настроек в графическом интерфейсе.

На данном этапе важно отметить, как организована связь пользователей базы данных с учетной записью пользователя сервера. Как я уже указывал, соответствие имен между двумя объектами необязательно. Это объясняется тем, что объекты объединены в системных таблицах не по имени, а по идентификатору, именуемому sid (идентификатор безопасности). Это позволяет избавиться от привязки к учетной записи, соответствующей имени пользователя, или избежать возникновения ситуации, в которой вы восстанавливаете базу данных с именем пользователя, например trevor на экземпляре SQL Server, где уже имеется учетная запись trevor, но это совершенно другое лицо, которое не должно иметь прав в вашей базе данных. Благодаря sid такая опасность исключается. Если посмотреть на два системных представления, отображающих данные об учетных записях и пользователях, то можно увидеть, как эти объекты выглядят внутри SQL Server. Я подготовил учетную запись и пользователя professor и назначил lifeboat базой данных по умолчанию, создав при этом соответствующего пользователя в lifeboat. Системное представление, отображающее информацию об именах пользователей, — sys.server_principals (sys — схема). Информация о пользователе базы данных выводится через представление sys.database_principals в каждой базе данных. Эти представления могут быть соединены на основе sid (см. листинг 2 и экран 7).

Экран 7. Объединение информации о пользователе

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

Схемы и роли

Теперь мы переходим к более подробному знакомству со схемами и ролями. И схемы, и роли представляют собой коллекции в терминологии SQL Server. Схемы — это коллекции объектов (таблиц, представлений, хранимых процедур и т. д.). Роли — коллекции прав: роли серверов для прав на уровне сервера/экземпляра и роли базы данных для прав в конкретной базе данных. Однако на этом сходство заканчивается.

Особого внимания заслуживают две схемы по умолчанию: sys и dbo. Схема sys — фактически владелец всех системных объектов в Microsoft SQL Server. Во многих системных представлениях и динамических объектах управления они именуются объектами ms_shipped, которые вы увидите отмеченными в столбцах bit-type в соответствующих представлениях как is_ms_shipped со значением 1 для системных объектов и 0 для пользовательских объектов. Вы можете сами создавать схемы, соответствующие вашим потребностям. Ранее я упоминал в качестве примера схему выставления счетов для бухгалтерских объектов. Если пользователь создает объект без указания схемы, объект будет создан в схеме по умолчанию для этого пользователя. Если схема по умолчанию не определена для пользователя, то в качестве схемы по умолчанию назначается dbo.

При направлении запросов к пользователям рекомендуется применять полные доменные имена, то есть указывать имя базы данных, имя схемы и имя объекта, а не только имя объекта. Как это выглядит на практике? Если имеется таблица с именем tblFoo в схеме dbo базы данных SQLCruise, то можно создать запрос, который будет выбирать все столбцы и строки из этой таблицы несколькими способами (см. листинг 3).

Каждый вариант работает успешно, если имеется только одна таблица с именем tblFoo в базе данных SQL_Cruise и текущим контекстом базы данных была база данных SQL_Cruise. Однако только первый вариант будет работать корректно, независимо от того, к какой базе данных в настоящее время вы подключены на экземпляре SQL Server, содержащем базу данных SQL_Cruise. Второй вариант будет выполнен, если вы подключены к базе данных SQL_Cruise, независимо от числа схем, имеющих tblFoo, так как вы указали схему dbo. Третий вариант выдаст сообщение об ошибке (см. экран 8), если в базе данных SQL_Cruise имеется несколько схем с tblFoo, как показано в листинге 4, где я создал как таблицу dbo.tblFoo, так и таблицу user.tblFoo.

Экран 8. Сообщение об ошибке, если в базе данных SQL_Cruise имеется несколько схем с tblFoo

Да, все верно — объект существует, но вы получаете сообщение об ошибке Invalid object name («Недопустимое имя объекта»). Никогда не будьте уверены заранее, что объекта с таким именем не существует. Сообщение может свидетельствовать о проблеме с синтаксисом.

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

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

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

Илон Маск рекомендует:  Asp файл данных о числе посещений
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL