Início / dba / Perguntas / 239032
Accepted
ConstantineK
Asked: 2019-05-25 16:30:05 +0800 CST

Quais permissões são necessárias para UPDATE STATISTICS #table WITH ROWCOUNT = xxx?

  • 772

Alguém pode me indicar a documentação correta ou permissões adicionais que eu preciso além do SA para fazer o seguinte?

Ao executá-lo, recebo o seguinte erro:

Não é possível encontrar o objeto "#test" porque ele não existe ou você não tem permissões.

IF NOT EXISTS 
(
SELECT 
    name  
FROM sys.server_principals
WHERE 
    name = 'testlimiteduser'
)
BEGIN
    CREATE LOGIN [testlimiteduser] WITH PASSWORD=N'apassword', DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END

IF NOT EXISTS 
(
SELECT 
    name  
FROM sys.database_principals
WHERE 
    name = 'testlimiteduser'
)
BEGIN
    CREATE USER [testlimiteduser] FOR LOGIN [testlimiteduser] WITH DEFAULT_SCHEMA=[dbo]
END 

IF NOT EXISTS
(
    SELECT 1
    FROM sys.database_principals AS p
    WHERE 
        p.name like 'testlimiteduser_app'
)
BEGIN
    CREATE ROLE testlimiteduser_app
    GRANT EXECUTE TO testlimiteduser_app 
    ALTER ROLE [db_datareader] ADD MEMBER testlimiteduser_app
    ALTER ROLE [db_datawriter] ADD MEMBER testlimiteduser_app
    ALTER ROLE testlimiteduser_app ADD MEMBER [testlimiteduser]   
    ALTER ROLE db_owner ADD MEMBER testlimiteduser  --   https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 says db_owner or SA
END

GO

exec as user='testlimiteduser'

drop table if exists #test
create table #test (id int )
exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000') 
select * from #test 
revert;

Verificando a documentação em https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 diz que db_owner ou SA são tudo bem, mas nem parece que db_owner funciona nos meus testes.

sql-server permissions

4 respostas

  1. Best Answer

    Fazer uma conta db_owner, mesmo para [tempdb]ele, ainda é arriscado. Felizmente, não é necessário fazer isso para atingir a meta. Você pode simplesmente usar a assinatura de módulo para permitir que um procedimento armazenado que reside no [tempdb]uso da db_ownerfunção de banco de dados:

    Configuração inicial

    Isso apenas nos leva ao mesmo estado que o código fornecido na pergunta (menos o testlimiteduser_apppapel, que é desnecessário para esse objetivo).

    IF (DB_ID(N'NotTempDB') IS NULL)
BEGIN
    CREATE DATABASE [NotTempDB];
    ALTER DATABASE [NotTempDB]
      SET RECOVERY SIMPLE;
END;
GO

USE [NotTempDB];

