Início / dba / Perguntas / 83028
Accepted
Kahn
Asked: 2014-11-20 00:22:34 +0800 CST

Problemas de permissão de procedimento com uma consulta de estado SQL

  • 772

Temos um login/usuário com DBO para um único banco de dados e agora estamos adicionando uma consulta que fornece algumas informações básicas de monitoramento que exigem permissões que se estendem um pouco para o lado do administrador do sistema.

Basicamente, a consulta precisa ser capaz de executar (somente leitura) os seguintes comandos:

DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
dbo.sysfiles
sys.master_files
tempdb.sys.database_files
tempdb.sys.dm_db_file_space_usage
sys.dm_exec_requests
sys.dm_tran_locks
sys.partitions
sys.indexes
sys.dm_os_waiting_tasks
sys.dm_os_tasks
sys.dm_exec_sessions
sys.dm_exec_sql_text

A maioria dessas coisas envolve permissões como VIEW SERVER STATE, VIEW ANY DEFINITION, etc, todas as quais podem ser usadas para conceder as permissões necessárias sem ir para a função de servidor sysadmin. Mas queremos apenas limitar essas permissões a essa única consulta.

Então, o que estamos procurando são opções. Minha última opção preferida é adicionar as várias permissões ao login/usuário, pois assim ele teria acesso a mais do que precisa (várias sub-permissões da função de servidor sysadmin), então tentei criar um procedimento com o EXECUTE AS cláusula, apenas aparentemente por causa do SQL dinâmico interno (precisamos enviar os resultados de DBCC SQLPERF para uma variável de tabela), o encadeamento de propriedade fica difícil. Mesmo independentemente disso, parece bastante complicado permitir que um procedimento seja executado com credenciais limítrofes de administrador de sistema.

Pesquisei mais sobre esse assunto do que posso entender e ainda não consigo fazer isso funcionar. Basicamente, vocês podem me dizer como fazer isso sem criar falhas de segurança.

Apenas um lote, que executa esta consulta, só pode ser alterado pelo administrador do sistema e pode ser lido/executado por qualquer pessoa com permissão para executá-lo? Obrigado!

EDITAR:

Atualmente estou analisando a possibilidade de criar um proxy de logins e/ou assinar o procedimento com um certificado. Parece uma opção bastante interessante.

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Acho que sua opção mais simples é um trabalho.

    Você pode conceder as permissões para iniciar um trabalho por meio da função SQLAgentOperatorRole no msdb. Os membros dessa função podem iniciar trabalhos pertencentes a um login de administrador de sistema, que pode executar os comandos necessários.

    Aqui está um exemplo baseado em seus requisitos:

    USE master
GO

CREATE LOGIN testUser WITH PASSWORD = 'strongPassword' , CHECK_POLICY = OFF
GO

USE msdb
GO

CREATE USER testUser FOR LOGIN testUser
GO

EXEC sp_addrolemember 'SQLAgentOperatorRole', 'testUser'
GO

DECLARE @jobId uniqueidentifier

EXEC sp_add_job @job_name=N'test', 
        @enabled=1, 
        @owner_login_name=N'sa',
        @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobserver @job_id=@jobid, @server_name = @@SERVERNAME


EXEC sp_add_jobstep @job_id=@jobId, @step_name=N'step1', 
    @subsystem=N'TSQL', 
    @command=N'DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
SELECT * FROM dbo.sysfiles
SELECT * FROM sys.master_files
SELECT * FROM tempdb.sys.database_files
SELECT * FROM tempdb.sys.dm_db_file_space_usage
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.partitions
SELECT * FROM sys.indexes
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_exec_sessions
'
GO


EXECUTE AS LOGIN = 'testUser'

EXEC msdb.dbo.sp_start_job @job_name = 'test'

REVERT
GO


-- CLEANUP:
-----------
-- EXEC msdb.sys.sp_executesql N'DROP USER testUser'
-- DROP LOGIN testUser
-- EXEC msdb.dbo.sp_delete_job @job_name = 'test'

    Embora conceder o SQLAgentOperatorRole ainda seja uma preocupação de segurança (menor), acho menos preocupante do que outras soluções.

    • 1

relate perguntas