SQL Server - Como utilizar auditoria para mapear permissões necessárias reais em um usuário

Post Views 3,128 views

Neste artigo

Esse post é a parte 10 de 21 da série Security and Auditing

Reading time 6 minutes

Hey guys!
Nesse artigo, vou demonstrar como utilizar auditoria para mapear permissões necessárias reais um usuário, identificando tudo o que esse usuário fez efetivamente no banco de dados durante o tempo observado, para gerar um script concedendo apenas as permissões que ele realmente precisa, eliminando assim, a necessidade de ter usuários de integrações e outros usuários, que não o principal da aplicação, com permissões em todos os objetos do banco, como db_owner, db_datareader e/ou db_datawriter, por exemplo.

Durante o atendimento a clientes, é muito comum presenciar situações onde usuários de integrações entre sistemas ou processos são criados no banco de dados exclusivamente para esse processo (boa prática), mas mesmo precisando acessar/alterar poucas tabelas, eles acabam recebendo permissões em todos os objetos desse database. Ainda mais em usuários de integração, que nem sempre são desenvolvidos pela equipe interna e em muitos casos, o sistema desses usuários de integração não está nem na responsabilidade do time de TI local e podem ser acessados via internet, por exemplo, uma brecha nesse sistema externo pode acabar sendo catastrófico para a sua empresa.

Com o objetivo de mitigar esse problema, vou demonstrar a vocês como logar tudo o que esses usuários fazem no banco de dados e permitir que vocês apliquem somente as permissões necessárias. Para essa necessidade, vou utilizar o recurso de Server Audit, disponível desde o SQL Server 2008 na versão Enterprise e a partir do SQL Server 2012 na versão Standard. Também vou utilizar o recurso Database Audit, disponível na versão Standard apenas a partir do SQL Server 2016 SP1.

Em resumo, para conseguir executar os scripts desse artigo, você precisará atender uma das condições abaixo:

SQL Server 2012 ou acima, edição Enterprise, Datacenter (2008) ou Developer
SQL Server 2016 SP1 ou acima (qualquer edição, até Express)

Criando a tabela para armazenar o histórico de acessos

Após a introdução acima, vamos agora começar a monitorar os acessos às tabelas. Antes de mais nada, vou criar a tabela que vai armazenar o histórico dos acessos coletados pela auditoria.

CREATE TABLE [dbo].[Auditoria_Acesso]
(
    [Id_Auditoria] [bigint] NOT NULL IDENTITY(1, 1),
    [Dt_Auditoria] [datetime] NOT NULL,
    [Cd_Acao] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Maquina] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Usuario] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Database] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Schema] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Objeto] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Query] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    [Fl_Sucesso] [bit] NOT NULL,
    [Ds_IP] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Ds_Programa] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
    [Qt_Duracao] [bigint] NOT NULL,
    [Qt_Linhas_Retornadas] [bigint] NOT NULL,
    [Qt_Linhas_Alteradas] [bigint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
WITH
(
DATA_COMPRESSION = PAGE
)
GO
ALTER TABLE [dbo].[Auditoria_Acesso] ADD CONSTRAINT [PK__Auditori__E9F1DAD4EE3743FE] PRIMARY KEY CLUSTERED ([Id_Auditoria]) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Auditoria_Acesso]

(

[Id_Auditoria] [bigint] NOT NULL IDENTITY(1, 1),

[Dt_Auditoria] [datetime] NOT NULL,

[Cd_Acao] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[Ds_Maquina] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[Ds_Usuario] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[Ds_Database] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[Ds_Schema] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[Ds_Objeto] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[Ds_Query] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,

[Fl_Sucesso] [bit] NOT NULL,

[Ds_IP] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[Ds_Programa] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[Qt_Duracao] [bigint] NOT NULL,

[Qt_Linhas_Retornadas] [bigint] NOT NULL,

[Qt_Linhas_Alteradas] [bigint] NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

WITH

(

DATA_COMPRESSION = PAGE

)

ALTER TABLE [dbo].[Auditoria_Acesso] ADD CONSTRAINT [PK__Auditori__E9F1DAD4EE3743FE] PRIMARY KEY CLUSTERED ([Id_Auditoria]) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]

Criando a Server Audit filtrando os usuários

O segundo passo para conseguir auditar os acessos realizados por determinados usuários, vamos criar um server audit na instância que será utilizado no próximo tópico para capturar esses eventos. Vale lembrar que o recurso de Server Audit está disponível desde o SQL Server 2008 na versão Enterprise e a partir do SQL Server 2012 na versão Standard. Além disso, só é possível utilizar filtros no Server Audit a partir do SQL Server 2012.

