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

Для этого вы можете использовать инструмент геообработки или скрипт Создать пользователя базы данных (Create Database User) чтобы выполнить следующие операции:

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

Добавить пользователя, который может создавать данные.

Вы можете запустить инструмент из ArcGIS for Desktop или вызвать инструмент в скрипте Python для создания пользователя базы данных, который может создавать таблицы, классы объектов и представления.

Вы должны подключиться к базе данных используя учетную запись с правами системного администратора в экземпляре SQL Server, чтобы запустить инструмент Создать пользователя базы геоданных (Create Database User) tool.

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

Использование инструмента Создать пользователя базы данных (Create Database User)

1. Запустите ArcMap или ArcCatalog.
2. Подключитесь к базе данных или базе геоданных , используя учетную запись с правами системного администратора в экземпляре SQL Server.
3. Откройте инструмент Создать пользователя базы данных (Create Database User) .

   Инструмент находится в группе инструментов Администрирование базы геоданных (Geodatabase Administration) набора Управление данными (Data Management).

4. Укажите подключение к базе данных в поле Входное подключение к базе данных (Input Database Connection) .
5. Выберите, будете ли вы создавать учетную запись с аутентификацией SQL Server или использовать существующую учетную запись с аутентификацией Windows.
   • Оставьте не отмеченным, чтобы создать пользователя с аутентификацией средствами SQL Server. Обратите внимание, что по умолчанию экземпляры SQL Server используют только аутентификацию Windows. Если ваш экземпляр не настроен для использования аутентификации SQL Server или Windows, вы не сможете создать пользователя с аутентификацией средствами базы данных.
   • Отметьте Создать пользователя с аутентификацией средствами операционной системы (Create Operating System Authenticated User) , чтобы использовать существующую учетную запись с аутентификацией средствами Windows.
6. Введите имя пользователя базы данных, который будет создан инструментом.

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

7. Введите пароль пользователя базы данных.
8. Если у вас уже определена роль, к которой вы хотите добавить пользователя, укажите ее.
9. Щелкните OK , чтобы запустить инструмент.

Запустите скрипт Python

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

