Determinar permissões efetivas do Mecanismo de Banco de Dados

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

Este artigo descreve como determinar quem tem permissões para vários objetos no Mecanismo de Banco de Dados do SQL Server. O SQL Server implementa dois sistemas de permissão para o Mecanismo de Banco de Dados. Um sistema mais antigo de funções fixas tem permissões pré-configuradas. A partir do SQL Server 2005 (9.x), um sistema mais flexível e preciso está disponível.

Note

As informações neste artigo se aplicam ao SQL Server 2005 (9.x) e versões posteriores. Alguns tipos de permissões não estão disponíveis em algumas versões do SQL Server.

Deve ter sempre em mente os seguintes pontos:

• As permissões efetivas são o agregado de ambos os sistemas de permissão.
• Uma negação de permissões substitui uma concessão de permissões.
• Se um usuário for membro da função de servidor fixa sysadmin, as permissões não serão verificadas, portanto, as recusas não serão impostas.
• O sistema antigo e o novo sistema têm semelhanças. Por exemplo, a associação à função fixa de servidor sysadmin é semelhante a possuir a permissão CONTROL SERVER. Mas os sistemas não são idênticos. Por exemplo, se um login tiver apenas a permissão CONTROL SERVER, e for efetuada uma verificação de associação à função de servidor fixa sysadmin através de procedimentos armazenados, a verificação de permissão falhará. O inverso também é verdadeiro.
• No Fabric SQL database, o Microsoft Entra ID para utilizadores de base de dados é o único método de autenticação suportado. As funções e permissões no nível do servidor não estão disponíveis, apenas no nível do banco de dados. Para obter mais informações, consulte Autorização no banco de dados SQL no Microsoft Fabric.

Summary

• A permissão no nível do servidor pode vir da associação às funções de servidor fixas ou às funções de servidor definidas pelo usuário. Todos pertencem à função de servidor fixa public e recebem todas as permissões atribuídas a ela.
• As permissões no nível do servidor podem vir de concessões de permissão para logons ou funções de servidor definidas pelo usuário.
• A permissão no nível de banco de dados pode vir da associação às funções de banco de dados fixas ou às funções de banco de dados definidas pelo usuário em cada banco de dados. Todos pertencem à função de banco de dados fixa public e recebem qualquer permissão atribuída a ela.
• As permissões no nível do banco de dados podem vir de concessões de permissão a usuários ou funções de banco de dados definidas pelo usuário em cada banco de dados.
• As permissões podem ser recebidas do login do guest ou do usuário do banco de dados guest, se estiver habilitado. O login guest e os usuários são desativados por padrão.
• Os usuários do Windows podem ser membros de grupos do Windows que podem ter logons. O SQL Server aprende sobre a associação a um grupo do Windows quando um usuário do Windows se conecta e apresenta um token do Windows com o identificador de segurança de um grupo do Windows. Como o SQL Server não gerencia nem recebe atualizações automáticas sobre associações de grupo do Windows, o SQL Server não pode relatar de forma confiável as permissões dos usuários do Windows recebidas da associação de grupo do Windows.
• As permissões podem ser adquiridas alternando para uma função de aplicativo e fornecendo a senha.
• As permissões podem ser adquiridas executando um procedimento armazenado que inclui a cláusula EXECUTE AS.
• As permissões podem ser adquiridas por logins ou utilizadores com a permissão IMPERSONATE.
• Os membros do grupo de administradores de computadores locais sempre podem elevar seus privilégios a sysadmin. (Não se aplica ao Banco de dados SQL.)
• Os membros da função de servidor fixa securityadmin podem elevar muitos de seus privilégios e, em alguns casos, podem elevar os privilégios a sysadmin. (Não se aplica ao Banco de dados SQL.)
• Os administradores do SQL Server podem ver informações sobre todos os logons e usuários. Usuários menos privilegiados geralmente veem informações apenas sobre suas próprias identidades.

Sistema de permissão de função fixa mais antigo

As funções de servidor fixas e as funções de banco de dados fixas têm permissões pré-configuradas que não podem ser alteradas. Para determinar quem é membro de uma função de servidor fixa, execute a seguinte consulta:

Note

Não se aplica ao Banco de Dados SQL ou ao Azure Synapse Analytics onde a permissão no nível do servidor não está disponível. A coluna is_fixed_role de sys.server_principals foi adicionada no SQL Server 2012 (11.x). Não é necessário para versões mais antigas do SQL Server.

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

Note

Todos os logins são membros da função pública e não podem ser removidos. A consulta verifica tabelas no banco de dados master, mas pode ser executada em qualquer banco de dados para o produto local.