No exemplo abaixo, estou definindo que a auditoria irá criar até 16 arquivos de 10 MB cada. A medida que os arquivos vão sendo preenchidos, novos arquivos com os dados coletados serão criados até atingir o limite estabelecido (16 arquivos). Quando todos os arquivos já tiverem sido criados e já estão totalmente preenchidos, aí os arquivos mais antigos começam a ser sobrescritos com as novas informações. Por este motivo, é importante ter uma rotina para coletar dados dos arquivos e armazenar em tabelas.

Além disso, a partir do SQL Server 2012, podemos aplicar diversos filtros para refinar nossas buscas, como capturar os dados dos usuários que terminem com ‘%User’ ou que começem com LS_% e também ignorar o Intellisense do SSMS e ferramentas da RedGate, além de não coletar dados de usuários que estejam no domínio “MEUDOMINIO” e também não coletar dados do usuário “usrDirceuResende”.

Nessa etapa que podemos filtrar os nomes de usuários, softwares, IP’s ou Hostnames que serão utilizados para refinar as pesquisas para retornar apenas os dados desejados.

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_audits WHERE [name] = 'Auditoria_Acessos') > 0)
BEGIN
    ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = OFF);
    DROP SERVER AUDIT [Auditoria_Acessos]
END


CREATE SERVER AUDIT [Auditoria_Acessos]
TO FILE
(	
    FILEPATH = N'C:\Audit\',
    MAXSIZE = 10 MB,
    MAX_ROLLOVER_FILES = 16,
    RESERVE_DISK_SPACE = OFF
)
WITH
(	
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE,
    AUDIT_GUID = '0b5ad307-ee47-43db-a169-9af67cb661f9'
)
WHERE (([server_principal_name] LIKE '%User' OR [server_principal_name] LIKE 'LS_%') AND [application_name]<>'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' AND NOT [application_name] LIKE 'Red Gate Software%')
GO


ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = ON)
GO

USE [master]

IF ((SELECT COUNT(*) FROM sys.server_audits WHERE [name] = 'Auditoria_Acessos') > 0)

BEGIN

ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = OFF);

DROP SERVER AUDIT [Auditoria_Acessos]

END

CREATE SERVER AUDIT [Auditoria_Acessos]

TO FILE

(

FILEPATH = N'C:\Audit\',

MAXSIZE = 10 MB,

MAX_ROLLOVER_FILES = 16,

RESERVE_DISK_SPACE = OFF

)

WITH

(

QUEUE_DELAY = 1000,

ON_FAILURE = CONTINUE,

AUDIT_GUID = '0b5ad307-ee47-43db-a169-9af67cb661f9'

)

WHERE (([server_principal_name] LIKE '%User' OR [server_principal_name] LIKE 'LS_%') AND [application_name]<>'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' AND NOT [application_name] LIKE 'Red Gate Software%')

ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = ON)

Criando a Database Audit capturando os acessos

No terceiro passo para montar nosso monitoramento, vamos agora criar uma database audit para cada banco de dados que você queira monitorar as permissões. No exemplo abaixo, vou monitorar os eventos de INSERT/DELETE/UPDATE/SELECT/EXECUTE realizados por qualquer usuário do banco (public).

Vale lembrar que, embora a Database Audit pareça capturar os eventos de todos os usuários, na Server Audit nós já limitamos os usuários que devem ser retornados e esse filtro é respeitado também no Database Audit.

O código abaixo vai percorrer todos os databases que não estejam na lista de exceções (master, tempdb, msdb e model) e vai criar a database audit.

DECLARE @Query VARCHAR(MAX)
SET @Query = '

IF (''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb''))
BEGIN

    USE [?];

    IF ((SELECT COUNT(*) FROM sys.database_audit_specifications WHERE [name] = ''Auditoria_Acessos'') > 0)
    BEGIN

        ALTER DATABASE AUDIT SPECIFICATION [Auditoria_Acessos] WITH (STATE = OFF);
        DROP DATABASE AUDIT SPECIFICATION [Auditoria_Acessos];

    END

    CREATE DATABASE AUDIT SPECIFICATION [Auditoria_Acessos]
    FOR SERVER AUDIT [Auditoria_Acessos]
    ADD (DELETE ON DATABASE::[?] BY [public]),
    ADD (EXECUTE ON DATABASE::[?] BY [public]),
    ADD (INSERT ON DATABASE::[?] BY [public]),
    ADD (SELECT ON DATABASE::[?] BY [public]),
    ADD (UPDATE ON DATABASE::[?] BY [public])
    WITH (STATE = ON);
    
END'


EXEC sys.sp_MSforeachdb @Query

DECLARE @Query VARCHAR(MAX)

SET @Query = '

IF (''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb''))

BEGIN

USE [?];

IF ((SELECT COUNT(*) FROM sys.database_audit_specifications WHERE [name] = ''Auditoria_Acessos'') > 0)

BEGIN

