Permissões DDL_admin vs db_owner

db_ddladmin vs db_owner

Pelo que posso dizer do que testei e li, na maioria das vezes sua lista parece precisa, exceto db_ddladminque permite que você CREATE SCHEMA. Confirmei que as outras permissões de segurança que você listou foram realmente negadas.

Negado apenas com DDLADMIN:

[ALTER ANY USER]

[BACKUP DATABASE], [BACKUP LOG],[CHECKPOINT]

[ALTER ANY APPLICATION ROLE],[ALTER ANY ROLE]

[DROP DATABASE]

Observando que o. . .

1. db_datareaderpermitirá o SELECTacesso a todas as tabelas
2. db_datarwriterpermitirá INSERT, UPDATEe DELETEacesso a todas as tabelas
3. db_executorpermitirá o EXECUTEacesso a todos os objetos executáveis

Além disso, ter permissões de função db_ddladmin pode significar. . .

Nota: Como você tem tantas versões diferentes do SQL Server de 2005 a 2014, pode ser melhor ter um pequeno conjunto de usuários testando isso inicialmente para ver quem grita para resolver quaisquer problemas, etc.

• Os objetos que eles possuem com essa função não serão de propriedade do DBO, portanto, você pode ter que lidar com problemas de mudança de propriedade se houver algum problema com algo nesse nível. Não estou 100% certo de que isso seria um problema, mas vale a pena mencionar apenas no caso.

  Fonte: Cadeias de propriedade

• Com esta função (pode variar dependendo da versão do SQL Server) eles podem adicionar princípios de segurança SQL definidos no banco de dados atual para objetos que eles possuem ainda, mas nem todos os objetos (aqueles que eles não possuem) nem adicionar um novo servidor -level definido principal para o nível de banco de dados.

Além disso, não ter permissões de função DBO pode significar. . .

Nota: Como você tem tantas versões diferentes do SQL Server de 2005 a 2014, pode ser melhor ter um pequeno conjunto de usuários testando isso inicialmente para ver quem grita para resolver quaisquer problemas, etc.

