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

Artigo
10/29/2023

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System)

As permissões no mecanismo de banco de dados são gerenciadas no nível do servidor por meio de funções de logon e de servidor, e no nível do banco de dados por meio de funções de usuários do banco de dados e funções de banco de dados. O modelo para o banco de dados SQL expõe o mesmo sistema dentro de cada banco de dados, mas as permissões no nível do servidor não estão disponíveis. Este artigo examina alguns conceitos básicos de segurança e descreve uma implementação comum das permissões.

Observação

O Microsoft Entra ID era conhecido como Azure Active Directory (Azure AD).

Objetos de segurança

Entidade de segurança é o nome oficial das identidades que usam o SQL Server e que podem receber uma permissão para executar ações. Geralmente são pessoas ou grupos de pessoas, mas podem ser outras entidades que fingem ser pessoas. As entidades de segurança podem ser criadas e gerenciadas usando o Transact-SQL listado ou usando o SQL Server Management Studio.

Logons

Logons são contas de usuário individuais para entrada no mecanismo de banco de dados do SQL Server. O SQL Server e o banco de dados SQL dão suporte a logons com base na autenticação do Windows e logons com base na autenticação do SQL Server. Para saber mais sobre os dois tipos de logons, confira Choose an Authentication Mode.

Funções fixas de servidor

No SQL Server, as funções de servidor fixas são um conjunto de funções pré-configuradas que fornecem um agrupamento 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 mais informações, confira ALTER SERVER ROLE (Transact-SQL). O banco de dados SQL não dá suporte a funções de servidor fixas, mas tem duas funções no banco de dados mestre master (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 a elas permissões no nível do servidor. Os logons podem ser adicionados às funções de servidor usando a instrução ALTER SERVER ROLE ... ADD MEMBER . Para mais informações, confira ALTER SERVER ROLE (Transact-SQL). O banco de dados SQL não dá suporte às funções de servidor definidas pelo usuário.

Usuários de banco de dados

Os logons recebem acesso a um banco de dados por meio da criação de um usuário de banco de dados em um banco de dados e por meio do mapeamento desse usuário de banco de dados para o logon. Normalmente, o nome de usuário do banco de dados é igual ao nome de logon, mas não é necessário. 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 independente, pois isso 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 Usuários do banco de dados independente – Tornando o banco de dados portátil.

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, confira CREATE USER (Transact-SQL).

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 agrupamento 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, veja ALTER ROLE (Transact-SQL).

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

Usuários com a permissão CREATE ROLE 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, veja ALTER ROLE (Transact-SQL).

Outras entidades

Entre as outras entidades de segurança que não são discutidas aqui estão as funções de aplicativo, logons e usuários baseados em certificados ou chaves assimétricas.

Para ver um gráfico mostrando as relações entre usuários do Windows, grupos do Windows, logons e usuários de banco de dados, confira Create a Database User.

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

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

Se a pessoa que está se conectando for se conectar a muitos bancos de dados

Crie um logon para os grupos do Windows. (Se você estiver usando a autenticação do SQL Server, ignore as etapas do Active Directory e crie os logons de autenticação do SQL Server aqui.)
No banco de dados de usuário, crie um usuário de banco de dados para o logon que representa os grupos do Windows.
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, analista financeiro e analista de vendas.
Adicione os usuários do banco de dados a uma ou mais funções de banco de dados definidas pelo usuário.
Conceda permissões às funções de banco de dados definidas pelo usuário.

Se a pessoa que está se conectando for se conectar a apenas um bancos de dados

No banco de dados de usuário, crie um usuário de banco de dados independente para o grupo do Windows. (Se você estiver usando a autenticação do SQL Server, ignore as etapas do Active Directory e crie a autenticação do SQL Server do usuário de banco de dados independente aqui.
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, analista financeiro e analista de vendas.
Adicione os usuários do banco de dados a uma ou mais funções de banco de dados definidas pelo usuário.
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 formato:

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

AUTHORIZATION deve ser GRANT, REVOKE ou DENY.
O PERMISSION estabelece qual ação é permitida ou proibida. O número exato de permissões é diferente entre o SQL Server e o Banco de Dados SQL. As permissões estão listadas no artigo Permissões (Mecanismo de Banco de Dados) e no gráfico referenciado abaixo.
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 ON SECURABLE::NAME, pois ele não é ambíguo ou é inadequado ao contexto. Por exemplo, a permissão CREATE TABLE não requer a cláusula ON 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 seguinte instrução de permissão de exemplo concede a permissão UPDATE no esquema Production e, consequentemente, em qualquer tabela ou exibição contida nesse esquema para a função chamada 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.

Dica

Um erro comum é tentar remover um GRANT usando DENY em vez de REVOKE. Isso pode causar problemas quando um usuário recebe permissões de várias fontes, o que é bastante comum. O exemplo a seguir demonstra a entidade.

O grupo de Vendas recebe as permissões SELECT na tabela OrderStatus por meio da instrução GRANT SELECT ON OBJECT::OrderStatus TO Sales;. O usuário Jae é membro da função Vendas. 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;. Vamos supor que o administrador deseja remover GRANT da função Vendas.

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 Vendas, terá a permissão SELECT negada, pois DENY para Vendas substitui essa GRANT individual.

Observação

As permissões podem ser configuradas usando o Management Studio. Encontre o item protegível no Pesquisador de Objetos, clique nele com o botão direito do mouse e selecione Propriedades. Selecione a página Permissões . Para obter ajuda sobre como usar a página de permissão, confira Permissions or Securables Page.

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, ou seja, 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 permissão CONTROL em um objeto, normalmente fornece a você 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 (Região) em um esquema (Clientes) em um banco de dados (SalesDB).

CONTROL na tabela Região inclui todas as outras permissões na tabela Região, incluindo ALTER, SELECT, INSERT, UPDATE, DELETEe algumas outras permissões.
SELECT no esquema Clientes que possui a tabela Região inclui a permissão SELECT na tabela Região.

Então, a permissão SELECT na tabela Região 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 acima (GRANT SELECT ON OBJECT::Region TO Jae;) é a mais granular, ou seja, essa instruçã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 o mínimo de permissão possível (você pode ler mais sobre o Princípio de privilégios mínimos), mas, ao mesmo tempo (contradizendo isso), tente conceder em níveis mais altos para simplificar o sistema de concessão. Então, se Jae precisar de permissões 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 funcionará melhor quando o banco de dados for projetado de modo que os objetos que precisam de permissões idênticas forem incluídos em um único esquema.

Dica

Ao criar um banco de dados e seus objetos desde o início, planeje quem ou quais aplicativos acessarão quais objetos e, com base nisso, posicione objetos, ou seja, tabelas, exibições, funções e procedimentos armazenados em esquemas de acordo com buckets de tipo de acesso o máximo possível.

Diagrama de permissões

A imagem a seguir mostra as permissões e os relacionamentos 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 Cartaz de permissões do mecanismo de banco de dados no tamanho normal, no formato PDF.

A screenshot from the Database Engine permissions 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 fixas de servidor e de banco de dados são semelhantes, mas não são exatamente as mesmas que as 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 permissão CONTROL SERVER 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 verificará as permissões consultando a função fixa e não verificando a permissão granular. Por exemplo, desanexar um banco de dados exige a associação à função de banco de dados fixa 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, sempre que possível, o uso do sistema de permissão granular mais recente em vez das funções fixas.

Monitorar permissões

Os modos de exibição a seguir retornam informações de segurança.

Os logons e funções de servidor definidas pelo usuário em um servidor podem ser examinadas usando o modo de exibição sys.server_principals . Essa exibição não está disponível no Banco de Dados SQL.
Os usuários e funções definidas pelo usuário em um banco de dados podem ser examinadas usando o modo de exibição sys.database_principals .
As permissões concedidas para logons e funções de servidor fixas definidas pelo usuário podem ser examinadas usando o modo de exibição sys.server_permissions . Essa exibição não está disponível no Banco de Dados SQL.
As permissões concedidas para usuários e funções de banco de dados fixas definidas pelo usuário podem ser examinadas usando o modo de exibição sys.database_permissions .
A associação à função de banco de dados pode ser examinada usando o modo de exibição sys.database_role_members .
A associação à função de servidor pode ser examinada usando o modo de exibição sys.server_role_members . Essa exibição não está disponível no Banco de Dados SQL.
Veja mais exibições relacionadas à segurança em Exibições de catálogo de segurança (Transact-SQL).

Exemplos

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

R. 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 instrução a seguir 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
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
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 (apenas do 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 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
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;