Write-Only permissions

3

August 11, 2016 by Kenneth Fisher

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

WriteOnly1

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

WriteOnly2

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

WriteOnly3

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

WriteOnly4

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.

3 thoughts on “Write-Only permissions

  1. Joe says:

    What about trying to insert a row into a table that generates a primary key violation — isn’t that information disclosure about a table that you’re not supposed to have access to?

    • Hmm, hadn’t thought of that. Let me give it a quick try.

      • Ok, very cool. Yes, you do get a primary key error, and yes it tells you the duplicate value. In fact if you have any unique index/constraint you can get the same errors.

        Which means that with some effort you could get back some information from the table even without read permissions. Of course if the only unique key on the table is an identity column you aren’t going to get very far 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: