Implode – Tratamento de strings com PHP

Implode para query

Imagem Ilustrativa

Mês passado, quando expliquei um pouco sobre a função explode(), fiquei de explicar a função reversa deste comando. A função em questão é a implode, ela também é bastante utilizada, no caso agora, para juntar um array em uma simples string. Abaixo deixarei um pouco sobre o que estudei estes dias sobre o implode com um exemplo prático.

O básico da função  implode ()

Como dito no começo, o implode foi criado como uma função reversa ao explode(). Sendo assim, ele faz a junção dos itens de um array em uma string simples. Para a utilização da função você pode passar dois parâmetros sendo que o primeiro se tornou opcional a partir da versão 4.3 do PHP.

implode([string $separador] , array $palavras).

No código acima temos a passagem dos 2 parâmetros necessários para o funcionamento da função. O primeiro $separador será um caractere que fará a separação das strings no array dentro desta nova string formada. Apesar de não ser mais necessário  a utilização deste parâmetro, usá-lo e a forma mais comum para os diversos tratamentos utilizados hoje.

O segundo parâmetro é o array de palavras ou itens que você deseja colocar na string final. Por fim, aqui não temos um limitador igual na função explode, isto porque o limitador é o próprio array.

Dando um implode em seus arrays

Vamos à parte prática da função implode. Ela pode ser muito útil para fazer queries no MySQL utilizando a cláusula IN. Vamos dizer que tenhamos uma tabela de clientes e seus pratos preferidos. Você então deseja selecionar aqueles clientes que gostam de batatas fritas, bife de boi e arroz. Você poderia fazer uma query cheia de cláusulas OR, mas acredito que desta forma seria bem mais prático.

Tabela de clientes

+------+---------+---------------------+
| id   | nome    | prato_preferido     |
+------+---------+---------------------+
| 1    | John    | batatas fritas      |
| 2    | Ram     | bife de boi         |
| 3    | Jack    | arroz               |
| 4    | Jill    | torresmo            |
+------+---------+--------------------+


Tendo esta tabela em vista vamos ao código. No código abaixo fiz um simples array com os pratos que desejo, implodi o mesmo utilizando o separador ‘,’ e então fiz a query vejam só:

<?php

		//Definindo os itens da busca no array
		$arr = array('batata frita','bife de boi','arroz');

		//Fazendo o implode dos itens em uma string simples
		$query_args = implode("','",$arr);

		//definindos os dados de conexão com o banco via PDO
		$user = 'local';
		$pass = 'local';
		$dbh = new PDO('mysql:host=localhost;dbname=devel2_implode', $user, $pass);

		//Montando a query da busca filtrada
		$query = "select * from clientes where prato_preferido IN ('".$query_args."')";
		
		//Executando a query com PDO
		$resultado = $dbh->query($query);
		$resultado = $resultado->fetchAll();
		
		//Trazendo os resultados da busca
		foreach ( $resultado as $row){
			echo "<p style=\"font-weight:bold;\">".$row["nome"]."</p>";
		}			
	?>

Para a conexão com o banco utilizei o PDO do PHP, por questões de melhor segurança dos dados e etc. caso prefira pode utilizar o mysql_query ou mysqli_query. Fica a seu critério.

Como explicado acima, eu defini os filtros da minha busca dentro do array. No caso você pode popular este array através de um formulário ou de um relatório. Implodi ele para que o mesmo venha como uma string só e assim permitir a utilização dos filtros no IN do MySQL. Por fim é só fazer a busca em seu banco e imprimir os resultados. Simples neh? Confiram o resultado final e com alguns ajustes neste link.

Concluindo, o implode consegue transformar as strings dentro de um array reunindo-as em uma string simples e assim várias possibilidades surgem para tratamento posterior. Espero que esta dica tenha sido de boa ajuda. Até o próximo post, Abs.

Tenha todo o desempenho para o seu site com a Hospedagem Cloud DialHost. Servidores em cloud prontos para receber sites em PHP. Saiba Mais ou veja nossos produtos.

 

Felipe Moraes
Felipe Moraes

