Como bloquear instruções do tipo SELECT * FROM

Boa Noite Pessoal,

Estou iniciando os primeiros posts nesse ano de 2009. Na verdade é o meu primeiro post técnico de fato embora não seja o primeiro desse ano (continuo muito feliz de ter sido premiado com o título MVP). Hoje postarei uma dica rápida embora bastante interessante. É sabido que instruções do tipo SELECT * FROM são um verdadeiro problema para o desempenho não só no SQL Server, mas em qualquer banco de dados. Há alguns mitos acerca dos reais malefícios dessa instrução. Na verdade ela não é tão "mortal" quanto pregam, mas não é ruim alardear sobre seus impactos visto que ela é sempre uma prática ruim. Todos sabem que não devem usar, mas é bem comum em novas aplicações aparecerem os preguiçosos e caso não haja padrões estabelecidos e rigorosamente obedecidos, fatalmente aparecerão instruções como essas no ambiente de produção.

Considerando aplicações de desenvolvimento controlado, é sempre uma boa prática evitar instruções do tipo SELECT * FROM. Se há um controle na fase de desenvolvimento e autonomia na elaboração dos modelos de dados e da aplicação em si, há um truque bem simples para evitar esse tipo de instrução. Façamos um simples teste para exemplificar.

— Criação da tabela
USE TEMPDB;

CREATE TABLE UF (
        Sigla CHAR(2),
        Nome VARCHAR(20),
        Capital VARCHAR(50),
        ExpectativaVida DECIMAL(3,1))

INSERT INTO UF VALUES (‘AC’,‘Acre’,‘Rio Branco’,71.4)
INSERT INTO UF VALUES (‘AL’,‘Alagoas’,‘Maceió’,66.8)
INSERT INTO UF VALUES (‘AP’,‘Amapá’,‘Macapá’,70.4)
INSERT INTO UF VALUES (‘AM’,‘Amazonas’,‘Manaus’,71.6)
INSERT INTO UF VALUES (‘BA’,‘Bahia’,‘Salvador’,72.0)
INSERT INTO UF VALUES (‘CE’,‘Ceará’,‘Fortaleza’,70.3)
INSERT INTO UF VALUES (‘DF’,‘Distrito Federal’,‘Brasília’,75.3)
INSERT INTO UF VALUES (‘ES’,‘Espírito Santo’,‘Vitória’,73.7)
INSERT INTO UF VALUES (‘GO’,‘Goiás’,‘Goiânia’,73.4)
INSERT INTO UF VALUES (‘MA’,‘Maranhão’,‘São Luís’,67.6)
INSERT INTO UF VALUES (‘MT’,‘Mato Grosso’,‘Cuiabá’,73.1)
INSERT INTO UF VALUES (‘MS’,‘Mato Grosso do Sul’,‘Campo Grande’,73.8)
INSERT INTO UF VALUES (‘MG’,‘Minas Gerais’,‘Belo Horizonte’,74.6)
INSERT INTO UF VALUES (‘PA’,‘Pará’,‘Belém’,72.0)
INSERT INTO UF VALUES (‘PB’,‘Paraíba’,‘João Pessoa’,69.0)
INSERT INTO UF VALUES (‘PR’,‘Paraná’,‘Curitiba’,74.1)
INSERT INTO UF VALUES (‘PE’,‘Pernambuco’,‘Recife’,68.3)
INSERT INTO UF VALUES (‘PI’,‘Piauí’,‘Teresina’,68.9)
INSERT INTO UF VALUES (‘RJ’,‘Rio de Janeiro’,‘Rio de Janeiro’,73.1)
INSERT INTO UF VALUES (‘RN’,‘Rio Grande do Norte’,‘Natal’,70.4)
INSERT INTO UF VALUES (‘RS’,‘Rio Grande do Sul’,‘Porto Alegre’,75.0)
INSERT INTO UF VALUES (‘RO’,‘Rondônia’,‘Porto Velho’,71.2)
INSERT INTO UF VALUES (‘RR’,‘Roraima’,‘Boa Vista’,69.9)
INSERT INTO UF VALUES (‘SC’,‘Santa Catarina’,‘Florianópolis’,75.3)
INSERT INTO UF VALUES (‘SP’,‘São Paulo’,‘São Paulo’,74.2)
INSERT INTO UF VALUES (‘SE’,‘Sergipe’,‘Aracaju’,70.9)
INSERT INTO UF VALUES (‘TO’,‘Tocantins’,‘Palmas’,71.3)

Supondo que a relação de unidades federativas seja utilizada em uma combo, provavelmente seria necessário apenas a sigla (possivel chave para outra tabela) e o nome da unidade federativa para exibição. Supondo que um maléfico SELECT * FROM apareça, fatalmente as demais colunas seriam selecionadas e posteriormente descartadas incorrendo em maiores custos de memória e rede literalmente desnecessários.

Uma forma muito sútil de impedir que instruções SELECT * FROM sejam executadas é adicionar uma coluna extra na tabela sem nenhum significado e bloquear a permissão nessa coluna. Ex:

— Adiciona uma coluna sem significado
ALTER TABLE UF ADD SS BIT NULL

— Bloqueia a permissão na coluna para todos
DENY SELECT ON UF (SS) TO Public

Se você testar o comando de SELECT * FROM com uma conta SysAdmin naturalmente que essa restrição não terá o menor efeito. O teste precisa ser feito com um usuário mais restrito. O script abaixo cria um login para fazer esse teste.

— Criação do usuário (sem mapeamento)
USE TEMPDB;
CREATE USER UsrTeste WITHOUT LOGIN

