Introdução às permissões do mecanismo de banco de dados

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Banco de dados SQL no Microsoft Fabric

Este artigo analisa alguns conceitos básicos de segurança e descreve uma implementação típica de permissões. As permissões no Mecanismo de Banco de Dados são gerenciadas no nível do servidor por meio de logons e funções de servidor e no nível do banco de dados por meio de usuários de banco de dados e funções de banco de dados.

O Banco de Dados SQL e o Banco de Dados SQL no Microsoft Fabric fornecem as mesmas opções em cada banco de dados, mas as permissões de nível de servidor não estão disponíveis.

• No Banco de Dados SQL, consulte Tutorial: Proteger um banco de dados no Banco de Dados SQL do Azure. Recomenda-se a autenticação do Microsoft Entra ID. Para obter mais informações, consulte Tutorial: criar e gerenciar usuários do Microsoft Entra usando aplicativos do Microsoft Entra.

• No banco de dados SQL no Microsoft Fabric, o único método de autenticação com suporte para usuários de banco de dados é a ID do Microsoft Entra. Funções e permissões no nível do servidor não estão disponíveis. Para obter mais informações, consulte Autorização no banco de dados SQL no Microsoft Fabric.

Princípios de segurança

Uma entidade de segurança é a identidade que o SQL Server usa, que pode receber permissão para executar ações. Princípios de segurança geralmente são pessoas, ou grupos de pessoas, mas podem ser outras entidades que se passam por pessoas. Os princípios de segurança podem ser criados e gerenciados usando os exemplos de Transact-SQL mostrados neste artigo, ou usando o SQL Server Management Studio.

Logins

Logons são contas de usuário individuais para entrar no Mecanismo de Banco de Dados do SQL Server. O SQL Server e o Banco de Dados SQL dão suporte a logons baseados na autenticação do Windows e logons com base na autenticação do SQL Server. Para obter informações sobre os dois tipos de logons, consulte Escolher um modo de autenticação.

Funções de servidor fixas

No SQL Server, as funções de servidor fixas são um conjunto de funções pré-configuradas que fornecem um grupo conveniente de permissões no nível do servidor. Os logons podem ser adicionados às funções usando a instrução ALTER SERVER ROLE ... ADD MEMBER . Para obter mais informações, consulte ALTER SERVER ROLE. O Banco de Dados SQL não dá suporte às funções de servidor fixas, mas tem duas funções no master banco de dados (dbmanager e loginmanager) que atuam como funções de servidor.

Funções de servidor definidas pelo usuário

No SQL Server, você pode criar suas próprias funções de servidor e atribuir permissões no nível do servidor a elas. Os logons podem ser adicionados às funções de servidor usando a instrução ALTER SERVER ROLE ... ADD MEMBER . Para obter mais informações, consulte ALTER SERVER ROLE. O banco de dados SQL não dá suporte às funções de servidor definidas pelo usuário.

Usuários de banco de dados

Para conceder acesso a um logon em um banco de dados, crie um usuário de banco de dados nesse banco de dados e mapeie o usuário do banco de dados para um logon. O nome de usuário do banco de dados geralmente é o mesmo que o nome de logon por convenção, embora não precise ser o mesmo. Cada usuário de banco de dados é mapeado para um logon único. Um logon pode ser mapeado para apenas um usuário em um banco de dados, mas pode ser mapeado como um usuário de banco de dados em vários bancos de dados diferentes.

Também é possível criar usuários de banco de dados em um logon correspondente. Esses usuários são chamados de usuários de banco de dados independente. A Microsoft incentiva o uso de usuários de banco de dados independentes, pois facilita a movimentação do banco de dados para um servidor diferente. Como um logon, o usuário de banco de dados independente pode usar a autenticação do Windows ou a autenticação do SQL Server. Para obter mais informações, consulte Torne seu banco de dados portátil usando bancos de dados independentes.

Há 12 tipos de usuários com pequenas diferenças no modo como são autenticados e quem eles representam. Para ver uma lista de usuários, consulte CREATE USER.

Funções de banco de dados fixas

