none
permissão de usuário RRS feed

  • 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
    terça-feira, 29 de abril de 2008 13:09

Respostas

  • 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

    terça-feira, 29 de abril de 2008 18:47

Todas as Respostas

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

    terça-feira, 29 de abril de 2008 13:20
  • 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

     

    terça-feira, 29 de abril de 2008 13:35
  • 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

    terça-feira, 29 de abril de 2008 13:45
  • Pablicio,

     

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

    terça-feira, 29 de abril de 2008 13:51
  • 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
    terça-feira, 29 de abril de 2008 14:13
  • 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

    terça-feira, 29 de abril de 2008 14:44
  • Gustavo,

    Temos bancos MSDE e SQLExpress


    terça-feira, 29 de abril de 2008 15:47
  • 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.

     

     

    terça-feira, 29 de abril de 2008 16:43
  • 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 ...


    terça-feira, 29 de abril de 2008 16:44
  • 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

     

    terça-feira, 29 de abril de 2008 18:09
  • Ótimo. Aguardo o exemplo.
    Obrigado.
    terça-feira, 29 de abril de 2008 18:42
  • 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

    terça-feira, 29 de abril de 2008 18:47
  • Valeu ....
    Obrigado.
    terça-feira, 29 de abril de 2008 18:54
  • 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

     

    terça-feira, 29 de abril de 2008 19:01
  • 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.

    terça-feira, 29 de abril de 2008 19:09
  • 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

     

    terça-feira, 29 de abril de 2008 19:21