Menor privilégio possível. Mas nem sempre.

Deixar o usuário ver apenas o que lhe interessa e nada mais

Boa noite =)
Hoje um amigo tinha uma dúvida, que também já tive, sobre visualizações de bases desnecessárias. Achei um bom assunto pra postar, pois gosto da temática segurança e apesar de parecer óbvio, nem sempre o SQL Server oferece um dos seus princípios básicos, o princípio do menor privilégio. Vamos conversar sobre isso?

Bem, nada melhor do que um case pra passar a ideia…

1 – A demanda

Você é o DBA de uma única instância SQL Server chamada NINJACORP.
Existem várias bases confidenciais em tal instância, e você é um DBA vigilante por default, pra combinar com o ambiente. Preza muito pela segurança. E essa é sua filosofia de vida.

Segue a lei da segurança máxima e menor privilégio como se fosse uma rígida religião.

Em um belo dia, com sol brilhando, umidade do ar agradável e pássaros cantando no céu, chega uma demanda pra inclusão de login de um desenvolvedor em produção. Seu coração pulsa de forma desequilibrada quando lê que, por A mais B, será necessário criar um sql login.

O chamado em resumo:

“Solicito a inclusão de login sql chamado servidorPublico, senha p@ssw0rd134#@3_2 e que tenha acesso de leitura ao banco DbInformacoesSagradas”

O DBA, relutante, faz a criação do sql login (e queria mesmo usar o Windows Authentication, mas não teve jeito) e do usuário sem maiores problemas.

–> Cria login do sujeito

CREATE LOGIN servidorPublico WITH PASSWORD = ‘p@ssw0rd134#@3_2’ , CHECK_POLICY = OFF

–> Cria usuário para a base

USE [DbInformacoesSagradas]
GO
CREATE USER usrServidorPublico FOR LOGIN [servidorPublico] WITH DEFAULT_SCHEMA = dbo

–> Adiciona o usuário para permissão de leitura (Role datareader)

EXEC sp_addrolemember ‘db_datareader’,’usrServidorPublico’

Passada a dor… Tá na hora do teste..

2 – A descoberta

Até que você, DBA terminou a tarefa e concluiu a demanda. Mas… curioso,pela primeira vez na vida, pois foi um DBA que sempre trabalhou com Windows Authentication (eu sei, muito difícil, mas vamos imaginar!), resolveu logar com o login recém criado, o ServidorPublico.

isNotAccessible

Olha só! Apesar do usuário ter apenas uma base como leitura, ele consegue visualizar os demais bancos. Vale ressaltar: ele consegue visualizar os bancos mas não possui acesso aos mesmos, sendo que uma expansão de qualquer banco resulta em erro (vide imagem acima).

Você precisa garantir de qualquer modo que o login consiga visualizar apenas o banco de dados que possui algum vínculo. Você quer manter o nome das bases em segredo. Quer seguir sua segurança instintiva. Mas como fazer? Bem, pausa pro café e vamos falar agora sobre a ponta do iceberg.

3 – Mínimo privilégio mas nem sempre

O que é mínimo privilégio possível? Bem, em suma, é oferecer o mínimo de permissão possível para que determinada conta/usuário consiga realizar, sem maiores problemas, sua atividade fim.

Você, pra matar uma barata, precisa de um chinelo ou de um fuzil?

Isso deveria ser sagrado em nosso trabalho, e não é só na área de Administração de Banco de Dados, mas em toda a nossa vida, em suas devidas proporções e sem radicalismos.  Tenho uma vontade enorme de falar sobre esse princípio e do “Security by default”, mas vamos nos limitar à definição dada acima pra não esfriar o pão de queijo (no caso, o código).

O SQL Server no geral segue tal princípio na parte de segurança, mas, curiosamente, no caso de visualizar bases de dados desnecessariamente, parece não ser uma preocupação tão crítica, observado que  isso já foi motivo de reclamação (antiga, inclusive) no Microsoft Connect e os caras não ligaram tanto assim.

Seguindo este mesmo princípio ainda, e lembrando do exemplo da barata:

Se o usuário deve ter disponível apenas o que é necessário, qual seria a razão de enxergar qualquer base que não tenha relação com ele?

Parando pra pensar, nenhuma.

Aqui existe uma óbvia contradição no princípio de mínimo privilégio. Pra quê visualizar bases que não são necessárias? Pode parecer bobo, mas isso abre brechas diversas que enfraquecem a segurança. Uma delas, é que, pra um desenvolvedor, realmente interessa saber que naquela instância existe um banco do RH? Bem, não vou entrar no campo ético da coisa, mas já imagina-se as possíveis situações em que algo aparentemente bobo pode se tornar… Basta um pouco de engenharia social, criatividade e um pouco de atitude…

Existe uma forma  de contornar essa situação de ocultar a visualização das bases (que deveria ser default, na minha opinião, segundo motivos já explicados do menor privilégio). Não resolve 100%, mas quase. Infelizmente, a solução a seguir oculta todas as bases, inclusive as que se tem algum vínculo.

4 – A forma mais aproximada de se resolver o problema

Existe uma permissão à nível de servidor chamada VIEW ANY DATABASE, que possibilita o usuário ver metadados relacionados aos bancos de dados, o que inclui suas visualizações na:

a) sys.databases

b) sys.sysdatabases

c) sp_helpdb

