Работа с разрешениями с помощью инструкций языка transact sql

Приветствую всех посетителей сайта Info-Comp.ru! Сегодня мы с Вами поближе познакомимся с операторами определения доступа к данным DCL в языке T-SQL, узнаем для чего и как их можно использовать, рассмотрим несколько примеров.

DCL – Data Control Language. Операторы определения доступа к данным языка T-SQL

Содержание

  1. Что такое DCL
  2. Операторы определения доступа к данным в T-SQL
  3. Исходные данные для примеров
  4. Оператор GRANT
  5. Оператор REVOKE
  6. Оператор DENY
  7. Видео-инструкция

Язык SQL, с точки зрения реализации, представляет собой набор операторов, которые делятся на определенные группы, и у каждой группы есть свое назначение. Так вот DCL – это одна из таких групп. Подробней о том, какие еще группы есть, можете почитать в отдельной статье – Что такое DDL, DML, DCL и TCL в языке SQL.

Data Control Language (DCL) – это группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.

К операторам DCL относятся:

  • GRANT
  • REVOKE
  • DENY

Давайте подробнее рассмотрим каждый из этих операторов.

Операторы определения доступа к данным в T-SQL

Исходные данные для примеров

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

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

В базе данных у нас есть таблица Goods, она содержит данные о товарах, и процедура TestProcedure. С этими объектами мы и будем работать.

Как Вы понимаете, все данные у нас тестовые, созданные только для демонстрации примеров.

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

   
   USE TestDB;

   GO

   --Создание имени входа
   CREATE LOGIN TestLogin
     WITH PASSWORD='Pa$$w0rd',
     DEFAULT_DATABASE=TestDB;

   GO

   --Создание пользователя базы данных и сопоставление с именем входа
   CREATE USER TestUser FOR LOGIN TestLogin;

   GO

   --Создание таблицы Goods
   CREATE TABLE Goods (
     ProductId       INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
     Category        INT NOT NULL,
     ProductName     VARCHAR(100) NOT NULL,
     Price           MONEY NULL,
   );

   GO

   --Добавление строк в таблицу Goods
   INSERT INTO Goods(Category, ProductName, Price)
     VALUES (1, 'Системный блок', 300),
            (1, 'Монитор', 200),
            (2, 'Смартфон', 100);

   GO

   --Создание процедуры
   CREATE PROCEDURE TestProcedure (@ProductId INT)
   AS
   BEGIN
     SELECT * FROM Goods
     WHERE ProductId = @ProductId;
   END

   GO

   SELECT * FROM Goods;

Скриншот 1

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

Оператор GRANT

GRANT – предоставляет пользователю или группе разрешения на определённые операции с объектом.

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

Операции над объектами могут быть разными, например, у таблицы это извлечение данных (SELECT), добавление данных (INSERT), изменение данных (UPDATE), удаление данных (DELETE), а также изменение самой таблицы (ALTER).

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

Скриншот 2

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

Для этого мы можем выполнить вот такую инструкцию.

   
   GRANT SELECT, INSERT, UPDATE, DELETE ON Goods TO TestUser;

Скриншот 3

Где после команды GRANT мы перечисляем операции, которые хотим разрешить выполнять пользователю. Затем пишем ключевое слово ON и указываем объект, в данном случае таблицу, на который представляем эти права. Далее пишем ключевое слово TO и указываем группу или, как в нашем случае, конкретного пользователя, которому мы представляем права.

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

Курс по SQL для начинающих

Скриншот 4

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

Однако если он сейчас попытается это сделать, у него выйдет ошибка.

Скриншот 5

Поэтому давайте дадим ему разрешение на запуск этой процедуры. Это делается следующим образом.

   
   GRANT EXECUTE ON TestProcedure TO TestUser;

Скриншот 6

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

Оператор REVOKE

REVOKE – отзывает выданные разрешения.

Иным словами, с помощью этого оператора мы можем отменить выданное или запрещенное ранее разрешение.

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

Мы можем это сделать с помощью следующих инструкций.

   
   --Отменяем разрешение на удаление данных
   REVOKE DELETE ON Goods TO TestUser;

   GO

   --Отменяем разрешение на запуск процедуры
   REVOKE EXECUTE ON TestProcedure TO TestUser;

Скриншот 7

После оператора REVOKE мы пишем разрешения, которые хотим отменить, с помощью ключевого слова ON указываем объект, а с помощью ключевого слова TO указываем субъект, т.е. пользователя, у которого мы хотим отобрать разрешения.

Оператор DENY

DENY– задаёт запрет, имеющий приоритет над разрешением.

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

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

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

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

   
   DENY EXECUTE ON TestProcedure TO TestUser;

Скриншот 8

В этом случае мы делаем практически все то же самое, только в начале пишем оператор DENY.

Вот мы с Вами и рассмотрели операторы определения доступа к данным языка T-SQL, мы научились предоставлять и отменять разрешения, а также явно запрещать выполнение определённых операций.

Заметка! Еще больше статей, посвященных языку T-SQL, Вы можете найти в сборнике статей для изучения Microsoft SQL Server.

Видео-инструкция

И на сегодня это все, надеюсь, материал был Вам полезен, удачи Вам!

Источник

Права
доступа (permissions)
— это права, дающие воз­можность
доступа к объекту (например, таблице)
базы данных. Право доступа предоставляется
пользователю или группе для выполнения
таких функций, как выборка дан­ных,
добавление но­вых строк или обновление
данных.

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

  • Право
    на выполнение инструкций SQL
    (statement
    permission)
    — определяет набор прав пользователя
    на выполне­ние выражений, например,
    на соз­дание базы данных.

  • Право
    на работу с объектами (object
    permission)
    — определяет набор прав пользователя
    при работе с данными или выполнении
    хранимых процедур.

  • Предопределенные
    права (predefined
    permission)
    — определяют набор дейст­вий, которые
    разрешено выполнять только пользователям,
    включенным в определенные стандартные
    роли, или владельцам объектов базы
    данных.

Право на выполнение выражений: Предположим,
что нам надо создать базу данных. Для
этого необходимо выполнить инструкцию
CREATE DATA­BASE,
даже если мы используем для этой целиSQLServerEn­terpriseManager. Система безопасностиSQLServerпредостав­ляет возможность запретить
пользователям, не являющимся чле­нами
ролейsysadmin, db_owner
или db_ddladmin,
выполнять такого рода ин­струкции, к
которым относятся:

CREATE
DATABASE CREATE DEFAULT CREATE

PROCEDURE
CREATE RULE

CREATE
TABLE CREATE VIEW BACKUP DATA­BASSE BACKUP LOG.

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

SP_CHANGEOBJECTOWNER
объект, владелец.

Но
в большинстве случаев это крайняя мера,
свидетельст­вующая о том, что
недостаточно четко продумана система
безо­пасности. И лучше все-таки
из­менить именно систему.

Право
доступа к объектам
(object
permission)
имеет отно­шение к таблицам и другим
объектам базы данных, таким как хранимые
процедуры и представ­ления.

Ниже
приведен список и описание прав доступа
к табли­цам:

  • SELECT
    — дает возможность пользователю
    вы­брать или прочесть данные из
    таблицы или представления. Сле­дует
    заметить, что право доступа SELECT
    может быть предос­тавлено к
    индивидуальным столбцам, а не только
    ко всей таблице или представлению.

  • INSERT
    — дает возможность пользователю
    доба­вить новые данные в таблицу или
    представление.

  • UPDATE
    — дает
    возможность пользователю изме­нить
    данные в таблице или представлении.

  • DELETE
    — дает возможность пользователю
    уда­лить данные из таблицы или
    представления.

  • EXECUTE
    — дает возможность пользователю
    вы­полнить хранимую про­цедуру.

  • DRI/REFERENCES
    — дает
    возможность пользова­телю добавить
    к табли­це условие на значение.

Для
управления правами доступа интерфейс
Transact—SQL
SQL
Server
со­держит инструкции GRANT,
DENY
и
RE­VOKE.
С точки зрения системы раз­граничения
доступа каждый пользователь может
находиться в одном из трех состояний:

  • Пользователь
    имеет право выполнять определенное
    действие.

  • Пользователю
    запрещено выполнять определенное
    действие.

  • Пользователь
    не имеет четко определенных разреше­ний
    и запретов.

Инструкция
GRANT
используется для предоставления прав
доступа пользова­телям или ролям в
SQL
Server.
Наличие определенного права доступа
дает пользователю или роли воз­можность
выполнять нужные действия. Синтак­сис
инструкции GRANT
для выражений и объектов:

GRANT
{ALL
| выражение [, … n]}

ТО
бюджет_безопасности [, … n]

GRANT
{ALL
[PRIVILEGES]
| список_прав_доступа [, … n]}

{
[(список_столбцов[, … n])]

ON
[таблица | представление} | [ … n]

[(список_
столбцов [, … n])]
|

ON
{хранимая процедура |
расширенная хранимая проце­дура}}

ТО
бюджет__безопасности [, … n]

[WITH
GRANT
OPTION]

[AS
{rpynna_Windows_NT |
роль}]

Инструкция
DENY
применяется для удаления права дос­тупа.
В отличие от GRANT,
инструкция DENY
служит для
уда­ления любого права доступа,
предос­тавленного пользователю или
роли. Кроме того, сервер отслеживает,
чтобы в будущем пользователь или роль
не унаследовали отобранные права от
дру­гой роли. Синтаксис инструкции
DENY
для выражений и объек­тов:

DENY
{ALL
| выражение [, … n]}

ТО
бюджет_безопасности [, … n]

DENY
{ALL
[PRIVILEGES]
|
список__прав_доступа [, … n]}

{
[(список_столбцов [, … n])]

ON
{таблица |
представление} | [… n]

[(список_столбцов
[, … n])]
|

ON
{хранимая процедура |
расшире­нная_хранимая_процедура}}

ТО
бюджет_безопасности [, … n]

[CASCADE]

Инструкция
REVOKE
используется для удаления предос­тавленных
ранее прав доступа пользователям или
ролям в SQL
Server.
При этом информация о правах берется
из соответствую­щей роли. Синтаксис
инструкции REVOKE
для выражений и объектов имеет,
соответственно, следующий вид:

REVOKE
{ALL
| выражение [, … n]}

ТО
бюджет_безопасности [, … n]

REVOKE
{ALL
[PRIVILEGES]
| список_прав_доступа [, … n]}
{ [(список столбцов [, .. n])]

ON
(таблица | представление} | […n]

[(список_столбцов
[, … n])]
| ON

{хранимая_процедура
| расширенная храни­мая_процедура}}

(ТО
| FROM}
бюджет_безопасности [, … n]

[CASCADE]

[AS
{группа_Wlndows_NT
| роль}]

Инструкции
GRANT,
DENY
и
REVOKE
имеют практи­чески идентичные
параметры. Рассмотрим некоторые из них:

  • список_прав_доступа
    — список прав доступа, кото­рые
    предоставляются или уничтожаются. Для
    отделения прав доступа в списке следует
    исполь­зовать запятую. Если указаны
    все права доступа (ALL),
    то данному поль­зователю или группе
    будут предоставлены права доступа
    того, кто их ус­танавливает;

  • таблица,
    представление, хранимая__процедура
    и т. п. — имя объекта, право доступа к
    которому предоставляется или уничтожается;

  • список
    — список имен пользователей или ролей,
    кото­рым предоставля­ются права
    доступа или права которых уничтожаются.
    Для отделения множества имен в списке
    следует использовать запятые.

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

Ниже
приведен пример предоставления прав
доступа SE­LECT
и
UPDATE
к таблице
Authors:

GRANT
SELECT, UPDATE

ON
Authors

TO
PUBLIC

GO

Ниже
приведен пример удаления права доступа
DELETE
к таблице Editors:

DENY
DELETE

ON
Editors

FROM
PUBLIC

GO

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

