Jak niebezpieczne jest udzielanie uprawnień ALTER TABLE?

Wyobraź sobie następujący scenariusz

CREATE DATABASE test

GO

USE test;    

CREATE TABLE dbo.Customer
  (
     CustomerId   INT,
     Email        VARCHAR(100),
     SensitiveData VARCHAR(20)
  );

INSERT INTO dbo.Customer
VALUES     (1,'abc@foo.com','12346789');

W pewnym momencie zapisywany jest proces ETL, który wykonuje pewne działania w testbazie danych.

CREATE USER etlUser WITHOUT LOGIN; /*For demo purposes*/

CREATE TABLE dbo.StagingTable
  (
     StagingTableId INT,
     SomeData       VARCHAR(100),
  )

GRANT UPDATE,INSERT,DELETE,SELECT,ALTER ON dbo.StagingTable TO etlUser;

DENY SELECT ON dbo.Customer TO etlUser;
DENY SELECT ON dbo.Customer (SensitiveData) TO etlUser; /*For good measure*/

EtlUser nie powinien mieć uprawnień do Customertabeli (a już na pewno nie do SensitiveDatakolumny), więc wyraźnie odmawia się ich powyżej.

Proces ETL jest obcinany, dbo.StagingTablewięc ma ALTERna to uprawnienia do tabeli.

Jest to oznaczane podczas audytu bezpieczeństwa. Jak niebezpieczny jest ten scenariusz?

Całkiem niebezpieczne ...

Oprócz oczywistego uprawnienia do zmiany samej struktury StagingTable, ALTER TABLEuprawnienie pozwala im tworzyć wyzwalacze na stole. W tym przypadku dzięki łańcuchowi własności są w stanie zarówno zobaczyć poufne dane klientów (pomimo wyraźnych DENYzezwoleń), jak i dokonać wandalizmu na tym drugim stole.

EXECUTE AS user='etlUser'

GO

CREATE OR ALTER TRIGGER TR ON dbo.StagingTable AFTER UPDATE AS 
/*Exposure of sensitive data*/
SELECT * FROM dbo.Customer;

/*Vandalism*/
DELETE FROM dbo.Customer;

go

--Fire the trigger
UPDATE dbo.StagingTable SET SomeData = SomeData WHERE 1=0;

REVERT

Oprócz możliwości dodawania wyzwalaczy uprawnienie ALTER TABLE pozwala również na:

1. Wyłączanie wyzwalaczy (unikaj ścieżki audytu)
2. Wyłącz ograniczenia (dopuszczając złe dane)
3. Zmiana przeciwwskazań (dopuszczenie złych danych)
4. Zmiana definicji kolumn (zmiana typu danych, maksymalny rozmiar, NULLability)
5. Dodaj kolumnę obliczeniową wywołującą UDF T-SQL (co bardzo utrudnia uzyskanie planu równoległego, co może z łatwością zaszkodzić wydajności)

Pozwala również na usunięcie kolumn, ale nie pozostanie to niezauważone (ponieważ wydaje się, że szukamy tutaj potencjalnych działań, które będą bardziej zwodnicze niż złośliwe).

Na szczęście nigdy nie jest konieczne, aby udzielić zgody na nikogo, ani nie jest to konieczne, by zakończyć to w procedurze przechowywanej, który korzysta z EXECUTE ASklauzuli (zazwyczaj następuje 'dbo'albo OWNER). Podpisywanie modułów pozwala na łatwą abstrakcję uprzywilejowanych działań za podpisanym kodem (procedury składowane, wyzwalacze, skalarne UDF i wielofazowe TVF). Mam przykładowy kod pokazujący, jak to zrobić w następujących odpowiedziach, tutaj na DBA.SE:

• Uprawnienie SQL do obcinania tabeli
• Uprawnienia SQL Server TRUNCATE TABLE

Różnica między tymi dwiema odpowiedziami polega na uprawnieniu przyznanym Użytkownikowi opartemu na podpisach. Zezwolenie, które ma zostać przyznane (lub rola DB, do której ma zostać dodane) zależy od zakresu potrzebnych potrzeb. Jeśli potrzebujesz uprawnień tylko dla pojedynczej tabeli, udziel jej tylko ALTERdla tej tabeli. Jeśli potrzebne są uprawnienia do wszystkich tabel w określonym schemacie, nie udzielaj uprawnień do poszczególnych tabel, ale zamiast tego przyznaj uprawnienia do samego schematu. I tak dalej.

Podpisywanie modułów to kilka dodatkowych kroków w porównaniu do tworzenia schematu specjalnie dla użytkownika ETL lub korzystania z EXECUTE ASklauzuli, ale:

1. jest to po prostu zwykła kopia i wklej, biorąc pod uwagę, że kod jest dostępny w obu odpowiedziach powyżej i
2. jest to z pewnością najbezpieczniejsza dostępna opcja. Zezwala tylko na tę operację za pośrednictwem tego fragmentu kodu i tylko tym, którym udzielono EXECUTEpozwolenia na ten kod. Bycie właścicielem schematu pozwala na pewne niejawne uprawnienia, które są niepotrzebne. I użycie EXECUTE AS 'dbo'lub EXECUTE AS OWNER(zakładając, że właścicielem jest dbo) da cały proces , od tego momentu, dbouprawnienia, a nie tylko procedurę przechowywaną / wyzwalacz / funkcję, której użyłeś EXECUTE AS. Podpisywanie modułów ogranicza uprawnienia tylko do podpisanego kodu, a nie do żadnego kodu wywoływanego przez podpisany kod.

Lepszą praktyką byłoby utworzenie schematu pomostowego, którego właścicielem jest użytkownik ETL. Następnie proces ETL może obcinać tabele, wyłączać ograniczenia, przeprowadzać przełączanie partycji itp. W ramach schematu pomostowego. Użytkownik ETL potrzebowałby jedynie ograniczonego pozwolenia na inne schematy.

Możesz także użyć roli bazy danych zamiast jednego użytkownika.

Oczywiście możesz również umożliwić ograniczonemu użytkownikowi wykonywanie obcinania tabel za pomocą procedury składowanej będącej własnością dbo, w następujący sposób:

create procedure truncate_t 
with execute as owner
as
begin
  truncate table t;
end