Gerente de desenvolvimento na DialHost, Designer Gráfico formado pela Universidade FUMEC, Minas Gerais e Pós graduando em Branding pelo Centro Universitário UNA, possuo interesse em design de interação e interfaces. Trabalha com programação e criação para web, desde 2006. Apreciador de Bacon, Pudim e music Tag.

Vulnerabilidades de injeção de SQL chegam aos níveis mais altos dos últimos três anos

Imagem ilustrativa

Imagem ilustrativa

Depois de alguns anos de um declínio estável, 2014 testemunhou um significativo aumento em vulnerabilidades de injeção de SQL identificadas em pacotes de software lançados publicamente. Uma pesquisa da DB Networks indica que isso pode ser atribuído diretamente à metodologia de desenvolvimento de software atual, que enfatiza deadlines e orçamentos que dão pouca atenção ao tipo de diligência em segurança necessária atualmente.

A DB Networks analisou estatísticas do National Vulnerability Database, um repositório de dados de cibervulnerabilidades financiado pelo governo federal e mantido pelo National Institute of Standards and Technology, para chegar às suas conclusões sobre o assunto. O ano passado produziu o maior número de vulnerabilidades relacionadas a SQL identificadas desde 2011 e 104% a mais do que 2013.

“Apesar dos esforços dos gerentes de projeto, o desenvolvimento de software quase sempre corre contra o tempo e restrições de custos”, disse Dave Rosenberg, CTO da DB Networks. “Quando o relógio está correndo, parece que testes de segurança estão entre as primeiras tarefas a serem deixadas de lado”, completou.

A esperança é que essas vulnerabilidades de injeção de SQL sejam identificadas e os pacotes de software liberados antes que criminosos possam explorar as falhas. Com a média de custo de vazamento de dados estando em torno de US$ 6 milhões, é fundamental identificar e remediar as vulnerabilidades o mais rápido possível.

As ramificações de uma vulnerabilidade de injeção de SQL ocorrendo em um pacote de software popular podem ser enormes. Em outubro do ano passado, uma falha de injeção de SQL foi identificada no Drupal e afetou milhões de sites.

Os cibercriminosos têm a seu lado as probabilidades e o tempo – aplicativos complexos com grandes números de rotinas que geram pesquisas em SQL têm mais chances de descobrirem uma falha, e aqueles procurando explorar um sistema precisam apenas de uma falha para alcançar seus objetivos. Eles podem aplicar suas ferramentas 24/7, até que a falha seja descoberta. Por outro lado, desenvolvedores de software precisam executar suas tarefas de forma perfeita enquanto respondem a benchmarks e deadlines.

Embora seja cedo para uma previsão concreta, os dados de 2015 indicam que a tendência de crescimento em vulnerabilidades de injeção de SQL deva continuar este ano.

—-

Artigo publicado no iMasters.

 

DialHost
DialHost

Contamos com nosso DataCenter no Brasil de alta qualidade, estabilidade e confiança para hospedar seu site. Utilize poderosas ferramentas para otimizar o uso do seu serviço e seja sempre atendido de forma rápida e clara pelo nosso atendimento 24h.

Importando múltiplos arquivos texto

multiplos arquivos

Imagem ilustrativa

Faz alguns meses que publiquei aqui nesta coluna um artigo descrevendo alguns problemas comuns com arquivos texto. Separadores de colunas, identificadores de linhas, caracteres “proibidos” etc. Desta vez eu apresento uma solução simples para importação de múltiplos arquivos texto no SQL Server. De quebra, eu uso também um truque muito interessante apresentado por Grzegorz Oledzki para conversão de texto no formato ‘DD-MM-YYYY’ em datas reconhecidas pelo SGBD.

Alternativas de importação

Existem vários comandos e ferramentas que auxiliam na importação de dados. Alguns são simples e práticos, outros mais complexos e robustos. Basta pensar que, em última análise, um comando BULK INSERT, uma operação de DATA FLOW do SSIS e o utilitário BCP fazem a mesma coisa: importam dados.

Eu sou fã da simplicidade do comando BULK INSERT, apesar dele ter limitações importantes. Talvez a mais séria destas limitações seja não suportar arquivos CSV, que são muito comuns quando se trata de importação de dados.

Por outro lado, o comando BULK INSERT oferece uma série de benefícios interessantes. Alguns deles são:

  1. Reforça a validação de tipos de dados;
  2. Valida restrições do modelo (como chaves estrangeiras);
  3. Permite configurar delimitadores de colunas e marcadores de final de linha;
  4. Permite definir a página de códigos, para trabalhar com caracteres diferentes do ASCII;
  5. Controla o fluxo de importação, comitando transações a cada N registros importados;
  6. Permite desabilitar gatilhos que seriam disparados em consequência da inserção dos novos registros;
  7. Permite definição de layouts de importação, essencial nos casos em que o arquivo importado tem número diferente de colunas em relação à tabela de destino;
  8. Permite definir as linhas inicial e final do arquivo de dados, desprezando eventuais cabeçalhos ou rodapés no arquivo importado;
  9. Controla o tipo de “lock” sobre a tabela;
  10. Possibilita cargas de dados em paralelo, aumentando dramaticamente a velocidade do processo;
  11. Dispensa log da operação de carga de dados e, portanto, não tem impacto sobre o “transaction log” da base;
  12. Especifica um arquivo de output para registrar erros da importação.

 

Estudo de caso

Considere que você recebeu uma solicitação especial de um diretor da sua empresa. Ele lhe passou um conjunto de 50 arquivos texto e pediu para importá-los num banco de dados para fazer algumas análises.

Ele fez questão de passar detalhes do seu pedido: todos os arquivos tem o mesmo layout, exatamente a mesma sequência de campos. Usam o mesmo separador de colunas (caractere de tabulação “\t”) e o mesmo identificador de final do registro (caractere de retorno de linha “\n”). Os 50 arquivos recebem nomes sequenciais, que vão de “Arquivo01.txt” a “Arquivo50.txt”.

A primeira linha dos arquivos é sempre um cabeçalho informando o nome dos campos. Porém os cabeçalhos apresentam rótulos diferentes em cada arquivo. E a quantidade de linhas de dados varia de arquivo para arquivo.

As tabelas usadas

O leitor mais atento já percebeu a descrição acima traz detalhes importantes misturados com informações irrelevantes para a sua tarefa. Mas a vida real é assim mesmo. Mesmo quando lida com um usuário com bastante conhecimento técnico, é comum que aconteçam alguns deslizes e ele suponha que alguns detalhes tenham muito mais importância do que de fato tem.

O primeiro detalhe irrelevante é sobre a variação da quantidade de registros entre os arquivos. Isso não nos afeta, até porque as ferramentas de carga de dados já estão preparadas para isso (importação roda até encontrar o marcador de final de arquivo).

O segundo é sobre os cabeçalhos variáveis. O que interessa no processo importação é o número de linhas que serão desprezadas no início de cada arquivo e a sequência das colunas. Se elas são descritas de forma diferente é irrelevante. Como eu disse, estas linhas de cabeçalho são desprezadas.

Não há nenhuma informação sobre campos que devam ser desprezados. Então eu assumo que devo importar todas as colunas do arquivo. Também não há comentário sobre existência de alguma chave nestes dados. Mas como serão feitas análises, é fundamental que seja criado um índice clusterizado nesta tabela.

Tenha em mente que o SQL Server nunca oferecerá boas performances em consultas se não existir um índice clusterizado na tabela. Isso é tão importante que o SQL Server cria automaticamente um índice clusterizado assim que se define a chave primária da tabela.

Portanto a tabela de destino deverá ter uma chave primária. Para isso, eu adiciono um campo IDENTITY nesta tabela. Veja Listagem 1:

Listagem 1: tabela de destino


1 CREATE TABLE dbo.tbDestino(
2     DestinoID       int identity(1,1) NOT NULL,
3     TipoPessoa      char(2) NOT NULL,
4     NomeCompleto    varchar(200) NOT NULL,
5     Titulo          varchar(8) NULL,
6     Prenome         varchar(50) NOT NULL,
7     NomeDoMeio      varchar(50) NULL,
8     Sobrenome       varchar(50) NOT NULL,
9     Sufixo          varchar(50) NULL,
10     EmailPromo      int NOT NULL,
11     DataAtualizacao datetime NOT NULL,
12  CONSTRAINT PK_Destino PRIMARY KEY CLUSTERED (DestinoID)
13 ) ON PRIMARY
14 GO