ALTER DATABASE AUDIT SPECIFICATION [Auditoria_Acessos] WITH (STATE = OFF);

DROP DATABASE AUDIT SPECIFICATION [Auditoria_Acessos];

END

CREATE DATABASE AUDIT SPECIFICATION [Auditoria_Acessos]

FOR SERVER AUDIT [Auditoria_Acessos]

ADD (DELETE ON DATABASE::[?] BY [public]),

ADD (EXECUTE ON DATABASE::[?] BY [public]),

ADD (INSERT ON DATABASE::[?] BY [public]),

ADD (SELECT ON DATABASE::[?] BY [public]),

ADD (UPDATE ON DATABASE::[?] BY [public])

WITH (STATE = ON);

END'

EXEC sys.sp_MSforeachdb @Query

Criando a Stored Procedure para armazenar os dados coletados

No 4º passo da rotina, vamos criar a Stored Procedure responsável por ler os dados dos arquivos de auditoria e grava os dados na tabela criada no passo 1. Lembre-se de criar um job no SQL Agent para executar a procedure abaixo a cada X minutos.

IF (OBJECT_ID('dbo.stpAuditoria_Acessos_Carrega_Dados') IS NULL) EXEC('CREATE PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados AS SELECT 1')
GO

ALTER PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados
AS
BEGIN

    DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())
    DECLARE @Dt_Max DATETIME = DATEADD(SECOND, 1, ISNULL((SELECT MAX(Dt_Auditoria) FROM dirceuresende..Auditoria_Acesso), '1900-01-01'))

    INSERT INTO dirceuresende.dbo.Auditoria_Acesso
    (
        Dt_Auditoria,
        Cd_Acao,
        Ds_Maquina,
        Ds_Usuario,
        Ds_Database,
        Ds_Schema,
        Ds_Objeto,
        Ds_Query
        Fl_Sucesso,
        Ds_IP,
        Ds_Programa,
        Qt_Duracao,
        Qt_Linhas_Retornadas,
        Qt_Linhas_Alteradas
    )
    SELECT DISTINCT
        DATEADD(HOUR, @TimeZone, event_time) AS event_time,
        action_id,
        server_instance_name,
        server_principal_name,
        [database_name],
        [schema_name],
        [object_name],
        [statement],
        succeeded,
        client_ip,
        application_name,
        duration_milliseconds,
        response_rows,
        affected_rows
    FROM 
        sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
    WHERE 
        DATEADD(HOUR, @TimeZone, event_time) >= @Dt_Max

END

IF (OBJECT_ID('dbo.stpAuditoria_Acessos_Carrega_Dados') IS NULL) EXEC('CREATE PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados AS SELECT 1')

ALTER PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados

BEGIN

DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())

DECLARE @Dt_Max DATETIME = DATEADD(SECOND, 1, ISNULL((SELECT MAX(Dt_Auditoria) FROM dirceuresende..Auditoria_Acesso), '1900-01-01'))

INSERT INTO dirceuresende.dbo.Auditoria_Acesso

(

Dt_Auditoria,

Cd_Acao,

Ds_Maquina,

Ds_Usuario,

Ds_Database,

Ds_Schema,

Ds_Objeto,

Ds_Query

Fl_Sucesso,

Ds_IP,

Ds_Programa,

Qt_Duracao,

Qt_Linhas_Retornadas,

Qt_Linhas_Alteradas

)

SELECT DISTINCT

DATEADD(HOUR, @TimeZone, event_time) AS event_time,

action_id,

server_instance_name,

server_principal_name,

[database_name],

[schema_name],

[object_name],

[statement],

succeeded,

client_ip,

application_name,

duration_milliseconds,

response_rows,

affected_rows

FROM

sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT)

WHERE

DATEADD(HOUR, @TimeZone, event_time) >= @Dt_Max

END

Consultando os dados coletados

E por fim, no 5º passo dessa rotina, vamos ler os dados coletados e armazenados na tabela dirceuresende.dbo.Auditoria_Acesso e vamos identificar os acessos realizados por cada usuário em cada database. Isso o ajudará a identificar o que esses usuários estão acessando atualmente no banco de dados. Acabou a desculpa para todos os usuários serem db_owner das databases.

Caso você queira também gerar os scripts para conceder as permissões atuais, você pode utilizar esse script:

SELECT DISTINCT 
    Ds_Usuario,
    Ds_Database, 
    Cd_Acao, 
    Ds_Objeto,
    'USE [' + Ds_Database + ']; GRANT ' + (CASE Cd_Acao
        WHEN 'UP' THEN 'UPDATE'
        WHEN 'IN' THEN 'INSERT'
        WHEN 'DL' THEN 'DELETE'
        WHEN 'SL' THEN 'SELECT'
        WHEN 'EX' THEN 'EXECUTE'
    END) + ' ON [' + Ds_Schema + '].[' + Ds_Objeto + '] TO [' + Ds_Usuario + '];' AS Comando 
