[SQL Server] Replicando acessos administrativos para usuários em um SQL Server gerenciado em Cloud

Recentemente tive a necessidade de inserir algumas instancias SQL Server que administro no Hashicorp Vault para que ele gerencia-se a criação de usuários de forma segura e auditada (posteriormente farei um post sobre como efetuar esta integração).

O Vault trabalha com o conceito de roles, cada role possui um comando de criação de usuário e um comando de remoção deste usuário, isso por que um usuário criado pelo Vault tem tempo para existir, isso é uma boa prática de segurança que deve ser observada.

Dado o contexto do Vault, qual o intuito deste post? Simples, o Vault executa um comando SQL para criar o usuário, mas e quando precisamos criar um usuário administrativo em uma instancia de banco de dados gerenciada em Cloud? Pois é. Os serviços gerenciados de bancos de dados em Cloud Pública restringem acessos dos usuários, mesmos dos usuários administrativos\master da instancia.

Outro exemplo de uso dessas procedures é quando não queremos que o usuário tenha permissões de SYSADMIN na instancia, limitando assim o nível de acesso a componentes e features internos da instancia como AlwaysOn, Resource Governor, etc.

Abaixo deixo 2 exemplos de procedures que podem ser criadas para conceder acesso a usuários de forma dinâmica de forma simples.

CREATE PROCEDURE sp_create_user (
        @user_name VARCHAR(100)
        ,@user_password NVARCHAR(512)
        ,@user_type VARCHAR(5) -- admin ou read
)
AS
BEGIN

    SET XACT_ABORT ON
    SET NOCOUNT ON

    DECLARE @command NVARCHAR(MAX)
        , @database_name VARCHAR(50)
        , @cont INT = 1

    SET @command = '
        USE master;

        CREATE LOGIN [' + @user_name +'] WITH PASSWORD=''' + @user_password + ''' , DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; 

        ALTER SERVER ROLE [PROCESSADMIN] ADD MEMBER [' + @user_name + '];

        ALTER SERVER ROLE [SETUPADMIN] ADD MEMBER [' + @user_name + '];
    '

    EXEC sp_executesql @command;

    IF @user_type = 'admin'
    BEGIN

        WHILE @cont <= (SELECT MAX(database_id) FROM sys.databases)
        BEGIN

            SET @database_name = DB_NAME(@cont)

            IF @database_name IS NOT NULL AND @database_name NOT IN ('master','model','tempdb','Distribuition','rdsadmin','ReportServer','ReportServerTempDB')
            BEGIN

                SET @command = 'USE [' + @database_name + ']; CREATE USER [' + @user_name + '] FOR LOGIN [' + @user_name + '] WITH DEFAULT_SCHEMA=[dbo];'

                EXEC sp_executesql @command;

                IF @database_name NOT IN ('msdb')
                BEGIN

                    SET @command = 'USE [' + @database_name + ']; ALTER ROLE [DB_OWNER] ADD MEMBER [' + @user_name + '];' 

                    EXEC sp_executesql @command;
                
                END

            END

            SET @cont += 1

        END

        SET @command = '
            USE master;

            GRANT ALTER ANY EVENT SESSION TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT ADMINISTER BULK OPERATIONS TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT ALTER ANY SERVER AUDIT TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT ALTER ANY CONNECTION TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT ALTER LOGIN TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT ALTER ANY LINKED SERVER TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT ALTER ANY SERVER ROLE TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT ALTER SERVER STATE TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT ALTER TRACE TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT CREATE ANY DATABASE TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT VIEW ANY DEFINITION TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT VIEW ANY DATABASE TO [' +  @user_name + '] WITH GRANT OPTION;
            GRANT VIEW SERVER STATE TO [' +  @user_name + '] WITH GRANT OPTION;
        '

        EXEC sp_executesql @command;

        SET @command = '
            USE msdb; 
            EXEC sp_addrolemember [SQLAgentUserRole],[' + @user_name + ']; 
            GRANT ALTER ANY USER ON DATABASE::[msdb] TO [' + @user_name + '] WITH GRANT OPTION;
            GRANT ALTER ON ROLE::[SQLAgentOperatorRole] TO [' + @user_name + '];   
        '

        EXEC sp_executesql @command; 

    END
    ELSE IF @user_type = 'read'
    BEGIN

        WHILE @cont <= (SELECT MAX(database_id) FROM sys.databases)
        BEGIN

            SET @database_name = DB_NAME(@cont)

            IF @database_name IS NOT NULL AND @database_name NOT IN ('master','model','msdb','tempdb','Distribuition','rdsadmin','ReportServer','ReportServerTempDB')
            BEGIN

                SET @command = '
                    USE [' + @database_name + ']; 
                    CREATE USER [' + @user_name + '] FOR LOGIN [' + @user_name + '] WITH DEFAULT_SCHEMA=[dbo]; 
                    ALTER ROLE [db_datareader] ADD MEMBER [' + @user_name + '];
                '

                EXEC sp_executesql @command;

            END

            SET @cont += 1

        END

    END

END

A procedure a seguir remove o usuário após o tempo especificado no Vault:

CREATE PROCEDURE sp_drop_user (
    @user_name VARCHAR(100)
)
AS
BEGIN

    DECLARE 
        @command NVARCHAR(MAX)
        ,@database_name VARCHAR(50)
        ,@cont INT  = 1

    -- Encerra todas as conexoes do usuario
    IF EXISTS(SELECT 1 FROM sys.dm_exec_sessions WHERE login_name = @user_name)
    BEGIN

        SET @command = (SELECT TOP 1 N'KILL ' + CONVERT(NVARCHAR(11), session_id) + N';' FROM sys.dm_exec_sessions WHERE login_name = @user_name);

        EXEC sp_executesql @command;

    END

    -- Remove o usuario de todos os bancos de dados
    WHILE @cont <= (SELECT MAX(database_id) FROM sys.databases)
    BEGIN

        SET @database_name = DB_NAME(@cont)

        IF @database_name IS NOT NULL AND @database_name NOT IN ('master','model','msdb','tempdb','Distribuition','rdsadmin','ReportServer','ReportServerTempDB')
        BEGIN

            SET @command = '
                USE [' + @database_name + ']; 
                CREATE USER [' + @user_name + '] FOR LOGIN [' + @user_name + '] WITH DEFAULT_SCHEMA=[dbo]; 
                ALTER ROLE [db_datareader] ADD MEMBER [' + @user_name + '];
            '

            EXEC sp_executesql @command;

        END

        SET @cont += 1

    END


    SET @command = 'USE [master]; DROP LOGIN [' + @user_name + '];'

    EXEC sp_executesql @command;

END

É isso gente, em breve faço um post fazendo essa implementação no Hashicorp Vault.

Até a próxima!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: