Início / dba / Perguntas / 171523
Accepted
Jeff
Asked: 2017-04-21 06:16:50 +0800 CST

Impedir a modificação de objetos dbo enquanto permite que outros esquemas sejam modificados

  • 772

Eu tenho uma solução de fornecedor que usa um banco de dados sql server (2008r2). Temos permissão para fazer o que quisermos em nosso próprio esquema, mas não podemos modificar objetos dbo sem permissão do fornecedor. Qualquer customização que fazemos é em nosso próprio esquema (cust). Temos controle total deste servidor e sempre permitimos direitos de administrador de sistema para qualquer desenvolvedor. Ultimamente, notei que essas regras não foram seguidas, então quero ver se posso configurar permissões para interromper isso em primeiro lugar.

Aqui está o que eu gostaria de realizar:

Tabelas: ler/gravar para todos, alterar apenas esquema personalizado, visualizar design

Visualizações/Sprocs/Funções: visualize qualquer definição, crie ou altere apenas o esquema personalizado

Criei um novo usuário (dev) e apliquei o seguinte:

deny alter on schema::dbo to dev
grant alter on schema::custom to dev
grant view definition to dev

Isso vai realizar o que eu quero, ou estou perdendo algo mais?

Solução (graças a AMtwo)

use master
go
create login dev with password = 'test', check_expiration = off, check_policy = off
grant control server to dev


use CustomerDB 
go
if not exists(select * from sys.database_principals where name = 'dev') begin
    create user dev for login dev 
    grant select, insert, update, delete, execute, alter on schema::custom to dev
    grant view definition to dev
    deny alter on schema::dbo to dev
    -- deny other schemas here
end`
sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Temos controle total deste servidor e sempre permitimos direitos de administrador de sistema para qualquer desenvolvedor.

    O sysadminprivilégio efetivamente causa um curto-circuito em quaisquer outras permissões. Se um usuário for membro da sysadminfunção de servidor fixa, todas as DENYpermissões serão ignoradas.

    Você pode contornar isso revogando sysadmine usando a CONTROL SERVERpermissão. CONTROL SERVERé semelhante à sysadminfunção de servidor fixa, exceto que CONTROL SERVERobedecerá às DENYpermissões.

    GRANT CONTROL SERVER TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'
DENY ALTER ON SCHEMA::dbo TO dev

    Se você deseja desfazer completamente a necessidade dos desenvolvedores de ter acesso de alto nível no servidor (o que é uma boa ideia), você pode adotar duas abordagens:

    • Conceda amplo acesso ao banco de dados e, em seguida, negue explicitamente as permissões no dbo.

      USE MyDB
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON DATABASE::MyDB TO dev
GRANT VIEW DEFINITION TO dev
DENY ALTER ON SCHEMA::dbo TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'

    • Conceda explicitamente apenas as permissões exigidas pelos usuários; não conceda ALTERpermissão em dbo.

      USE MyDB
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON SCHEMA::custom TO dev
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON SCHEMA::dbo TO dev
GRANT VIEW DEFINITION TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'

    Em última análise, você deseja seguir (ou estar próximo) o princípio do menor privilégio . Mas você também precisa equilibrar complexidade e capacidade de gerenciamento.

    Sugiro que você crie alguns cenários de teste para testar suas permissões ao executar como dev, para garantir que o usuário tenha as permissões corretas. Você pode fazer isso usando EXECUTE AS LOGIN:

    EXECUTE AS LOGIN='SomeDeveloper';
CREATE TABLE custom.AM2 (foo BIT);
GO
CREATE PROCEDURE custom.GetAM2 AS SELECT * FROM custom.AM2;
GO
DROP TABLE custom.AM2;
DROP PROCEDURE custom.GetAM2;
GO
REVERT;
    • 9

  2. Vou responder isso em duas partes.

    O que você deveria fazer

    Sinceramente, acho que ninguém além de um DBA responsável pela instância deveria ter sysadminou mesmo control server. Eu raramente vi situações em que você precisa passar pelo seguinte:

    • Ler
    • Escreva
    • Executar
    • ddl_Admin
    • ver definição

    Nesse caso, para conceder as permissões desejadas, você pode fazer o seguinte:

    EXEC sp_addrolemember 'db_datareader','dev'
EXEC sp_addrolemember 'db_datawriter','dev'
EXEC sp_addrolemember 'db_ddladmin','dev'
GRANT EXECUTE, VIEW DEFINITION TO dev
DENY ALTER ON SCHEMA::dbo TO dev

    O que você pode ter que fazer

    Eu entendo que às vezes você não tem uma opção. A política, seja o que for, exige sysadminpara todos. Embora eu diga novamente que esta é uma ideia horrível. Dito isso, você pode usar um gatilho DDL (ou gerenciamento baseado em políticas) para evitar alterações no dbo. Ele basicamente ignorará a segurança.

    Eu tenho um exemplo de um gatilho DDL fazendo algo semelhante aqui e uma política aqui .

    Em ambos os casos, eles eram códigos de demonstração em que impedi o uso de arquivos nolock. Dito isso, você provavelmente ainda pode usá-los como modelo. Você precisará ter o cuidado de incluir em seu código a capacidade de permitir a permissão de seu fornecedor para fazer as alterações. Aqui está um exemplo (não testado) de como pode ficar:

    CREATE TRIGGER No_DBO_Changes
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE,
    CREATE_FUNCTION, ALTER_FUNCTION,
    CREATE_VIEW, ALTER_VIEW,
    CREATE_TRIGGER, ALTER_TRIGGER
AS
    IF ORIGINAL_LOGIN() != 'vendoruser'
    BEGIN
        IF EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') = 'dbo'
        BEGIN
            ROLLBACK
        END
    END
GO
    • 0

relate perguntas