Usuário com melhor resposta
permissão de usuário

Pergunta
-
Senhores,
Estou tendo alguns problemas com pemissão para usuário.
No nosso ambiente tenho vários bancos e tem alguns que alguns usuários não podem alterar estrutura, sp, triggers e etc.
Fiz o seguinte:
Banco A - todos podem acessar - associei um grupo de usuário criado no Windows como sysAdmin neste banco
Banco B - alguns podem ter acesso - associei o grupo que pode como sysAdmin, e os demais deixei na role Public, db_datareader e db_datawriter.
Muito bem, está quase tudo correto (pelo menos estou acredito estar certo)
O erro acontece porque tem procedures que estão no Banco A que estão acessando o banco B (inner join para devolver valores). Neste caso está dando erro, porque o usuário está com permissão apenas para leitura. As sp estão com grant to public (por enquanto).
O que devo mudar? Onde estou errando?
Obrigado.
Pablicio
Respostas
-
Boa Tarde,
Para exemplificar segue um script demonstrando como resolver o problema postado
Code Snippet-- Criação do banco A
CREATE
DATABASE BancoAGO
-- Criação da tabela tbl1 no banco A
CREATE
TABLE BancoA.dbo.tbl1 (CODIGO INT)INSERT
INTO BancoA.dbo.tbl1 (CODIGO) VALUES (1)-- Criação do banco B
CREATE
DATABASE BancoBGO
-- Criação da tabela tbl2 no banco B
CREATE
TABLE BancoB.dbo.tbl2 (CODIGO INT)INSERT
INTO BancoB.dbo.tbl2 (CODIGO) VALUES (2)-- Habilitar o Cross Database Owner Ship Chaining em BancoA e BancoB
sp_dboption
'BancoA', 'db chaining', truego
sp_dboption
'BancoB', 'db chaining', truego
-- Criar a stored procedure em BancoA
USE
BancoAGO
CREATE
PROCEDURE usp_TblAS
SELECT
CODIGO FROM tbl1UNION
ALLSELECT
CODIGO FROM BancoB.dbo.tbl2GO
-- Cria um login e dá permissões no bancoA
exec
master.dbo.sp_addlogin 'UsrTeste','SenhaTeste'GO
sp_grantdbaccess
'UsrTeste','UsrTeste'go
GRANT
EXECUTE ON usp_tbl TO UsrTeste-- Muda o contexto para o usuário Teste e executa a procedure
-- Um erro de permissão ocorre
SETUSER
'UsrTeste'GO
EXEC
usp_tbl-- Muda o contexto para o SA
SETUSER
-- Cria o usuário Guest em BancoB
USE
BANCOBGO
-- Habilita o usuário guest (ele já existe em qualquer banco mas é desabilitado)
sp_adduser
'guest'-- Muda o contexto para o usuário usrTeste
USE
BANCOAGO
SETUSER
'UsrTeste'GO
-- Executa a procedure
EXEC
usp_tbl-- Volta o contexto para o SA
SETUSER
-- Dropa os bancos de dados
USE
MASTERGO
DROP
DATABASE BancoADROP
DATABASE BancoBEssa solução mostra que o login SA cria uma stored procedure que acessa dados de bancos diferentes. Como o SA tudo pode não há problema. Ao dar a permissão para o usuário UsrTeste esse não consegue executar a procedure já que o Cross Database Owner Ship Chain não está completo. Após o usuário guest ter sido habilitado, o usuário UsrTeste consegue executar a procedure com êxito. Não foi necessário nenhuma permissão adicional no banco B (o usuário usrTeste nem consegue logar no banco B) e nem colocá-lo em nenhuma role no banco B.
Claro que nem tudo são flores... Habilitar o Cross Database Ownership chaining irá resolver um problema de segurança mas pode criar outro. Como os bancos estão habilitados a trabalhar com ele, qualquer um que criar uma procedure em BancoA que acesse recursos em BancoB (desde que o usuário criador tenha permissões para isso) poderá dar a permissão de execução para outros usuários e nesse caso os recursos podem envolver vários bancos comprometendo o gerenciamento da segurança (como apenas olhando para o bancoB saberemos que um usuário do bancoA tem acesso)
Por essas e outras que o 2005 desenvolveu a segurança baseada em certificados e assinaturas que resolve de uma forma bem superior questões de acesso intra bancos e intra servidores. No entanto, como você tem o MSDE envolvido teremos que nos limitar ao Cross Database Owner Shipchaining.
Opcionalmente você poderia ter criado um usuário em BancoB para o login UsrTeste e dar permissões em Tbl2. No entanto, dessa forma bastaria que ele se conectasse em BancoB e poderia acessar tbl2 diretamente (muitas vezes isso não é desejável). Se muitas tabelas fossem necessárias, aí sim poderíamos avaliar a solução do Jr. em adicionar o usuário na role db_datareader ao invés de dar permissões individuais em tabelas (segurança vs manutenção). Isso somente se não optássemos pelo cross database ownership chaining.
Agora é escolher qual você quer implementar...
[ ]s,
Gustavo
Todas as Respostas
-
-
Olá Pablício,
Se você deseja que um usuário no banco A acesse um recurso no banco B você tem duas possibilidades
Criação do usuário no banco B
Partindo do pressuposto que o usuário UsrA representa o login LoginX, você deve criar um usuário no banco B (UsrB) mapeado para o login LoginX e dar permissão nas tabelas usadas pela Stored Procedure. Essa abordagem funciona mas representa riscos a segurança. Uma vez que o LoginX tem permissão direta no Banco B, ele pode ir direto ao banco B sem utilizar a sua Stored Procedure
Ativação do recurso Cross Database Ownership Chain
Nesse caso, uma relação de confiança entre os bancos será estabelecida e se os bancos confiam mutuamente um no outro, uma procedure criada no banco A poderá ser acessar recursos no banco B sem que se tenha que criar um usuário para isso. Para fazer isso existem alguns pré-requisitos.
- O Owner do banco deve ser o mesmo (se for o caso use a procedure sp_changeobjectowner)
- Os bancos devem ter a opção 'db chaining' ativada como on (se for o caso use a procedure sp_dboption)
- Os bancos devem ter o usuário guest habilitado
Embora essa última seja melhor que a primeira ela ainda é carente em termos de segurança (uma vez que o Cross Database Owner Ship Chaining esteja ativado é possível criar outras procedures com o mesmo comportamento). No SQL Server 2005 temos o uso de certificados e assinaturas para contonar esse problema
[ ]s,
Gustavo
-
Júnior,
Os usuários do Banco B já tem permissão para role db_datareader
As sps executam join entre tabelas dos dois bancos de aloca o resultado em uma tem e devolve o resultado da temp. (isso se deve por que o MSDE quando uma coluna é identity apresenta problema no recordset - item que deverei pesquisar também).
Estou achando que o fato de jogar para uma tabela temp pode ser o problema. O que acha? Tenho que dar permissão em mais algum banco?
Então a sp tem mais ou menos a lógica abaixo:
SET NOCOUNT ON
SELECT
*
INTO #TEMP
FROM bancoa..tabela_a inner join bancob..tabela_b on tabela_a.id = tabela_b.id
SET NOCOUNT OFF
SELECT * FROM #TEMP WITH (NOLOCK)
DROP TABLE #TEMP -
-
Gustavo,
O owner dos bancos são os mesmos.
Quanto a opção db chaining estão assim:
ANSI null default
ANSI nulls
ANSI padding
ANSI warnings
arithabort
auto create statistics
auto update statistics
autoclose
autoshrink
concat null yields null
cursor close on commit
db chaining
dbo use only
default to local cursor
merge publish
numeric roundabort
offline
published
quoted identifier
read only
recursive triggers
select into/bulkcopy
single user
subscribed
torn page detection
trunc. log on chkpt.
Agora o guest está desabilitado. Como faço para habilitá-lo?
valeu -
Olá Pablício,
Esse é o result da procedure sp_dboption. Ela diz as opções possíveis mas não as ativadas. Use sp_dboption 'Nome Banco' para verificar as opções ativas e sp_dboption 'Nome Banco', 'opcao', true para ativar uma determinada opção
Para habilitar o GUEST use sp_grantdbaccess 'guest'
Seu SQL Server é 2000 ou 2005 ?
Sobre o TEMPDB não é necessário permissões para a tabela temporária. Você pode averiguar isso retornando apenas o SELECT em sua SP.
Como boa prática de segurança, evite colocar usuários na role db_datareader. Você estará permitindo que o login conecte-se diretamente ao banco de dados B e possa ler todos os dados desse banco sem restrição. Isso é uma violação do princípio do menor privilégio. Se realmente for optar por essa estratégia, dê permissão apenas nas tabelas usadas pela procedure (prefiro a solução do cross database ownership chaining)
[ ]s,
Gustavo
-
-
Gustavo,
Bom respeito a sua colocação, principalmente por seu conhecimento tão elevado em relação ao SQL Server, mas dependendo da quantidade de tables existentes nos bancos de dados, ficar dando permissão para cada objeto é algo muito cansativo e complicado.
Reconheço que a utilização da role db_datareader representa alguns situações que possam permitir o acesso de determinados objetos desnecessário, mas como você bem sabe tem situações que nos forçam a trabalhar desta forma.
-
Gustavo,
Habilitei as opções e o usuário guest conforme orientação.
Mas ao executar estou recebendo a seguinte mensagem:
Msg 1088, Level 16, State 11, Procedure PR_bpm_pessoa_s, Line 32
Não é possível encontrar o objeto "bpm_pessoa" porque ele não existe ou você não tem permissões.
Tentei executar estando no contexto do BancoA e do BancoB
Valeu ... -
Oi Jr.
O mais difícil quando se fala em segurança é balancear o ônus da proteção vs manutenção. Quanto mais se detalha, quanto mais se é específico, mas se tem segurança mas junto com essa segurança são mais objetos, políticas e detalhezinhos a serem administrados e muitas vezes a complexidade nessa administração acaba por deixá-la difícil e por indiretamente comprometer a segurança
Quando se é genérico a manutenção é bem mais fácil, mas a segurança fica um pouco mais comprometida.
Adicionar o usuário a role db_datareader certamente representa um ponto nesse tradeoff (segurança vs manutenção). No entanto, o que quis dizer não foi criticar sua situação e como você bem disse algumas situações nos forçam a trabalhar dessa forma.
Só que acho que se usarmos a solução do cross database owner ship chaining poderemos ter segurança e manutenção em um nível melhor do que adicionar os usuários nas role db_datareader. Com o cross database owner ship chaining não será necessário dar permissão em nenhuma tabela e nem em nenhuma role. Basta dar permissão na sp independente das tabelas que ela usa (desde que o criador da SP tenha as permissões necessárias).
No entanto, senão optarmos por essa solução, talvez colocar o usuário na role como você falou possa ser um bom negócio já que dar permissões individuais pode ser bem cansativo...
Vou postar um exemplo dessa feature logo que possível. Acho que irá esclarescer as dúvidas aqui.
[ ]s,
Gustavo
-
-
Boa Tarde,
Para exemplificar segue um script demonstrando como resolver o problema postado
Code Snippet-- Criação do banco A
CREATE
DATABASE BancoAGO
-- Criação da tabela tbl1 no banco A
CREATE
TABLE BancoA.dbo.tbl1 (CODIGO INT)INSERT
INTO BancoA.dbo.tbl1 (CODIGO) VALUES (1)-- Criação do banco B
CREATE
DATABASE BancoBGO
-- Criação da tabela tbl2 no banco B
CREATE
TABLE BancoB.dbo.tbl2 (CODIGO INT)INSERT
INTO BancoB.dbo.tbl2 (CODIGO) VALUES (2)-- Habilitar o Cross Database Owner Ship Chaining em BancoA e BancoB
sp_dboption
'BancoA', 'db chaining', truego
sp_dboption
'BancoB', 'db chaining', truego
-- Criar a stored procedure em BancoA
USE
BancoAGO
CREATE
PROCEDURE usp_TblAS
SELECT
CODIGO FROM tbl1UNION
ALLSELECT
CODIGO FROM BancoB.dbo.tbl2GO
-- Cria um login e dá permissões no bancoA
exec
master.dbo.sp_addlogin 'UsrTeste','SenhaTeste'GO
sp_grantdbaccess
'UsrTeste','UsrTeste'go
GRANT
EXECUTE ON usp_tbl TO UsrTeste-- Muda o contexto para o usuário Teste e executa a procedure
-- Um erro de permissão ocorre
SETUSER
'UsrTeste'GO
EXEC
usp_tbl-- Muda o contexto para o SA
SETUSER
-- Cria o usuário Guest em BancoB
USE
BANCOBGO
-- Habilita o usuário guest (ele já existe em qualquer banco mas é desabilitado)
sp_adduser
'guest'-- Muda o contexto para o usuário usrTeste
USE
BANCOAGO
SETUSER
'UsrTeste'GO
-- Executa a procedure
EXEC
usp_tbl-- Volta o contexto para o SA
SETUSER
-- Dropa os bancos de dados
USE
MASTERGO
DROP
DATABASE BancoADROP
DATABASE BancoBEssa solução mostra que o login SA cria uma stored procedure que acessa dados de bancos diferentes. Como o SA tudo pode não há problema. Ao dar a permissão para o usuário UsrTeste esse não consegue executar a procedure já que o Cross Database Owner Ship Chain não está completo. Após o usuário guest ter sido habilitado, o usuário UsrTeste consegue executar a procedure com êxito. Não foi necessário nenhuma permissão adicional no banco B (o usuário usrTeste nem consegue logar no banco B) e nem colocá-lo em nenhuma role no banco B.
Claro que nem tudo são flores... Habilitar o Cross Database Ownership chaining irá resolver um problema de segurança mas pode criar outro. Como os bancos estão habilitados a trabalhar com ele, qualquer um que criar uma procedure em BancoA que acesse recursos em BancoB (desde que o usuário criador tenha permissões para isso) poderá dar a permissão de execução para outros usuários e nesse caso os recursos podem envolver vários bancos comprometendo o gerenciamento da segurança (como apenas olhando para o bancoB saberemos que um usuário do bancoA tem acesso)
Por essas e outras que o 2005 desenvolveu a segurança baseada em certificados e assinaturas que resolve de uma forma bem superior questões de acesso intra bancos e intra servidores. No entanto, como você tem o MSDE envolvido teremos que nos limitar ao Cross Database Owner Shipchaining.
Opcionalmente você poderia ter criado um usuário em BancoB para o login UsrTeste e dar permissões em Tbl2. No entanto, dessa forma bastaria que ele se conectasse em BancoB e poderia acessar tbl2 diretamente (muitas vezes isso não é desejável). Se muitas tabelas fossem necessárias, aí sim poderíamos avaliar a solução do Jr. em adicionar o usuário na role db_datareader ao invés de dar permissões individuais em tabelas (segurança vs manutenção). Isso somente se não optássemos pelo cross database ownership chaining.
Agora é escolher qual você quer implementar...
[ ]s,
Gustavo
-
-
-
Gustavo,
Mais uma dúvida.
Você dá permissão de execução para a sp criada para o usuário específico. Correto?
GRANT EXECUTE ON usp_tbl TO UsrTeste
Muito bem, a sp que estou tentando executar está assim:
GRANT EXECUTE ON <nome da sp> TO PUBLIC
Neste caso tem que funcionar?
Sei que sps com grant to public são periogosas e coloca a segurança em check, mas estamos trabalho para tirar isso.
Obrigado.
-
Olá Pablício,
Se você for usar a role public será necessário utilizar o recurso de cross database ownership chaining. Isso por que da outra forma, só funcionaria porque um mesmo login tem usuários em bancos diferentes. Como a role public não pode estar associada a nenhum login apenas com a implementação Cross database ownership chaining isso seria possível.
Em princípio tem de funcionar embora eu nunca tenha lidado com uma situação dessas. Como você mesmo disse, atribuir permissões a role public pode ser perigoso.
[ ]s,
Gustavo