SQL-Ex blog

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

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

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

• Принципалы - это логины SQL или Active Directory, которые используются для доступа к SQL Server, а также соответствующие пользователи базы данных и роли:
  
  
  • Принципалы уровня сервера.
  
  
  • Принципалы уровня базы данных.


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

Лучшей практикой считается предоставление привилегий ролям, а затем приписывать роли логину/пользователю порознь, а не предоставлять непосредственно одни и те же привилегии нескольким пользователям порознь. Например, давайте предположим, что нам необходимо присвоить разрешения на чтение таблиц Invoice и InvoiceLine пользователям-финансистам.

Давайте создадим роль базы данных:

CREATE ROLE [Finance]

Предоставим привилегию SELECT на эти две таблицы (Invoice и InvoiceLine) этой роли:

GRANT SELECT ON [dbo].[Invoice] TO [Finance]

GRANT SELECT ON [dbo].[InvoiceLine] TO [Finance]

Теперь для предоставления финансисту привилегии на чтение этих таблиц нам необходимо назначить пользователю роль Finance. Давайте создадим логин и пользователя с назначенной ролью:

USE [master]

GO

CREATE LOGIN [l.callahan] WITH PASSWORD=N'GoldenHorde', DEFAULT_DATABASE=[Chinook], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [Chinook]

GO

CREATE USER [l.callahan] FOR LOGIN [l.callahan]

GO

ALTER ROLE [Finance] ADD MEMBER [l.callahan]

GO

Теперь давайте попробуем подключиться, используя логин SQL, и проверим, что этот пользователь может делать:



Запрос ниже выбирает 30 первых строк из таблицы Invoice:

select top 30 * from Invoice;

Но что, если мы попытаемся обратиться к другой таблице, например, Artist:

select top 30 * from artist;

Поскольку пользователь не имеет привилегий, связанных с этой таблицей, а только на Invoice и InvoiceLine, то ему запрещен доступ. Вспомните принцип минимальных привилегий!

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

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

USE [master]

GO

CREATE LOGIN [Report] WITH PASSWORD=N'pippo120', DEFAULT_DATABASE=[Chinook], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [Chinook]

GO

CREATE USER [Report] FOR LOGIN [Report]

GO

ALTER ROLE [db_datareader] ADD MEMBER [Report]

GO

Теперь мы можем получить доступ к базе данных Chinook под пользователем Report:



Сделаем выборку из пары различных таблиц:

select top 30 * from Artist;

select * from Genre;

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

update Genre

set name='Punk'

where GenreId = 4

то получим сообщение об отсутствии разрешений.

Серверные роли в SQL Server 2022

До сих пор мы говорили о ролях уровня базы данных. Но, как было упомянуто ранее, имеются также роли уровня сервера. Они могут быть созданы ad-hoc как пользовательские серверные роли (начиная с SQL Server 2012), а также имеется несколько фиксированных ролей.

Имеется несколько новинок, введенных в SQL Server 2022, включая 10 дополнительных фиксированных серверных ролей, которые отличаются префиксом ##MS_ и суффиксом ##. (Официальная документация).

Давайте обсудим несколько примеров. Наиболее мощной фиксированной серверной ролью является роль sysadmin. Получив эту роль, мы получим разрешения ко всему на экземпляре SQL Server, почти как SA.

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



Замечание. Создавая новый логин в SSMS, новые фиксированные роли появляются, когда мы выбираем страницу Server Roles (роли сервера).

Как и прежде, мы можем использовать T-SQL:

USE [master]

GO

CREATE LOGIN [Management] WITH PASSWORD=N'Riesenhotter44', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [Management]

GO

Теперь мы можем протестировать новый логин:



Давайте попробуем перевести базу данных в автономный режим на сервере:

USE [master]

GO

ALTER DATABASE [WideWorldImporters] SET OFFLINE

GO

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

USE [master]

GO