Importante notar que agora temos uma tabela com um campo a mais do que os arquivos. É o campo [DestinoID], chave primária da tabela.

Neste ponto, temos que escolher entre dois caminhos. A primeira opção é criar um arquivo de formatação, especificando o mapeamento dos campos entre os arquivos de origem e a tabela de destino. A segunda é criar uma tabela de carga de dados, normalmente chamada de tabela de staging. Esta tabela teria o mesmo número de campos da fonte de dados e a mesma sequência de campos. Todos os campos da tabela de staging usam o tipo VARCHAR, evitando conversões durante o processo de importação.

Como eu disse, meu objetivo é criar um processo de carga de múltiplos arquivos que seja o mais simples possível e menos suscetível a falha. Por conta disso eu escolho usar a tabela de staging. O script de criação desta tabela é apresentado na Listagem 2.

Listagem 2: tabela de staging


1 CREATE SCHEMA staging
2 GO
3
4 CREATE TABLE staging.tbDestino(
5     TipoPessoa      varchar(2)   NULL,
6     NomeCompleto    varchar(200) NULL,
7     Titulo          varchar(8)   NULL,
8     Prenome         varchar(50)  NULL,
9     NomeDoMeio      varchar(50)  NULL,
10     Sobrenome       varchar(50)  NULL,
11     Sufixo          varchar(50)  NULL,
12     EmailPromo      varchar(50)  NULL,
13     DataAtualizacao varchar(50)  NULL
14 ) ON PRIMARY
15 GO

O script de carga de dados

Agora é necessário tratar do comando BULK INSERT. Um detalhe que sempre causa confusão é que ao usarmos esse comando, devemos especificar um caminho de arquivo conforme ele é especificado no servidor onde roda a instância SQL.

No caso atual, os arquivos estão gravados no diretório ‘C:\TEMP’ do servidor. A  Listagem 3 mostra o comando necessário.

Listagem 3: comando BULK INSERT


1 BULK INSERT staging.tbDestino
2 FROM 'C:\Temp\Arquivo01.txt'
3 WITH (
4     FIELDTERMINATOR ='\t',
5     ROWTERMINATOR = '\n',
6     FIRSTROW = 12
7 )
8 GO

Temos 50 arquivos para importar e naturalmente seria possível executar esta tarefa repetindo esta instrução para cada um dos arquivos. Porém, o legal de fazer scripts genéricos é que eles podem ser reutilizados numa gama muito grande de situações. “Reusabilidade” é algo muito importante e você deve estar atento a isso quando cria seus scripts.

Este script de importação de múltiplos arquivos é fácil de criar, mas ainda assim pode ser reaproveitado para qualquer importação de arquivos que tenham nomes sequenciais. Basta criar as variáveis adequadas. No caso, eu uso os seguintes parâmetros:

  • Nome do arquivo (@vchArquivo)
  • Extensão do arquivo (@vchArquivoExtensao)
  • Diretório de origem (@vchDiretorioOrigem)
  • Nome completo da tabela de destino (@vchTabelaDestino)
  • Quantidade de arquivos a importar (@intQTDArquivos)
  • Número de linhas de cabeçalho (@intLinhasCabecalho)
  • Separador de colunas (@vchColuna)
  • Identificador de linha (@vchLinha)

 

A instrução inteira precisa ser montada e guardada numa outra variável que então será executada com a instrução EXEC(). A Listagem 4 mostra este script parametrizado de carga de dados.

Listagem 4: script de carga

 