E afeta também o modo visual do Management Studio (ssms), o que é extremamente importante.

Em suma: mesmo o usuário servidorPublico tendo acesso a um só banco, ele consegue, utilizando esses três itens mencionados acima, enxergar alguns metadados de outros bancos.

O legal dessa história é que essa permissão VIEW ANY DATABASE é automaticamente concedida ao Public, ou seja, todo usuário ou conta que se conecta no SQL Server automaticamente ganha as permissões adquiridas desta role. Tecnicamente, isso equivale a dizer que, a menos que você negue tal permissão pro public, todas as contas/usuários do banco podem enxergar metadados que não são de seu interesse (acredita-se) por padrão.

Mas como contornar o problema?

Sabendo que todo login (inclusive o que acabamos de criar) pertence a uma role public (que é default), temos duas possibilidades:

1) Alterar o login desejado;

USE MASTER
GO
DENY VIEW ANY DATABASE TO servidorPublico

É uma solução interessante. O usuário não enxerga banco nenhum no modo visual mas consegue acessar a base (que possui permissão) normalmente através de T-SQL. Aqui cabe uma observação minha… Seria extremamente desejável que ele pudesse enxergar as bases na qual possui vínculo. Ou seja, seria interessante aqui termos uma permissão a mais (no final do artigo eu chego nesse ponto).

2) Alterando a role public

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC

É uma solução “broadcast”, hehe . Lembre-se que TODOS os usuário criados são atribuidos ao public, e qualquer efeito sobre o mesmo pode afetar permissões de todo mundo que está interligado. Lembrando que poucas roles escapam da  restrição deste método, como por exemplo, os sysadmins.

TRIVIA: de todas as server roles, PUBLIC é a única que pode sofrer operações de DCL. Tente dar DENY em outras server roles, como por exemplo, no securityadmin.

5 – O fim

Você, DBA grilado, implementa a primeira solução logado em sua conta de sysadmin, volta pro login criado e percebe que agora, apesar do login não conseguir nem ver a base descrita no client do ssms, consegue ler (intenção desejada) os dados e só consegue enxergar na tempdb e na master informações relacionadas às suas permissões. Independente do método utilizado acima, o resultado é:

(Clique na imagem para ampliar)

p2

Pra testar a sp_helpdb, você  resolve testar com nome de outro banco qualquer:

SP_HELPDB ‘DbFinanceiro’

E recebe o seguinte erro (Totalmente justificável):

Msg 15010, Level 16, State 1, Procedure sp_helpdb, Line 45

The database ‘DbFinanceiro’ does not exist. Supply a valid database name. To see available databases, use sys.databases.

Antigamente sem o DENY na ALTER VIEW ANY DATABASES, o usuário conseguiria enxergar alguns metadados relacionados aos bancos sem maiores dificuldades. Agora não mais.

E lá vai você, o DBA ninja, tomar um café pra encerrar essa empreitada aqui narrada.

6 – Considerações finais

A parte ruim dessa história toda é que não existe essa permissão de visualizar por banco. Seria legal que pudéssemos setar um ‘VIEW DATABASE’ (Se existissse) pra apenas as bases que gostaria, porém, isso não seria possível. Ou seja, seria desejável que o SQL Server permitisse a visualização de bancos que fossem interessantes ao login/user em específico. Imagino esse recurso voltado para os desenvolvedores.

Espero que tenha passado informações úteis.

Se você tiver alguma crítica, sugestão, correção, adendo, proposta de emprego ou comentário, fique à vontade também. Esse blog é nosso 😉

Conversa sobre a cláusula GO

Cláusula GO, funcionamento básico e seus conceitos
Vamos falar um pouco sobre o comando GO?
Duas pequenas observações antes de começar o artigo:

1) Nada nessa vida, nem uma cláusula de programação, é por acaso;

2) GO não é um comando T-SQL, muito menos SQL padronizado…É um comando usado pelo client para separação de batch (lote).

Introdução

O GO não é um comando T-SQL e também não é reconhecido como padrão na linguagem SQL. É um comando usado pelos aplicativos ‘clients’ que se conectam à instâncias do  SQL Server, como, por exemplo, o  sqlcmd e o Management Studio, que entendem o GO como um separador de instruções, e por isso enviam o código  de modo organizado, por sequência, usando GO como um separador. Se você usar o GO em qualquer client que não tenha suporte à cláusula, o seu código dará erro.

Pra ilustrar o que é batch e como o GO age no SQL Server, vamos a um exemplo básico:

Cláusula Go 1

O segundo SELECT dará erro por motivos óbvios. Alguns pensam “mas esse erro não tem lógica! Eu já declarei a variável na janelinha do Management Studio e a janelinha representa uma sessão aberta (até aqui correto), então, a variável, já que foi declarada na sessão, deveria ser reconhecida em qualquer parte da sessão (e aqui mora o erro). Engano clássico.

É justamente este pensamento  que possibilita a ocorrência de uma quantidade assustadora de erros em programação T-SQL, simplesmente por ignorar que existem escopos e escopos.

Ilustrando da melhor forma possível como funciona a separação de lotes a mesma consulta acima:

go2

Perceba que o GO delimita um lote de código de acordo com sua declaração, considerando qualquer código até que haja outro GO, que é a delimitação deste comando.O Management Studio, nesse caso, diz pra cada lote até onde ele deve executar. Em declarações de variáveis com o uso de GO, o Management Studio limita o escopo (alcance) da variável @data.

Outro detalhe, o comando GO precisa estar sozinho na linha, caso contrário, dará erro. Se quiser pode testar deixando um GO mesmo antecedido com ponto e vírgula (;) e notará que dará erro. Só comprova que ele é um comando com característica procedural, daqueles da moda antiga, que gostam realmente de separar instruções por lote de código (lote =  batch):

E pra que eu uso o GO afinal sendo que ele pode me levar à erros?

O comando tem uma propriedade de contador (inteiro). Isso pode ser usado em nosso favor quando usamos um client com suporte à cláusula. Segue um exemplo fantasioso que preenche uma tabela com registros totalmente aleatórios. Agora, vamos trabalhar com o modo de separação que é nativo do GO além do atributo de repetição.

go3

Basicamente, o GO vai executar 1000 vezes as instruções que foram declaradas desde o último GO.  Agora, uma pergunta. Se você der um F5, CTRL+E, PLAY, o que seja, pra executar o código, o que acontece?

a)      Um erro;

b)      O SQL Server cria a tabela #serial e insere mil vezes nessa tabela através do comando INSERT INTO;

c)       Vai  executar MIL VEZES o comando SELECT * from #serial;

d)      Vai dar USE no banco MASTER mil vezes;

Imagine mentalmente o GO dizendo assim: “Quero executar TUDO que tá emcima de mim. Tudo é meu, ATÉ QUE EU ENCONTRE OUTRO GO, aí o lote de código não é meu. Irmão não invade território de irmão!”.

Aliás, de curiosidade,  saiba  que o comando GO possui implícitamente a forma GO 1, pois ele só executa aquela instrução de código uma vez. Resumindo o que acontece na questão…

– O primeiro GO vai executar uma vez  o comando USE máster

–  O segundo GO vai executar o comando CREATE TABLE #serial e o INSERT mil vezes e…

Isso mesmo. A opção correta era a A. Ele vai criar a tabela, inserir uma vez e vai voltar de novo no loop, só que na segunda vez, ele vai voltar pro primeiro comando do lote que é um CREATE TABLE, porém como a tabela já existe, vai dar erro.

Msg 2714, Level 16, State 6, Line 3

There is already an object named ‘#serial’ in the database.

** An error was encountered during execution of batch. Continuing.

Batch deu erro e foi abortado. Simples assim. Então, imagine que você não tem essa tabela #serial. Drope-a:

DROP TABLE #serial

Repare a diferença testando o código abaixo (Não liga para os rabiscos paint-fast-like):

go4

O código acima irá funcionar. Vai executar apenas o INSERT (e não o CREATE) mil vezes. Simples assim.

FAQ (Recapitulando)

É certo dizer que GO é um comando?

Reposta: É sim.

Mas você disse que ele não é um comando..

Resposta: De novo, é um comando sim, relaxa. Do client, e não do SQL  ANSI ou T-SQL. Tanto que se você tentar executar uma procedure com GO, dará erro, porque o SQL Server é um Server e não sabe o que significa a cláusula GO.

Posso executar código por código então, sem usar o GO, uma porção de cada vez, no mouse, sabe, na mão?

Claro que pode. E isso, inclusive, é execução em “batch” também (teoricamente). A diferença é que você está fazendo sua própria separação de lotes, mas repare que o conceito é o mesmo.

Para maiores dúvidas:

GO: Documentação do próprio MSDN 

Batch: Documentação do próprio MSDN

Qualquer dúvida, feedback, sugestão, sinta-se à vontade para comentar.

Second Shot está de volta!

Segunda chance

Boa notícia pra quem está estudando pra tirar certificações Microsoft.

A Microsoft liberou ontem (30/08/2013) o Second Shot!
Pra quem não conhece, é literalmente, uma segunda chance: você marca o seu exame pela Prometric informando o voucher adquirido em um dos links abaixo, e se por acaso o pior acontecer, você poderá marcar uma segunda vez o mesmo exame sem cobrança alguma.

Vouchers expire on May 31, 2014. All exams, including retakes, must be taken by May 31, 2014.

Lembrando que, extremamente importante…É necessário informar o voucher no ato do primeiro agendamento. Muita gente não se atenta para esse detalhe e acaba por perder a segunda chance que tava ali de graça por vacilo 😀

É isso. Bons estudos pra quem está nessa empreitada. =)

Impressões sobre o Exame 70-461: SQL Server 2012 – Querying

query

Boa noite 🙂

Quarta passada tive êxito no exame 70-461: Querying, o primeiro capítulo da trilogia MCSA.

Se você não conhece o funcionamento sobre a nova trilha certificação, verifique este link, baixe os webcasts e veja como funciona.

Dei uma folheada Training Kit 70-461 e usei como base séria o Skills Measure . Pra não sair no escuro, dei uma lida também, durante um bom tempo, em impressões de outros profissionais que fizeram essa prova. Você pode dar uma pesquisada na internet que com certeza acha. Só pra saber onde eu direcionava mais atenção. E meu post tem esse intuito, de quem sabe, fazer essas dicas úteis pra quem vai prestar o exame.

A forma de prática foi o Prep-Kit, que vem junto com o Training Kit.
Recomendo o uso do Prep-kit apenas para reforçar conceitos e revisar comandos e não para pegar o ritmo da prova, que é bem diferente, sendo supérfluo e sem complexidade alguma! Não tem como comparar o nível de dificuldade do Prep-kit com a prova, btw.