ALTER DATABASE [WideWorldImporters] SET ONLINE

GO

Теперь давайте вернем ее в режим онлайн с помощью пользователя Management

USE [master]

GO

ALTER DATABASE [WideWorldImporters] SET ONLINE

GO

Опять таки, новые фиксированные серверные роли идентифицируются префиксом ##MS_ и суффиксом ##. Полный список этих ролей можно найти в официальной документации.

Давайте попробуем другой пример, использующий ##MS_DatabaseConnector##. На практике члены этой фиксированной роли могут подключаться к любой базе данных на экземпляре SQL Server без необходимости иметь пользовательский аккаунт в базе данных.

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

USE [master]

GO

CREATE LOGIN [Supervisor] WITH PASSWORD=N'PinoLavatrice', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER SERVER ROLE [##MS_DatabaseConnector##] ADD MEMBER [Supervisor]

GO

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



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

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

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

О разной терминологии и основных отличиях в трех РСУБД можно ознакомиться здесь.

Концепция схемы/пользователи в Oracle отличается от SQL Server. Но тут тоже имеются роли. Роли могут быть созданы непосредственно (ad hoc), как и в SQL Server, но также имеется несколько фиксированных ролей. Кроме этого, мы имеем системные привилегии, которые могут быть предоставлены непосредственно пользователю или роли ad-hoc. Эти роли могут быть присвоены пользователям/схемам, как и в SQL Server для логинов пользователей. Давайте рассмотрим аналогичный пример создания роли Finance и присвоении ее конкретному пользователю:

create role finance;

Мы создали роль как и в SQL Server. Теперь назначим этой роли привилегию select на таблицы счетов:

grant select on chinook.invoice to finance;

grant select on chinook.invoiceline to finance;

Создадим конкретного пользователя и предоставим ему роль finance:

CREATE USER "l.callahan" IDENTIFIED BY "GoldenHorde"

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

GRANT "CONNECT" TO "l.callahan" ;

GRANT "FINANCE" TO "l.callahan" ;

ALTER USER "l.callahan" DEFAULT ROLE "CONNECT","FINANCE";

Здесь сразу бросаются в глаза некоторые отличия в создании пользователя. Во-первых, нам необходимо назначить пользователю временное табличное пространство и табличное пространство по умолчанию. Табличное пространство по умолчанию будет использоваться для создания новых объектов, в то время как пространство temp - для временных объектов типа CTE. Табличное пространство является базовой структурой хранения в Oracle, подобно файлам базы данных в SQL Server.

Затем мы назначаем роль пользователю с помощью простого оператора GRANT. Отличие от SQL Server состоит в том, что мы используем ALTER, чтобы добавить роль пользователю.

Наконец, этого недостаточно, чтобы назначить роль; мы также должны установить ее по умолчанию, или она не будет использоваться. Другое замечание состоит в том, что нам необходимо предоставить пользователю роль CONNECT, иначе он не сможет войти (и, естественно, эта роль также требует установки по умолчанию). Эта концепция может быть полезна, чтобы предоставить пользователю возможность выполнять определенные задачи, которые требуют высоких привилегий, но не постоянно. Поэтому мы просто назначаем пользователю роль с более высокими разрешениями без определения их как DEFAULT, а при необходимости пользователь сам может выполнить в сеансе оператор SET ROLE, и назначить ему роль; а когда сеанс закрывается, он теряет роль или, в качестве альтернативы, пользователь сам может отключить ее с помощью SET ROLE NONE;

Давайте попытаемся выполнить подключение с этим новым пользователем и протестировать его. Используя SQL Developer, мы создаем новое подключение:



Обратите внимание на имя пользователя: поскольку оно содержит ".", мы должны использовать двойные кавычки, как и ранее при создании пользователя; в противном случае мы получим ошбку о неверном имени пользователя/пароле!

Подключившись, мы можем попроболвать выполнить те же самые запросы, которые использовались в SQL Server:

select *

from chinook.Invoice

fetch FIRST 30 rows only;

Если не считать различия в диалекте Oracle PL/SQL, который не содержит предложения TOP, а использует конструкцию FETCH FIRST n ROWS ONLY, мы имеем те же самые результаты. Теперь давайте проверим таблицу, для которой пользователь не авторизован:

select *

from chinook.artist

fetch FIRST 30 rows only;

Обратите внимание на отличающиеся типы ошибок в Oracle. Она не дает информации о таблице artist. Говорится, что таблица не существует!

С сожалению, в Oracle нет такой роли, как db_datareader в SQL Server, но имеется системная привилегия для этого. Давайте сделаем тот же пример и создадим пользователя Report, которому необходимо читать данные из каждой схемы:

CREATE USER "report" IDENTIFIED BY "pippo120"

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

 

GRANT "CONNECT" TO "report" ;

ALTER USER "report" DEFAULT ROLE "CONNECT";

-- Системные привлегии

GRANT SELECT ANY TABLE TO "report" ;

Мы предоставили пользователю Report системную привилегию на выборку данных (select) из любой таблицы базы данных. Давайте попробуем:

select *

from chinook.artist

fetch first 30 rows only;

И из другой случайно выбранной таблицы:

select *

from chinook.genre

fetch first 30 rows only;

Теперь давайте попробуем выполнить запись в ту же самую таблицу:

update chinook.Genre

set name='Punk' 

where GenreId=4;

Как ожидалось, мы получил ошибку недостаточных привлегий.

Говоря о системных привилегиях в Oracle, упомянем роль DBA (администратора баз данных), которой мы можем сопоставить подобную роль sysadmin в SQL Server. Давайте рассмотрим ее и другие роли, используя создание пользователя в графическом интерфейсе SQL Developer, и создадим такого же пользователя Management, как и прежде:



Перейдем теперь на вкладку Granted Roles, чтобы увидеть список всех доступных ролей:



Мы просто используем роли CONNECT и DBA. Мы вернемся к списку ролей и системных представлений во второй части этой статьи.

Теперь перейдем на вкладку SQL, чтобы получить скрипт SQL:

CREATE USER "Management" IDENTIFIED BY "Riesenhotter44"

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

-- Часть

-- Роли

GRANT "DBA" TO "Management" ;

GRANT "CONNECT" TO "Management" ;

ALTER USER "Management" DEFAULT ROLE "DBA","CONNECT";

И после создания этого пользователя давайте протестируем его:



Давайте попробуем заблокировать пользователя l.callahan. Вспомните, что в Oracle есть только одна база данных на экземпляр, если это не в среде CDB/PDB.

ALTER USER "l.callahan" ACCOUNT LOCK ;

Без проблем. Но если мы теперь попытаемся разблокировать пользователя с помощью пользователя Report:



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



Теперь мы можем разблокировать его, используя пользователя Management:

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

В PostgreSQL имеется подобная Oracle концепция пользователей/схем, но они называются роли. Роли являются принципалами в PostgreSQL, как говорится в документации: концепция ролей включает понятия "пользователей" и "групп". В версиях PostgreSQL до 8.1 пользователи и группы были разным видами сущностей, но теперь это только роли. Любая роль может действовать как пользователь, группа или и то, и другое.

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

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

CREATE ROLE "Finance" WITH

   NOLOGIN

   NOSUPERUSER

   NOCREATEDB

   NOCREATEROLE

   INHERIT

   NOREPLICATION

   CONNECTION LIMIT -1;

grant select on "Invoice" to "Finance";

grant select on "InvoiceLine" to "Finance";



CREATE ROLE "l.callahan" WITH

   LOGIN

   NOSUPERUSER

   NOCREATEDB

   NOCREATEROLE

   INHERIT

   NOREPLICATION

   CONNECTION LIMIT -1

   PASSWORD 'Goldenhorde';

GRANT "Finance" TO "l.callahan";

Вы видите, что командой всегда является CREATE ROLE, но в случае логина/пользователя мы указывали опцию LOGIN и пароль. В то время как для роли Finance мы использовали опцию NOLOGIN без пароля.

Теперь подключимся к базе данных с этим новым пользователем и выполним обычные запросы:

select *

from "Invoice"

fetch first 30 rows only;

Обратите внимание, что PostgreSQL использует как стандартный SQL для возвращения n строк с помощью FETCH FIRST N ROWS ONLY, так и собственную нотацию LIMIT N. Теперь мы можем попытаться обратиться к другой таблице:

select *

from "Artist"

fetch first 30 rows only;

Также в PostgreSQL имеются предопределенные роли, как в SQL Server и Oracle. Вот полный список, приведенный в документации. В PostgreSQL имеется роль pg_read_all_data, которая почти эквивалентна роли db_datareader в SQL Server.

Теперь мы можем попробовать предоставить эту роль пользователю Report, как мы это делали в SQL Server:

CREATE ROLE "Report" WITH

   LOGIN

   NOSUPERUSER

   NOCREATEDB

   NOCREATEROLE

   INHERIT

   NOREPLICATION

   CONNECTION LIMIT -1

   PASSWORD 'pippo120';

 

GRANT pg_read_all_data TO "Report";

Теперь мы можем проверить запрос:

select *

from "Artist"

fetch first 30 rows only;

Попробуем другую таблицу:

select *

from "Genre"

fetch first 30 rows only;

Но если мы попробуем выполнить запись:

update "Genre"

set "Name"='Punk' 

where "GenreId"=4;

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

Как утверждалось ранее, роль pg_read_all_data почти идентична роли уровня базы данных db_datareader в SQL Server. Это потому что роли в PostgreSQL больше на уровне сервера (кластера), подобно серверным ролям, а не на уровне базы данных.

Есть также способ определить полномочного пользователя, подобно роли sysadmin в SQL Server или роли DBA в Oracle, но тут это не делается с помощью специальной роли. Во время создания скриптов для ролей мы использовали NOSUPERUSER. Но если поменять этот параметр на SUPERUSER, то мы получим эквивалент пользователя sysadmin в SQL Server. Давайте использовать те же примеры, что для двух других РСУБД. Сначала мы создаем суперпользователя Management:

CREATE ROLE "Management" WITH

   LOGIN

   SUPERUSER

   CREATEDB

   CREATEROLE

   INHERIT

   NOREPLICATION

   CONNECTION LIMIT -1

   PASSWORD 'Riesenhotter44';

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

CREATE DATABASE "Apache"

    WITH

    OWNER = "Management"

    ENCODING = 'UTF8'

    CONNECTION LIMIT = -1

    IS_TEMPLATE = False;

Два замечания относительно скрипта CREATE DATABASE:

1. OWNER - Нам всегда нужно определить владение базой данных, приписанное роли (с или без логина).


2. IS_TEMPLATE - В PostgreSQL мы можем определить базу данных в качестве шаблона для использования при создании других баз данных. В данном случае это не шаблонная база данных.

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



Мы получим отказ в разрешении.

Заключение

В этой статье мы рассмотрели некоторые роли и способы присвоить их пользователям/логинам в SQL Server, Oracle и PostgreSQL. Мы также отметили различие в концепции ролей и некоторые из новых предопределенных ролей в SQL Server 2022. В следующей части статьи мы глубже погрузимся в оператор GRANT и способы предоставления привилегий на схемы, а не только таблицы, наследование, а также те различные системные представления, которые могут помочь управлять всеми этими привилегиями.

Статьи по теме

1. Безопасность SQL Server - модель безопасности с использованием определяемых пользователем ролей.

2. Основы PostgreSQL: роли и привилегии

3. Создание схемы SQL для организации объектов базы данных, предоставления разрешений и упрощения обслуживания