Introdução, administração de índices!

Índices

Bom galera, venho aqui introduzir sobre um assunto que certamente é bem conhecido e familiarizado por vocês, índices.

Basicamente sempre aprendemos a criar uma Primary Key que geralmente é o índice clusterizado e indexamos as outras colunas que são usadas como filtro. Ex.: Nome, CPF e com isso a sua consulta vai ficando cada vez mais performática diminuindo o tempo de resposta.

Acontece que com o tempo a tendência de qualquer banco de dados é crescer e o índice que você criou anteriormente para agilizar as suas consulta já não está mais tão performático assim. O tempo de resposta começa a crescer, com isso cresce os incidentes de bloqueios e a situação vai ficando crítica.

E agora para vocês qual seria a solução?

Para muitos aqui a resposta é obvia: “Desfragmentação dos índices”, mas acreditem nem muitos pensam assim.

Em bancos de dados “administrados” por programadores a solução que me foi dada foi o particionamento de tabelas. Poxa! Vamos pensar… Nessa situação em que nem um simples índice está sendo administrado corretamente, você realmente acredita que a solução é particionar as tabelas? Acho que não em. Há várias coisas que deveremos fazer antes de pensar em particionar as tabelas.

Já uma resposta vindo da maioria dos DBA’s é a desfragmentação dos índices. Comandos do tipo INSERT, UPDATE e DELETE na maioria dos casos fragmentam os índices fazendo com que os mesmo já não sejam tão performáticos assim. Porém esse é um caso fácil de se resolver, podemos encontrar várias soluções para esses “probleminhas” na internet, com um simples ALTER INDEX estará tudo resolvido! Segue um comando que muitos usam para realizar a desfragmentação, um comando dinâmico e fácil de ser implementado.

http://technet.microsoft.com/en-us/library/bb838727(v=office.12).aspx

Essa solução funciona, mas deixo aqui para vocês pensarem… É tão simples assim de manter um índice? É só criá-lo, e desfragmentá-lo? Quando o seu banco é muito grande essa continua sendo a solução? Vamos pensando que daqui alguns dias posto o quanto um índice pode te dar dor de cabeça rsrsrs.

Enquanto eu não posto o lado negativo disso tudo, coloco aqui um post muito interessante do Gustavo Maia que explica bem direitinho o funcionamento de um índice:

http://gustavomaiaaguiar.wordpress.com/2012/12/30/introduo-aos-ndices-em-bancos-de-dados-e-no-sql-server/

Até a próxima 😀

SQL Injection, como o DBA pode se proteger?

Depois da empolgante (e até um pouco aterrorizante) palestra “Invadindo o SQL Server? DBA Vs. Hacker” apresentada pelo Luan Moreno (blog|twitter) e pelo Lenon Leite (twitter) no XIX encontro do grupo SQLServerDF, me senti quase obrigado a falar sobre segurança. Logo, neste post irei falar sobre algumas dicas e formas para o DBA se prevenir de um possível ataque através de uma SQL Injection.

No entanto, antes de qualquer coisa, o que é SQL Injection? SQL Injection, ou Injeção de SQL, é uma técnica de inserção de código malicioso que explora vulnerabilidades na segurança da aplicação ou da base de dados. Geralmente esses ataques ocorrem quando as entradas de dados dos usuários não são devidamente tratadas pela aplicação ou quando a consulta é construída dinamicamente, mas é claro que os ataques não são limitados apenas a essas formas.

SQL dinâmico é um código ou comando que é executado dinamicamente (Duh?!). Em outras palavras, é quando se concatena linhas de código com entradas do usuário para montar um comando SQL “na hora”, ou, dinamicamente. Codificar utilizando o SQL dinâmico traz grande flexibilidade e, quando bem aplicado, pode gerar planos de execução mais eficientes. Mas, como nem tudo na vida são flores, a manutenção de códigos com SQL dinâmico é bem mais complicada e esse código, quando utilizado indevidamente, pode gerar brechas enormes na segurança do banco de dados e uma baixíssima taxa de reutilização de planos de execução. Sem prolongar mais esta introdução, vamos demonstrar como pode acontecer um ataque por SQL Injection.

