- Auditoria no SQL Server (Server Audit)
- Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
- Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
- SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
- Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
- Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
- SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
- SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
- SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
- SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
- SQL Server – Trigger para prevenir e impedir alterações em tabelas
- SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
- SQL Server – Como evitar ataques de força bruta no seu banco de dados
- SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
- SQL Server – Como saber a data do último login de um usuário
- SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
- SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
- SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
- SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
- SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
- SQL Server – Como criar uma auditoria de logins utilizando os logs da instância
Fala pessoal!
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 |
Resultado:
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.
Observação: 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!
Portuguese 
English
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
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
)
Muito bom Dirceu, obrigado por compartilhar.
Já até estou utilizando onde trabalho hehehehehe
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.