Determinar permissões efetivas do mecanismo de banco de dados
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)
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. Está disponível, a partir do SQL Server 2005 (9.x), um sistema mais flexível e preciso.
Observação
As informações deste 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.
Você deve sempre se lembrar destes pontos:
- As permissões efetivas são a agregação dos dois sistemas de permissão.
- Uma negação de permissões substitui uma concessão de permissões.
- Se o usuário é membro da função de servidor fixa sysadmin, as permissões não são verificadas e, portanto, negações não são impostas.
- O sistema antigo e o novo têm semelhanças. Por exemplo, associação à função fixa de servidor
sysadminé semelhante a ter permissãoCONTROL SERVER. Mas os sistemas não são idênticos. Por exemplo, se um logon só tem a permissãoCONTROL SERVERe os procedimentos armazenados verificarem a associação na função de servidor fixasysadmin, a verificação de permissão falhará. O contrário também é verdade.
Resumo
- A permissão de nível de servidor pode vir da associação a funções de servidor fixas ou funções de servidor definidas pelo usuário. Todas pertencem à função de servidor fixa
publice recebem qualquer permissão atribuída lá. - As permissões de nível de servidor podem vir de permissões concedidas para logons ou funções de servidor definidas pelo usuário.
- A permissão de nível de banco de dados pode vir da associação em funções de banco de dados fixas ou funções de banco de dados definidas pelo usuário em cada banco de dados. Todas pertencem à função de banco de dados fixa
publice recebem qualquer permissão atribuída lá. - As permissões de nível de 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 logon
guestou do usuário de banco de dadosguest, se habilitado. O logongueste os usuários estão desabilitados por padrão. - Os usuários do Windows podem ser membros de grupos do Windows que podem ter logons. O SQL Server aprende com a associação de 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 modo confiável, as permissões de 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 ao executar um procedimento armazenado que inclui a cláusula
EXECUTE AS. - As permissões podem ser adquiridas com logons ou usuários com a permissão
IMPERSONATE. - Os membros do grupo administrador do computador local sempre podem elevar seus privilégios para
sysadmin. (Não se aplica ao banco de dados SQL.) - Os membros da função de servidor fixa
securityadminpodem elevar muitos de seus privilégios e, em alguns casos, podem elevar os privilégios parasysadmin. (Não se aplica ao banco de dados SQL.) - Os administradores do SQL Server podem ver as informações sobre todos os logons e usuários. Usuários com menos privilégios geralmente veem apenas as informações de suas próprias identidades.
Sistema de permissão de função fixa anterior
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 é um membro de uma função de servidor fixa, execute a consulta a seguir:
Observação
Não se aplica ao Banco de Dados SQL ou ao Azure Synapse Analytics em que a permissão de nível de servidor não esteja disponível. A coluna is_fixed_role de sys.server_principals foi adicionada no SQL Server 2012 (11.x). Ela não é necessária para versões anteriores 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;
Observação
Todos os logons e os usuários são membros das funções públicas e não podem ser removidos. A consulta verifica tabelas no banco de dados master, mas ela pode ser executada em qualquer banco de dados do produto local.
Para determinar quem é membro de uma função de banco de dados fixa, execute a consulta a seguir 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 em ilustrações nos Manuais Online (Funções de nível de servidor e Funções de nível de banco de dados).
Novo sistema de permissão granular
Esse sistema é flexível, o que significa que ele poderá ser complicado se as pessoas que estão definindo a configuração buscarem precisão. Para simplificar, ele ajuda a criar funções, atribuir permissões a funções e adicionar grupos de pessoas para as funções. E é mais fácil se a equipe de desenvolvimento de banco de dados separar atividades por esquema e, em seguida, conceder permissões de função para um esquema inteiro em vez de tabelas individuais ou procedimentos. Cenários do mundo real são complexos e as necessidades de negócios podem criar requisitos de segurança inesperados.
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.
Classes de segurança
As permissões podem ser concedidas no nível do servidor, no nível de banco de dados, no nível de esquema ou no nível de objeto etc. Há 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.) Fornecer informações completas sobre cada classe requer uma consulta diferente.
Principals
As permissões são concedidas a entidades de segurança. Entidades de segurança podem ser usuários, logons, funções de banco de dados ou funções de servidor. Os logons 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 saberá que é 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 de 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 do Windows individual. Por exemplo, um grupo do Windows (Engenheiros) contém os usuários (Mary, Todd, Pat) e o grupo de engenheiros têm uma conta de usuário de banco de dados. Se Mary tem permissão e cria uma tabela, um usuário (Mary) pode ser criado para ser o proprietário da tabela. Ou, se Todd tiver uma permissão negada que o restante do grupo de engenheiros tem, então, o usuário Todd deverá ser criado para acompanhar a negação 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 logon de engenheiros, ao logon de gerentes, concedidas ou negadas ao usuário individualmente e concedidas ou negadas a funções das quais o usuário é membro, serão agregadas e avaliadas para as permissões efetivas. A função HAS_PERMS_BY_NAME pode revelar se um usuário ou logon tem uma permissão específica. No entanto, não há nenhuma maneira óbvia de determinar a origem da concessão ou negação de permissão. Estude a lista de permissões e, talvez, realize testes de tentativa e erro.
Consultas úteis
Permissões de servidor
A consulta a seguir retorna uma lista das permissões concedidas ou negadas no nível do servidor. Essa consulta pode ser executada no banco de dados master.
Observação
As permissões de nível de servidor não podem ser concedidas nem 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 concedidas ou negadas no nível do banco de dados. Essa consulta pode 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ões 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óximas etapas
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de