Para determinar quem é membro de uma função de banco de dados fixa, execute a seguinte consulta em cada banco de dados.

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

Para entender as permissões concedidas a cada função, consulte as descrições de função nas ilustrações dos Manuais Online (Funções de nível de servidore Funções de nível de banco de dados).

Sistema de permissão granular mais recente

Este sistema é flexível, o que significa que pode ser complicado se as pessoas que o configuram quiserem ser precisos. Para simplificar as coisas, ajuda a criar funções, atribuir permissões a funções e, em seguida, adicionar grupos de pessoas às funções. E é mais fácil se a equipe de desenvolvimento do banco de dados separar a atividade por esquema e, em seguida, conceder permissões de função a um esquema inteiro em vez de a tabelas ou procedimentos individuais. Os cenários do mundo real são complexos e as necessidades empresariais podem criar requisitos de segurança inesperados.

A imagem a seguir mostra as permissões e suas relações entre si. Algumas das permissões de nível superior (como CONTROL SERVER) são listadas muitas vezes. Neste artigo, o cartaz é demasiado pequeno para ser lido. Você pode baixar o poster de Permissões do Mecanismo de Banco de Dados em tamanho real em formato PDF.

Aulas de segurança

As permissões podem ser concedidas no nível do servidor, no nível do banco de dados, no nível do esquema ou no nível do objeto, etc. Existem 26 níveis (chamados classes). A lista completa de classes em ordem alfabética é: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASESCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. (Algumas classes não estão disponíveis em alguns tipos de SQL Server.) Para fornecer informações completas sobre cada classe requer uma consulta diferente.

Principals

As permissões são concedidas a entidades de . As entidades principais podem ser funções de servidor, logons, funções de banco de dados ou utilizadores. Os logins podem representar grupos do Windows que incluem muitos usuários do Windows. Como os grupos do Windows não são mantidos pelo SQL Server, o SQL Server nem sempre sabe quem é membro de um grupo do Windows. Quando um usuário do Windows se conecta ao SQL Server, o pacote de logon contém os tokens de associação do grupo do Windows para o usuário.

Quando um usuário do Windows se conecta usando um logon baseado em um grupo do Windows, algumas atividades podem exigir que o SQL Server crie um logon ou usuário para representar o usuário individual do Windows. Por exemplo, um grupo do Windows (Engenheiros) contém usuários (Mary, Todd, Pat) e o grupo Engenheiros tem uma conta de usuário de banco de dados. Se a Mary tiver permissão e criar uma tabela, um utilizador (Mary) pode ser criado para ser o proprietário da tabela. Ou se for negada a Todd uma permissão que o resto do grupo de Engenheiros tem, então o usuário Todd deve ser criado para rastrear a recusa de permissão.

Lembre-se de que um usuário do Windows pode ser membro de mais de um grupo do Windows (por exemplo, engenheiros e gerentes). As permissões concedidas ou negadas ao login dos Engenheiros, ao login dos Gerentes, concedidas ou negadas ao usuário individualmente e concedidas ou negadas às funções das quais o usuário é membro, serão todas agregadas e avaliadas para as permissões efetivas. A função HAS_PERMS_BY_NAME pode revelar se um usuário ou login tem uma permissão específica. No entanto, não existe uma forma óbvia de determinar a origem da concessão ou recusa de permissão. Estude a lista de permissões e talvez experimente usando tentativa e erro.

Consultas úteis

Permissões do servidor

A consulta a seguir retorna uma lista das permissões que foram concedidas ou negadas no nível do servidor. Essa consulta deve ser executada no banco de dados master.

Note

As permissões no nível do servidor não podem ser concedidas ou consultadas no Banco de Dados SQL ou no Azure Synapse Analytics.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

Permissões de banco de dados

A consulta a seguir retorna uma lista das permissões que foram concedidas ou negadas no nível do banco de dados. Essa consulta deve ser executada em cada banco de dados.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

Cada classe de permissão da tabela de permissão pode ser unida a outras exibições do sistema que fornecem informações relacionadas sobre essa classe de protegível. Por exemplo, a consulta a seguir fornece o nome do objeto de banco de dados que é afetado pela permissão.

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

Use a função HAS_PERMS_BY_NAME para determinar se um usuário específico (neste caso, TestUser) tem uma permissão. Por exemplo:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

Para obter os detalhes da sintaxe, consulte HAS_PERMS_BY_NAME.

Próximos passos

• Introdução às permissões do mecanismo de banco de dados
• Tutorial: Introdução ao Mecanismo de Banco de Dados