Descrever permissões de banco de dados e objeto
Todas as plataformas de Gerenciamento de Banco de Dados Relacional têm quatro permissões básicas, que controlam operações de linguagem de manipulação de dados (DML). Essas permissões são SELECT
, , e , INSERT
UPDATE
e DELETE
se aplicam a todas as plataformas do SQL Server. Todas essas permissões podem ser concedidas, revogadas ou negadas em tabelas e exibições. Se uma permissão for concedida usando a instrução, a GRANT
permissão será dada ao usuário ou função referenciada GRANT
na instrução. Os usuários também podem ter permissões negadas usando o DENY
comando. Se um usuário receber uma permissão e a mesma permissão for negada, a sempre substituirá a DENY
concessão e o acesso ao objeto específico será negado ao usuário.
No exemplo acima, o usuário Demo é concedido SELECT
e, em seguida, as permissões negadas SELECT
no dbo. Tabela da empresa . Quando o usuário tenta executar uma consulta que seleciona a partir do dbo. Tabela da empresa , o usuário recebe um erro que SELECT
a permissão foi negada.
Permissões de tabela e exibição
Tabelas e exibições representam os objetos nos quais as permissões podem ser concedidas em um banco de dados. Dentro dessas tabelas e exibições, você também pode restringir as colunas que são acessíveis a uma determinada entidade de segurança (usuário ou login). O SQL Server e o Banco de Dados SQL do Azure também incluem segurança em nível de linha, que pode ser usada para restringir ainda mais o acesso.
Permissão | Definição |
---|---|
SELECT |
Permite que o usuário visualize os dados dentro do objeto (tabela ou exibição). Quando negado, o usuário será impedido de visualizar os dados dentro do objeto. |
INSERT |
Permite que o usuário insira dados no objeto. Quando negado, o usuário será impedido de inserir dados no objeto. |
UPDATE |
Permite ao usuário os dados de atualização dentro do objeto. Quando negado, o usuário será impedido de atualizar dados no objeto. |
DELETE |
Permite que o usuário exclua dados dentro do objeto. Quando negado, o usuário será impedido de excluir dados do objeto. |
O Banco de Dados SQL do Azure e o Microsoft SQL Server têm outras permissões, que podem ser concedidas, revogadas ou negadas conforme necessário.
Permissão | Definição |
---|---|
CONTROL |
Concede todos os direitos sobre os objetos. Ele permite que o usuário que tem essa permissão execute qualquer ação que desejar contra o objeto, incluindo a exclusão do objeto. |
REFERENCES |
Concede ao usuário a capacidade de visualizar as chaves estrangeiras no objeto. |
TAKE OWNERSHIP |
Permite ao usuário a capacidade de assumir a propriedade do objeto. |
VIEW CHANGE TRACKING |
Permite que o usuário visualize a configuração de controle de alterações para o objeto. |
VIEW DEFINITION |
Permite que o usuário visualize a definição do objeto. |
Permissões de função e procedimento armazenado
Como tabelas e exibições, funções e procedimentos armazenados têm várias permissões, que podem ser concedidas ou negadas.
Permissão | Definição |
---|---|
ALTER |
Concede ao usuário a capacidade de alterar a definição do objeto. |
CONTROL |
Concede ao usuário todos os direitos sobre o objeto. |
EXECUTE |
Concede ao usuário a capacidade de executar o objeto. |
VIEW CHANGE TRACKING |
Permite que o usuário visualize a configuração de controle de alterações para o objeto. |
VIEW DEFINITION |
Permite que o usuário visualize a definição do objeto. |
EXECUTAR COMO
Os EXECUTE AS [user name]
comandos , ou EXECUTE AS [login name]
(disponível apenas no SQL Server e na Instância Gerenciada SQL do Azure) permitem que o contexto do usuário seja alterado. Como comandos e instruções subsequentes serão executados usando o novo contexto com as permissões concedidas a esse contexto.
Se um usuário tiver uma permissão e não precisar mais ter essa permissão, as permissões poderão ser removidas (concede ou nega) usando o comando REVOKE. O comando revogar removerá qualquer GRANT
ou DENY
permissões para o direito especificado para o usuário especificado.
Cadeias de propriedade
Um conceito chamado encadeamento se aplica a permissões, que permite que os usuários herdem permissões de outros objetos. O exemplo mais comum de encadeamento é uma função ou procedimento armazenado que acessa uma tabela durante sua execução. Se o procedimento tiver o mesmo proprietário da tabela, o procedimento armazenado poderá ser executado e acessar a tabela, mesmo que o usuário não tenha direitos para acessar a tabela diretamente. Esse acesso está disponível porque o usuário herda os direitos de acesso à tabela do procedimento armazenado, mas somente durante a execução do procedimento armazenado e somente no contexto da execução dos procedimentos armazenados.
No exemplo abaixo, executado como proprietário de banco de dados ou administrador de servidor, um novo usuário é criado e adicionado como membro de uma nova função SalesReader , que recebe permissão para selecionar qualquer objeto e executar qualquer procedimento no esquema Sales. Um procedimento armazenado é então criado no esquema Sales que acessa uma tabela no esquema Production.
O exemplo então altera o conteúdo para ser o novo usuário e uma tentativa é feita para selecionar diretamente da tabela no esquema de produção.
USE AdventureWorks2016;
GO
CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO
CREATE ROLE [SalesReader];
GO
ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO
GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO
CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name,
SUM(SOD.LineTotal) AS TotalSales,
SOH.OrderDate
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name,
SOH.OrderDate
ORDER BY TotalSales DESC;
GO
EXECUTE AS USER = 'DP300User1';
SELECT P.Name,
SUM(SOD.LineTotal) AS TotalSales,
SOH.OrderDate
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name,
SOH.OrderDate
ORDER BY TotalSales DESC;
A consulta acima resulta em um erro de que o usuário DP300User1 não tem permissão, porque a função à qual o usuário pertence não tem SELECT
nenhum privilégio no esquema de produção. Agora podemos tentar executar o procedimento armazenado:
EXECUTE AS USER = 'DP300User1';
EXECUTE Sales.DemoProc;
O usuário DP300User1 tem permissão no procedimento armazenado no esquema Sales, porque a função do usuário tem EXECUTE
EXECUTE
permissão no esquema Sales. Como a tabela tem o mesmo proprietário do procedimento, temos uma cadeia de propriedade ininterrupta, e a execução será bem-sucedida e os resultados serão retornados.
As alterações de permissão não se aplicam quando o SQL dinâmico está sendo usado em procedimentos armazenados. A razão pela qual o SQL dinâmico quebra a cadeia de permissões é porque o SQL dinâmico é executado fora do contexto do procedimento armazenado de chamada. Você pode ver esse comportamento alterando o procedimento armazenado acima para executar usando SQL dinâmico, conforme mostrado abaixo.
CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
DECLARE @sqlstring NVARCHAR(MAX)
SET @sqlstring = '
SELECT P.Name,
SUM(SOD.LineTotal) AS TotalSales,
SOH.OrderDate
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, SOH.OrderDate'
EXECUTE sp_executesql @sqlstring
GO
--
EXECUTE AS USER = 'DP300User1'
EXECUTE Sales.DemoProc
O usuário DP300User1 receberá um erro informando que o usuário não tem SELECT
permissão na tabela Production.Product , assim como o usuário tentou executar a consulta diretamente. As cadeias de permissões não se aplicam e a conta de usuário que está executando o SQL dinâmico deve ter direitos para as tabelas e exibições que estão sendo usadas pelo código dentro do SQL dinâmico.
Princípio do menor privilégio
O princípio do menor privilégio é bastante simples. A ideia básica por trás do conceito é que os usuários e aplicativos só devem receber as permissões necessárias para que eles concluam a tarefa. Os aplicativos só devem ter permissões que precisam fazer para concluir a tarefa em mãos.
Por exemplo, se um aplicativo acessa todos os dados por meio de procedimentos armazenados, o aplicativo só deve ter a permissão para executar os procedimentos armazenados, sem acesso às tabelas.
Dynamic SQL
SQL dinâmico é um conceito onde uma consulta é criada programaticamente. O SQL dinâmico permite que instruções T-SQL sejam geradas dentro de um procedimento armazenado ou de uma consulta propriamente dita. Um exemplo simples é mostrado abaixo.
SELECT 'BACKUP DATABASE ' + name + ' TO DISK =''\\backup\sql1\' + name + '.bak'''
FROM sys.databases
A instrução acima gerará uma lista de instruções T-SQL para fazer backup de todo o banco de dados no servidor. Normalmente, esse T-SQL gerado será executado usando sp_executesql
ou passado para outro programa para executar.