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.

SQL Server logbestand van een database leegmaken

Wellicht dat u dit probleem herkent: u heeft een testdatabase waarvan door het testen de logfile behoorlijk groot is geworden. Op een cursus heeft u wel eens gehoord van Transact-SQL functie “DBCC SHRINKFILE”. Via Microsoft Developer Network leest u vervolgens hoe DBCC SHRINKFILE moet worden gebruikt. Vervolgens past u dit op uw eigen omgeving toe en u merkt dat het logbestand na het gebruik van de functie DBCC SHRINKFILE (‘logical name’,1) nog steeds erg groot is. Wat nu?

Een alternatieve manier om het logbestand kleiner te maken
Er is een alternatieve manier om een logbestand kleiner te maken. Het proces is iets bewerkelijker en geeft het gewenste resultaat.

Methode
Deze procedure mag NOOIT op een productieomgeving worden uitgevoerd!
De werkwijze om de logfile op een alternatieve manier te verkleinen, is als volgt:

– Detach de database van SQL Server
– Verplaats de logfile van de database naar een tijdelijke andere locatie
– Attach de database, zonder logfile (bij melding ‘not found’ regel verwijderen)

Verplaatsen logbestand
Mocht de locatie van de logfile vervolgens anders moeten zijn, dan dienen
aanvullend de volgende stappen te worden toegevoegd:

– Detach de database opnieuw
– Verplaats de logfile naar de nieuw locatie
– Voer onderstaande query uit

CREATE DATABASE [Naam van de database]
ON (FILENAME = '[schijf/locatie]\[Naam mdf bestand].mdf'),
(FILENAME = '[schijf/locatie]\[Naam log bestand].ldf')
FOR ATTACH;

Het verplaatsen van een bestand binnen een database wordt door Microsoft
op deze locatie behandeld.