• Não ter a função DBO pode impedir que certas interfaces GUI do designer do SSMS (variação da versão do SQL Server) sejam preenchidas ou abertas sem erros (por exemplo, ao modificar tabelas ou colunas por meio da GUI) , embora fazê-lo via T-SQL funcione e as permissões estejam em vigor . Em algumas versões do SQL Server, isso pode ser resolvido permitindo GRANT VIEW DEFINITIONque isso seja um problema e também pode ser apenas um aviso apenas em determinadas versões do SQL Server.

  Recursos

  • Você não está conectado como o proprietário do banco de dados ou como um usuário que é membro da função db_owner. Você não poderá salvar alterações em tabelas que não são de sua propriedade.

  • A função db_ddladmin não permite o uso de funções de "design" no SSMS

    _{"Nós tentamos evitar dar aos usuários/desenvolvedores dbo em seus bancos de dados QA tanto quanto podemos. Um dos problemas com isso é que eles ainda precisam ser capazes de criar e modificar objetos de banco de dados como tabelas de usuários. Muitos desenvolvedores são novos para MS SQL e, portanto, tendem a ficar com a GUI (SSMS) para esse tipo de trabalho. O problema surge quando concedemos a eles db_ddladmin (não dbo) e eles não são mais capazes de modificar tabelas ou colunas através da GUI do designer de tabelas. Em vez disso, eles precisam dedicar mais tempo para aprender os comandos TSQL e sua sintaxe (que talvez nunca mais precisem) ou envolver a equipe de DBA, o que tira tempo de nossas outras atividades.}

    _{Não sei se isso é um bug ou uma solicitação de recurso, mas considero um bug, pois o usuário tem permissões suficientes para alterar a tabela via TSQL, mas a GUI fornece mensagens informando:}

    _{" Você não está conectado como proprietário do banco de dados ou administrador do sistema. Talvez você não consiga salvar as alterações em tabelas que não são suas." E "A tabela [schema].[table]está definida como somente leitura, o usuário não tem direitos suficientes nesta tabela. "}

    _{Um rastreamento parece apontar para a verificação ser um is_member('db_owner') que impedirá membros de db_ddladmin mesmo que eles tenham permissões para modificar o objeto. Estúdio de Gerenciamento do Microsoft SQL Server"}

    ---

    _{Postado pelo Agente DBA em 25/01/2010 às 7h06}

    _{Eu tive um problema semelhante e consegui resolvê-lo realizando a seguinte concessão}

    GRANT view definition on schema:: <schemaname> to <username>
    

outras considerações

Como você afirma que isso está sendo analisado caso a caso

Uma das permissões atualmente limitadas são as permissões db_owner.

Essa permissão está sendo analisada caso a caso, mas uma alteração comum é substituir as permissões db_owner pelo seguinte:

• db_datareader
• db_datawriter
• db_ddladmin
• db_executor

Você já pensou em criar funções personalizadas adicionais para mais acesso em nível de banco de dados "todos os objetos" que cada pessoa precisa, em vez de conceder a db_ddladminfunção, pois isso provavelmente fornecerá mais do que realmente precisa para objetos de nível de banco de dados também.

Eu geralmente dou o que é necessário exatamente e nada mais para eles fazerem seu trabalho e se houver uma necessidade "normal" ou "padrão" de acesso a objetos de nível de banco de dados a todos os objetos em um banco de dados, eu crio uma função de banco de dados personalizada como o db_executormas veja meu exemplo abaixo. Dessa forma, você pode conceder às pessoas o que elas realmente precisam para TODOS os objetos de banco de dados em um banco de dados específico se você não estiver obtendo o nível de objeto explícito em seus bancos de dados para sua segurança.

----Custom Database Roles

/* CREATE A NEW ROLE  -- Execute to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Execute
GRANT EXECUTE TO db_All_StoredProc_Execute

/* CREATE A NEW ROLE  -- Alter to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Alter
GRANT ALTER ANY SCHEMA TO db_All_StoredProc_Alter

/* CREATE A NEW ROLE  -- View Definition to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_View
GRANT VIEW DEFINITION TO db_All_StoredProc_View

/* CREATE A NEW ROLE - Any schema alter and create procedure permissions */
-- Database specific
CREATE ROLE db_All_CreateProc_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateProc_AlterSchema
GRANT CREATE PROCEDURE TO db_All_CreateProc_AlterSchema
GO

/* CREATE A NEW ROLE - Any schema alter and create table permissions */
-- Database specific
CREATE ROLE db_All_CreateTable_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateTable_AlterSchema
GRANT CREATE TABLE TO db_All_CreateTable_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create function permissions */
-- Database specific
CREATE ROLE db_All_CreateFunction_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateFunction_AlterSchema
GRANT CREATE FUNCTION TO db_All_CreateFunction_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create aggregate permissions */
-- Database specific
CREATE ROLE db_All_CreateAggregate_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateAggregate_AlterSchema
GRANT CREATE AGGREGATE TO db_All_CreateAggregate_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create view permissions */
-- Database specific
CREATE ROLE db_All_CreateView_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateView_AlterSchema
GRANT CREATE VIEW TO db_All_CreateView_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create schema permissions */
-- Database specific
CREATE ROLE db_All_CreateSchema_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateSchema_AlterSchema
GRANT CREATE SCHEMA TO db_All_CreateSchema_AlterSchema

Eu também queria compartilhar uma função db_DDLAdmin_Restriction que você pode considerar criar de outra forma com explícito DENYpara restringir o que db_ddladmindar acesso para que você possa pelo menos criar isso nos bancos de dados em que você concede essa função e definir o explícito DENYpara os tipos de objeto reais , etc. você não quer que eles tenham acesso.

Por exemplo, se você sabe que eles definitivamente criarão procedimentos e funções armazenados, você pode excluir DENY CREATE FUNCTION, DENY CREATE PROCEDURE, DENY ALTER ANY SCHEMA.

---Create ddladmin restriction custom DB role
DENY ALTER ANY ASSEMBLY                    TO db_DDLAdmin_Restriction
DENY ALTER ANY ASYMMETRIC KEY              TO db_DDLAdmin_Restriction
DENY ALTER ANY CERTIFICATE                 TO db_DDLAdmin_Restriction
DENY ALTER ANY CONTRACT                    TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE DDL TRIGGER        TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO db_DDLAdmin_Restriction
DENY ALTER ANY DATASPACE                   TO db_DDLAdmin_Restriction
DENY ALTER ANY FULLTEXT CATALOG            TO db_DDLAdmin_Restriction
DENY ALTER ANY MESSAGE TYPE                TO db_DDLAdmin_Restriction
DENY ALTER ANY REMOTE SERVICE BINDING      TO db_DDLAdmin_Restriction
DENY ALTER ANY ROUTE                       TO db_DDLAdmin_Restriction
DENY ALTER ANY SCHEMA                      TO db_DDLAdmin_Restriction
DENY ALTER ANY SERVICE                     TO db_DDLAdmin_Restriction
DENY ALTER ANY SYMMETRIC KEY               TO db_DDLAdmin_Restriction
DENY CHECKPOINT                            TO db_DDLAdmin_Restriction
DENY CREATE AGGREGATE                      TO db_DDLAdmin_Restriction
DENY CREATE DEFAULT                        TO db_DDLAdmin_Restriction
DENY CREATE FUNCTION                       TO db_DDLAdmin_Restriction
DENY CREATE PROCEDURE                      TO db_DDLAdmin_Restriction
DENY CREATE QUEUE                          TO db_DDLAdmin_Restriction
DENY CREATE RULE                           TO db_DDLAdmin_Restriction
DENY CREATE SYNONYM                        TO db_DDLAdmin_Restriction
DENY CREATE TABLE                          TO db_DDLAdmin_Restriction
DENY CREATE TYPE                           TO db_DDLAdmin_Restriction
DENY CREATE VIEW                           TO db_DDLAdmin_Restriction
DENY CREATE XML SCHEMA COLLECTION          TO db_DDLAdmin_Restriction
DENY REFERENCES                            TO db_DDLAdmin_Restriction
GO

Usando um SQL Script para listar todas as permissões, fui e criei usuários para cada caso.

EXECUTE AS USER = 'test_user'
SELECT 
    permission_name 
FROM fn_my_permissions(null, 'DATABASE')
ORDER BY subentity_name, permission_name
REVERT;

Em seguida, comparei os resultados e cheguei à lista a seguir, com documentação principalmente do msdn (qualquer citação não especificamente referenciada é do link do msdn).
Abaixo está uma parte da documentação que usei para informar às pessoas que perderiam as permissões do dbo o que exatamente elas estavam perdendo.

ALTERAR

Confere a capacidade de alterar as propriedades, exceto a propriedade, de um determinado protegível. Quando concedido em um escopo, ALTER também concede a capacidade de alterar, criar ou descartar qualquer protegível contido nesse escopo. Por exemplo, a permissão ALTER em um esquema inclui a capacidade de criar, alterar e descartar objetos do esquema.

ALTERAR QUALQUER FUNÇÃO DO APLICATIVO
ALTER QUALQUER AUDITORIA DO BANCO DE DADOS
ALTER QUALQUER FUNÇÃO
ALTER QUALQUER USUÁRIO

Confere a capacidade de CREATE, ALTER ou DROP instâncias individuais do Database Securable. Por exemplo, ALTER ANY SCHEMA confere a capacidade de criar, alterar ou descartar qualquer esquema no banco de dados.

As funções de aplicativo são entidades de banco de dados que permitem que um aplicativo seja executado com suas próprias permissões de usuário.

A auditoria de uma instância do SQL Server ou de um banco de dados do SQL Server envolve rastrear e registrar eventos que ocorrem no sistema. O objeto Database-Level Audit Specification pertence a uma auditoria. Você pode criar uma especificação de auditoria de banco de dados por banco de dados SQL Server por auditoria.

Funções de banco de dados são usadas para gerenciar facilmente as permissões em seus bancos de dados, o SQL Server fornece várias funções que são entidades de segurança que agrupam outras entidades. Eles são como grupos no sistema operacional Microsoft Windows. As funções de nível de banco de dados abrangem todo o banco de dados em seu escopo de permissões.

AUTENTICAR
Encontrado no msdn.

As permissões AUTHENTICATE & AUTHENTICATE SERVER são usadas apenas ao usar EXECUTE AS em cenários de banco de dados cruzado e acesso ao servidor (respectivamente).

BACKUP LOG DE BACKUP DO BANCO DE DADOS

CONECTAR REPLICA

Usado para permissões de replicação de banco de dados.

AO CONTROLE

Confere capacidades de propriedade ao beneficiário. O beneficiário efetivamente tem todas as permissões definidas no protegível. Um principal que recebeu CONTROL também pode conceder permissões no protegível.

CRIAR PAPEL

Confere ao beneficiário a capacidade de criar o Banco de Dados Protegido.

SHOWPLAN

As permissões do plano de execução são usadas para várias opções de instrução SET do plano de execução quando são usadas com lotes Transact-SQL .

INSCREVA-SE NOTIFICAÇÕES DE CONSULTA

Documentação sobre notificações de consulta.

Construídas com base na infraestrutura do Service Broker, as notificações de consulta permitem que os aplicativos sejam notificados quando os dados forem alterados. Esse recurso é particularmente útil para aplicativos que fornecem um cache de informações de um banco de dados, como um aplicativo da Web, e precisam ser notificados quando os dados de origem são alterados.

TOMAR POSSE

Permite que o beneficiário assuma a propriedade do protegível no qual foi concedido.

VER ESTADO DO BANCO DE DADOS

Usado para exibir exibições e funções de gerenciamento dinâmico (Transact-SQL) .

VER DEFINIÇÃO

Documentação sobre permissões de definição de exibição.

A permissão VIEW DEFINITION permite que um usuário veja os metadados do protegível no qual a permissão foi concedida. No entanto, a permissão VIEW DEFINITION não confere acesso ao protegível em si. Por exemplo, um usuário que recebe apenas a permissão VIEW DEFINITION em uma tabela pode ver os metadados relacionados à tabela na exibição do catálogo sys.objects. No entanto, sem permissões adicionais, como SELECT ou CONTROL, o usuário não pode ler dados da tabela.