As funções de banco de dados fixas são um conjunto de funções pré-configuradas que fornecem um grupo conveniente de permissões no nível do banco de dados. É possível adicionar usuários de banco de dados e funções de banco de dados definidas pelo usuário às funções de banco de dados fixas usando a instrução ALTER ROLE ... ADD MEMBER. Para obter mais informações, consulte ALTER ROLE.

Funções de banco de dados definidas pelo usuário

Os usuários com a CREATE ROLE permissão podem criar novas funções de banco de dados definidas pelo usuário para representar grupos de usuários com permissões comuns. Normalmente, as permissões são concedidas ou negadas para toda a função, simplificando o gerenciamento e o monitoramento de permissões. É possível adicionar usuários de banco de dados às funções de banco de dados usando a instrução ALTER ROLE ... ADD MEMBER . Para obter mais informações, consulte ALTER ROLE.

Outros participantes

Outras entidades de segurança não discutidas aqui incluem funções de aplicativo, logons e usuários com base em certificados ou chaves assimétricas.

Para obter um gráfico mostrando as relações entre usuários do Windows, grupos do Windows, logons e usuários de banco de dados, consulte Criar um usuário de banco de dados.

Cenário típico

Veja a seguir um exemplo que representa um método comum e recomendado de configuração de permissões.

No Windows Active Directory ou no Microsoft Entra ID

1. Crie um usuário do para cada pessoa.
2. Crie grupos do Windows que representam as unidades de trabalho e as funções de trabalho.
3. Adicione usuários do Windows aos grupos do Windows.

Se o usuário estiver se conectando a muitos bancos de dados

1. Crie um logon para os grupos do Windows. (Se você usar a autenticação do SQL Server, ignore as etapas do Active Directory e crie logons de autenticação do SQL Server aqui.)

2. No banco de dados de usuário, crie um usuário de banco de dados para o logon que representa os grupos do Windows.

3. No banco de dados de usuário, crie uma ou mais funções de banco de dados definidas pelo usuário, cada uma representando uma função semelhante. Por exemplo, você pode ter uma função de analista financeiro e uma função de analista de vendas.

4. Adicione os usuários do banco de dados a uma ou mais funções de banco de dados definidas pelo usuário.

5. Conceda permissões às funções de banco de dados definidas pelo usuário.

Se o usuário estiver se conectando a apenas um banco de dados

1. No banco de dados de usuário, crie um usuário de banco de dados independente para o grupo do Windows. (Se você usar a autenticação do SQL Server, ignore as etapas do Active Directory e crie a autenticação do usuário do banco de dados independente SQL Server aqui.)

2. No banco de dados de usuário, crie uma ou mais funções de banco de dados definidas pelo usuário, cada uma representando uma função semelhante. Por exemplo, você pode ter uma função de analista financeiro e uma função de analista de vendas.

3. Adicione os usuários do banco de dados a uma ou mais funções de banco de dados definidas pelo usuário.

4. Conceda permissões às funções de banco de dados definidas pelo usuário.

O resultado mais comum neste ponto é que um usuário do Windows é membro de um grupo do Windows. O grupo do Windows tem um logon no SQL Server ou no banco de dados SQL. O logon é mapeado para uma identidade de usuário no banco de dados do usuário. O usuário é membro de uma função de banco de dados. Agora, você precisa adicionar permissões à função.

Atribuir permissões

A maioria das instruções de permissão tem o seguinte formato:

<authorization> <permission> ON <securable>::<name> TO <principal>;

• <authorization> deve ser GRANT, REVOKEou DENY.

• O <permission> estabelece a ação que você permite ou proíbe. O número exato de permissões difere entre o SQL Server e o Banco de Dados SQL do Azure. Para obter informações sobre permissões, consulte Permissões (Mecanismo de Banco de Dados) e consulte o gráfico mais adiante neste artigo.

• ON <securable>::<name> é o tipo de item protegível (servidor, objeto de servidor, banco de dados ou objeto de banco de dados) e seu nome. Algumas permissões não exigem <securable>::<name> porque são inequívocas ou inadequadas no contexto. Por exemplo, a permissão CREATE TABLE não requer a cláusula <securable>::<name> (GRANT CREATE TABLE TO Mary; permite que Mary crie tabelas).