1. Создайте текстовый файл на клиентском компьютере ArcGIS и скопируйте следующий скрипт в файл.

   """ Name: create_database_user.py Description: Provide connection information to a database user. Type create_database_user.py -h or create_database_user.py --help for usage """ # Import system modules import arcpy import os import optparse import sys # Define usage and version parser = optparse . OptionParser (usage = "usage: %prog " , version = "%prog 1.0 for 10.1 release" ) #Define help and options parser . add_option ("--DBMS" , dest = "Database_type" , type = "choice" , choices = [ "SQLSERVER" , "ORACLE" , "POSTGRESQL" , "" ], default = "" , help = "Type of enterprise DBMS: SQLSERVER, ORACLE, or POSTGRESQL." ) parser . add_option ("-i" , dest = "Instance" , type = "string" , default = "" , help = "DBMS instance name" ) parser . add_option ("-D" , dest = "Database" , type = "string" , default = "none" , help = "Database name: Not required for Oracle" ) parser . add_option ("--auth" , dest = "Account_authentication" , type = "choice" , choices = [ "DATABASE_AUTH" , "OPERATING_SYSTEM_AUTH" ], default = "DATABASE_AUTH" , help = "Authentication type options (case-sensitive): DATABASE_AUTH, OPERATING_SYSTEM_AUTH. Default=DATABASE_AUTH" ) parser . add_option ("-U" , dest = "Dbms_admin" , type = "string" , default = "" , help = "DBMS administrator user" ) parser . add_option ("-P" , dest = "Dbms_admin_pwd" , type = "string" , default = "" , help = "DBMS administrator password" ) parser . add_option ("--utype" , dest = "user_type" , type = "choice" , choices = [ "DATABASE_USER" , "OPERATING_SYSTEM_USER" ], default = "DATABASE_USER" , help = "Authentication type options (case-sensitive): DATABASE_USER, OPERATING_SYSTEM_USER. Default=DATABASE_USER" ) parser . add_option ("-u" , dest = "dbuser" , type = "string" , default = "" , help = "database user name" ) parser . add_option ("-p" , dest = "dbuser_pwd" , type = "string" , default = "" , help = "database user password" ) parser . add_option ("-r" , dest = "role" , type = "string" , default = "" , help = "role to be granted to the user" ) parser . add_option ("-t" , dest = "Tablespace" , type = "string" , default = "" , help = "Tablespace name" ) # Check if value entered for option try : (options , args ) = parser . parse_args () #Check if no system arguments (options) entered if len (sys . argv ) == 1 : print " %s : error: %s \n " % (sys . argv [ 0 ], "No command options given" ) parser . print_help () sys . exit (3 ) #Usage parameters for spatial database connection database_type = options . Database_type . upper () instance = options . Instance database = options . Database . lower () account_authentication = options . Account_authentication . upper () dbms_admin = options . Dbms_admin dbms_admin_pwd = options . Dbms_admin_pwd dbuser = options . dbuser dbuser_pwd = options . dbuser_pwd tablespace = options . Tablespace user_type = options . user_type role = options . role if (database_type == "SQLSERVER" ): database_type = "SQL_SERVER" if ( database_type == "" ): print (" \n %s : error: \n %s \n " % (sys . argv [ 0 ], "DBMS type (--DBMS) must be specified." )) parser . print_help () sys . exit (3 ) if (database_type == "SQL_SERVER" ): if ( account_authentication == "DATABASE_AUTH" and dbms_admin == "" ): print (" \n %s : error: %s \n " % (sys . argv [ 0 ], "DBMS administrator must be specified with database authentication" )) sys . exit (3 ) if ( account_authentication == "OPERATING_SYSTEM_AUTH" and dbms_admin != "" ): print (" \n Warning: %s \n " % ("Ignoring DBMS administrator specified when using operating system authentication..." )) else : if ( dbuser . lower () == "" ): print (" \n %s : error: %s \n " % (sys . argv [ 0 ], "Database user must be specified." )) sys . exit (3 ) if ( dbms_admin == "" ): print (" \n %s : error: %s \n " % (sys . argv [ 0 ], "DBMS administrator must be specified!" )) sys . exit (3 ) if ( user_type == "DATABASE_USER" and (dbuser == "" or dbuser_pwd == "" )): print (" \n %s : error: \n %s \n " % (sys . argv [ 0 ], "To create database authenticated user, user name and password must be specified!" )) parser . print_help () sys . exit (3 ) # Get the current product license product_license = arcpy . ProductInfo () # Checks required license level if product_license . upper () == "ARCVIEW" or product_license . upper () == "ENGINE" : print (" \n " + product_license + " license found!" + " Creating a user in an enterprise geodatabase or database requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license." ) sys . exit ("Re-authorize ArcGIS before creating a database user." ) else : print (" \n " + product_license + " license available! Continuing to create..." ) arcpy . AddMessage ("+++++++++" ) # Local variables instance_temp = instance . replace (" \\ " , "_" ) instance_temp = instance_temp . replace ("/" , "_" ) instance_temp = instance_temp . replace (":" , "_" ) Conn_File_NameT = instance_temp + "_" + database + "_" + dbms_admin if os . environ . get ("TEMP" ) == None : temp = "c: \\ temp" else : temp = os . environ . get ("TEMP" ) if os . environ . get ("TMP" ) == None : temp = "/usr/tmp" else : temp = os . environ . get ("TMP" ) Connection_File_Name = Conn_File_NameT + ".sde" = temp + os . sep + Conn_File_NameT + ".sde" # Check for the .sde file and delete it if present arcpy . env . overwriteOutput = True if os . path . exists (Connection_File_Name_full_path ): os . remove (Connection_File_Name_full_path ) try : print (" \n Creating Database Connection File... \n " ) # Process: Create Database Connection File... # Usage: out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true) #arcpy.CreateDatabaseConnection_management(temp , Connection_File_Name, database_type, instance, database, account_authentication, dbms_admin, dbms_admin_pwd, "TRUE") arcpy . CreateDatabaseConnection_management (out_folder_path = temp , out_name = Connection_File_Name , database_platform = database_type , instance = instance , database = database , account_authentication = account_authentication , username = dbms_admin , password = dbms_admin_pwd , save_user_pass = "TRUE" ) for i in range (arcpy . GetMessageCount ()): if "000565" in arcpy . GetMessage (i ): #Check if database connection was successful arcpy . AddReturnMessage (i ) arcpy . AddMessage (" \n +++++++++" ) arcpy . AddMessage ("Exiting!!" ) arcpy . AddMessage ("+++++++++ \n " ) sys . exit (3 ) else : arcpy . AddReturnMessage (i ) arcpy . AddMessage ("+++++++++ \n " ) print ("Creating database user... \n " ) arcpy . CreateDatabaseUser_management (input_workspace = Connection_File_Name_full_path , user_authentication_type = user_type , user_name = dbuser , user_password = dbuser_pwd , role = role , tablespace_name = tablespace ) for i in range (arcpy . GetMessageCount ()): arcpy . AddReturnMessage (i ) arcpy . AddMessage ("+++++++++ \n " ) except : for i in range (arcpy . GetMessageCount ()): arcpy . AddReturnMessage (i ) #Check if no value entered for option except SystemExit as e : if e . code == 2 : parser . usage = "" print (" \n " ) parser . print_help () parser . exit (2 )

   Вы можете запустить скрипт на компьютере с установленным ArcGIS for Desktop (Standard или Advanced), ArcGIS for Server (Standard или Advanced) или ArcGIS Engine с дополнительным модулем Geodatabase Update.