Представления
дают удобный способ улучшения
безопас­ности, т.к. они ог­раничивают
данные, доступные пользователям.
Например, можно иметь группу пользователей,
которым не доз­волено просматривать
информацию по авторам, получающим бо­лее
чем 50% гонорара. Эта информация доступ­на
только главным менеджерам или другим
работникам внутри компании. Ниже
по­казано, как можно выполнить эту
задачу с помощью Transact—SQL.

/*
Сначала
добавим
роль
*/

SP_ADDROLE
grp_junior_emp

GO

/*
Теперь уничтожим право доступа SELECT

роли
public
к таблицам базы данных */

DENY
SELECT ON TitleAuthors FROM public

GO

DENY
SELECT ON Authors FROM public

GO

/*
Теперь создадим представление,
ограничивающее дос­туп */

CREATE VIEW
View_Authors

AS

SELECT
*

FROM
Authors

WHERE
au_id IN (SELECT au_id

FROM
TitleAuthors

WHERE
royaltyper <= 50)

GO

/*
Предоставим
членам
роли
grp_junior_emp право
доступа
SELECT к
представлению
View_Authors */

GRANT
SELECT ON View_Authors TO grp_junior_emp

GO

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

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

/* Сначала добавим
роль */

SP_ADDROLE
grp_junior_emp

GO

/*
Теперь уничтожим право доступа SELECT
группы pub­lic
к таблицам базы данных */

DENY
SELECT ON TitleAuthors FROM public

GO

DENY
SELECT ON Authors FROM public

GO

/*
Теперь создадим хранимые процедуры,
ограничиваю­щие доступ */

CREATE
PROCEDURE up__SelectAuthors

AS

SELECT
*

FROM
Authors

WHERE
au_id IN (SELECT au_id

FROM
TitleAuthors

WHERE
royaltyper <= 50)

GO

/*
Предоставим
членам
роли
grp_junior_emp право
доступа
EXECUTE к
хранимой
процедуре
up_SelectAuthors */

GRANT
EXECUTE ON up_SelectAuthors TO grp_junior_emp

GO

Как
видно из приведенного примера, младшие
работники (grp_junior_emp)
имеют возможность обновлять флаг
contract
таблицы
Au­thors
без дополнительных прав доступа к ней.
Этот вид проце­дуры дает возможность
скрыть от пользователей манипуляцию
данными, в то же вре­мя они будут иметь
ограниченные возмож­ности работы с
доступными им данными на сервере.

/* Сначала добавим
группу*/

SP_ADDGROUP
grp_junior_emp

GO

/*
Теперь уничтожим право доступа UPDATE,
DELETE,
INSERT
группы public
к таблице базы данных Authors
*/

DENY
UPDATE, DELETE, INSERT ON Authors FROM public

GO

/*Теперь
создадим хранимые процедуры, ограничивающие
доступ */

CREATE
PROCEDURE up_SetContractForAuthor

@nAu_Id
id,

@bContract
bit

AS

UPDATE
Authors

SET
contract = @bContract

WHERE
au_id
= @nAu_Id

PRINT
«Автор заключил контракт»

GO

/*
Предоставим членам группы grp_junior_emp
право дос­тупа EXECUTE
к хранимой процедуре up__SetContractForAuthor
*/

GRANT
EXECUTE ON up_SetContractForAuthor TO grp_junior_emp

GO

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

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

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

  • #
  • #
  • #
  • #
  • #
  • #
  • #
  • #
  • #
  • #
  • #
Источник

