O Guia Definitivo para Conceder Permissões em Todas as Tabelas de Usuário no SQL Server

Gerenciar permissões no SQL Server é crucial para manter a integridade e segurança do seu banco de dados. Um cenário comum surge quando você precisa conceder permissões em todas as tabelas de usuário a um papel de banco de dados específico de forma transparente. Se você se sente frustrado ao escrever vários scripts ou ajustando permissões manualmente toda vez que adiciona novas tabelas, você não está sozinho. Neste guia, exploraremos como criar um script SQL versátil que permite conceder permissões de SELECT, REFERENCES, INSERT, UPDATE e DELETE a um papel de banco de dados em todas as tabelas de usuário com facilidade.

O Desafio: Conceder Permissões de Forma Eficiente

O SQL Server Management Studio (SSMS) geralmente gera scripts para objetos de banco de dados individuais, o que pode ser tedioso ao lidar com várias tabelas. Além disso, modificar permissões para tabelas recém-adicionadas pode rapidamente se tornar uma tarefa que consome tempo. O que você precisa é de um script robusto de “fire-and-forget” que possa ser executado para aplicar permissões em todas as tabelas de usuário sem a necessidade de revisitar o script a cada vez.

A Solução: Um Procedimento Armazenado para Concessão de Permissões

Para automatizar eficazmente a concessão de permissões, podemos criar um procedimento armazenado que utiliza um cursor para percorrer todos os objetos de usuário e conceder as permissões desejadas. Vamos dividir o processo em etapas gerenciáveis:

Etapa 1: Criar o Procedimento Armazenado

Comece definindo seu procedimento armazenado. Use o seguinte código SQL como modelo:

IF EXISTS (
    SELECT 1 FROM sysobjects
    WHERE name = 'sp_grantastic'
    AND type = 'P'
)
DROP PROCEDURE sp_grantastic
GO

CREATE PROCEDURE sp_grantastic
AS
DECLARE
 @object_name VARCHAR(30),
 @time VARCHAR(8),
 @rights VARCHAR(20),
 @role VARCHAR(20)

DECLARE c_objects CURSOR FOR
    SELECT name
    FROM sysobjects
    WHERE type IN ('P', 'U', 'V')
    FOR READ ONLY

BEGIN
    SELECT @rights = 'ALL',
           @role = 'PUBLIC'

    OPEN c_objects
    WHILE (1=1)
    BEGIN
        FETCH c_objects INTO @object_name
        IF @@SQLSTATUS <> 0 BREAK

        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)
        PRINT '[%1!] atingindo o objeto %2!', @time, @object_name
        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)
    END

    PRINT '[%1!] fim!', @time

    CLOSE c_objects
    DEALLOCATE CURSOR c_objects
END
GO

GRANT ALL ON sp_grantastic TO PUBLIC
GO

Etapa 2: Compreender os Componentes

• Declaração do Cursor: O cursor c_objects é configurado para percorrer todas as tabelas de usuário (U), views (V) e procedimentos armazenados (P).
• Execução das Concessões: Para cada objeto recuperado pelo cursor, o sistema executa um comando GRANT, aplicando os direitos definidos ao papel especificado.
• Informações de Saída: O procedimento imprime logs indicando o horário da execução e quais objetos receberam permissões.

Etapa 3: Executar o Procedimento

Uma vez que o procedimento armazenado é criado, você pode executá-lo com um comando simples:

EXEC sp_grantastic

Com apenas esse comando, seu papel de banco de dados receberá as permissões necessárias em todas as tabelas de usuário de forma transparente.

Conclusão

Você acabou de aprender a criar um poderoso script SQL que concede permissões a um papel de banco de dados em todas as tabelas de usuário de maneira simples. Essa abordagem não apenas otimiza seu fluxo de trabalho, mas também garante que suas permissões estejam sempre atualizadas sem intervenção manual. Da próxima vez que você adicionar uma nova tabela ao seu banco de dados, basta executar o procedimento armazenado novamente, e você estará pronto para seguir em frente.

Feliz codificação!