2. Сохраните файл с расширением .py .
3. Запустите скрипт, задав опции и указав сведения, соответствующие вашему экземпляру SQL Server и пользователю, которого вы хотите создать.

   В следующем примере имя созданного скрипта – create_database_user.py. В экземпляре SQL Server ssi5 создана учетная запись с аутентификацией SQL Server (gisd_owner), а также соответствующая схема и пользователь созданы а в базе данных gisdata. Пользователь не добавлен к роли.

   create_database_user.py --DBMS SQL_SERVER -i ssi5 -D gisdata --auth DATABASE_AUTH -U sa -P !nocopy! --utype DATABASE_USER -u gisd_owner -p T3mpPass

   Подсказка:

   Введите -h или --help в командной строке для вывода справки синтаксиса.

Теперь ваша база данных содержит пользователя, который может создавать таблицы.

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

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

Создание учетных записей и пользователей с помощью инструментов SQL Server

Если вы хотите создать пользователя, которые имеет права, отличающиеся от предоставляемых инструментом Создать пользователя базы данных (Create Database User) , или не имеет прав, предоставленным непосредственно ему, вы можете воспользоваться для этого инструментами SQL Server. Существует несколько вещей, о которых нужно помнить при создании собственных учетных записей и пользователей, которые будут использоваться с ArcGIS:

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

Инструкции по использованию инструментов SQL Server для создания учетных записей, пользователей и схем смотри в документации к Microsoft SQL Server.

Десятки лет существовала возможность определения пользовательских ролей в базах данных для облегчения управления процессом предоставления разрешений на доступ к базе данных, но на уровне экземпляра всегда существовало девять фиксированных ролей (или восемь, если вы используете версию, предшествующую SQL Server 2000, - роль bulkadmin появилась в SQL Server 2005). Теперь, с появлением SQL Server 2012 наконец появилась возможность создания нестандартных, или пользовательских, ролей сервера.

Многие годы недоступность такой функциональности была источником головной боли администраторов SQL Server. Что вы делали, если нужно было предоставить права нескольким пользователям или группам на уровне экземпляра и обеспечить синхронизацию этих прав? Допустим, вам нужно предоставить право View System State большому числу пользователей, чтобы они могли видеть информацию о блокировке в центрах разработки. Приходилось предоставлять права по отдельности каждому пользователю или доменной группе.

Если все имена входа являлись доменными, был обходной путь: можно было создать доменную группу, в которую включить всех пользователей, кому были нужны такие права (при этом надо было создавать отдельные группы для каждого сервера, если нужно было предоставить права на нескольких серверах). Можно было добавить пользователей в эту группу, создать имя входа, связанное с этой доменной группой, после чего предоставить группе право View Server State на сервере. Но это нужно было делать очень внимательно. Иначе можно было предоставить право входа пользователям, у которых такого права раньше не было. Существовала даже возможность предоставления пользователям прав, которых им предоставлять нельзя.

Создание роли средствами T/SQL

Существует много способов создания пользовательской роли на сервере, в том числе средствами T/SQL, SQL Server Management Studio и Windows PowerShell. Если нужно создать пользовательскую серверную роль с применением T/SQL, нужно воспользоваться тремя командами: инструкция Create Server Role создаст пользовательскую серверную роль, инструкция Alter Server Role добавит пользователя в роль и, наконец, инструкция Grant предоставит нужные права роли.

Использование этих трех инструкций показано в следующем коде, который создает пользовательскую серверную роль по имени ViewServerState. В роль добавляется пользователь SomeFakeLogin, и роли предоставляется право View Server State. Чтобы предоставить это право другим пользователям, достаточно просто добавить их в готовую роль инструкцией Alter Server Role.

USE GO CREATE SERVER ROLE AUTHORIZATION GO ALTER SERVER ROLE ADD MEMBER GO GRANT VIEW SERVER STATE TO GO

Для удаления пользователя из роли используется инструкция Alter Server Role. При этом используется на параметр Add Member, а Drop Member:

ALTER SERVER ROLE DROP MEMBER GO

Когда нужно развернуть одну или более пользовательских серверных ролей на многих экземплярах SQL Server, есть несколько возможных вариантов. Наверняка вам не понравится подключаться к каждому серверу по отдельности и создавать на них пользовательские серверные роли. Один из вариантов - использование консоли SQL Server Management Studio. В ней можно выполнять сценарии на T/SQL сразу на нескольких экземплярах.

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

Консоль SQL Server Management Studio

Задачу можно легко выполнить средствами графического интерфейса SQL Server Management Studio. Чтобы создать пользовательскую серверную роль, подключитесь к экземпляру средствами Object Explorer. В обозревателе объектов перейдите к узлу <имя_экземпляра >/Security/Server Roles. Щелкните правой кнопкой Server Roles и выберите New Server Role. В открывшемся окне New Server Role в поле Server role name укажите имя роли, в поле Owner укажите владельца роли, после чего укажите объекты и разрешения, которые должны предоставляться членам роли (рис. 1 ).

Рис. 1. Укажите права, которые должна предоставлять роль

По завершении работы на странице General перейдите на страницу Members (рис. 2 ) и укажите имена входа SQL Server, которые должны входить в эту пользовательскую серверную роль.

Рис. 2. Определение членов роли

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

Если создать пользовательскую роль и сделать ее членом фиксированной серверной роли serveradmin (рис. 3 ), все члены вновь созданной пользовательской роли будут также обладать правами роли serveradmin. Как и при вложении доменных групп или ролей баз данных, при вложении ролей надо соблюдать большую осторожность, чтобы не предоставить пользователям прав, которых у них быть не должно.

Рис. 3. Пользовательские роли можно включать в другие серверные роли

Чтобы организовать вложение ролей средствами T/SQL, снова потребуется инструкция Alter Server Role с параметром Add Member. Например, чтобы сделать пользовательскую серверную роль ViewServerState членом фиксированной серверной роли setupadmin, нужно изменить фиксированную серверную роль setupadmin.

ALTER SERVER ROLE ADD MEMBER GO

В пользовательских серверных ролях может быть много пользователей. Есть десятки прав уровня экземпляра, которые можно предоставить пользовательской серверной роли для упрощения управления этими правами. Можно также создать роль для младшего администратора БД, которой предоставить часть, но не все права администратора. Можно создать группу AlwaysOnAdmin, которая предоставляет право на выполнение перехода базы данных AlwaysOn (что должно выполняться в рамках SQL Server) не предоставляя всей полноты административных прав.

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

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

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

Запустите Microsoft SQL Server Management Studio и подключитесь к серверу.

В списке «Обозреватель объектов» (если не отображается, нажмите клавишу F8 ) откройте папку «Безопасность» . Кликните правой кнопкой мышки по папке «Имена входа» , в появившемся меню выберите «Создать имя входа...» .