IF (SUSER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE LOGIN [testlimiteduser]
      WITH PASSWORD = N'apassword',
      DEFAULT_DATABASE = [tempdb],
      CHECK_EXPIRATION = OFF,
      CHECK_POLICY = OFF;
END;
GO

IF (USER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE USER [testlimiteduser]
      FOR LOGIN [testlimiteduser];
END;

    Configuração básica

    Precisamos de um módulo que possa ser assinado (o SQL ad hoc não funcionará), mas ainda não o estamos assinando.

    Aqui nós:

    1. crie o procedimento armazenado
    2. conceder a capacidade de executar esse procedimento armazenado para o usuário de teste.
    GO
CREATE OR ALTER PROCEDURE dbo.[UpdateStats]
(
  @TableName sysname,
  @RowCount INT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = CONCAT(N'UPDATE STATISTICS ',
    QUOTENAME(@TableName),
    N' WITH ROWCOUNT = ',
    @RowCount);

EXEC (@SQL);
GO


GRANT EXECUTE ON dbo.[UpdateStats] TO [testlimiteduser];
GO

    TESTE 1

    Observe: é melhor usar EXECUTE AS LOGIN, não apenas AS USERporque LOGINé mais preciso em termos do que acontece quando o aplicativo realmente se conecta ao SQL Server, mas USERtambém adiciona restrições que podem distorcer o comportamento/resultados do teste.

    USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;

    Configuração de assinatura do módulo

    1. Crie o certificado
    2. Assine o procedimento armazenado com esse certificado
    3. Copie o certificado (somente chave pública) para[master]
    4. Crie o login baseado em certificado a partir dele.
    IF (CERT_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE CERTIFICATE [Permissions$UpdateStats]
    ENCRYPTION BY PASSWORD = 'Super Bass-o-matic ''76'
    WITH SUBJECT = N'Permission to exec UPDATE STATISTICS ... WITH ROWCOUNT = ...',
    EXPIRY_DATE = '2099-12-31';
END;


-- Associate permissions (via future cert-based login) to stored procedure:
ADD SIGNATURE TO dbo.[UpdateStats]
  BY CERTIFICATE [Permissions$UpdateStats]
  WITH PASSWORD = 'Super Bass-o-matic ''76';


-- Copy certificate (public key only) to [master]
-- and create the cert-based login from it.
DECLARE @CopySQL NVARCHAR(MAX) = N'
USE [master];
    CREATE CERTIFICATE [Permissions$UpdateStats]
      FROM BINARY = ' + CONVERT(NVARCHAR(MAX),
      CERTENCODED(CERT_ID(N'Permissions$UpdateStats')), 1) + N';

    CREATE LOGIN [Permissions$UpdateStats]
      FROM CERTIFICATE [Permissions$UpdateStats];
';

EXEC(@CopySQL);

    TESTE 2

    Assinamos o procedimento armazenado e o associamos a um login, mas ainda não demos a permissão necessária a esse login, portanto, os resultados são os mesmos.

    USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;

    Configuração final: atribuir permissão para login OU usuário

    Isso foi mantido como uma etapa separada apenas para deixar bem claro que é realmente a assinatura do módulo que está fazendo isso funcionar. E você tem duas opções (escolha apenas uma):

    1. adicione o login baseado em certificado existente à sysadminfunção no nível da instância 

      ALTER SERVER ROLE [sysadmin]
  ADD MEMBER [Permissions$UpdateStats];

      OU:

    2. Crie um usuário a tempdbpartir do login baseado em certificado e adicione o novo usuário à db_ownerfunção no nível do banco de dados

      USE [tempdb];
IF (USER_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE USER [Permissions$UpdateStats]
      FOR LOGIN [Permissions$UpdateStats];

    ALTER ROLE [db_owner]
      ADD MEMBER [Permissions$UpdateStats];
END;

    TESTE 3

    USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- SUCCESS!!!!

REVERT;

    Conclusão

    As principais diferenças entre as opções são:

    1. Opção 1
      • Bom: nenhum objeto tempdb(que precisa ser criado a cada início do serviço SQL Server)
      • Ruim: sysadmina permissão é mais poderosa (não exatamente "menos privilegiada")
    2. opção 2
      • Bom: db_owneré mais contido que sysadmin, em termos de potência
      • Bad: object in tempdb(que precisa ser criado a cada inicialização do serviço SQL Server)

    A diferença entresysadmin e db_owner` é mínima, se não relevante aqui, pois a única coisa que pode ser feita com qualquer uma dessas permissões é o que o código no procedimento armazenado faz (este é um dos principais benefícios da assinatura de módulo: é altamente granular).

    Portanto, em um nível prático, a complexidade de criar o usuário tempdbe adicioná-lo a db_ownercada inicialização do serviço SQL Server (que requer a criação de um procedimento armazenado em master, marcando-o como um "procedimento de inicialização" e habilitando a configuração em nível de instância opção "scan for startup procs") é pior do que fazer o login sysadmin.

    Se o banco de dados que precisa da permissão adicional fosse um banco de dados de usuário, eu provavelmente usaria a Opção 2, mesmo que apenas com o objetivo de tê-lo mais próximo do uso pretendido, em vez de ser uma preocupação real com relação sysadminao db_owner.

    Para obter uma explicação detalhada das etapas seguidas para aplicar a assinatura do módulo, consulte minha postagem:

    Use com segurança e facilidade permissões de alto nível sem concedê-las a ninguém: no nível do servidor

    Para obter mais informações sobre a assinatura do módulo em geral, consulte minha postagem:

    POR FAVOR, por favor, por favor, pare de usar personificação, TRUSTWORTHY e encadeamento de propriedade entre bancos de dados

    • 4

  2. Você precisa executar exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000')dentro do banco de dados tempdb.

    Basicamente, as tabelas temporárias existem no tempdb, não em outros.

    • 1

  3. Eh, parece que eu estava em erro. A resposta é simples: o usuário não tem permissões no banco de dados tempdb para executar este comando de atualização de estatísticas

    As tabelas temporárias são criadas no banco de dados tempdb e as permissões por padrão são limitadas.

    Existem quatro tipos de tabelas:

    1. Mesas Físicas
      • Tabelas que são permanentes e guardam valores finais. A propriedade está sempre relacionada ao banco de dados em que são criados.
    2. @Tabelas de Variáveis
      • Tabelas Tempdb que existem apenas em uma instrução de lote e são limpas em cada GOseparador de lote ou no final do script
      • O SQL Optimizer não tem estatísticas e o trata como tendo em linha.
    3. #Tabelas temporárias
      • Tabelas Tempdb que persistem por toda a sessão, não apenas uma instrução em lote.
      • as estatísticas são criadas e persistem no momento da criação.
      • Não altere uma tabela temporária
      • É limpo pelo coletor de lixo no final da sessão, portanto, não descarte tabelas temporárias
      • é sempre criado no esquema dbo. Qualquer referência, mesmo na instrução create, a outro esquema é ignorada.
    4. ##Tabelas Globais
      • o dual ## no nome (##temptable vs #temptable) permite que você saiba sua global
      • Tabelas Tempdb que estão disponíveis para todos os usuários na Instância.
      • pode ser modificado por outro processo durante sua execução em lote
      • É limpo depois que todas as sessões que fazem referência à tabela são fechadas.
      • é sempre criado no esquema dbo. Qualquer referência, mesmo na instrução create, a outro esquema é ignorada.

    Vale a pena notar que os objetos Temp podem ser referenciados via tempdb..#tablename, embora o uso explícito do nome tempdb em instruções normais seja ignorado, pois todas as tabelas temporárias são criadas no Tempdbe no dboesquema

    Observe como todos eles estão no banco de dados tempdb. A razão pela qual sua instrução está falhando é simplesmente devido à tabela #temp existente no tempdb e você provavelmente não deu ao usuário permissões no tempdb para realizar a atualização.

    Normalmente, tudo o que você precisa para conceder alter na tabela, embora eu tenha descoberto que as tabelas temporárias são um pouco diferentes e podem exigir permissões mais altas. mas como esta é a mesma sessão, basta fazer isso:

    REVERT
UPDATE STATISTICS ON #TABLE
EXEC AS USER='User’

    Agora você nem precisa conceder permissões elevadas ao usuário e satisfez seu problema.

    • 1

  4. Eu fiz algumas pesquisas adicionais e encontrei o seguinte: Qualquer consulta na tabela temporária funciona bem, é o WITH ROWCOUNT que causa o problema. Um padrão UPDATE STATISTICS também prossegue sem problemas.

    Passei pela documentação do MSDN e não encontrei uma nota explícita para as permissões SET ROWCOUNT combinadas com UPDATE STATISTICS - após algumas especulações e testes, descobri que a documentação do DBCC UPDATEUSAGE estabelece a necessidade de sysadmin ou db_owner no banco de dados que contém o tabela.

    Se você adicionar isso ao meu exemplo, o código funciona:

    USE [tempdb]
GO
CREATE USER [testtwouser] FOR LOGIN [testtwouser]
GO
USE [tempdb]
GO
ALTER ROLE [db_owner] ADD MEMBER [testtwouser]
GO

    Infelizmente, meu código original não esclareceu que está sendo executado em outro banco de dados além do tempdb, mas essa é a suposição implícita de que, se você ignorar, todo o script funcionará (porque agora eles têm tempdb db_owner.)

    • 1

relate perguntas