Yep, that’s right, you heard me. Write-Only not Read-Only. I was presenting SQL Server Security Basics at NTSSUG the other night and there was an interesting discussion on the idea of granting someone write permissions without corresponding read permissions.

So for example:

-- 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

The user WriteOnlyUser now has permission to insert, update and delete from any table in AdventureWorks2014. They do not, however, have permission to read from any of the tables.

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

So they can INSERT without a problem, but can’t SELECT. They can also UPDATE and DELETE. Or can they?

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

Now wait, why are they getting a read error when trying to UPDATE or DELETE? Because of the WHERE clause. The WHERE requires reading the data to see if a row meets the required conditions.

This however will work

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

So WriteOnlyUser can UPDATE or DELETE but only if it’s not actually looking at any of the data. i.e. a WHERE clause. I don’t know about you but that rather felt like working without a net. Or maybe running with scissors.

Someone in the session suggested the interesting (possible) workaround of using the OUTPUT clause. In other words could WriteOnlyUser update (or delete) a bunch of rows and bypass needing read permissions by looking at the output created by the OUTPUT clause. As it happens someone went home, tried it, and sent me the results before I even got home that night. (Yes, you beat me Lee, but you have a much shorter drive home than I do. :p ) We are going to pretend they didn’t and try it out ourselves.

EXECUTE AS USER = 'WriteOnlyUser';
GO
CREATE TABLE #TempPersonPhone (
BusinessEntityId INT,
PhoneNumber varchar(50),
PhoneNumberTypeId smallint,
ModifiedDate datetime);
GO
UPDATE TOP (1) Person.PersonPhone
SET PhoneNumberTypeId = PhoneNumberTypeId
OUTPUT inserted.BusinessEntityID, inserted.PhoneNumber,
    inserted.PhoneNumberTypeID, inserted.ModifiedDate
    INTO #TempPersonPhone;
GO
SELECT * FROM #TempPersonPhone;
GO
REVERT;
GO

So not only could WriteOnlyUser not use the OUTPUT clause but when it tried nothing happened (no rows affected).

Fun experement but the real point here is that if you don’t actually have read permission (SELECT) you can’t read anything from the table.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: database permissions, microsoft sql server, security