• <principal> é a entidade de segurança (logon, usuário ou função) que recebe ou perde a permissão. Conceda permissões às funções sempre que possível.

O exemplo de instrução de concessão a seguir concede a permissão UPDATE na tabela Parts ou na exibição contida no esquema Production à função chamada PartsTeam:

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

A declaração de exemplo a seguir concede a permissão UPDATE no esquema Production e por extensão em qualquer tabela ou visualização contida neste esquema, para a função denominada ProductionTeam, que é uma abordagem mais eficaz e comerciável para atribuir permissões do que no nível de objeto individual:

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

As permissões são concedidas a entidades de segurança (logons, usuários e funções) usando a instrução GRANT . As permissões são explicitamente negadas usando o comando DENY. Um permissão concedida ou negada anteriormente é removida usando a instrução REVOKE . As permissões são cumulativas, com o usuário recebendo todas as permissões concedidas ao usuário, logon e quaisquer associações de grupo; no entanto, qualquer negação de permissão substitui todas as concessões.

O grupo Vendas recebe permissões SELECT na tabela OrderStatus por meio da instrução GRANT SELECT ON OBJECT::OrderStatus TO Sales;. O usuário Jae é um membro da Sales função. Jae também recebeu a permissão SELECT para a tabela OrderStatus em seu próprio nome de usuário por meio da instrução GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Presuma que o administrador deseja remover o GRANT para função Sales.

• Se o administrador executar corretamente REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, Jae manterá o acesso SELECT à tabela OrderStatus por meio da instrução GRANT individual.

• Se o administrador executar incorretamente DENY SELECT ON OBJECT::OrderStatus TO Sales;, Jae, como membro da função Sales, terá a permissão SELECT negada, pois DENY para Sales substitui essa GRANT individual.

Hierarquia de permissões

Permissões têm uma hierarquia de pai/filho. Ou seja, se você conceder a permissão SELECT em um banco de dados, essa permissão incluirá a permissão SELECT em todos os esquemas (filho) no banco de dados. Se você conceder a permissão SELECT em um esquema, ela incluirá a permissão SELECT em todas as tabelas (filho) e modos de exibição no esquema. As permissões são transitivas: se você conceder a permissão SELECT em um banco de dados, ele incluirá a permissão SELECT em todos os esquemas (filho) e em todos os modos de exibição e tabelas (netos).

As permissões também têm permissões de cobertura. A CONTROL permissão em um objeto normalmente permite que você tenha todas as outras permissões no objeto.

Como a hierarquia de pai/filho e a hierarquia de cobertura podem agir na mesma permissão, o sistema de permissões pode ficar complicado. Por exemplo, vamos usar uma tabela (Region), em um esquema (Customers), em um banco de dados (SalesDB).

• CONTROLA permissão na tabela Region inclui todas as outras permissões na tabelaRegion, incluindoALTER, SELECT, , INSERT, UPDATEDELETEe algumas outras permissões.

• SELECT no esquema Customers que possui a tabela Region inclui a permissão SELECT na tabela Region.

Então, a permissão SELECT na tabela Region pode ser obtida por meio de qualquer uma das seis instruções a seguir:

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

Concessão de permissão mínima

A primeira permissão listada anteriormente (GRANT SELECT ON OBJECT::Region TO Jae;) é a mais granular. Essa declaração é a permissão mínima possível que concede a SELECT. Nenhuma permissão para subordinar objetos vem com ela. É um bom princípio sempre conceder a menor permissão possível, mas você deve considerar a concessão em níveis mais altos, a fim de simplificar o sistema de concessão.

Então, se Jae precisar de permissão para o esquema inteiro, conceda SELECT uma vez no nível do esquema, em vez de conceder SELECT várias vezes no nível da tabela ou da exibição. O design do banco de dados pode afetar drasticamente o sucesso da estratégia. Essa estratégia funciona melhor quando o banco de dados é projetado para que os objetos que precisam de permissões idênticas sejam incluídos em um único esquema.

Diagrama de permissões

