Selectively Locking Down Data – Gracefully

I have a situation where I need to retrieve the data in an encrypted column from, but don’t want to give all my users access to the symmetric key used to encrypt that column. The data is of the sort where it’s important for the  application to produce the required output, but if a user runs the stored procedure to see what the application is getting from it, it’s not critical that they see this one field.

The catch is that if the stored procedure is written with the assumption that the caller has permission to access the encryption key or its certificate, they’ll get an error. After a bit of research and pondering later, I came up with two options:

  1. Create the stored procedure with EXECUTE AS OWNER (the owner in this case is dbo). This would let all users see the encrypted data; not an ideal solution.
  2. Use SQL Server’s TRY/CATCH construct to gracefully handle the error thrown when the user attempts to open the key, but doesn’t have permission to do so.

Let’s check out option 2. This example is simplified from my actual scenario to demonstrate the idea.

[code language=”sql”]

declare @BankId varchar(6) = ‘123456’;

SELECT cast(” as varchar(50)) AS AccountNum,
,AccountName
,AccountOwner
INTO #AccountData
FROM dbo.Accounts
WHERE OriginatingBank = @BankId
AND AccountType = ‘Checking’

DECLARE @AcctNo VARCHAR(30);

BEGIN TRY
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE My_Cert

SELECT @AcctNo = CONVERT(VARCHAR, decryptbykey(AccountNum))
FROM dbo.Accounts
WHERE OriginatingBank = @BankId
AND AccountType = ‘Checking’

CLOSE SYMMETRIC KEY MyKey
END TRY

BEGIN CATCH
SET @AcctNo = ‘Access Restricted’;
END CATCH

UPDATE #AccountData SET AccountNum = @AcctNo;

SELECT * FROM #AccountData;

DROP TABLE #AccountData;

[/code]

TRY/CATCH in T-SQL works similarly to how it does in languages like C# or PowerShell. It allows you to attempt an operation and take care of any error conditions fairly easily.

In this case, I’m attempting to open the encryption key. But if the user doesn’t have permission to do so, it doesn’t terminate the stored procedure with an error. Instead, it jumps to the CATCH block, where I’ve defined an alternate way of handling the situation. Here, if the user doesn’t have the appropriate permissions, they’ll just get “Access Restricted” for the account number, and access to that sensitive data is a little more tightly controlled – while still letting users access the data they do need.

Hello GETDATE() My Old Friend…

So you’ve decided that your new web application needs to record some page load time metrics so you can keep tabs on performance. Terrific!  You set up a couple page load/complete functions to write to a logging table when a page request comes in, and then update the record when it finishes loading.

[code lang=”sql”]INSERT INTO PageLogs (
RequestTime
,ResponseTime
,RemoteIP
,UserName
,PageId
,Parameters
,SessionId
) VALUES (
GETDATE()
,NULL
,127.0.0.1
,’Dave’
,’Home’
,’Pd=2015Q2′
,’883666b1-99be-48c8-bf59-5a5739bc7d1d’
);[/code]

[code lang=”sql”]UPDATE PageLogs
SET ResponseTime = GETDATE()
WHERE SessionId = ‘883666b1-99be-48c8-bf59-5a5739bc7d1d’;[/code]

You set up an hourly job to delete any logs older than 2 weeks (just to prevent information overload) and you call it a day. Each morning, you run a report to look at the previous day’s performance, watch the trends over the past week or so, and you’re pretty happy with things. Pages are loading in a fraction of a second, according to the logs. People find the application useful, word spreads around the office, and adoption takes off. The project is a success!

Then the support calls start rolling in. Users say it’s taking “forever” to load pages (they don’t have exact numbers, but it’s just too slow). This can’t be possible. The report says everything’s running just as fast as it did in test!

You walk down the hall and visit your friendly Senior DBA. He pulls up his monitoring tools and shows you that the hourly maintenance that keeps the PageLogs table fit & trim is causing a bunch of blocking while it does lots of DELETEs. And your INSERT queries are the victims.

Here’s the thing: GETDATE() (like any other function) doesn’t get evaluated until that query executes. Not when you call ExecuteNonQuery(), not even when SQL Server receives the query. So even if your INSERT isn’t holding up the execution of your page (because you’ve executed it asynchronously), it won’t accurately represent when the page load started. Instead, it tells you when your query executed. In this context that can be misleading because it won’t tell you how long it really took for your page to load.

If you need to log the time an event transpired accurately, GETDATE() isn’t your friend. You need to explicitly set the time in the query.

[code lang=”sql”]INSERT INTO PageLogs (
RequestTime
,ResponseTime
,RemoteIP
,UserName
,PageId
,Parameters
,SessionId
) VALUES (
‘2015-05-15T09:45:00Z’
,NULL
,127.0.0.1
,’Dave’
,’Home’
,’Pd=2015Q2′
,’883666b1-99be-48c8-bf59-5a5739bc7d1d’
);[/code]

[code lang=”sql”]UPDATE PageLogs
SET ResponseTime = ‘2015-05-15T09:45:02Z’
WHERE SessionId = ‘883666b1-99be-48c8-bf59-5a5739bc7d1d’;[/code]

If you aren’t used to seeing significant blocking in your databases, you may not have run into this. But get into this habit anyway. At some point you probably will see blocking on a table like this, and logging with GETDATE() will make the data you attempted to write during that blocking invalid. If you can’t trust all of your data, can you trust any of it?