Блокировка доступа к данным в SQL Server
Иногда возникает необходимость ограничения доступа ряду сотрудников к определенной информации, хранящейся в базе банных. Я предлагаю познакомиться с одной из функций SQL Server, которая позволяет контролировать доступ к уровню строк в таблице базы данных в зависимости от пользователя, выполняющего запрос.
Безопасность на уровне строк (Row-Level Security, RLS) ограничивает пользователей таким образом, чтобы они могли работать исключительно с теми данными, к которым у них имеется доступ. Кроме того, данная функция не дает возможности пользователям вставлять, обновлять или удалять данные, доступ к которым запрещен.
Давайте рассмотрим, как можно использовать функцию Row-Level Security на примере условной таблицы, назовем ее ObjectCheck, в которой отражена информация об объектах аудита, сроках начала и завершения проверок и т.д.
Перед нами поставлена задача: каждый пользователь базы данных должен получить доступ к данным, относящимся только к нему, без доступа к проверкам, назначенным другим сотрудникам.
Для выполнения примера создадим трех пользователей базы данных:
create user Ivanovii without login; create user PetrovPP without login; create user SidorovSS without login;
предоставим доступ select к таблице ObjectCheck для этих пользователей:
grant select on ObjectCheck to Ivanovii; grant select on ObjectCheck to PetrovPP; grant select on ObjectCheck to SidorovSS;
Кроме того, желательно создать отдельную схему для объектов базы данных Row-Level Security:
create schema rs; GO
Ограничение доступа к данным с использованием Row-Level Security достигается путем определения предиката безопасности (Security predicate) как функции, которая ограничивает строки на основе логики фильтрации, которая вызывается и применяется политикой безопасности (Security Policy), созданной с помощью T-SQL оператора create security policy, и работает как контейнер предикатов.
Давайте выполним настройку функции безопасности на уровне строк для таблицы ObjectCheck.
Создаем функцию, зависящую от пользователей, вошедших в систему, чтобы была возможность фильтровать пользователей и проверять их доступы к данным:
create function rs.fn_secureData(@Username as sysname) returns table with schemabinding as return select 1 as ‘secureObjectCheck’ where @Username = User_Name();
Создадим политику безопасности в таблице ObjectCheck, используя ранее созданную функцию предиката:
create security policy Object_Check add filter predicate rs.fn_secureData(User) on dbo.ObjectCheck with (state = on);
Теперь защита на уровне строк настроена и готова к фильтрации доступа к данным в таблице ObjectCheck.
Так, если выполнить запрос, указав пользователя Ivanovii:
execute as user = ‘Ivanovii’; select * from ObjectCheck; revert;
то запрос вернет только две записи, связанные с пользователем Ivanovii:
Аналогичный результат будет возвращен при запросе данных из таблицы ObjectCheck пользователями PetrovPP и SidorovSS:
execute as user = ‘PetrovPP’; select * from ObjectCheck; revert;
execute as user = ‘SidorovSS’; select * from ObjectCheck; revert;
Из приведенных примеров видно, что функция безопасности на уровне строк может использоваться для фильтрации данных, которые может видеть каждый пользователь, в зависимости от критериев фильтрации, определенных в функции предиката.
Если необходимо прекратить использование функции безопасности rs.fn_SecureData, то можно отключить политику безопасности Object_Check с помощью инструкции ALTER security policy:
alter security policy Object_Check with (state = off)
Затем выполнить запрос DROP для удаления функции и политики безопасности:
drop security policy Object_Check drop function rs.fn_secureData
Теперь безопасность на уровне строк полностью удалена из таблицы Object_Check.
Можно легко добавлять предикаты и критерии фильтрации, подходящие для определенных ситуаций, однако, слишком сложные предикаты ухудшают производительность базы данных, поскольку предикат будет проверяться каждый раз при выполнении доступа к данным. Более подробно ознакомиться с Row-Level Security можно на сайте Microsoft.
SQL пользователь с ограниченным доступом
После восстановления базы MS SQL можем получить такую ошибку Для решения этой проблемы мы должны выполнить команду в консоли
USE test ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE test SET MULTI_USER GO 
Нужна помощь?
Если у Вас возникли трудности и Вы не можете справиться самостоятельно, наши специалисты готовы оказать удаленную помощь. HELP
Эту и другие технические статьи написали наши программисты 1С и получили за них премии. Если вы тоже работаете с 1С и любите делиться опытом, приходите разработчиком в МИТ
Наши сервисы по этой теме:
Линия консультаций 1С
Консультации по телефону и электронной почте дают специалисты службы технической поддержки по «1С:Предприятию» и обслуживающего партнера
УПРАВЛЕНИЕ ДОСТУПОМ В СУБД SQL SERVER
Приобретение практических навыков настройки разрешений на доступ к объектам баз данных в среде СУБД Microsoft SQL Server 2008/2012.
Используемые программные средства.
Компьютер или виртуальная машина с установленной ОС семейства Windows и СУБД SQL Server. При работе в сети возможно совместное использование одного экземпляра SQL Server. В этом случае на остальных компьютерах должны быть установлены только клиентские компоненты и SQL Server Management Studio. Для выпол- 202
нения работы необходимы права, позволяющие создавать новую базу данных на экземпляре SQL Server.
Теоретические сведения.
Управление разрешениями на объекты реляционной базы данных несколько отличается от аналогичных операций в отношении объектов файловой системы. В данной лабораторной работе на примере СУБД SQL Server эти отличия будут показаны.
При работе с разрешениями в SQL Server используется понятие участников (principals), которые могут запрашивать ресурсы SQL Server, и которым могут предоставляться разрешения на использование таких ресурсов. Выделяются следующие группы участников:
- — участники уровня Windows, к которым относятся локальные и доменные учетные записи пользователей и группы;
- — участники уровня SQL Server, к которым относятся учетные записи SQL Server и роли уровня сервера;
- — участники уровня базы данных — пользователи базы данных и роли уровня базы данных и приложения.
Необходимо отметить, что SQL Server разделяет понятие учетной записи (login) и пользователя (user). Сервер может быть сконфигурирован на использование только аутентификации Windows (англ. Windows Authentication Mode, используется по умолчанию) или на использование смешанного режима аутентификации (англ. SQL Server and Windows Authentication mode), см. рис. 5.3. В первом случае login можно создать только для пользователя или группы Windows. Во втором случае также возможно использовать собственные учетные записи SQL Server — логин и пароль хранятся самой СУБД, и ее же средствами выполняется проверка подлинности. При использовании смешанной аутентификации егановится доступной административная учетная запись sa, которую рекомендуется переименовать и назначить ей надежный пароль. Учетная запись авторизуется на выполнение одной из серверных ролей (табл. 5.1).
Рис. 5.3. Окно редактирования настроек безопасности экземпляра SQL Server
Роли уровня сервера
Описание возможностей
Разрешено выполнять любые действия на сервере.
Разрешено создавать базы данных.
Могут выполнять инс трукцию BULK INSERT.
Позволяет управлять файлами на диске.
Позволяет управлять подключениями, запускать и приостанавливать экземпляр SQL Server.
Создание и управление учетными записями, право «сбросить» пароль учетной записи. Управление разрешениями на уровне сервера и на уровне базы данных (при наличии доступа к базе данных).
Включает возможности ролей diskadmin и processadmin, позволяет изменять параметры конфигурации на уровне сервера и выключать сервер.
Добавление и удаление связанных серверов.
Каждая учетная запись принадлежит этой роли, членство в роли public изменить нельзя.
На уровне базы данных учетной записи сопоставляется пользователь (user). Для одной и той же учетной записи в различных базах данных сервера могут создаваться пользователи с разными именами.
Пользователи получают разрешения на работу с объектами базы данных или напрямую, или путем авторизации пользователя на выполнение одной из ролей уровня базы данных (рис. 5.4). Последний способ является более предпочтительным и позволяет организовать управление доступом в соответствии с ролевой моделью, описанной в первой главе пособия.
Рис. 5.4. Учетные записи и соответствующие им пользователи и роли в базах данных
Список ролей уровня сервера предопределен, и новые создавать нельзя (табл. 5.1). Также есть предопределенный набор ролей уровня базы данных (табл. 5.2), но в этом случае имеется возможность создавать новые роли.
Роли уровня базы данных
Описание возможностей
Владелец базы данных, можно выполнять все действия по настройке и обслуживанию базы данных, а также удалять базу данных.
Управление составом ролей (кроме роли db_owner) и связанными с ними разрешениями.
Добавление и удаление пользователей базы данных.
Возможность создавать резервные копии базы данных.
Выполнение DDL-инструкций (создание, изменение, удаление объектов базы данных, таких как таблицы, представления и т. д.).
Чтение данных (SELECT) из всех пользовательских таблиц, представлений и функций.
Запрет на чтение данных (SELECT) из всех пользовательских таблиц, представлений и функций.
Право добавлять, удалять или изменять данные во всех пользовательских таблицах.
Запрет добавлять, изменять или удалять данные в пользовательских таблицах.
Роль по умолчанию, имеющаяся в каждой базе данных. Каждый пользователь БД авторизован на эту роль.
Болес подробную информацию об организации системы безопасности СУБД SQL Server можно получить из справочной системы TechNet: http://technet.microsoft.com/ru-ru/library/bb510589.aspx.
План работы.
- 1. Используя указанную преподавателем доменную или локальную учетную запись Windows, с помощью SQL Server Management Studio подключитесь к используемому экземпляру SQL Server. Проверьте установленный на сервере режим аутентификации.
- 2. В окне Object Explorer (по умолчанию — левая часть окна Management Studio) откройте список учетных записей (logins). На выполнение каких серверных ролей авторизована используемая вами учетная запись?
- 3. В каких базах данных сервера вашей учетной записи сопоставлены пользователи? На выполнение каких ролей они авторизованы?
- 4. В среде Management Studio создайте новую базу данных. Откройте список пользователей и ролей. Убедитесь, что учетная запись, под которой вы работаете, сопоставлена пользователю dbo, авторизованному на роль db owner.
- 5. Используя приведенный ниже скрипт, создайте в базе данных таблицы. Перед гем как запустить скрипт, уберите символы комментария («—») из первой строки и после ключевого слова use укажите имя вашей базы данных.
- —use MyTestl GO
CREATE TABLE dbo.Book (
book_id int IDENTITY (1, 1) primary key,
Title varchar(50) NOT NULL, —название книги Author varchar(50), — автор Publisher varchar(50), — издательство [Year] smallint) — год издания GO
CREATE TABLE dbo.Status (
Status_id int IDENTITY (1, 1) primary key, Status_name varchar(50) NOT NULL ) — статус: выдана, в библиотеке и т.д.
CREATE SCHEMA libr GO
CREATE TABLE libr.Book_in_lib (
lib_id int primary key , —номер экземпляра book_id int references dbo.Book , status_id int references dbo.[Status])
Обратите внимание, что приведенный скрипт создает не только три таблицы, но и схему libr. В SQL Server схема является контейнером логического уровня, к которому относятся объекты базы данных. Во вновь созданной БД уже будет несколько схем: dbo, sys, information_schema и т. д. Схема dbo — это схема по умолчанию для новых пользовательских объектов, sys и infor- mation schema используются системными объектами. Оператором CREATE SCHEMA в БД можно создавать новые схемы.
Защищаемым объектом, на действия с которым пользователю предоставляются разрешения, может быть база данных, схема или объект базы данных. Определенное для схемы разрешение неявным образом распространяется на все объекты схемы, разрешение для базы данных — на все схемы и объекты этих схем.
6. Для указанной преподавателем учетной записи SQL Server (при самостоятельном выполнении работы создайте учётную запись Windows и учётную запись SQL Server для нее) создайте пользователя в вашей базе данных, в качестве схемы по умолчанию выберите dbo. В Management Studio это можно сделать из графического интерфейса (контекстное меню узла Security для выбранной БД, там New. -> User) или выполнив оператор CREATE USER. Например (если схема не указана, подразумевается dbo):
CREATE USER ns FOR LOGIN [HOME s]
Добавьте этого пользователя в роль db_datareader. Это можно сделать или через графический интерфейс или с помощью системной хранимой процедуры sp_addrolemember, первым параметром которой будет имя роли, а вторым — имя пользователя.
EXEC sp_addrolemember ‘db_datareader’, ‘ns 1 Введите в таблицы тестовый набор данных.
Подключитесь к серверу с учетной записью другого пользователя. Убедитесь, что можно получить доступ к базе данных и читать записи из всех таблиц, а добавлять или изменять данные нельзя.
7. Создадим новую роль уровня базы данных и добавим ей разрешение на удаление (DELETE), изменение (UPDATE) и добавление данных (INSERT) в объектах схемы libr. Добавим нашего пользователя к этой роли. Указанные действия надо выполнять с правами администратора или владельца базы данных. Как и в предыдущем случае, все это можно сделать в графическом интерфейсе или запуском скрипта.
CREATE ROLE libr_writer GO
GRANT INSERT, UPDATE, DELETE ON SCHEMA :: libr TO
EXEC sp_addrolemember ‘libr_writer’, ‘ns’
Используемый в приведенном скрипте оператор GRANT позволяет предоставить разрешения. Оператор DENY позволяет запретить выполнение каких-то действий, а оператор REVOKE отменяет установленные оператором GRANT или DENY настройки разрешений. Таким образом, у разрешения может быть три состояния: «разрешено», «запрещено», «не задано». Действие можно выполнить, только если оно разрешено непосредственно пользователю или одной из ролей, на которые он авторизован. Запрещение более приоритетно, чем разрешение: если пользователь авторизован на выполнение двух ролей, одной из них действие разрешено, а другой — запрещено, то пользователь это действие выполнить нс сможет. В SQL Server Management Studio можно просмотреть эффективные разрешения для пользователя (рис. 5.5).
Конкретный набор возможных разрешений зависит от тина объекта, с полным списком разрешений рекомендуется ознакомиться по справке или приведенной ниже статье TechNet: http://technet.microsoft.coin/ru-ru/library/ms 191291 .aspx.
Рис. 5.5. Эффективные разрешения пользователя
Выполните описанные действия. Убедитесь, что пользователь с ограниченными правами может изменять данные в таблице Book_in_lib, относящейся к схеме libr.
8. Иногда нужно предоставить пользователю права на изменение отдельных столбцов. Как отмечается в документации SQL Server, на столбец могут быть предоставлены только разрешения SELECT, REFERENCES и UPDATE. Например:
GRANT UPDATE ON dbo.Book(Title) TO libr_writer
Выполните аналогичные действия в своей базе данных, проверьте, что пользователь получил указанные разрешения.
9. Самостоятельно по справке ознакомьтесь с форматом оператора CREATE VIEW, особое внимание обратите на задаваемые дополнительные параметры. Создайте представление, выбирающее из таблицы Book книги, изданные нс ранее 2000 года. Предоставьте пользователю с ограниченными правами возможность изменять и добавлять подобные книги. Возможности изменять прочие записи таблицы и добавлять книги, изданные до 2000 года, он иметь не должен.
Права и роли пользователя базы данных
В Plesk предусмотрены роли для пользователей баз данных MySQL и Microsoft SQL Server. Роли работают как шаблоны, помогающие предоставлять права пользователям баз данных. Поддерживаются следующие роли пользователей баз данных: Чтение и запись (используется по умолчанию), Только чтение и Только запись. Для каждой роли существует определенный набор прав, который при ее присвоении предоставляется учетной записи пользователя базы данных. Вы можете изменять наборы прав для каждой из ролей.
Кроме того, MySQL поддерживает Персональную роль с пользовательским набором прав. Пользователи Microsoft SQL Server не могут изменять наборы прав, предоставленных при присвоении роли.
Вы можете не предоставлять и запретить пользователям Plesk предоставлять какое-либо право.
Более подробную инормацию о том, как выбирать роли для пользователей баз данных на странице Сайты и домены > Базы данных > Управление пользователями, смотрите в разделе Управление пользователями базы данных . Обратите внимание, что пользователям баз данных в подписке администратора предоставляются глобальные права в дополнение к правам на таблицы баз данных.
Роли пользователей баз данных MySQL
В Plesk с MySQL пользователи могут выбирать роли для пользователей баз данных, а также добавлять и удалять отдельные права.
Наборы прав MySQL для каждой роли по умолчанию перечислены ниже.
| Право | Чтение и запись | Только чтение | Только запись |
|---|---|---|---|
| Select | ➕ | ➕ | ➖ |
| Insert | ➕ | ➖ | ➕ |
| Update | ➕ | ➖ | ➕ |
| Delete | ➕ | ➖ | ➕ |
| Create | ➕ | ➖ | ➕ |
| Drop | ➕ | ➖ | ➕ |
| Alter | ➕ | ➖ | ➕ |
| Index | ➕ | ➖ | ➕ |
| Create Temporary Tables | ➕ | ➖ | ➕ |
| Lock Tables | ➕ | ➖ | ➕ |
| Create View | ➕ | ➖ | ➕ |
| Show View | ➕ | ➕ | ➖ |
Чтобы изменить наборы прав по умолчанию, внесите изменения в файл panel.ini , перечислив все права для каждой из ролей, которую хотите изменить.
Помните, что клиенты Plesk при этом все же смогут выбирать больше прав, чем вы указали в файле panel.ini , если вы не запретили их использование (смотрите далее на этой странице).
[databaseManagement] features.roles.mysql.readWrite = Select,Update,Insert features.roles.mysql.readOnly = Select features.roles.mysql.writeOnly = Update
Примечание: Изменение набора прав для роли (например, роли Чтение и запись) не влияет на права уже существующих пользователей базы данных MySQL с этой ролью. Роль таких пользователей базы данных автоматически изменится на Персональную.
Как отозвать у всех пользователей какое-либо право
Вам может потребоваться отозвать какое-либо право, например права Delete, у всех пользователей баз данных. Чтобы отозвать право, пропишите список прав MySQL в файле panel.ini , не включая в него право, которое вы хотите отозвать.
[databaseManagement] features.privileges.mysql.dataAccess = Select, Insert, Update, Delete features.privileges.mysql.structureAccess = Create, Drop, Alter, Index, Create Temporary Tables, Lock Tables, Create View, Show View
Указанные в списке права будут отображаться в интерфейсе Plesk (Сайты и домены > Базы данных > Управление пользователями). Пользователи смогут предоставлять или отзывать только перечисленные права. Если соответствующего права нет в списке, с точки зрения Plesk это действие запрещено пользователям баз данных.
Примечание: Право отзывается только после того, как пользователь Plesk сохранит какие-либо изменения в настройках пользователя базы данных: (Сайты и домены > Базы данных > Управление пользователями > имя пользователя > ОК).
Роли пользователей баз данных Microsoft SQL Server
В Plesk с сервером баз данных Microsoft SQL Server пользователи могут выбирать роли (Чтение и запись, Только чтение, Только запись), но не могут добавлять или удалять отдельные права (роли на уровне базы данных SQL Server). Права не отображаются в интерфейсе Plesk.
Ниже перечислены права, предоставленные по умолчанию каждой роли в Microsoft SQL Server:
| Право | Чтение и запись | Только чтение | Только запись |
|---|---|---|---|
| db_backupoperator | ➕ | ➕ | ➕ |
| db_datareader | ➕ | ➕ | ➖ |
| db_datawriter | ➕ | ➖ | ➕ |
| db_ddladmin | ➕ | ➖ | ➕ |
Чтобы изменить набор прав по умолчанию для какой-либо роли, внесите изменения в файл panel.ini , перечислив необходимые права.
[databaseManagement] features.roles.mssql.readWrite = db_datareader,db_backupoperator,db_ddladmin features.roles.mssql.readOnly = db_datareader,db_backupoperator features.roles.mssql.writeOnly = db_datawriter
Примечание: При изменении набора прав для роли изменяются права существующих пользователей базы данных с этой ролью. У всех существующих и вновь создаваемых пользователей с этой ролью будет набор прав, который вы указали в panel.ini .
Восстановление прав по умолчанию для пользователей Microsoft SQL Server
Для каждой роли пользователей баз данных существует свой набор прав по умолчанию (роли пользователей на уровне базы данных SQL Server). Чтобы восстановить для существующих пользователей баз данных набор прав по умолчанию (в соответствии с их ролью), используйте следующую команду:
%plesk_dir%bin\repair.exe —update-mssql-users-permissions [-database-server ] [-database-name ]
Примечание: Если вы изменили набор прав для роли (например, роли Чтение и запись) в файле panel.ini , в результате работы команды —update-mssql-users-permissions будет восстановлен набор указанных в файле прав, а не набор по умолчанию (например, набор прав по умолчанию для роли Чтение и запись: db_datareader,db_datawriter,db_backupoperator,db_ddladmin ).