1 declare @vchArquivo as varchar(50)
2 declare @vchArquivoExtensao as varchar(50)
3 declare @vchDiretorioOrigem as varchar(4000)
4 declare @vchTabelaDestino as varchar(200)
5 declare @intQTDArquivos as integer
6 declare @intLinhasCabecalho as integer
7 declare @vchColuna as varchar(2)
8 declare @vchLinha as varchar(2)
9
10 --insercao dos valores iniciais
11 set @vchArquivo = 'Arquivo'
12 set @vchArquivoExtensao = '.txt'
13 set @vchDiretorioOrigem = 'C:\TEMP\'
14 set @vchTabelaDestino = 'staging.tbDestino'
15 set @intQTDArquivos = 50
16 set @intLinhasCabecalho = 1
17 set @vchColuna = '\t'
18 set @vchLinha = '\n'
19
20 -- declaracao de variaveis de controle
21 declare @intContador as integer
22 declare @vchSQL as varchar(500)
23 declare @vchLinhaInicial as varchar(5)
24
25 set @intContador = 1
26 set @vchLinhaInicial = convert(varchar(5), (@intLinhasCabecalho + 1 ))
27
28
29 --limpa tabela de destino
30 set @vchSQL = 'truncate table ' + @vchTabelaDestino
31 exec(@vchSQL)
32
33 while @intContador <= @intQTDArquivos
34     begin
35
36     --define caminho completo para o arquivo de importação
37     set @vchSQL = @vchDiretorioOrigem + @vchArquivo
38         + convert(varchar(3), @intContador) + @vchArquivoExtensao
39
40     --define a instrucao completa do BULK INSERT   
41     set @vchSQL =  'BULK INSERT ' + @vchTabelaDestino
42         + ' FROM ''' + @vchSQL
43         + ''' WITH (FIELDTERMINATOR =''' + @vchColuna
44         + ''', ROWTERMINATOR = ''' + @vchLinha
45         + ''', FIRSTROW = ' + @vchLinhaInicial + ')'
46
47     -- importa o arquivo
48     exec(@vchSQL)
49
50     --identifica o novo arquivo
51     set @intContador += 1
52     end

O script de conversão de tipos de dados

O passo final é a conversão dos dados para os tipos de dados adequados. Eu costumo tratar esta tarefa em separado da carga de dados, porque é praticamente impossível generalizar qualquer tipo de tratamento. A conversão depende essencialmente do layout do arquivo de dados e, portanto, não há como generalizá-la sem restringir o layout considerado.

Neste estudo, o layout envolve campos VARCHAR na maioria dos casos. Existem apenas três exceções:

  • Campo TipoPessoa, usando CHAR(2)
  • Campo EmailPromo, que tem valores tipo INTEGER
  • Campo DataAtualizacao, com tipo DATE (entenda-se mm/dd/yy)

 

Na tabela de staging todos os campos usam VARCHAR. Mas as conversões de VARCHAR para CHAR ou para INTEGER são automáticas. É preciso tratar apenas da conversão das datas. Elas estão registradas no formato “dd.mm.yyyy”.

Observe que a conversão seria automática se o formato da coluna data fosse “mm/dd/yyyy”.  Mas no nosso caso é necessário haver um tratamento. É aqui que entra um truque que eu encontrei tempos atrás num fórum. É uma sugestão de Grzegorz Oledzki (veja Referências) e é um recurso que eu mantenho na minha “caixa de ferramentas”.

Convertemos as datas usando a função CONVERT(),  especificando o novo tipo de dados como DATE e o parâmetro de formatação 103, que representa o formato “dd/mm/yyyy”.

A Listagem 5 mostra a declaração INSERT com a conversão necessária:

Listagem 5: populando a tabela de destino


1 insert into dbo.tbDestino (
2      TipoPessoa
3     ,NomeCompleto
4     ,Titulo
5     ,Prenome
6     ,NomeDoMeio
7     ,Sobrenome
8     ,Sufixo
9     ,EmailPromo
10     ,DataAtualizacao   
11         )
12 select
13      TipoPessoa
14     ,NomeCompleto
15     ,Titulo
16     ,Prenome
17     ,NomeDoMeio
18     ,Sobrenome
19     ,Sufixo
20     ,EmailPromo
21     ,convert(datetime, DataAtualizacao,103) as DataAtualizacao 
22 from staging.tbDestino
23 GO

Conclusão

Carga de dados é um tópico muito importante para qualquer sistema novo. Neste aspecto, dispor de um script simples e versátil como aquele apresentado neste artigo pode ser de grande utilidade para desenvolvedores e DBAs.

Em resumo, o processo apresentado aqui envolve cinco etapas:

  1. Criação de uma tabela de staging com a mesma sequência de colunas dos arquivos de dados, usando sempre campos VARCHAR();
  2. Criação de uma tabela de pesquisa que use os tipos de dados adequados para cada coluna e também inclua uma chave primária;
  3. Adaptação (se necessário) dos nomes de arquivos de texto para que sejam sequenciais;
  4. Identificação dos parâmetros do processo de importação;
  5. Definição das transformações necessárias entre a tabela de staging e a de pesquisa.

 

Esta é uma solução bastante versátil e de implementação simples. E espero que lhe seja útil assim como tem sido para mim.

Referências

MICROSOFT. BULK INSERT (Transact-SQL). MSDN. MICROSOFT CORP
OLEDZKI, Grzegorz. How to convert a “dd/mm/yyyy” string to datetime in SQL Server? STACKOVERFLOW. Maio/2010
MICROSOFT. CAST and CONVERT (Transact-SQL). MSDN. MICROSOFT CORP

DialHost
DialHost

Contamos com nosso DataCenter no Brasil de alta qualidade, estabilidade e confiança para hospedar seu site. Utilize poderosas ferramentas para otimizar o uso do seu serviço e seja sempre atendido de forma rápida e clara pelo nosso atendimento 24h.

SQL Server e o clássico problema dos usuários órfãos

 

SQL Server

SQL Server

Se você já tem alguma experiência com SQL Server, muito provavelmente já teve problemas com uma conta de usuário que deixa de funcionar. Geralmente isso acontece após a execução de uma restauração da base que está recusando a conexão. Este é um problema clássico do SQL Server, conhecido como “usuário órfão”.

Muita coisa já foi publicada sobre este tema. Este artigo discute por quê ainda temos este problema depois de quase 20 anos de existência do SQL Server.

Um pouco de História

Segundo a Wikipedia, a história do SQL Server começa em 1989. Particularmente, eu prefiro dizer que a Microsoft lançou seu produto comercialmente em 1998. Antes disso, o que havia era uma versão do Sybase adaptada para Windows, fruto de uma parceria muito obscura da Microsoft com a empresa Sybase, que naturalmente terminou com um processo judicial.

Em 1998, já encerrada esta estranha parceria, a Microsoft lançou o SQL Server 7.0, que tinha semelhanças enormes com o produto Sybase, se é que você me entende… Muita coisa foi herdada e continua assim até hoje. Já no seu lançamento, o SQL Server 7.0 dispunha do chamado “modo de autenticação mista”. Isso quer dizer que o usuário poderia se logar usando autenticação do próprio SGBD (login + senha) ou então com autenticação pelo Windows (reconhecimento do usuário de Windows logado no domínio).

Porém, o modelo de segurança foi herdado do Sybase e permanece assim até hoje. Diferentemente de outros SGBDs, SQL Server e Sybase usam duas entidades distintas:

“login”: que autentica o usuário na instância
“user”: que controla os privilégios do usuário dentro de cada banco de dados
E é este modelo de segurança que dá origem ao problema dos usuários órfãos. Por conta disso, SQL Server e Sybase são igualmente afetados por este problema.

Entendendo os usuários órfãos

O problema começa pelo fato dos logins serem gravados na base de sistema Master, enquanto os usuários (ou “users” se preferir) são armazenados em cada base em que eles têm acesso. A visão MASTER.SYS.SYSLOGINS informa detalhes do login: número do identificador de segurança (SID), data de criação, senha (se usar autenticação SQL), autoridades a atribuídas ao nível da instância etc. Já a visão NomeBD.SYS.SYSUSERS informa detalhes do usuário: identificador do usuário (UID), data de criação e, entre vários outros detalhes, o SID associado.

Quando usamos a autenticação do Windows, o SGBD transfere a tarefa de autenticação de usuário para o próprio Windows. Portanto, o SQL Server precisa apenas identificar os logins que estão cadastrados, reaproveitando o SID já usado no domínio para aquele login.

Porém a coisa muda quando usamos autenticação SQL. Ao criarmos um novo login, o SGBD vai gerar um novo SID para este login. Ocorre que as instâncias do SQL Server são independentes, não havendo nenhum tipo de integração entre os SIDs cadastrados em cada instância.

Deste modo, quando criamos o mesmo login em duas ou mais instâncias (por exemplo, as instâncias SQL2012DEV e SQL2012PROD, referentes a desenvolvimento e produção, obviamente), serão gerados SIDs diferentes. Quando fizermos um backup da base BDTESTE de produção e a restaurarmos na instância de desenvolvimento, a visão BDTESTE.SYS.SYSUSERS trará o SID que era usado em produção e que não tem nenhuma correspondência com aquele usado na instância desenvolvimento.

E assim encontramos o famoso usuário órfão. Neste quadro, o login continuará funcionando normalmente e o dono daquele login conseguirá conectar em todas as bases… Exceto na base BDTESTE, que acabou de ser restaurada.

Corrigindo a situação

Este problema foi solucionado desde os primórdios do SQL Server, usando a rotina SP_CHANGE_USERS_LOGIN.

Este é um procedimento em dois passos. No primeiro, identificamos quem são os usuários órfãos da base, rodando o procedimento usando o parâmetro “Report”. Feito isso, podemos escolher entre três caminhos:

  1. Se o login e o “user” tem o mesmo nome, usamos os parâmetros “Autofix” e o nome do usuário.
  2. Se login e “user” forem diferentes, então devemos usar três parâmetros, que são a ação “Update_One”, o nome do “user” e o nome do login correspondente.
  3. Se não existe login adequado, então devemos usar quatro parâmetros, que são a ação “Update_One”, o nome do “user” e o nome do login a ser criado e a senha deste novo login.

 

A listagem 1 mostra este exemplo:

1 EXEC sp_change_users_login 'Report'
2 --alternativa 1
3 EXEC sp_change_users_login 'Auto_Fix', 'NomeUsuario'
4 -- alternativa 2
5 EXEC sp_change_users_login ' Update_One', 'NomeUsuario', 'LoginDiferente'
6 -- alternativa 3
7 EXEC sp_change_users_login ' Update_One', 'NomeUsuario', 'NovoLogin’, 'Senha'

 

Como evitar órfãos

Uma forma de evitar os usuários órfãos é usar apenas autenticação pelo Windows. Desta forma, o SQL Server não controla e não autentica nada. Quem faz isso é o sistema operacional. Esta é a estratégia usada pelo DB2, por exemplo.

O modelo de segurança do SQL Server é desenhado para trabalhar com instâncias independentes com múltiplas bases de dados. E isso realmente complica o uso de autenticação SQL.

Se desejássemos usar autenticação SQL e não termos problemas com usuários orfãos, seria necessário haver um repositório central de logins usados em todas as instâncias SQL no domínio, de modo que houvesse um único SID para cada login em todas as instâncias SQL.

Conclusão

Ter usuários órfãos é o preço que se paga para o SQL Server trabalhar ao mesmo tempo com a ideia de instâncias com múltiplas bases e também oferecer dois tipos de autenticação diferentes (Windows ou SQL).

A solução do problema é bem simples, como vimos aqui. E na minha opinião, este é um custo baixo pelas funcionalidades que o SQL Server oferece.

Leituras Sugeridas

NM, Vinoth.  Find Orphan SQL Users and Fix using ALTER USER. TOAD WORLD. 24/10/2013.
BERRY, Glenn. How To Avoid Orphaned Database Users with SQL Server Authentication.  SQLSKILLS. 23/08/2013.
SHEHZAD, Atif. Understanding and dealing with orphaned users in a SQL Server database.  MSSQLTIPS. 24/09/2008.
CAMPBELL, Michael K.  SQL Server 2012 Contained Databases.  SQL SERVER PRO. 20/09/2012. http://sqlmag.com/sql-server-2012/sql-server-2012-contained-databases

—-

Artigo de Wagner Crivelini, publicado originalmente no iMasters.

DialHost
DialHost

Contamos com nosso DataCenter no Brasil de alta qualidade, estabilidade e confiança para hospedar seu site. Utilize poderosas ferramentas para otimizar o uso do seu serviço e seja sempre atendido de forma rápida e clara pelo nosso atendimento 24h.