O que mais me prejudicou na prova, e sim, prepare pra rir: marquei, sem querer, a prova em Português. Não cometa esse erro jamais na sua vida. As traduções em português não estão “ruins”, só não estão compatíveis com o bom senso. Por exemplo, é fácil reconhecer VIEWS em banco de dados, mas não “Modos de Exibição”. Teve vários termos, que eu nem consigo mais lembrar de tão exóticos que são. Alguns traduzidos de forma extremamente literal. Até demais. “Larga de frescura rapaz!” – disse alguém. Mas quero ver esse alguém sacar NA HORA o que significa “Função definida pelo usuário com valor de tabela”. Rola um WAITFOR na cabeça, pelo menos na minha. A massa cinza até processa a tradução mas não na hora 🙂

Enfim, compartilho algumas dicas importantes pra quem for prestar o exame:

– Aprenda e PRATIQUE todos os tipos de função, principalmente as TVF (Table Value Functions);

– FOR XML PATH, AUTO e RAW. Vai cair, saiba o que cada cláusula traz e qual é a estrutura do XML. Saiba olhar para determinado XML e imaginar qual FOR XML gerou;

– Modos de Isolamento. Saiba qual causa mais bloqueios, o padrão do SQL Server, o que “versiona” os registros e o que evita leitura suja…

– Views indexadas. Saiba usar SCHEMABINDING. Aliás, views estão bastante em foco neste exame, inclusive para operações DML. De foco nessa parte!

– Relembre: INTERSECT, UNION, UNION ALL, DISTINCT,  ALL, SOME, ANY, RANK FUNCTION…

– CONSTRAINTS em geral. Computed Columns, inclusive com funções;

– Questões DRAG and DROP parecem fáceis. Mas o ‘cão’ mora nos detalhes…Preste bastante atenção no enunciado da questão. Por exemplo, se na questão disse que o usuário USRXPTO já possui acesso de leitura na tabela XPTO, e você deve ALTERAR a tabela mantendo as permissões, você não vai dar um DROP, CREATE e GRANT ou seja lá o que for desnecessários, certo? Se você for alterar, nesse caso, também não precisa do GRANT. Saiba também que criar um campo NOT NULL em tabela já populada não é nenhum almoço grátis. Saiba como resolver esse tipo de pedido.Enfim, parece besteira, mas quem não pratica isso ou nunca teve experiência, pode perder muitos pontos com questões do gênero.

– Essa prova tem um foco exagerado em views (já falei, mas reforço). Pratique inserir/atualizar/deletar alguns registros nela (sim, é possível, via trigger instead of). Inclusive em views que possuem SELECT em mais de uma tabela (dica de ouro);

– Planos de Execução: Aprenda as formas de visualizá-lo;

– Tabelas inserted e deleted + triggers são essenciais. Aliás triggers foram assuntos bem presentes, não tanto quanto VIEWS, mas quase;

– Cairam algumas cláusulas novas: LEAD, LAG , EOMONTH, FORMAT, TRY_CONVERT, PARSE, dentre outros. Inclusive, caiu uma questão de EOMONTH combinada com DATEADD que me confundiu bastante, principalmente por não ter oportunidade de validar o código ):

– Saiba o que faz um PIVOT, UNPIVOT e APPLY (Outer e Cross). Não precisa focar seu estudo nisso (estou pensando em economia de tempo), apenas saiba em que situação aplicar;

– Diferencie newid e newsequentialid, saiba o que cada um faz e TESTE!

– Pratique abrir transações com tratamento de erros. Nada complexo pra quem já trabalha com T-SQL: TRY, CATCH, @@ERRORCOUNT e outras variáveis comuns à transações podem cair. Conhecer o funcionamento do XACT_ABORT e conhecer Triggers que fazem o uso deste tipo de programação é fundamental!

Creio que são informações úteis para quem está estudando. Claro que a prova vai muito além, e esses tópicos acima estiveram presentes no exame que fiz, então, reforço, não deixe de estudar pelo Skills Measure e não se limite ao que foi dito acima.

Uma coisa que me chamou bastante a atenção é que conhecer T-SQL, mas não praticar pode ser um perigo potencial no exame. Existem alguns peguinhas que só quem codifica no dia a dia e executa vai identificar. Parece uma frase clichê, e não deixa de ser, mas é uma verdade crua nessa prova.

E  Socorro, se tiver lendo isto, obrigado pelas dicas e conhecimentos trocados, especialmente as de XML o/

E é isso pessoal.

Estou estudando agora para a 70-462 agora e já tô preocupado com a 70-463 (BI).

[]’s

Resposta do Desafio T-SQL #2 – MERGE e OUTPUT

BIRD

Boa noite pessoal 🙂

1) Segue resposta do desafio anterior.

Eu coloquei o script com BEGIN TRAN, pra que possamos ver o resultado sem commit no banco, pra que testemos de novo o script sem necessidade de rodar tudo de novo.

Vamos primeiro traduzir alguns termos no contexto de um comando MERGE:

TARGET:  A tabela alvo, que você tem por objetivo atualizar. Eu prefiro traduzir TARGET como destino pra assimilar com mais facilidade;

SOURCE:  A tabela fonte de onde vem a informação necessária para se atualizar a tabela alvo. Eu prefiro traduzir SOURCE neste caso como origem pra assimilar com mais facilidade;