A imagem a seguir mostra as permissões e as relações entre elas. Algumas das permissões de nível superior (como CONTROL SERVER) são listadas várias vezes. Neste artigo, o cartaz é pequeno demais para ser lido. Você pode baixar o Pôster de Permissões do Mecanismo de Banco de Dados em tamanho completo no formato PDF.

Para conferir um gráfico que mostra os relacionamentos entre as entidades de segurança do mecanismo de banco de dados e o servidor e objetos de banco de dados, consulte Hierarquia de permissões (Mecanismo de Banco de Dados).

Permissões versus funções fixas de banco de dados e de servidor

As permissões das funções de servidor fixas e das funções de banco de dados fixas são semelhantes, mas não exatamente iguais às permissões granulares. Por exemplo, membros da função de servidor fixa sysadmin têm todas as permissões na instância do SQL Server, assim como os logons com a permissão CONTROL SERVER.

Mas, conceder a(s) CONTROL SERVER permissão(ões) não torna um logon membro da função de servidor fixa sysadmin e adicionar um logon à função de servidor fixa sysadmin não concede explicitamente ao logon a permissão CONTROL SERVER. Às vezes, um procedimento armazenado verifica as permissões verificando a função fixa e não verificando a permissão granular.

Por exemplo, para desanexar um banco de dados, é necessário ser membro da função fixa de banco de dados db_owner. A permissão CONTROL DATABASE equivalente não é suficiente. Esses dois sistemas operam em paralelo, mas raramente interagem entre si. A Microsoft recomenda usar o sistema de permissões granular mais recente em vez de funções fixas sempre que possível.

Monitorar permissões

Os modos de exibição a seguir retornam informações de segurança. Para todas as exibições relacionadas à segurança, consulte Exibições do Catálogo de Segurança (Transact-SQL).

¹ Essa exibição não está disponível no Banco de Dados SQL.

Examples

As instruções a seguir retornam informações úteis sobre as permissões.

A. Lista de permissões do banco de dados para cada usuário

Para retornar as permissões explícitas concedidas ou negadas em um banco de dados (SQL Server e Banco de Dados SQL), execute a seguinte instrução Transact-SQL no banco de dados.

SELECT perms.state_desc AS State,
       permission_name AS [Permission],
       obj.name AS [on Object],
       dp.name AS [to User Name]
FROM sys.database_permissions AS perms
     INNER JOIN sys.database_principals AS dp
         ON perms.grantee_principal_id = dp.principal_id
     INNER JOIN sys.objects AS obj
         ON perms.major_id = obj.object_id;

B. Lista dos membros da função do servidor

Para retornar os membros das funções de servidor (somente SQL Server), execute a instrução a seguir.

SELECT roles.principal_id AS RolePrincipalID,
       roles.name AS RolePrincipalName,
       server_role_members.member_principal_id AS MemberPrincipalID,
       members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
     INNER JOIN sys.server_principals AS roles
         ON server_role_members.role_principal_id = roles.principal_id
     LEFT OUTER JOIN sys.server_principals AS members
         ON server_role_members.member_principal_id = members.principal_id;

C. Listar todas as entidades de segurança de banco de dados que são membros da função de nível de banco de dados

Para retornar os membros das funções de banco de dados (SQL Server e Banco de Dados SQL), execute a instrução a seguir no banco de dados.

SELECT dRole.name AS [Database Role Name],
       dp.name AS [Members]
FROM sys.database_role_members AS dRo
     INNER JOIN sys.database_principals AS dp
         ON dRo.member_principal_id = dp.principal_id
     INNER JOIN sys.database_principals AS dRole
         ON dRo.role_principal_id = dRole.principal_id;

Conteúdo relacionado

• Segurança do Mecanismo de Banco de Dados do SQL Server e Banco de Dados SQL do Azure
• Funções de segurança (Transact-SQL)
• Funções e exibições de gerenciamento dinâmico relacionadas à segurança (Transact-SQL)
• Exibições do catálogo de segurança (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• Determinar permissões efetivas do Mecanismo de Banco de Dados
• Tutorial: Introdução ao Mecanismo de Banco de Dados
• Lição 1: criar e consultar objetos de banco de dados
• Tutorial: SQL Server Management Studio
• Tutorial: Gravar instruções do Transact-SQL