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

SQL Server 2000

В SQL Server 2000 аудит разрешений сервера и ролей сервера можно выполнить, выполнив запрос к системной хранимой процедуре sp_helpsrvrolemember. Эта хранимая процедура предоставляет информацию о членстве во встроенных ролях сервера. Чтобы получить информацию для всех ролей сервера, вы можете просто выполнить хранимую процедуру без параметров:

EXEC sp_helpsrvrolemember;

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

CREATE TABLE #ServerRoles (
  ServerRole VARCHAR(20),
  MemberName sysname,
  sid VARBINARY(85)
);

INSERT INTO #ServerRoles
(ServerRole, MemberName, sid)
EXEC sp_helpsrvrolemember;

SELECT SL.name, SR.ServerRole
FROM syslogins SL
  JOIN #ServerRoles SR
    ON SL.sid = SR.sid
ORDER BY SL.name, SR.ServerRole;

DROP TABLE #ServerRoles;

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

SQL Server 2005 и более поздние версии

Начиная с SQL Server 2005, была введена новая модель безопасности, в которой разрешения могут быть назначены для объектов безопасности. Для аудита безопасности в SQL Server 2005 и более поздних версиях нам необходимо более подробно изучить представления каталога sys.server_principals, sys.server_permissions и sys.server_role_members.

Чтобы получить членов ролей сервера, мы можем объединить представления sys.server_principals и sys.server_role_members:

SELECT SP1.[name] AS 'Login', SP2.[name] AS 'ServerRole'
FROM sys.server_principals SP1
  JOIN sys.server_role_members SRM
    ON SP1.principal_id = SRM.member_principal_id
  JOIN sys.server_principals SP2
    ON SRM.role_principal_id = SP2.principal_id
ORDER BY SP1.[name], SP2.[name];

Чтобы получить разрешения, мы можем использовать представления sys.server_principals и sys.server_permissions:

SELECT SP.[name] AS 'Login', SPerm.state_desc + ' ' + SPerm.permission_name AS 'ServerPermission'
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
ORDER BY [Login], [ServerPermission];

Если вам не интересно разрешение CONNECT SQL, вы можете отфильтровать его с помощью соответствующего оператора WHERE.

Чтобы сгенерировать единый отчет с членами ролей сервера и разрешениями, мы можем объединить два запроса с помощью UNION ALL:

SELECT SP1.[name] AS 'Login', 'Role: ' + SP2.[name] COLLATE DATABASE_DEFAULT AS 'ServerPermission'
FROM sys.server_principals SP1
  JOIN sys.server_role_members SRM
    ON SP1.principal_id = SRM.member_principal_id
  JOIN sys.server_principals SP2
    ON SRM.role_principal_id = SP2.principal_id
UNION ALL
SELECT SP.[name] AS 'Login', SPerm.state_desc + ' ' + SPerm.permission_name COLLATE DATABASE_DEFAULT AS 'ServerPermission'
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
ORDER BY [Login], [ServerPermission];

Выполнив этот запрос, вы сможете успешно просмотреть все разрешения на уровне сервера в SQL Server 2005 и более поздних версиях.

Важно отметить использование COLLATE DATABASE_DEFAULT в SELECT-запросе для каждого второго столбца. Это необходимо для предотвращения конфликтов сортировки в операторе ORDER BY.

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