Uitdagingen bij alleen schrijfrechten en de WHERE clausule

Bron en credits: https://sqlstudies.com/2016/08/11/write-only-permissions/
Gebruikte demo database: AdventureWorks 2014

Tijdens het verzamelen van informatie kwam ik een interessante reproductie tegen van een probleem bij het uitgeven van schrijfrechten zonder leesrechten. Deze situatie kan zich onder andere voorden bij het ontwikkelen van applicaties, waarbij de applicatie uitsluitend tabellen mag bijwerken, zonder dat tabellen hoeven te worden gelezen. Daar zit een behoorlijke adder onder het gras waar bij het uitrollen van de beveiliging van de database wel rekening mee moet worden gehouden.

Het scenario werkt als volgt: een applicatie draagt uitsluitend zorg voor het bijwerken van velden in een database. Theoretisch zou de rol db_datawriter rechten dan voldoende moeten zijn. Laten we een dergelijke gebruiker aanmaken en aan de database AdventureWorks 2014 koppelen:

-- Setup code
CREATE LOGIN WriteOnlyUser WITH PASSWORD = 'WriteOnlyUser',CHECK_POLICY = OFF;
GO
USE AdventureWorks2014;
GO
CREATE USER WriteOnlyUser FROM LOGIN WriteOnlyUser;
GO
ALTER ROLE db_datawriter ADD WriteOnlyUser;
GO

De gebruiker WriteOnlyUser heeft nu uitsluitend update rechten op de database AdventureWorks.

EXECUTE AS USER = 'WriteOnlyUser';
GO
INSERT INTO Person.PersonPhone (BusinessEntityID, PhoneNumber, PhoneNumberTypeID) 
    VALUES (1,'999-999-9999',1);
GO
REVERT;
GO

Werkt prima! Mooi! Met andere woorden de gebruiker WriteOnlyUser kan de tabel in de database aanvullen. Nu vraagt de applicatie om één record in de tabel aan te passen:

EXECUTE AS USER = 'WriteOnlyUser';
GO
UPDATE Person.PersonPhone SET PhoneNumberTypeID = 3
    WHERE BusinessEntityId = 1
      AND PhoneNumber = '999-999-9999';
GO
DELETE Person.PersonPhone
    WHERE BusinessEntityId = 1
      AND PhoneNumber = '999-999-9999';
GO
REVERT;
GO

Msg 229, Level 14, State 5, Line ##
The SELECT permissions was denied on the object ‘PersonPhone’, database ‘AdventureWorks 2014’

Eh… wat??? Waarom verschijnt hier een leesfout, terwijl gevraagd wordt de tabel in de database bij te werken? Wat helemaal gek is, is dat dit wel werkt:

EXECUTE AS USER = 'WriteOnlyUser';
GO
UPDATE TOP (1) Person.PersonPhone SET PhoneNumberTypeID = 3;
GO
DELETE TOP (1) Person.PersonPhone;
GO
REVERT;
GO

Het antwoord
Een WHERE clausule is een leesactie. Immers, de tabel moet gelezen worden om te bepalen welke records aan de voorwaarde voldoen. Bij het inrichten van de rechtenstructuur is het dus belangrijk met de WHERE clausule rekening te houden. Er is geen workaround voor dit probleem. Ik kan mij geen situaties voorstellen waarin applicaties structureel alle records in een tabel wilt bijwerken of verwijderen. Je ontkomt er dan bijna niet aan om de rechten voor de applicatie met leesrechten uit te breiden.