Vamos supor que uma locadora de filmes empresa que oferece serviços de TV pela internet tem um sistema de busca de filmes no seu catálogo. Nesse sistema o usuário pode procurar o filme por título, diretor ou gênero. Agora, vamos supor que esta busca é feita por uma stored procedure como a abaixo.

CREATE PROCEDURE [dbo].[RetornaFilme](
	@Titulo  VARCHAR(MAX) = NULL,
	@Diretor VARCHAR(MAX) = NULL,
	@Genero  VARCHAR(MAX) = NULL)
AS

DECLARE @sql VARCHAR(MAX);

SET @sql =      'SELECT [Titulo],[Diretor],[Genero] '+
                'FROM [dbo].[Filmes] '+
                'WHERE (1=1) ';

IF(@Titulo IS NOT NULL)
SET @sql = @sql + 'AND [Titulo] LIKE '''+@Titulo+'''';

IF(@Diretor IS NOT NULL)
SET @sql = @sql + 'AND [Diretor] LIKE '''+@Diretor+'''';

IF(@Genero IS NOT NULL)
SET @sql = @sql + 'AND [Genero] LIKE '''+@Genero+'''';

EXEC(@sql)
GO

Esse tipo de consulta acaba sendo bem comum quando utilizamos filtros dinâmicos. Vale ressaltar que esta SP não segue boas práticas de programação. Em breve ficarão evidentes os motivos.

Então, supondo que estejamos atrás de filmes de aventura, basta executarmos nossa busca.

RetornaFilme_01

Tudo dentro do esperado! Agora que vemos que ela funciona, que tal vermos se aquele filme Rock ‘n’ Rolla está no catálogo?

RetornaFilme_02

Com esta mensagem de erro, evidenciamos uma brecha na segurança em nossa SP. “Mas… o que deu errado?” Um dos problemas aqui é o tratamento (ou falta de) da entrada do usuário. Se imprimirmos o comando que está sendo executado, temos o seguinte.

SELECT [Titulo],[Diretor],[Genero]
FROM [dbo].[Filmes]
WHERE (1=1) AND [Titulo] LIKE 'Rock 'n' Rolla';

Aqui fica claro que a letra “n” ficou fora da string. Isso já foi o suficiente para gerar um erro que demonstra que esta SP não trata nomes que contenham aspas simples. Através deste tipo brechas que hackers vão inserir trechos de código malicioso. Eis um exemplo de código malicioso para obter mais informação que o esperado.

RetornaFilme_03

“O que é esse result… Essas são as tabelas no banco e suas colunas?!” Exatamente. Uma relação de todas as tabelas criadas e suas colunas. Depois de algumas tentativas e um pouco de conhecimento em banco de dados – e pode ter certeza que hackers tem tanto tempo quanto o google à disposição deles – um usuário malicioso poderia entrar com esse parâmetro e voilà. Agora ele já sabe o que tem dentro da sua base de dados. Agora basta ajustar novamente o parâmetro e…

RetornaFilme_04

Neste momento o sorriso está estampado na cara do hacker, afinal de contas, agora ele tem todos os dados de todos os usuários cadastrados nesta empresa. Quanto será que isso deve valer para a empresa concorrente? Bastante, imagino. Mas vamos ver o que ocorreu com essa SP para que houvesse esta brecha. Imprimindo o parâmetro “@sql” para este último caso temos:

SELECT [Titulo],[Diretor],[Genero]
FROM [dbo].[Filmes] WHERE (1=1) AND [Titulo] LIKE 'a'
UNION
SELECT nome,email,conta_bancaria
FROM Usuario --'

Pelo fato da execução do código ser baseada na concatenação de strings, observa-se que o trecho de código malicioso fecha a busca por título – com a’ – e então utiliza o operador UNION para trazer informações adicionais que neste caso são bem sensíveis. Depois ele utiliza o comentário de linha “- -“ para anular qualquer código posterior.

Outro exemplo mais agressivo de ataque é quando o código malicioso tenta alterar ou apagar algo dentro da sua base ou até em seu servidor. O objetivo pode ser apagar o catálogo de filmes, os registros dos usuários ou só dificultar a sua vida como DBA.

EXEC [dbo].[RetornaFilme] @Titulo = 'a''; DROP TABLE Filmes; --';

Mas é claro que sempre é possível piorar. Então, aviso antes mesmo de colocar o comando abaixo: Não execute o código abaixo em lugar nenhum (Não, nem mesmo assim). A SP xp_cmdshell é capaz de executar comandos em shell como se estivesse no command prompt do Windows. Dependendo do nível de permissão do usuário que executa esta SP, ele pode fazer o que quiser com a máquina. Então, depois de avisar que você não deve brincar com essa SP, eis a possível injeção de SQL.

EXEC [dbo].[RetornaFilme]
@Titulo = 'a''; EXEC master.dbo.xp_cmdshell ''format d:''--';

Calma. A SP xp_cmdshell é desativada por padrão a partir do SQL Server 2005, mas se o invasor conseguir privilégios de sysadmin ele pode mudar isso (Aí sim, já era). No entanto, apesar do potencial destrutivo desta SP, é comum encontrá-la ativada para usos administrativos – o que também não é considerado uma boa prática de segurança.

Como vimos, a utilização de SQL dinâmico pode gerar uma brecha enorme na segurança e nosso objetivo neste artigo é saber como reduzir essa superfície de ataque ao máximo para evitarmos uma injeção de SQL. Então vejamos algumas medidas que podemos tomar!

1.Dimensione e determine as variáveis apenas de acordo com o necessário. Variáveis fortemente tipadas diminuem a liberdade do invasor ao inserir código malicioso. No nosso caso, o tipo VARCHAR(MAX) dá liberdade total para o ataque.

2.Valide toda e qualquer entrada do usuário. Aproveitando até os tipos bem definidos, utilize funções de verificação. Se o tipo deve ser numérico, use ISNUMERIC(). Se for data, ISDATE(), e assim por diante. Em caso de strings, utilize REPLACE() para substituir aspas simples por aspas duplas.

3.Utilize prints para debugar o código. Ao acrescentar uma forma de debugar o resultado da concatenação de strings, vemos com muito mais clareza possíveis brechas no código. Além disso, fica muito mais fácil realizar uma manutenção!

4.Dê o mínimo de permissão ao usuário que executar a SP. Aqui é mais um controle de danos. Pois, por mais que o invasor consiga entrar com algum código malicioso, ele não conseguirá fazer muito estrago.

Então se ajustarmos nossa consulta de acordo com essas premissas, teríamos algo como o código abaixo.

CREATE PROCEDURE [dbo].[RetornaFilme](
	@Titulo  VARCHAR(100) = NULL,
	@Diretor VARCHAR(50)  = NULL,
	@Genero  VARCHAR(20)  = NULL,
	@Debug   BIT          = 1)
AS

DECLARE @sql VARCHAR(MAX);

SET @sql =      'SELECT [Titulo],[Diretor],[Genero] '+
                'FROM [dbo].[Filmes] '+
                'WHERE (1=1) ';

IF(@Titulo IS NOT NULL)
SET @sql = @sql + 'AND [Titulo] LIKE '''+REPLACE(@Titulo,'''','''''')+'''';

IF(@Diretor IS NOT NULL)
SET @sql = @sql + 'AND [Diretor] LIKE '''+REPLACE(@Diretor,'''','''''')+'''';

IF(@Genero IS NOT NULL)
SET @sql = @sql + 'AND [Genero] LIKE '''+REPLACE(@Genero,'''','''''')+'''';

IF(@Debug = 1) PRINT @sql;
EXEC(@sql) AS USER = 'usuario_acesso_minimo';
GO

Desta forma diminuímos consideravelmente a superfície de ataque do invasor. No entanto, ainda estamos gerando uma baixíssima taxa de reutilização dos planos de execução. Afinal de contas, somente se os parâmetros usados forem idênticos o plano de execução poderá ser reutilizado. Então vamos para a última – e talvez mais importante – dica:

1.Sempre que possível, utilize sp_executesql ao invés de EXEC(). A stored procedure sp_executesql, por ser parametrizada, facilita a reutilização dos planos de execução e, como iremos ver, acaba blindando as entradas do usuário dentro das variáveis.

Reformulando nossa consulta para utilizar a sp_executesql, temos o código a seguir.

CREATE PROCEDURE [dbo].[RetornaFilme](
	@Titulo  VARCHAR(100) = NULL,
	@Diretor VARCHAR(50)  = NULL,
	@Genero  VARCHAR(20)  = NULL,
	@Debug   BIT          = 1)
AS

DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);

SET @sql =  'SELECT [Titulo],[Diretor],[Genero] '+
            'FROM [dbo].[Filmes] '+
            'WHERE (1=1) ';

SET @params = '@Titulo  VARCHAR(100) = NULL,'+
              '@Diretor VARCHAR(50)  = NULL,'+
              '@Genero  VARCHAR(20)  = NULL';

IF(@Titulo IS NOT NULL)
SET @sql = @sql + 'AND [Titulo] LIKE @Titulo ';

IF(@Diretor IS NOT NULL)
SET @sql = @sql + 'AND [Diretor] LIKE @Diretor ';

IF(@Genero IS NOT NULL)
SET @sql = @sql + 'AND [Genero] LIKE @Genero ';

IF(@Debug = 1) PRINT @sql;
EXEC sp_executesql @sql, @params, @Titulo, @Diretor, @Genero
GO

Pronto! Além de termos um código mais limpo e simples, nossos planos de execução serão reutilizados com muito mais frequência graças à parametrização que agora é possível!

Neste artigo, vimos como o DBA pode se proteger melhor de uma SQL Injection e digo “se proteger melhor” pois, é muito difícil – se não impossível – se proteger 100% contra esse tipo de ataque. No entanto, diminuímos a superfície de ataque de possíveis invasores e ainda parametrizamos nossa consulta para uma maior reutilização dos planos de execução. Mas, como esse tópico me deixou com mais vontade de ler e estudar sobre o assunto, deixo aqui alguns lugares para que você continue sua leitura.

1.Consultas parametrizadas, ISNULL e SQL dinâmica, um post do Gustavo Maia Aguiar que explora um pouco mais sobre implementações de consultas parametrizadas e seus efeitos nos planos de execução.

2.The Curse and Blessings of Dynamic SQL, um artigo excelente do Erland Sommarskog que menciona SQL Injection, boas práticas quando utilizar SQL dinâmico e casos comuns de quando não se utilizar SQL dinâmico.

3.Inside Microsoft SQL Server 2008: T-SQL Programming, livro de Itzik Ben-Gan e companhia que considero leitura obrigatória para qualquer DBA/AD/Desenvolvedor que trabalhe com T-SQL.

Atachar/Recuperar arquivos MDF sem o LDF

Quem já precisou atachar ou recuperar um arquivo MDF sem o LDF? Pode ser em diversos casos, mesmo sendo um “Backup” de um cliente que ele enviou para você analisar algum erro no seu banco ou até mesmo em algum ambiente (Desenvolvimento. Obs.: não executar em produção, pois esse método perde a integridade dos dados) que por “engano” apagaram o LDF (para que você consiga apagar o arquivo LDF, a base deverá estar offline). Como em um ambiente de desenvolvimento, o desenvolvedor é o “dono” do ambiente, ele pode (e faz) tudo.

Um caso que peguei foi o seguinte:

  • De tempos em tempos, o desenvolvedor faz uma limpa no ambiente para liberar espaço;
  • Coloca a base offline e espera alguém se manifestar;
  • Caso ninguém se manifeste a base é excluída. Como a base estava offline os arquivos mdf e ldf continuam no disco até que alguém vai e os deletam diretamente;
  • Caso alguém se manifeste, a base é colocada online novamente.

Um belo dia uma pessoa reclamou que não estava conseguindo acessar a base dela. O desenvolvedor rapidamente deu o comando para a base ficar online novamente, porém estava dando erro… O desenvolvedor entrou em contato com a equipe de DBA para verificar o motivo pelo qual a base não ficava online e em uma análise mais profunda foi identificado que o arquivo LDF não existia mais. E agora, perdemos a base? Não! Vamos desatachar a base e “brincar” um pouco.

Aqui iremos realizar alguns processos simples para atachar o arquivo. Temos três opções, qualquer uma pode ser realizada.

  1. Attach arquivo MDF:
USE [master]
GO
-- Método 1:
EXEC sp_attach_single_file_db @dbname= TestDb,
@physname=N'E:\Data1\TestDb.mdf'
GO
-- Método 2:
CREATE DATABASE TestDb ON
(FILENAME = N'E:\Data1\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO
-- Método 3:
CREATE DATABASE TestDb ON
(FILENAME =N'E:\Data1\TestDb.mdf')


Todos os métodos anteriores funcionam quando o arquivo primário aceita escrita. Caso o banco de dados, antes de ser colocado como offline, estivesse com o status de read_only o SQL Server retornará o seguinte erro:

“Log file ‘F:\Log1\TestDb.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.The log cannot be rebuilt when the primary file is read-only.”

Como podemos observar, o arquivo primário não aceita escrita e com isso o log não pode ser recriado. Quando ocorre esse cenário, os seguintes métodos podem ser tentados.

  1. Recuperando um arquivo MDF quando o mesmo está como read_only.

 

2.1.  Criar um banco de dados “falso”, contendo o mesmo nome tanto do banco quanto dos arquivos. Os arquivos de dados e LOG deverão ser criados no mesmo caminho do banco original.

CREATE DATABASE TestDb

ON

(NAME = TestDb_dat,

FILENAME = 'E:\Data1\TestDb.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

(NAME = TestDb_log,

FILENAME = 'F:\Log1\TestDb.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB ) ;

2.2.  Apontar o novo banco de dados para read_only e em seguida para offline:

alter database TestDb set read_only

alter database TestDb set offline

2.3.  Substituir o arquivo MDF “falso” pelo original.

2.4.  Apontar a base como online:

alter database TestDb set online

O SQL Server retornará o seguinte erro:

“Msg 5173, Level 16, State 1, Line 1

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

Log file ‘F:\Log1\TestDb.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

The log cannot be rebuilt when the primary file is read-only.

Msg 945, Level 14, State 2, Line 1

Database ‘TestDb cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.”

2.5.  Apontar a base como read_write:

alter database TestDb set read_write

O SQL Server retornará o seguinte erro:

“Msg 5173, Level 16, State 1, Line 1

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

Log file ‘F:\Log3\TestDb.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

Msg 945, Level 14, State 2, Line 1

Database ‘TestDb’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.”

Como podemos observar pelas mensagens, o arquivo de LOG ainda não foi incluído na base e para que a mesma fique online é necessitando recriá-lo.

  1. Recriando o arquivo de LOG.

Para recriar o arquivo de log, deveremos saber o nome do banco, nome do arquivo e o caminho do arquivo.

3.1.  Mudar o status do banco para emergency:

alter database TestDb set emergency

3.2.  Mudar o status do banco para single_user:

alter database TestDb set single_user

3.3.  Excluir o arquivo de Log do banco “falso”;

3.4.  Recriar o LOG:

alter database TestDb rebuild log on

(Name= TestDb_log,filename='F:\Log1\TestDb.ldf')

3.5.  Mudar o status do bando para online:

alter database TestDb set online

O SQL Server retornará a seguinte mensagem:

“The Service Broker in database “TestDb” will be disabled because the Service Broker GUID in the database (50132F87-D4CE-4A8B-9C3A-EB50D6F7C707) does not match the one in sys.databases (D1B68542-A698-4EDE-9B1D-8DF13611D7E1).

Warning: The log for database ‘TestDb’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.”

3.6.  Mudar o status do banco para multi_user:

alter database TestDb set MULTI_USER

Aqui felizmente conseguimos restaurar a base com “sucesso”. Digo “sucesso”, pois obviamente os dados que estavam no arquivo de log foram perdidos.

Enfim, como podemos observar, esse método não é recomendado realizar em produção, pois dados podem ser perdidos. Caso isso ocorra em produção, realmente espero que haja outros tipos de recovery (replicação de storage, backup…).

Referências

 

  1. http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/
  2. http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/