Início / dba / Perguntas / 322561
Accepted
adam.g
Asked: 2023-01-26 06:11:04 +0800 CST

Que permissão deve ser definida para o usuário SQL para configuração do controle de alterações no banco de dados?

  • 772

Estou construindo um aplicativo que precisa ter a opção de configurar (desativar/ativar) o controle de alterações em tabelas em um banco de dados específico. Estou me conectando ao banco de dados via usuário SQL. Quais permissões de banco de dados precisam ser adicionadas para o usuário SQL para desativar/ativar o rastreamento de alterações nas tabelas e a opção de executar a consulta abaixo:

SELECT DISTINCT
       sct1.name                                             AS CT_schema
       , sot1.name                                           AS CT_table
       , ps1.row_count                                       AS CT_rows
       , sct2.name                                           AS tracked_schema
       , sot2.name                                           AS tracked_name
       , CHANGE_TRACKING_MIN_VALID_VERSION(sot2.object_id)   AS min_valid_version
       , itt.create_date                                     AS change_tracking_table_creation_date
       , CAST(ps1.reserved_page_count * 8. / 1024 AS BIGINT) AS CT_reserved_MB
       , CAST(ps2.reserved_page_count * 8. / 1024 AS BIGINT) AS tracked_base_table_MB
       , ps2.row_count                                       AS tracked_rows
FROM sys.internal_tables            it
JOIN sys.objects                    sot1
  ON it.object_id = sot1.object_id
JOIN sys.schemas                    AS sct1
  ON sot1.schema_id = sct1.schema_id
JOIN sys.dm_db_partition_stats      ps1
  ON it.object_id = ps1.object_id
     AND ps1.index_id IN (0, 1)
LEFT JOIN sys.objects               sot2
       ON it.parent_object_id = sot2.object_id
LEFT JOIN sys.schemas               AS sct2
       ON sot2.schema_id = sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2
       ON sot2.object_id = ps2.object_id
          AND ps2.index_id IN (0, 1)
INNER JOIN sys.internal_tables      itt
        ON itt.name = sot1.name
WHERE it.internal_type IN (209, 210);
t-sql

1 respostas

  1. Best Answer

    A melhor abordagem é uma combinação de tentar e ler a documentação.

    Estou assumindo que o Change Tracking (CT) já está ativado no nível do banco de dados.

    Vamos criar um novo login e usuário nesse banco de dados (estou usando o StackOverflow)

    CREATE LOGIN BillyNoRights WITH PASSWORD = '$tr0ngPassword'
GO
USE [StackOverflow]
GO 
CREATE USER BillyNoRights FROM LOGIN BillyNoRights

    Em seguida, você pode representar o Login e executar sua consulta.

    EXECUTE AS LOGIN = 'BillyNoRights'
SELECT DISTINCT
       sct1.name  
…

    Dando um erro:

    Msg 262, Nível 14, Estado 1, Linha 9
    Permissão VIEW DATABASE STATE negada no banco de dados 'StackOverflow'.
    Msg 297, Nível 16, Estado 1, Linha 9
    O usuário não tem permissão para executar esta ação.

    (Isso se aplica ao SQL 2019 e anteriores. As permissões para SQL 2022 são VIEW DATABASE PERFORMANCE STATE)

    Se você conceder essa permissão (de uma sessão diferente que não está representando), poderá executar sua consulta.

    GRANT VIEW DATABASE STATE TO BillyNoRights

    A segunda parte é sobre habilitar o Change Tracking em qualquer tabela. Você não forneceu um código para isso, mas a sintaxe é simples

    ALTER TABLE dbo.Votes
ENABLE CHANGE_TRACKING

    Desta vez, o erro não é tão útil

    Msg 1088, Nível 16, Estado 13, Linha 40
    Não é possível encontrar o objeto "Votes" porque ele não existe ou você não tem permissões.

    Temos que olhar a documentação para a instrução Alter Table

    Requer permissão ALTER na tabela.

    Podemos conceder permissão granular em tabelas, mas como você deseja habilitar CT em qualquer tabela (possivelmente até em tabelas futuras), temos que ser menos granulares.

    A permissão de nível de banco de dados mais próxima que pude encontrar é

    GRANT ALTER ANY SCHEMA TO BillyNoRights

    que funciona, mas dá ao usuário permissão para ALTERquaisquer objetos (procedimentos, funções, etc.)

    Portanto, eu recomendaria um SQL dinâmico envolvido em um procedimento armazenado com um nome de tabela como parâmetro.

    Como fazer isso corretamente e sem injeções SQL está fora do escopo desta questão, então gostaria de apontar para Permissões de empacotamento em procedimentos armazenados por Erland Sommarskog

    • 3

relate perguntas