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

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:

Se o login e o “user” tem o mesmo nome, usamos os parâmetros “Autofix” e o nome do usuário.
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.
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:

EXEC sp_change_users_login 'Report'
--alternativa 1
EXEC sp_change_users_login 'Auto_Fix', 'NomeUsuario'
-- alternativa 2
EXEC sp_change_users_login ' Update_One', 'NomeUsuario', 'LoginDiferente'
-- alternativa 3
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

De 0 a 10, o quanto você recomendaria este artigo para um amigo?

Powered by: