Início / dba / Perguntas / 123119
Accepted
Steve Mangiameli
Asked: 2015-12-08 10:40:42 +0800 CST

Como você GRANT SELECT no banco de dados de recursos ocultos mssqlsystemresource?

  • 772

Eu recebo um erro quando executo esta consulta:

    SELECT 
      dependencies.referencing_id as object_id,
      dependencies.referencing_minor_id as column_id,
      dependencies.referenced_id AS referenced_object_id,
      RTRIM(referenced_objects.type) AS referenced_type
    FROM 
      sys.sql_expression_dependencies AS dependencies
    JOIN sys.objects AS referenced_objects WITH(NOLOCK) ON referenced_objects.object_id = dependencies.referenced_id
    WHERE dependencies.referencing_id != dependencies.referenced_id

Erro :

A permissão SELECT foi negada no objeto 'sql_expression_dependencies', banco de dados 'mssqlsystemresource', esquema 'sys'.

O Login tem associação apenas na função de servidor PUBLIC e concedeu VIEW ANY DATABASE e VIEW ANY DEFINITION. Adicionei um usuário com associação na função de banco de dados PUBLIC para o banco de dados que estou comparando. Presumi que a permissão no nível do servidor seria suficiente, mas estava errado. Fiz isso em um banco de dados 2008R2, além de um banco de dados 2014.

No banco de dados de 2014, também concedi permissão de nível de servidor SELECT ALL USER SECURABLES. O erro persistiu.

Eu concedi o SELECT do usuário no banco de dados. O erro persistiu.

Eu removi a permissão SELECT e adicionei o usuário à função de banco de dados data_reader. A consulta foi resolvida, mas nenhum dado foi retornado (executar a mesma consulta como um administrador de sistema retorna mais de 130 linhas). Agora estou completamente perplexo.

Eu concedi SELECT ao usuário em sys.sql_expression_dependencies explicitamente com os mesmos resultados acima.

Depois de pesquisar essas permissões, o consenso parece ser que conceder SELECT a um usuário em um banco de dados é o mesmo que adicionar o usuário à função data_reader. Mas este obviamente não é o caso. Quero criar um Login/Usuário com o menor conjunto de privilégios possível para realizar a comparação.

Como faço para conceder acesso à exibição sys.sql_expression_dependencies em mssqlsystemresource sem conceder acesso data_reader em todo o banco de dados? Existe uma maneira de conceder SELECT apenas em mssqlsystemresource?

** Observação - li algumas outras postagens no SE sobre mssqlsystemresource, mas elas não se referem a isso:

  • Não tenho NEGAÇÃO de nada atribuído a este Login/Usuário
  • A participação na função PUBLIC (servidor ou banco de dados) não tem influência, pois as permissões são idênticas às de um servidor "conhecido em bom estado".
  • Prefiro que o usuário não tenha permissões SELECT gerais em todo o banco de dados.
sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Não tenho certeza onde você tropeçou ao longo do caminho, mas isso funciona para mim:

    CREATE LOGIN permtest WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE somedatabase;
GO
CREATE USER permtest FROM LOGIN permtest;
GO

    De acordo com esta página , o usuário precisa SELECTde permissão no sys.sql_expression_dependenciese VIEW DEFINITIONno banco de dados.

    Na minha experiência, o seguinte permitiu que o usuário selecionasse na exibição, mas retornou 0 linhas, porque eles não têm a capacidade de visualizar a definição (que inclui cadeias de dependência):

    GRANT SELECT ON sys.sql_expression_dependencies TO permtest;

    Para realmente ver qualquer relacionamento em somedatabase, também tive que adicionar o seguinte:

    GRANT VIEW DEFINITION ON DATABASE::floob TO permtest;

    Não consegui encontrar nenhuma maneira de tornar isso mais granular ( VIEW/ DENYdefinição funcionou para objetos individuais, mas sem o direito no nível do banco de dados, ainda não consegui ver nenhuma linha na exibição do catálogo e DENYnão impedi que os objetos aparecessem em a visualização do catálogo nem sequer me impediu de visualizar a definição). Eu sinto que o SQL Server teria dificuldade em resolver essa granularidade de qualquer maneira - se você tivesse uma visão que referenciasse uma tabela, como deveria ser a visão do catálogo se você concedesse na visão e negasse na tabela, ou vice-versa?

    Se você não deseja conceder VIEW DEFINITIONno banco de dados, crie procedimentos que usem EXECUTE AS OWNER, selecione (filtrado?) Linhas da exibição do catálogo e dê aos usuários (e, claro, isso também pode ser uma função) executar permissões no procedimento .

    CREATE PROCEDURE dbo.GetDependencies
WITH EXECUTE AS OWNER
AS
BEGIN
  SELECT is_schema_bound_reference --, ...
    FROM sys.sql_expression_dependencies;
END
GO

GRANT EXECUTE ON dbo.GetDependencies TO permtest;
    • 7

relate perguntas