— Concede permissão de SELECT em UF
GRANT SELECT ON UF TO UsrTeste

— Muda o contexto de execução
EXECUTE AS User = ‘UsrTeste’

— Tenta executar um SELECT * FROM em UF
SELECT * FROM UF

— Volta o contexto de execução
REVERT

Após executar o SELECT * FROM em UF, uma mensagem é retornada.

Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ‘SS’ of the object ‘UF’, database ‘tempdb’, schema ‘dbo’.

Essa mensagem era esperada, pois, embora o usuário UsrTeste tenha permissão de SELECT em UF, ele não tem permissão de SELECT na coluna SS (herdada da negativa para a Role Public). Uma vez que vale a permissão mais restritiva, o usuário UsrTeste ficou impedido de executar a instrução SELECT contra UF. Como a permissão de visualizar a coluna SS foi negada para a role public, nenhum usuário estará autorizado a visualizar essa coluna. Mesmo que o usuário UsrTeste estivesse na role db_owner não conseguiria visualizar essa coluna.

Como na prática, a coluna SS não tem nenhum significado negocial, para que a tabela possa ser consultada, será necessário especificar as colunas desejadas. O script abaixo, mostra que caso as colunas desejadas sejam escolhidas, o SELECT funciona perfeitamente.

— Muda o contexto de execução
EXECUTE AS User = ‘UsrTeste’

— Executa a instrução SELECT informando somente as colunas necessárias
SELECT Sigla, Nome FROM UF

— Volta o contexto de execução
REVERT

Se essa implementação for utilizada em todas as tabelas, todas as consultas serão obrigadas a informar todas as colunas efetivamente necessárias e a instrução SELECT * FROM não irá funcionar contra nenhum tabela (a menos que o usuário da aplicação seja SA ou pertença a role SysAdmin). Ainda que pareça ser uma boa idéia, há argumentos que irão surgir naturalmente por conta de uma resistência natural.

"Se você criar essa coluna, a largura do registro ficará maior e haverá perda de desempenho"
Inegavelmente isso está correto. De fato colunas a mais (fixas ou variáveis) incorrem em bytes adicionais (de espaço e (ou) de controle) e o aumento da largura do registro tende a prejudicar o desempenho. Entretanto um argumento desses é um sem dúvida um grande paradoxo. Se por um lado, um bit é gasto a mais, quantas economias de recursos não serão feitas evitando-se a instrução SELECT * FROM ? Se essa economia não for suficientemente convincente, basta argumentar que a coluna existirá apenas nos ambientes de desenvolvimento e homologação. Se tudo der certo, as colunas podem ser retiradas no ambiente de produção evitando-se o pequeno desperdício da coluna adicional e economizando-se bastante na utilização de recursos já que instrução SELECT * FROM não estarão no ambiente de produção visto que nos ambientes de desenvolvimento e homologação elas não erão permitidas.

"O tempo de desenvolvimento vai aumentar, pois, será necessário que as colunas sejam informadas"
Essa na minha opinião é uma desculpa pouco convincente visto que se o código for encapsulado em uma SP será digitado apenas uma vez. Considerando ainda que o Query Analyser e o SQL Server Management Studio possuem Templates de SELECT que já preenchem todas as colunas a desculpa fica ainda mais frágil. Para finalizar o SQL Server Management do SQL Server 2008 possui o recurso de Intelissense.

"Essa coluna adicional confunde o meu modelo de dados"
A aparição de uma coluna SS (ou qualquer outro nome que denote "Sem Significado") é esteticamente desagradável para um modelo de dados. Se considerarmos que o modelo foi desenvolvido e implementado via script no banco, apenas o banco possuirá a coluna e o modelo não. Não há problemas nessa divergência visto que negocialmente essa coluna nunca será necessária. Ela servirá apenas para garantir que o SELECT * FROM não seja utilizado e nada mais. O único entrave seria no caso de procedimentos de engenharia reversa e (ou) comparação de modelos. Em todo caso, acho que vale o sacrifício.

A dica é bastante simples e pode ser implementada inclusive em outros SGBDs. Confesso que nunca consegui aplicar algo nesse sentido nos ambientes em que possuia mais influência sobre a administração de dados. É sem dúvida um grande desafio fazer com que um time de desenvolvimento (principalmente desenvolvedores iniciantes) abandonem o hábito de rodar instruções SELECT * FROM. Todos sabemos que é "proibido", mas durante o desenvolvimento é muito tentador digitá-la. Implementar sua proibição ou não é uma questão um pouco mais além, mas caso seja um concenso, há como fazê-lo. Estou certo de que muitos DBAs e administradores já procuraram uma forma de fazer isso.

[ ]s,

Gustavo

4 Respostas para “Como bloquear instruções do tipo SELECT * FROM

  1. Interessante!Obrigado.

  2. Olá Gustavo. Primeiro parabéns pelo MVP. Assisti a um webcast seu sobre: Utilizando o SQL Profiler e ferramentas auxiliares para resolução de problemas no SQL Server.Você tem o excel com os contadores de performance? Poderia disponibilizar? Obrigado.Abs

  3. Gostei bastante da dica Gustavo, trabalho como DBA também mas nunca tinha pensando em como bloquear consultas deste tipo. Porém eu ainda acho que não compensa pelo "custo", mas ainda assim é uma boa dica para expandir ideias "encapsuladas".E muitos parabéns pelo título de MVP! Abraços!

  4. Olá Thiago,Realmente há "custos", mas é possível minizá-los.Basta retirar as colunas em produção e gerar um script para adicionar e bloquear as colunas em todas as tabelas.Acho que a maior dificuldade será cultural.Abs,

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s