Sobre o algoritmo: Quero usar MERGE na tabela Empregado, que é o destino da atualização. Quero pra isso utilizar a tabela ListaMudancasRH, que é a fonte que irei utilizar para atualizar minha tabela Empregado. Eu quero associar as duas utilizando o nome do funcionário. Tendo isso em mente, é fácil entender qual a utilidade do MATCH…

#1 – WHEN MATCHED AND SOURCE.ativo = 1 THEN UPDATE…

Significa: Quando o nome do funcionário coincidir nas duas tabelas (ou seja, fizer o MATCH sucessivamente) e na tabela origem com as novas informações, tiver o ativo =1, significa que o funcionário ainda está empregado. Logo, devo atualizar o cargo na tabela destino utilizando a origem como base

#2 WHEN MATCHED AND SOURCE.ativo = 0 THEN DELETE…

Significa : Quando o nome do funcionário coincidir nas duas tabelas (ou seja, fizer o MATCH sucessivamente)  e na tabela origem com as novas informações. tiver o ativo =0, significa que o funcionário está/será desempregado. Logo, devo DELETAR ele na tabela de destino utilizando a origem como base (É importante ressaltar que isso é um exemplo apenas. Em casos reais, dependendo da modelagem dos sistemas que apontam para o banco, registros de pessoas comumente não são deletados, e sim atualizados para algo que indique desuso, que nesse caso poderia ser simplesmente a flag ativo da destino (tabela Empregados) atualizada para 0.

#3 – WHEN NOT MATCHED BY TARGET THEN INSERT…

Significa: Quando o nome do funcionário não coincidir na TARGET, ou seja, na tabela destino, se faz necessária a inserção de um funcionário que não existia antes na tabela de destino. Veja só, aqui inserimos os dados da tabela origem na tabela destino, a de Empregados. —

2) A segunda questão pede que façamos uma tabela de auditoria. Lembram que recomendei o uso da cláusula output? Não foi por acaso. Bem, existem várias formas de se realizar auditoria de um registro, e uma delas é utilizando as famosas “tabelas especiais” deleted e inserted,  que são duas tabelas utilizadas pelo SQL Server na execução de tarefas DML (que manipulam dados).

Esse post não pretende cobrir assunto , mas você pode vê-lo aqui e recomendo que o faça caso precise aprender novas possibilidade pra debugging e auditoria… Mas enfim… Cada vez que o código faz um:

DELETE: Internamente, no SQL Server, é salva uma cópia do registro apagado na tabela DELETED.

INSERT: Internamente, no SQL Server é salva uma cópia do registro recém-inserido na tabela INSERTED

UPDATE: Internamente, no SQL Server, é salva o registro na tabela DELETED e insere o novo no INSERTED.

Em resumo, combinando com a cláusula OUTPUT (maiores informações aqui), que a grosso modo é um “SELECT pra registros que estão sendo manipulados em expressões DML”, é possível capturar os registros que o SQL Server joga nas tabelas INSERTED e DELETED e assim sendo, persistir estes dados com o uso de uma cláusula INSERT INTO!

Para praticar, e já considerando que a cláusula MERGE soa mais familiar pra você, leitor, mostro uma nova possibilidade: Realizar o MERGE e manipulando os registros modificados (apagados, atualizados e inseridos).

Informações adicionais… COALESCE compara dois nomes e traz o primeiro resultado não nulo que encontrar. Inevitavelmente o nome do funcionário se encontrará NULO se o registro sofreu operações de DELETE na inserted e encontrará NULO também na deleted se foi feita uma operação de inserção.

No caso de um UPDATE, os dois campos estarão preenchidos, pois uma atualização consiste, internamente para o SQL Server, de uma deleção + inserção. A variável $ACTION guarda internamente qual operação foi realizada naquele registro, e que aqui foi mantida em inglês por motivos de praticidade. Você pode tratar isso com um CASE caso queira registrar um outro nome diferente do retornado ao servidor.

Essa foi a resolução básica! Tem várias outras formas de se resolver o problema, sendo esta apenas uma delas.

Espero que tenha passado alguma informação útil. Caso tenha alguma crítica, correção, ou opinião, sinta-se à vontade para comentar!
[]’s

SQL Server 2012 – Logical Functions e mais economia de código

logic

Boa noite 🙂

Outro post básico e rápido sobre funções lógicas (do mesmo grupo das já conhecidas estruturas lógicas, que envolvem WHILE, IF, CASE)…

Duas agradáveis adições foram realizadas no SQL Server 2012. Duas funções lógicas que já são velhas conhecidas para quem já programa(ou): IIF e CHOOSE.

IIF

(Documentação Oficial na MSDN para maior apronfudamento)

Também conhecido como IF ternário, nada mais, no contexto de algumas linguagens de programação, uma forma mais organizada de se fazer realizar uma escolha condicional entre dois valores utilizando uma expressão.

Estrutura:

IIF (boolean_expression, true_value, false_value) ou em bom português IIF (expressão_booleana, valorSeVerdadeiro, valorSeFalso)

É justamente pela estrutura que vem o nome de IF Ternário, por possui três elementos (parâmetros) em sua condição.

Seguem três exemplos pra posteriores comentários.