FROM 
    dirceuresende..Auditoria_Acesso 
WHERE 
    Cd_Acao <> 'UNDO'
ORDER BY
    Ds_Usuario,
    Ds_Database,
    Ds_Objeto

SELECT DISTINCT

Ds_Usuario,

Ds_Database,

Cd_Acao,

Ds_Objeto,

'USE [' + Ds_Database + ']; GRANT ' + (CASE Cd_Acao

WHEN 'UP' THEN 'UPDATE'

WHEN 'IN' THEN 'INSERT'

WHEN 'DL' THEN 'DELETE'

WHEN 'SL' THEN 'SELECT'

WHEN 'EX' THEN 'EXECUTE'

END) + ' ON [' + Ds_Schema + '].[' + Ds_Objeto + '] TO [' + Ds_Usuario + '];' AS Comando

FROM

dirceuresende..Auditoria_Acesso

WHERE

Cd_Acao <> 'UNDO'

ORDER BY

Ds_Usuario,

Ds_Database,

Ds_Objeto

Result:

Pronto! Rotina de auditoria de acessos implementada. O próximo passo agora, é utilizar os dados coletados para conceder as permissões que os usuários efetivamente utilizaram durante o tempo em que o Audit ficou habilitado e remover permissões elevadas desses usuários, como sysadmin e db_owner.

Observation: Um ponto muito importante que deve ser analisado, é a quantidade de registros que essa tabela irá manter. Em ambientes que possuem muitos usuários simultaneos, essa coleta de dados de acesso pode acabar gerando um volume de dados muito grande e consumindo muito espaço em disco. Por conta disso, é sempre importante acompanhar o volume de dados e criar uam rotina para limpeza da tabela, mantendo apenas X dias de histórico.

Além disso, não preciso nem falar que recomendo que você TESTE bastante antes de aplicar em produção né ? Dependendo da quantidade de transações por segundo, isso pode até gerar um impacto de performance ao ativar essa feature (embora eu já tenha testado em ambientes bem críticos e não tenha percebido nenhuma mudança).

Bom pessoal, espero que tenha ajudado vocês a ter uma forma de validar todos os objetos acessados por determinados usuários, auxiliando vocês no mapeamento das permissões necessárias.
Um grande abraço e até mais!

Cristiano Felipe Cavendish disse:

October 20, 2020 às 10:34

bom dia.
muito legal vai me ajudar muito!
entretanto rodei todos os comandos
criou tudo certinho, mas eu estou forçando insert, updade e quando dou select na tabela Auditoria_Acesso ou executo a procedure stpAuditoria_Acessos_Carrega_Dados

so me retorna vazio as tabelas.. poderia me ajudar?
Grato
Cavendish

Responder
Patrik disse:

April 23, 2020 às 12:57

Excelente…
Está faltando uma vírgula nesse pedação do código

Ds_Objeto,
Ds_Query
Fl_Sucesso,

INSERT INTO dirceuresende.dbo.Auditoria_Acesso
(
Dt_Auditoria,
Cd_Acao,
Ds_Maquina,
Ds_Usuario,
Ds_Database,
Ds_Schema,
Ds_Objeto,
Ds_Query
Fl_Sucesso,
Ds_IP,
Ds_Programa,
Qt_Duracao,
Qt_Linhas_Retornadas,
Qt_Linhas_Alteradas
)

Responder
Tássio Passos Barros Carlini disse:

January 21, 2019 às 12:49

Muito bom Dirceu, obrigado por compartilhar.
Já até estou utilizando onde trabalho hehehehehe

Responder
Junior Galvão - MVP disse:

January 21, 2019 às 09:17

Dirceu, bom dia.

Gostei muito do post, se me permitir, vou utilizar este material em minha aula de banco de dados na Fatec São Roque, na parte de segurança.

Abraços.

Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server - How to use auditing to map actual required permissions on a user

Criando a tabela para armazenar o histórico de acessos

Criando a Server Audit filtrando os usuários

Criando a Database Audit capturando os acessos

Criando a Stored Procedure para armazenar os dados coletados

Consultando os dados coletados

You may also like...

4 Responses

Deixe uma respostaCancelar resposta

Criando a tabela para armazenar o histórico de acessos

Criando a Server Audit filtrando os usuários

Criando a Database Audit capturando os acessos

Criando a Stored Procedure para armazenar os dados coletados

Consultando os dados coletados

You may also like...

SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)

SQL Server - Study Material for the 70-764 Administering a SQL Database Certification Exam

Seu SQL Server está atualizado ? Retornando a última versão do Service Pack (SP) ou Cumulative Update (CU) para a sua instância

4 Responses

Deixe uma respostaCancelar resposta