Версия SQL Server 2005 была первой крупной версией SQL Server, выпущенной после появления достопамятного документа о Trustworthy Computing Билла Гейтса в 2002 году (http://www.computerbytesman.com/security/billsmemo.htm). После этого документа подход и отношение компании к безопасности продуктов изменились, как и компоненты безопасности SQL Server 2005. В результате SQL Server 2012 и 2014 отличаются изначально чрезвычайно высокой защищенностью, достигнутой благодаря развитию этих компонентов и их усилению в каждом новом выпуске. Но сохранять высокий уровень защиты по мере добавления баз данных и предоставления доступа пользователям — трудная задача.

Одним из самых значительных изменений в 2005 году была новая инфраструктура разрешений при проверке подлинности серверов. Ушли в прошлое времена, когда требовалось назначать пользователям системные роли, чтобы предоставить им единственное разрешение. Сегодня система разрешений отличается глубокой детализацией и позволяет назначать минимальные разрешения, необходимые субъекту безопасности для выполнения работы, в соответствии с принципом предоставления минимальных прав.

При использовании разрешений следует учитывать три основных фактора:

  • Кто должен получить разрешение?
  • На какой объект должно распространяться разрешение?
  • Разрешения какого вида должны быть выданы?

Основная идея разрешений состоит в предоставлении разрешения определенного типа для какого-нибудь объекта или объектов в экземпляре SQL Server некоторому субъекту безопасности.

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

Субъекты безопасности: кто должен получить разрешение?

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

Субъекты безопасности уровня Windows

  • Имя входа домена или локальное имя входа Windows;
  • Группа Windows.

Субъекты безопасности уровня SQL Server

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

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

Объект разрешения

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

Иерархия защищаемых объектов в экземпляре SQL Server
Рисунок. Иерархия защищаемых объектов в экземпляре SQL Server

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

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

Инструкции разрешения

После знакомства с субъектами безопасности и защищаемыми объектами самое время рассмотреть собственно разрешения и способы их назначения. Эти действия можно выполнить с помощью замечательного инструментария в среде SQL Server Management Studio (SSMS) или инструкций T-SQL. Я подробно рассмотрю последний вариант, в основном потому, что таким образом можно полностью использовать весь набор разрешений.

Можно воспользоваться тремя инструкциями разрешения. Инструкция GRANT предоставляет разрешение для защищаемого объекта субъекту безопасности. Инструкция REVOKE отменяет результат инструкции GRANT: удаляет разрешения для объекта, ранее назначенные субъекту безопасности. Если в момент выполнения инструкции REVOKE у субъекта безопасности нет соответствующего разрешения, то оно и дальше будет отсутствовать; ошибки не произойдет. Важно понимать, что отозванное разрешение может быть унаследовано через членство в роли, которое имеет разрешение. Это может привести к сложным ситуациям, поэтому оптимальный подход — не назначать слишком часто разрешений отдельным пользователям, именам входа или иным одиночным субъектам, только ролям.

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

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

Использование разрешения на уровне экземпляра сервера

Рассмотрим практический пример использования разрешений и код T-SQL для выполнения различных действий. Весь приведенный программный код есть в файле Permissions Code.sql (см. листинг A), наряду с программным кодом для очистки и вспомогательных функций, а также комментариями. Программный код анализирует разрешения как на уровне сервера, так и на уровне базы данных.

Сначала создается имя входа Bonsai (с достаточно надежным паролем!) для сервера и пользователь Bonsai сопоставляется имени входа в базе данных AdventureWorks2012. Используйте любую базу данных по своему желанию, но в программном коде используются объекты и данные именно из этой базы данных.

CREATE LOGIN Bonsai WITH PASSWORD = 'EDxQk!R209*:ZJ5';
GO
USE AdventureWorks2012;
GO
CREATE USER Bonsai FOR LOGIN Bonsai WITH DEFAULT_SCHEMA = Production;
GO

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

USE master;
GO
CREATE SERVER ROLE LimitedAdmin;
GO
GRANT CONTROL SERVER TO LimitedAdmin;
GO
DENY ALTER ANY SERVER ROLE TO LimitedAdmin;
DENY ALTER ANY LOGIN TO LimitedAdmin;
DENY ALTER ANY DATABASE TO LimitedAdmin;

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

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

EXECUTE AS LOGIN = 'Bonsai';
Тестирование разрешений Bonsai до назначения ему роли LimitedAdmin
Экран. Тестирование разрешений Bonsai до назначения ему роли LimitedAdmin

Затем мы возвращаемся к разрешениям sysadmin с помощью следующей инструкции.

REVERT;

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

ALTER SERVER ROLE LimitedAdmin ADD MEMBER Bonsai;

Теперь, когда контекст выполнения изменен на Bonsai, первые три операции по-прежнему завершаются неудачей, но включение кода SQLCLR работает успешно. Мы назначили роли LimitedAdmin разрешение CONTROL SERVER, которое охватывает почти все действия, совершаемые на экземпляре сервера, но при этом отменили некоторые разрешения. Если вы намерены запретить роли LimitedAdmin изменять параметры сервера, например включением кода SQLCLR, то можно запретить разрешение SETTINGS с помощью следующей инструкции:

DENY ALTER SETTINGS TO LimitedAdmin;

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

Использование разрешений на уровне базы данных

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

В этом случае сначала выполняется перемещение базы данных AdventureWorks2012, а затем создается определяемая пользователем роль ProdDataEntry и к роли добавляется Bonsai.

USE AdventureWorks2012;
GO
CREATE ROLE ProdDataEntry AUTHORIZATION dbo;
ALTER ROLE ProdDataEntry ADD MEMBER Bonsai;
GO

Затем нужно назначить роли какие-нибудь разрешения и одно разрешение будет удалено. Идея состоит в том, что членам роли нужны права для вставки и обновления записей в двух таблицах в схеме Production. На данном этапе важно управлять разрешениями для действий. Следующие инструкции назначают разрешения INSERT и UPDATE для таблиц Production.UnitMeasure и Production.ProductCategory, а также инструкция SELECT для таблицы ProductCategory. С ее помощью будет показано, как можно управлять действиями с данными на детальном уровне.

GRANT INSERT ON Production.UnitMeasure TO ProdDataEntry;
GRANT UPDATE ON Production.UnitMeasure TO ProdDataEntry;
GRANT INSERT ON Production.ProductCategory TO ProdDataEntry;
GRANT UPDATE ON Production.ProductCategory TO ProdDataEntry;
GRANT SELECT ON Production.ProductCategory TO ProdDataEntry;

Затем разрешение EXECUTE назначается для хранимой процедуры dbo.uspGetEmployeeManagers, но такое же разрешение удаляется для процедуры dbo.uspGetManagerEmployees. Это означает, что у различных объектов имеются разрешения разных типов. Помните, что если Bonsai имеет разрешение EXECUTE для uspGetManagerEmployees через членство в другой роли, то она сможет выполнить процедуру. В этом отличие REVOKE от DENY.

GRANT EXECUTE ON dbo.uspGetEmployeeManagers TO ProdDataEntry;
REVOKE EXECUTE ON dbo.uspGetManagerEmployees TO ProdDataEntry;

Затем можно использовать программный код, приведенный в листинге 2, для тестирования разрешений при выполнении кода от имени Bonsai. Прежде чем читать статью дальше, просмотрите программный код, чтобы понять, можете ли вы определить, удачно или нет завершается каждая инструкция.

Фрагмент 1 завершается успешно, так как Bonsai имеет разрешение INSERT для таблицы UnitMeasure, но фрагмент 2 завершается неудачно из-за отсутствия разрешения SELECT. Фрагменты 3 и 4 завершаются успешно, так как роль имеет разрешения INSERT и SELECT для таблицы ProductCategory. Фрагмент 5 завершается неудачей, поскольку роль вообще не имеет разрешений для таблицы HumanResources.Department. Фрагмент 6 успешен, так как роль имеет разрешение EXECUTE для хранимой процедуры uspGetEmployeeManagers, но фрагмент 7 завершается неудачей, потому что разрешения для процедуры uspGetManagerEmployees были отозваны ранее.

SQL Server предоставляет значительный объем метаданных о разрешениях, и в листинге 3 показано несколько способов доступа к метаданным. Первая инструкция перечисляет разрешения, которые имеются у роли ProdDataEntry в базе данных AdventureWorks2012 после выполнения предшествующего программного кода. Три другие инструкции показывают, как использовать sys.fn_builtin_permissions для получения списка всех встроенных разрешений в экземпляре сервера, всех разрешений на уровне сервера и всех на уровне базы данных. Это превосходный способ проанализировать полный набор доступных разрешений. Вы почерпнете массу полезной информации!

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

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

Листинг A. Содержимое файла Permissions Code.sql

— SQL Server 2012/2014 Permissions Code
— donkiely@computer.org
— *** SQL Server Logins ***
— *************************
CREATE LOGIN Bonsai WITH PASSWORD = 'EDxQk!R209*:ZJ5';
GO
USE AdventureWorks2012;
GO
CREATE USER Bonsai FOR LOGIN Bonsai WITH DEFAULT_SCHEMA = Production;
— User name doesn't need to be the same as login name, but here it is.
GO
— *** User-Defined Server Role and Permissions ***
— ************************************************
USE master;
GO
— Create a user-defined server role
CREATE SERVER ROLE LimitedAdmin;
GO
— Grant sysadmin privileges
GRANT CONTROL SERVER TO LimitedAdmin;
GO
— Members of LimitedAdmin role now have omnipotent powers over the server instance and all its objects
— Restrict that to some extent
DENY ALTER ANY SERVER ROLE TO LimitedAdmin;
DENY ALTER ANY LOGIN TO LimitedAdmin;
DENY ALTER ANY DATABASE TO LimitedAdmin;
— Statements that exercise server-level actions — don't run these yet!
— ** Start statements
CREATE SERVER ROLE TempRole;
CREATE LOGIN TempLogin WITH PASSWORD = 'AK8l*9%fwy/xvH';
CREATE DATABASE TempDatabase;
EXEC SP_CONFIGURE 'show advanced options', '1';
GO
RECONFIGURE;
GO
EXEC SP_CONFIGURE 'clr enabled', '1'
GO
RECONFIGURE;
GO
— ** End statements
— Statement cleanup — run as sysadmin
— ** Start statements
DROP SERVER ROLE TempRole;
DROP LOGIN TempLogin;
DROP DATABASE TempDatabase;
— ** End statements
— Test the statements, logged in as sysadmin
— Go execute statements. Should all succeed.
— Go execute cleanup statements.
— Test permissions
— Test 1: Can Bonsai do these things?
EXECUTE AS LOGIN = 'Bonsai';
— go execute the block of statements
REVERT;
— Test 2: Add Bonsai to LimitedAdmin
ALTER SERVER ROLE LimitedAdmin ADD MEMBER Bonsai;
GO
EXECUTE AS LOGIN = 'Bonsai';
— go execute the block of statements
— still can't execute first four operations, but can the fifth
REVERT;
— Test 3: Deny ALTER SERVER STATE permission, which won't allow DBCC FREPROCCACHE
DENY ALTER SETTINGS TO LimitedAdmin;
GO
EXECUTE AS LOGIN = 'Bonsai';
— go execute the block of statements
— still can't execute first four operations, but can the fifth
REVERT;
— *** User-defined Database Role and Permissions ***
— **************************************************
USE AdventureWorks2012;
GO
— Create a user-defined data entry role in the production schema
CREATE ROLE ProdDataEntry AUTHORIZATION dbo;
— Assign Bonsai to the role
ALTER ROLE ProdDataEntry ADD MEMBER Bonsai;
GO
— Assign permissions to the ProdDataEntry role
GRANT INSERT ON Production.UnitMeasure TO ProdDataEntry;
GRANT UPDATE ON Production.UnitMeasure TO ProdDataEntry;
GRANT INSERT ON Production.ProductCategory TO ProdDataEntry;
GRANT UPDATE ON Production.ProductCategory TO ProdDataEntry;
GRANT SELECT ON Production.ProductCategory TO ProdDataEntry;
GRANT EXECUTE ON dbo.uspGetEmployeeManagers TO ProdDataEntry;
REVOKE EXECUTE ON dbo.uspGetManagerEmployees TO ProdDataEntry;
— See what Bonsai can do
EXECUTE AS USER = 'Bonsai';
— Succeeds — has permission
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES ('BAR', 'Standard Bar');
— Fails
SELECT * FROM Production.UnitMeasure WHERE UnitMeasureCode = 'BAR';
— Succeeds — has permission
INSERT INTO Production.ProductCategory (Name)
VALUES ('Navigation');
— Succeeds
SELECT * FROM Production.ProductCategory WHERE Name = 'Navigation';
— Fails
INSERT INTO HumanResources.Department
(Name, GroupName)
VALUES
('Advertising', 'Sales and Marketing');
GO
— Succeeds
DECLARE @rc INT;
EXECUTE @rc = dbo.uspGetEmployeeManagers 113;
GO
— Fails
DECLARE @rc INT;
EXECUTE @rc = dbo.uspGetManagerEmployees 113;
GO
REVERT;
— *** Permissions metadata ***
— ****************************
— View the permissions for the ProdDataEntry database role
USE AdventureWorks2012;
GO
SELECT DB_NAME() AS 'Database', p.name, p.type_desc, dbp.state_desc,
dbp.permission_name, so.name, so.type_desc
FROM sys.database_permissions dbp
LEFT JOIN sys.objects so ON dbp.major_id = so.object_id
LEFT JOIN sys.database_principals p ON dbp.grantee_principal_id = p.principal_id
WHERE p.name = 'ProdDataEntry'
ORDER BY so.name, dbp.permission_name;
— Get a list of all built-in permissions
SELECT * FROM sys.fn_builtin_permissions(DEFAULT);
— Get a list of server-level permissions
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
— Get a list of database-level permissions
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;
— *** Clean Up ***
— ****************
— Be sure to run this as sysadmin!
— Run earlier cleanup statements, if necessary
USE AdventureWorks2012;
GO
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode = 'BAR';
DELETE FROM Production.ProductCategory WHERE Name = 'Navigation';
GO
DROP USER Bonsai;
DROP ROLE ProdDataEntry;
GO
USE master;
GO
DROP LOGIN Bonsai;
DROP SERVER ROLE LimitedAdmin;
GO

Листинг 1. Программный код для тестирования разрешений пользователя LimitedAdmin

CREATE SERVER ROLE TempRole;
CREATE LOGIN TempLogin WITH PASSWORD = 'AK8l*9%fwy/xvH';
CREATE DATABASE TempDatabase;
EXEC SP_CONFIGURE 'show advanced options', '1';
GO
RECONFIGURE;
GO
EXEC SP_CONFIGURE 'clr enabled', '1'
GO
RECONFIGURE;
GO

Листинг 2. Программный код для тестирования разрешений определяемой пользователем роли базы данных ProdDataEntry

EXECUTE AS USER = 'Bonsai';
— Фрагмент 1
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES ('BAR', 'Standard Bar');
— Фрагмент 2
SELECT * FROM Production.UnitMeasure WHERE UnitMeasureCode = 'BAR';
— Фрагмент 3
INSERT INTO Production.ProductCategory (Name)
VALUES ('Navigation');
— Фрагмент 4
SELECT * FROM Production.ProductCategory WHERE Name = 'Navigation';
— Фрагмент 5
INSERT INTO HumanResources.Department
(Name, GroupName)
VALUES
('Advertising', 'Sales and Marketing');
GO
— Фрагмент 6
DECLARE @rc INT;
EXECUTE @rc = dbo.uspGetEmployeeManagers 113;
GO
— Фрагмент 7
DECLARE @rc INT;
EXECUTE @rc = dbo.uspGetManagerEmployees 113;
GO
REVERT;

Листинг 3. Программный код для доступа к некоторым метаданным разрешений

USE AdventureWorks2012;
GO
SELECT DB_NAME() AS 'Database', p.name, p.type_desc, dbp.state_desc,
dbp.permission_name, so.name, so.type_desc
FROM sys.database_permissions dbp
LEFT JOIN sys.objects so ON dbp.major_id = so.object_id
LEFT JOIN sys.database_principals p ON dbp.grantee_principal_id = p.principal_id
WHERE p.name = 'ProdDataEntry'
ORDER BY so.name, dbp.permission_name;
SELECT * FROM sys.fn_builtin_permissions(DEFAULT);
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;
Источник

Понравилась статья? Поделить с друзьями:
  • Работа риэлтора как правильно ее организовать пошаговая инструкция
  • Работа по фбс вайлдберриз инструкция
  • Работа на узи аппарате mindray инструкция
  • Работа на сайте госзакупок пошаговая инструкция
  • Работа на пвз вайлдберриз инструкция