Em suma, como IF não pode ser utilizado em comandos SELECT deliberadamente para realizar condições, sendo utilizado mais em Stored Procedures e Funções, IIF serve como sintaxe alternativa para a função CASE, que faz exatamente o que a instrução IIF faria em uma instrução SELECT.  O que acho bacana nesse comando:

  • Sintaxe simples, limpa e direta (embora de começo pareça complexa);
  • Segue padrões .net (ao trabalhar com Integration Services, por exemplo, é uma cláusula largamente utilizada para faze comparações) e de outras linguagens;
  •  Economia de código, facilitando legibilidade;

Tem só um pequeno detalhe… Não estou dizendo que é um substituto pra CASE, por um simples e humilde motivo, e lógico ainda por cima:

CASE, assim como IF (em seu devido lugar) permitem mais de duas expressões lógicas. IIF é X, se não for X, ah meu amigo, é Y. Sem choro nem vela.

Códigos para expressar o sentimento:

Logo, é necessário avaliar qual a melhor construção para a sua lógica.

CHOOSE

Documentação Inicial na MSDN

Resumindo, assim como o IIF, o CHOOSE é outra cláusula condicional que pode, dependendo do caso, substituir o CASE. É mais uma construção bastante utilizada em .net e inclusive no Integration Services (Olha a MS casando particularidades de produtos “distintos” da suite  do SQL Server aos poucos aí!)

Vamos falar antes de sua construção:

A função CHOOSE vai retornar exatamente o valor que corresponde ao index passado no primeiro parâmetro:

O protocolo é claro. Existem cinco parâmetros dentro do CHOOSE. O primeiro vale um (1) e o último cinco (5). O número (index) passado é Quatro (4) então ele vai buscar o quarto valor nos parâmetros. No segundo exemplo, como ele não acha o parâmetro cujo valor é sete (7) ele simplesmente retorna NULO. A mesma coisa vale para o ZERO.
Outra aplicação prática:

But, contudo, todavia, todos os parâmetros devem ser do mesmo tipo, pois caso um dos valores esteja fora do padrão, ocorrerá um erro na execução. Veja só:

(6 row(s) affected)
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value ‘DOMINGO’ to data type int.

Enfim, alguns pontos positivos:

  • As mesmas vantagens do IIF

Minha única observação sobre a função é que poderia ser MUITO, mas MUITO útil caso fosse possível utilizar intervalos de valores, como é possível com CASE (usando BETWEEN por exemplo) e outras possibilidades lógicas. Acho o comando um pouco limitado no geral assim como o IIF. Mas tem quem gostará, com certeza. Qual a melhor escolha? A resposta pra praticamente qualquer coisa no SQL Server é: It’s depends.

Espero ter sido claro e ter ajudado alguém. Se sim, Já valeu a postagem.

Obrigado pela visita and…

Keep Querying !

[]’s

Desafio #2 T-SQL – MERGE e OUTPUT

Merge

MERGE é uma cláusula sensacional que foi implementada no SQL Server 2008. Pouca gente usa, e eu imagino que seja pela complexidade de declaração se comparando com outros comandos DML (Como por exemplo, DELETE, UPDATE e INSERT).

Leia mais sobre o comando aqui

Devo lembrar que a prova 70-461 vai cobrar esse conhecimento e provavelmente em mais de uma questão (assim como aconteceu na 70-433) e é bastante interessante que o conhecimento da mesma seja algo natural e intuitivo, além de ser um ótimo modo de se pensar/programar DML. Qual a forma que encontrei falar sobre a cláusula? Bem, claro, com um desafio! Vamos ao Desafio T-SQL #2 – MERGE e OUTPUT

Case

A empresa Trupe123 resolve, depois de anos sem dar aumento para seus funcionários, tomar uma atitude. Muito foi discutido sobre plano de crescimento, meritocracia, etc e depois de muita discussão, polêmica e briga. Como várias pessoas também saíram da empresa em um curto período de tempo, o RH aproveitou o momento para realizar mudanças no quadro de funcionários (acrescentando e/ou retirando profissionais) ou alterando cargos.O responsável pelo RH, chamado Pedro Bial entregou a você, o DBA, uma lista de mudanças que devem ser refletidas na tabela de empregados.

Com o material necessário pronto para fazer o seu trabalho, algumas instruções lhe são impostas: O pedido do RH foi bastante claro: associar pelo nome que já existe no sistema.

1) Quem estiver com ativo = 0, foi desligado. Logo, deve-se apagar da tabela de Empregados quem foi desligado.

2) O Profissional que estiver na lista de Mudanças do RH mas não estiver na tabela de Empregados deve ser incluído.

3) Pode ter havido alguma alteração de cargo para quem não foi desligado. O valor que deve permanecer é o que vem da lista de Mudanças do RH, desde que o funcionário exista tanto na tabela como na Lista do RH.

4) O profissional que estiver na tabela de Empregados mas não estiver na Lista de Mudanças deve ser mantido como está.

Agora, como dizia Bane: LET THE GAMES BEGIN! O desafio, composto de duas etapas:

1) Construa uma instrução MERGE que esteja dentro do pedido do Pedro Bial, do RH;

2) Construa uma tabela de auditoria temporária que irá receber:

a) O nome do funcionário envolvido na atualização;

b) O nome do evento (INCLUSÃO, EXCLUSÃO OU ATUALIZAÇÃO) seguido do valor antigo e do valor novo, se necessário;

c) Um campo de DATA que informe DATA E HORÁRIO de quando o registro foi incluído na tabela de auditoria; PS: Para o desafio de no.2, sinta-se a vontade para recriar toda a estrutura e adapte a sua estrutura MERGE utilizando a cláusula OUTPUT.

Outra coisa: como você provavelmente vai testar bastante tais comandos, vale a pena abrir algumas transações e ver o resultado por lá mesmo. É isso. Na próxima postagem volto com o resultado. Provavelmente final de semana. Novamente, obrigado pela sua visita e sinta-se a vontade.

SQL Server 2012 – Cláusula CONCAT() e o que isso significa pra galáxia

golphin

Bom dia. Post rápido, de nível básico pra não deixar água parada 🙂

Vários Sistemas Gerenciadores de Bancos de Dados (ou DBMS, no original) utilizam, pra concatenar, ou seja, juntar textos, pipes (o símbolo  –> | <–)  sendo que o padrão ANSI-92 prevê dois pipes como concatenação (mas não é obrigado a ser seguido tanto que o SQL Server não o faz) ou a função CONCAT(), que é bastante popular no Oracle e no MySQL. SQL Server, por sua vez, sempre concatenou os textos com o símbolo + (plus, conhecimento popularmente como MAIS).

Felizmente, assim como de costume em cada release, novos comandos (ou velhos, dependendo do ponto de vista) são adicionados no SQL Server de modo a facilitar a portabilidade entre sistemas. Aliás, features assim eu já vejo de cara como tentativa da MS de justificar migrações de código, deixando-as menos penosas. Mas bem, adivinha quem entrou como alternativa pra concatenações no SQL Server?

THIS GUY  ————> link

Mas essa ainda não é a maior vantagem do CONCAT sobre o método tradicional. Observe que todos os envolvidos na concatenação eram tipos textuais. Mas e nos casos comuns, onde geralmente temos que concatenar texto com números?

Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the varchar value ‘SQL SERVER ‘ to data type int.

A mensagem é clara demais…Ele tenta concatenar um número com texto e falha. Pra que o código acima rodasse tranquilamente, era necessário um CAST ou CONVERT na variável @release para um tipo textual, sendo possível assim realizar a concatenação. Exemplo:

Significa que quando recebermos a tarefa de migrar um script do MySQL ou Oracle pra SQL Server por exemplo, teremos menos uma dor de cabeça em relação a cláusula de concatenação, já que a função agora é a mesma. Parece uma mudança irrelevante e sem graça, e de fato, perto do que o 2012 traz, não é aquela senhora feature, mas achei um passo até notável. Alguns pontos:

1) Evite sempre que possível o uso de extensões proprietárias. Prefira sempre comandos ANSI e se não houver, algo de sintaxe similar ou que seja comum a outros bancos. Isso facilita o reuso, migrações de código entre plataformas. SEMPRE pense nisso quando for desenvolver algo em SQL.

2) Concatenação geralmente é um recurso bastante presente em códigos legados. Não precisar portar boa (senão toda) parte dele é economia de tempo. Isso tem impacto sobre sua vida, sua produtividade e na vida dos ursos no polo norte.

3) SQL Server cada vez mais se torna amigável adaptando em si alguns recursos que são usados de modo frequente. Vejo isso como uma oportunidade  de desmistificar pra profissionais que mexem com outros sistemas que MSSQL é algo de outro mundo (sendo que nem é e possui inclusive um dos dialetos (T-SQL) mais fáceis entre os disponíveis). O uso destes recursos pelo desenvolvedor possibilita a propagação dessa idéia. ;

4) Desenvolvedores (T)-SQL fazem com que o código mais universal  reduzam o impacto de meteoritos para longe da órbita do planeta Migrations;

Enfim, recomendo que você use CONCAT() sempre que possível partindo da versão SQL Server 2012 se não houver envolvimento com o legado, é claro.

[]’s

Vamos estudar SQL Server 2012?

birds

Boa noite Pessoal! Post rápido e preguiço devido às correrias da vida.

Estou bastante contente (e empolgado, não tem como esconder) sobre SQL Server atualmente. Comecei a estudar em conjunto pra prova de certificação (exame 70-461) e isso tem gerado resultados mais eficientes em comparação ao estudo individual. Até então, meu contato com SQL Server 2012 se restringia apenas a leitura, uma logada+fuçada  em uma coisa ou outra, mas agora vai (!) , e vou refletir isso em postagens aqui no blog, reforçando aqui meu compromisso de usar o wordpress de verdade 🙂

Resolvi, além disso, deixar o convite para quem tá afim de dar uma mexida também:

  • Baixe o SQL Server 2012 Evaluation (Ou trial/teste, como preferir) aqui
  • Algumas novidades no T-SQL do 2012 bem explicadas aqui
  • O conteúdo da prova de Querying (primeira prova) mencionada no post aqui
  • Caso tenha dúvida e precise de uma mão, depois de dar aquela garimpada de leve na documentação oficial (Books Online/MSDN) e internet, a comunidade SQL Server está presente em peso nos fóruns.
  • Tem material no MVA também neste link:

Outros fóruns e blogs relacionados ao conteúdo estão aí disponíveis também. Não vou postar por pura preguiça, e acho que leitura de blog deve ser on-demand de acordo com o internauts.

