GRANT, DENY i kolumny tabeli

Model uprawnień SQL Server stosuje kaskadowe dziedziczenie uprawnień. To znaczy, że uprawnienia nadane na wyższym poziomie automatycznie obowiązują na poziomach niżej. Pełna ścieżka dziedziczenia to:

1. Instancja
2. Baza danych
3. Schemat
4. Element schematu (tabela, widok, procedura, funkcja)
5. Kolumna (w przypadku tabel i widoków)

W modelu uprawnień jest dodatkowo przyjęta zasada, że DENY jest ważniejsze niż GRANT. Czyli np. DENY na poziomie schematu powoduje brak dostępu do wszystkich obiektów w schemacie – nawet jeśli do elementu schematu przypisano wprost uprawnienie za pomocą GRANT. Dotyczy to wszystkich obiektów, poza kolumnami.

W dokumentacji polecenia DENY możemy znaleźć informację, że to ze względu na zachowanie zgodności z poprzednimi wersjami (na razie nie znalazłem informacji względem której wersji początkowej, ale samo DENY wprowadzono w wersji SQL Server 7.0)

A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for backward compatibility.

OK. Zrobiono wyjątek dla kolumn. A czy zawsze on działa? Czy da się wymusić, żeby DENY na poziomie tabeli było ważniejsze od GRANT na poziomie kolumn baz danych?

Tak. Da się wymusić, ale wcale nie musi nam się podobać sposób, w jaki można to osiągnąć.

Pierwszy sposób: GRANT jest ważniejsze niż DENY tylko w przypadku, jeśli najpierw wykonamy operację DENY na poziomie tabeli, a potem GRANT na poziomie kolumny. Jeśli zamienimy kolejność operacji, lub po prostu jeszcze raz nadamy DENY dla tabeli uprawnienia do kolumn nie będą obowiązywały:

-- to daje uprawnienia do kolumn id, code użytkownikowi testUser
DENY SELECT ON dbo.TestTable TO testUser;
GRANT SELECT ON dbo.TestTable(id, code) TO testUser;
GO

-- a to zabierze, mimo że przed chwilą było GRANT
DENY SELECT ON dbo.TestTable TO testUser;
GO

-- ten sam efekt, jeśli najpierw zrobimy GRANT, a potem DENY
GRANT SELECT ON dbo.TestTable(id, code) TO testUser;
DENY SELECT ON dbo.TestTable TO testUser;
GO

Drugi sposób jest o wiele większego kalibru i warto się dobrze zastanowić czy warto. SQL Server udostępnia opcję konfiguracji Enable Common Criteria compliance, ale dotyczy ona całej instancji, nie pojedynczej bazy danych.

Włączenie tej opcji może się odbyć albo przez powyższe okno w SSMS, albo przez wykorzystanie sp_configure:

EXEC sys.sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sys.sp_configure 'common criteria compliance enabled', 1;
GO

RECONFIGURE;
GO

Na koniec ważne, żeby zrestartować instancję.

OK, ale po co to włączać? W dokumentacji tej opcji znajdziemy m.in. informację:

After the common criteria compliance enabled option is enabled, a table-level DENY takes precedence over a column-level GRANT. When the option is not enabled, a column-level GRANT takes precedence over a table-level DENY.

Oprócz tego w pakiecie otrzymujemy Residual Information Protection oraz ability to view login statistics. Co to dokładnie oznacza można przeczytać w dokumentacji.

Czyli mamy dwa rozwiązania:

• włączyć opcję Common Criteria compliance – co powoduje także kilka dodatkowych operacji, na które niekoniecznie musimy mieć ochotę
• zawsze ponawiać DENY na poziomie tabeli

Pełny skrypt obrazujący działanie GRANT/DENY można znaleźć na moim GitHubie. Warto wykonać najpierw z wyłączoną opcją Common Criteria compliance, a po jej włączeniu i restarcie instancji powtórnie.

• bezpieczeństwo
• sql server