Início / dba / Perguntas / 5759
Accepted
David George
Asked: 2011-09-15 07:31:03 +0800 CST

Definir permissões em todos os objetos para usuários SQL

  • 772

Eu tenho um procedimento que percorre todos os objetos no banco de dados e atribui a eles as permissões adequadas a esse objeto. Eu quero saber se há uma maneira melhor de fazer isso? Eu uso um banco de dados modelo para criar novos bancos de dados, então tenho que executar isso toda vez que crio um novo banco de dados. Aqui está uma ideia de como fica (nota: falta um pedaço desde o início que elimina todos os usuários e recria os usuários necessários; isso é necessário desde a alteração do SID):

CREATE PROCEDURE usp_SetPermissions 
AS 
 BEGIN

DECLARE @CurrentId INT
DECLARE @ObjectName NVARCHAR(128)
DECLARE @Message NVARCHAR(160)
DECLARE @Error INT
DECLARE @Sql NVARCHAR(256)

CREATE TABLE #tmpDbObjects 
(
   ID INT IDENTITY(1,1),
   ObjectName NVARCHAR(128),
   Completed BIT
)

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'U' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
 BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
    EXEC sp_sqlexec @Sql

    -- Update object completion 
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'P' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
     BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
EXEC sp_sqlexec @Sql

    -- Update object completion 
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

Este script continua a fazer coisas semelhantes para todas as visualizações, funções, etc. no banco de dados. Alguma ideia para acelerar isso ou existe uma maneira melhor de fazer isso?

sql-server database-design

1 respostas

  1. Best Answer

    Linha única para dar permissões no nível do esquema para uma função

    GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

    E uma segunda linha para adicionar usuários à função

    EXEC sp_addrolemember 'SomeRole', 'whatever user'

    E faça isso no modelo para que todos os novos bancos de dados sejam herdados.

    Razões, você deve definir permissões apenas uma vez :

    • Um esquema é um recipiente para objetos.
    • Novos objetos herdam permissões do esquema
    • Uma função é um contêiner para usuários
    • Novos usuários são adicionados a uma função e herdam

    Como você descobriu, migrar ou restaurar um banco de dados pode perder permissões de objeto quando atribuídas diretamente aos usuários. Então, por que se colocar nessa posição?

    Você também pode CRIAR LOGIN com um SID para que seja o mesmo em todos os seus servidores e também não obtenha usuários órfãos.

    Se você tivesse feito a pergunta correta, poderíamos ter economizado um pouco de codificação...

    Pessoalmente e finalmente, eu diria que esta é uma prática ruim sobre "necessidade de ter", não "cobertor para fazer qualquer coisa"

    • 6

relate perguntas