В появившемся окне, в поле «Имя входа» укажите логин. Например: «yolper» (без кавычек ). Затем выберите тип проверки подлинности. По умолчанию стоит «Проверка подлинности Windows» , это означает, что при подключении к SQL Server будет использоваться учетная запись Windows . Как правило, на удаленных серверах подобный метод не используется. Выберите «Проверка подлинности SQL Server» . В разделе «Пароль» , укажите любой пароль, главное запишите его, чтобы в последующем передать пользователю. Если пароль будет утерян, его в любое время можно будет поменять.

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

В этом же окне, в самом низу, выберите базу данных по умолчанию . Именно эта база будет открываться после подключения пользователя к серверу. Нажмите «Ok» , чтобы создать учетную запись.

Учётная запись создана, но это еще не всё. Теперь нужно связать учётную запись с таблицами. На самом деле это можно было сделать при создании учётной записи, однако на момент написания этого ёлпера, SQL Server 2008 почему-то отказывался запоминать связь создаваемой учётной записи с таблицами (в 2005 такого глюка не наблюдалось).

Чтобы связать учётную запись с конкретными таблицами и определить права пользователя, разверните папку «Имена входа» в обозревателе объектов. Найдите созданную учётную запись в списке и кликните по ней правой кнопкой мышки, выберите меню «Свойства» .

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

После этого нажмите на кнопку «Ok» . Теперь можно использовать созданную учётную запись для доступа к SQL Server .

07.12.2016 Тим Форд

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

Безопасность 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).

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

Это вкладка 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

Листинг 1. Код, соответствующий настройкам, сделанным в графическом интерфейсе

USE GO CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE= GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCHEMA= GO CREATE SCHEMA AUTHORIZATION GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCHEMA= GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCHEMA= GO use GO GRANT VIEW ANY DEFINITION TO GO

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

SELECT name , sid , principal_id , type_desc , default_database_name FROM sys.server_principals WHERE name = "professor"; SELECT name , sid , principal_id , type_desc , default_schema_name FROM lifeboat.sys.database_principals WHERE name = "professor";

Листинг 3. Пример запроса для выбора столбцов и строк таблицы

ВАРИАНТ 1: полное доменное имя -========================================================================= SELECT * FROM SQL_Cruise.dbo.tblFoo; -========================================================================= - ВАРИАНТ 2: имя, определенное через схему -========================================================================= SELECT * FROM dbo.tblFoo; -========================================================================= - ВАРИАНТ 3: только имя объекта -========================================================================= SELECT * FROM tblFoo; Листинг 4. Создание таблиц с несколькими схемами USE GO CREATE SCHEMA AUTHORIZATION GO CREATE TABLE dbo.tblFoo (id INT); CREATE TABLE .tblFoo (id INT); SELECT * FROM tblFoo;

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

5 ответов

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

Создание входа легко и необходимо (очевидно) сделать до создания учетной записи пользователя для входа в конкретную базу данных:

CREATE LOGIN NewAdminName WITH PASSWORD = "ABCD" GO

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

Use YourDatabase; GO IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N"NewAdminName") BEGIN CREATE USER FOR LOGIN EXEC sp_addrolemember N"db_owner", N"NewAdminName" END; GO

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

Однако, если вы собираетесь получать доступ к SQL Server из приложения, вам нужно настроить сервер для "Смешанного режима" (как для Windows, так и для SQL-входа) и создать Login, как показано выше. Затем вы получите "GRANT" priviliges для этого SQL-входа на основе того, что необходимо для вашего приложения. См. для получения дополнительной информации.

ОБНОВЛЕНИЕ: Аарон указывает на использование sp_addsrvrolemember для назначения подготовленной роли вашей учетной записи. Это хорошая идея - быстрее и проще, чем предоставление привилегий вручную. Если вы его найдете, вы увидите множество ссылок. Однако вы все равно должны понимать различие между логином и пользователем.

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

USE ; GO CREATE LOGIN MyNewAdminUser WITH PASSWORD = N"abcd", CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; GO EXEC sp_addsrvrolemember @loginame = N"MyNewAdminUser", @rolename = N"sysadmin";

Вам может потребоваться оставить параметры CHECK_ в зависимости от того, какую версию SQL Server Express вы используете (почти всегда полезно включать эту информацию в ваш вопрос).

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

Declare @userName as varchar(50); Declare @defaultDataBaseName as varchar(50); Declare @LoginCreationScript as varchar(max); Declare @UserCreationScript as varchar(max); Declare @TempUserCreationScript as varchar(max); set @defaultDataBaseName = "data1"; set @userName = "domain\userName"; set @LoginCreationScript ="CREATE LOGIN [{userName}] FROM WINDOWS WITH DEFAULT_DATABASE ={dataBaseName}" set @UserCreationScript =" USE {dataBaseName} CREATE User [{userName}] for LOGIN [{userName}]; EXEC sp_addrolemember ""db_datareader"", ""{userName}""; EXEC sp_addrolemember ""db_datawriter"", ""{userName}""; Grant Execute on Schema:: dbo TO [{userName}];" /*Login creation*/ set @LoginCreationScript=Replace(Replace(@LoginCreationScript, "{userName}", @userName), "{dataBaseName}", @defaultDataBaseName) set @UserCreationScript =Replace(@UserCreationScript, "{userName}", @userName) Execute(@LoginCreationScript) /*User creation and role assignment*/ set @TempUserCreationScript =Replace(@UserCreationScript, "{dataBaseName}", @defaultDataBaseName) Execute(@TempUserCreationScript) set @TempUserCreationScript =Replace(@UserCreationScript, "{dataBaseName}", "db2") Execute(@TempUserCreationScript) set @TempUserCreationScript =Replace(@UserCreationScript, "{dataBaseName}", "db3") Execute(@TempUserCreationScript)

Вы можете использовать:

CREATE LOGIN WITH PASSWORD = "" ; GO

Вы также можете использовать:

GRANT permission [ ,...n ] ON SCHEMA:: schema_name

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

На прошлой неделе я установил версию Microsoft SQL Server 2014 Developer Edition на свою dev-страницу и сразу же столкнулся с проблемой, которую я никогда раньше не видел.

Я установил различные версии SQL Server бесчисленное количество раз, и это, как правило, безболезненная процедура. Установите сервер, запустите консоль управления, это так просто. Однако после завершения этой установки, когда я попытался войти на сервер с помощью SSMS, у меня появилась ошибка, подобная приведенной ниже:

Ошибка входа в SQL Server 18456 "Ошибка входа для пользователя... (Microsoft SQL Server, ошибка: 18456)" Я использовал эту ошибку, если я ввел неверный пароль при входе в систему - но это только если Im использует смешанный режим (Windows и SQL Authentication). В этом случае сервер был настроен только с помощью проверки подлинности Windows, а учетная запись пользователя была моей. Im все еще не уверен почему он не добавлял моего потребителя к роли SYSADMIN во время установки; возможно, я пропустил шаг и забыл добавить его. Во всяком случае, не всякая надежда была потеряна.

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

Остановить службу MSSQL. Откройте командную строку, используя команду "Запуск от имени администратора". Перейдите в папку, в которой хранится EXE файл SQL Server; по умолчанию для SQL Server 2014 используется "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn". Выполните следующую команду: "sqlservr.exe -m". Это запустит SQL Server в однопользовательском режиме. Выйдя из этой командной строки, откройте еще один, повторите шаги 2 и 3. Во втором окне командной строки запустите "SQLCMD -S Server_Name\Instance_Name" В этом окне запустите следующие строки, нажав Enter после каждого из них: 1

СОЗДАТЬ ВХОД [имя_домена\имя_пользователя] ИЗ WINDOWS 2 ИДТИ 3 SP_ADDSRVROLEMEMBER "LOGIN_NAME", "SYSADMIN" 4 ИДТИ Используйте CTRL + C для завершения обоих процессов в окнах командной строки; вам будет предложено нажать Y, чтобы завершить процесс SQL Server.

Перезапустите службу MSSQL. Это оно! Теперь вы можете войти в систему, используя свой сетевой логин.