Eu sei que é um tanto engraçado em meados de 2013 eu dizer, no título, pra estudar a versão 2012 sendo que a 2014 já está aí, kkkkkkkk. Mas bem, levando em conta que eu estava estudando/trabalhando/lendo sobre o 2008 R2, só deu pra dizer isso agora.

Por enquanto é isso. Até!

Reposta do Desafio #1 T-SQL, Ranking Functions (ROW_NUMBER, RANK E DENSE_RANK)

Resposta do desafio

Bom dia pessoal 🙂

Semana passada eu disse que postaria a resposta do desafio #1 T-SQL no começo da semana. Andei ocupado, só deu pra terminar agora.

Pra relembrar, o desafio foi este: http://radialog.wordpress.com/2013/07/12/checkpoint-pessoal-desafio-1-t-sql/.

Precisamos obter os segundos maiores salários de cada departamento. O desafio praticamente disse, com todas as letras (pelo menos a solução mais óbvia) foi que devemos particionar por departamentos e  rankear pelo segundo maior salário.

A resolução mais óbvia se encontra no que chamados de Ranking Functions, informação que você encontra aqui nesse link do MSDN. De cara, a questão sugere que façamos uma classificação…. A minha solução (a mais intuitiva possível) foi utilizando CTE (Common Table Expression).

A questão anterior trabalha com uma variável de tabela. Para fins de prática e para entendimento das funcionalidades, execute o script abaixo que é o mesmo código do desafio, porém, materializado para tabelas físicas, no tempdb.

Ok, temos a tabela populada. Note que em um SELECT simples, a tabela está ordernada por departamento, algo já pensando na inserção dos dados no início deste exercício. Como poderíamos tentar classificar as linhas? Vamos tentar um ROW_NUMBER() ?

Que trará o seguinte resultado:

Explicando o que a linha ROW_NUMBER() fez: Particionou por departamento e ordernou por salário. Falando de modo mais fácil,  ele vai numerar as linhas de acordo com o departamento, e não se baseando na tabela inteira (por isso a partição por departamento). Essa foi a forma MAIS PRÓXIMA que se dá pra chegar no desafio, claro, utilizando um r0w_number() sem maiores firulas. Porque mais próxima e não a correta? Bem, observe as linhas em negrito e lembre-se que se existirem dois maiores salários, os dois devem ser mostrados. Vejam que existe empate do que  questão pede, que é o “segundo maior salário” entre diversos empregados. 

O row_number() não leva em consideração empate, então, não é a melhor função pra resolver o problema. Observe:

E o que o select traz pra gente?

Ele trouxe corretamente o que foi proposto sintaticamente falando: as segundas linhas (já que existe ordernação do maior salário ao menor) de cada deparmento. Mas os nossos amigos negritados não apareceram por completo devido ao empate, logo, row_number (em tradução simples, número da linha) não atende o desafio proposto.

NTILE() é uma cláusula que não serve também para resolver o desafio de forma simples. Não vou realizar o exemplo pro post não ficar muito grande.

Agora sim, temos RANK e DENSE_RANK que fazem exatamente o que o exerício pede, porém com uma sutil diferença… Vamos utilizar a mesma lógica de particionamento por departamento e ordenação por salário…

Olha o resultado:

É exatamente o que estamos precisando! Se formos realizar uma consulta filtrando as colunas “RANK” ou “RANK_DENSE” (usando uma CTE, foi a forma mais intuitiva que achei) ele trará o resultado correto da resposta. Porém, como disse antes, existe uma diferença sutil que faz a diferença entre “O certo” e o “O mais certo”. Não fez muito sentido o que eu disse? É justamente a palavra DENSE do rank. Se tivesse ali entre os dados DOIS “primeiros lugares” (sim amigos, um empate em um departamento dos primeiros maiores salários) o resultado certamente sairia diferente! Isso considerando dois, imagina três ou quatro…. Pra ilustrar melhor o que eu disse, vamos aos scripts:

Fazendo o teste…

Olha só o resultado!

Observe os empregados “S Martin” e “J Garcia”. Eles possuem os segundos maiores salários de seus departamentos, mas comparando os números de RANK() e DENSE_RANK() obtemos resultados diferentes entre si. A razão pra isso é muito simples: RANK() classificou em primeiro lugar dois itens. Como é natureza dessa função considerar os itens que estão agrupados em certa classificação, os teoricamente “segundo colocados” na verdade estão em terceiros, já que houve um empate entre os dois primeiros (então, como não foi dado nenhum outro critério de desempate, os dois ocupam o mesmo rank porém “tiram” a vaga dos próximos que virão).
DENSE_RANK() não faz isso. Ele considera uma classificação consecultiva, sem pular posições (por isso é “denso”).
Logo, a resposta mais correta seria o código abaixo utilizando dense_rank():

Resultado:

Pra ficar bonito o código sem esse rank_dense, você pode tirá-lo do SELECT pra ficar idêntico a resposta desejada. Deixei ele aqui só pra mostrar que era isso o que a questão queria: os segundos maiores salários de cada departamento, segundo que se houver empate entre os “segundo maior”, que todos os envolvidos sejam listados.

A resposta completa já estava no outro post neste link  aqui. Pra mim, a explicação daquele código já é suficiente para entender um pouco do problema, mas eu queria explorá-lo mais e pra isso, fiz o post.

Você conseguiu encontrar a solução? Utilizou outra forma? Fique a vontade para comentar. Agora vou dormir porque o dia é longo 🙂