permissão de usuário

Question

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

Answer 1

Pablicio,

 

Estas stored procedures estão alterando dados no Banco B?

 

Altere as permissões do usuários no Banco B para role db_datareader.

Answer 2

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

 

Answer 3

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

Answer 4

Pablicio,

 

Mas este usuário tem permissão para acessar, lêr e gravar dados no Temp?

Answer 5

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

Answer 6

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

Answer 7

Gustavo,

Temos bancos MSDE e SQLExpress

Answer 8

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.

 

 

Answer 9

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 ...

Answer 10

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

 

Answer 11

Ótimo. Aguardo o exemplo.
Obrigado.

Answer 12

Boa Tarde,

 

Para exemplificar segue um script demonstrando como resolver o problema postado

 

Code Snippet

-- Criação do banco A

CREATE DATABASE BancoA

GO

 

-- 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 BancoB

GO

 

-- 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', true

go

sp_dboption 'BancoB', 'db chaining', true

go

 

-- Criar a stored procedure em BancoA

USE BancoA

GO

CREATE PROCEDURE usp_Tbl

AS

SELECT CODIGO FROM tbl1

UNION ALL

SELECT CODIGO FROM BancoB.dbo.tbl2

GO

 

-- 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 BANCOB

GO

-- 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 BANCOA

GO

SETUSER 'UsrTeste'

GO

 

-- Executa a procedure

EXEC usp_tbl

 

-- Volta o contexto para o SA

SETUSER

 

-- Dropa os bancos de dados

USE MASTER

GO

 

DROP DATABASE BancoA

DROP DATABASE BancoB

 

 

Essa 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

Answer 13

Valeu ....
Obrigado.

Answer 14

Olá Pablício,

 

Que bom que resolveu sua dúvida

 

A propósito. No 2005 ao invés de usar a sp_grantdbaccess use a instrução CREATE USER. A sp_grantdbaccess funciona no 2005 mas ela irá criar um schema desnecessariamente.

 

[ ]s,

 

Gustavo

 

Answer 15

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.

Answer 16

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