This week’s #sqlnewblogger posts!
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:
- 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.
- 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.
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;
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.
This week’s #sqlnewblogger posts!
If you need to move data from one table into a new table, or even tables in a database into another database, the Import/Export Wizard in SQL Server Management Studio looks pretty tempting. Set up a source & destination, click a few buttons, kick back with a cup of tea and watch the progress bars, right?
It turns out that the wizard just isn’t as smart as it may seem. If you’re not careful, you won’t get what you’re expecting. Let’s check it out.
We’ll start by creating a real simple table in a database, containing a primary key and a computed column.
Create table sample.dbo.SourceTable ( RowId int identity(1,1) not null primary key, Num1 int not null, Num2 int not null, Total as (Num1+Num2) );
Let’s populate it with a few rows of data, then update some of that data to make sure the computed column is working. Remember, this is just to demonstrate the idea.
insert into sample.dbo.SourceTable (Num1, Num2) values (1,2); go 100 select top 5 * from sample.dbo.SourceTable order by RowId; update sample.dbo.SourceTable set Num1 = Num1 * RowId where RowId <= 3; select top 5 * from sample.dbo.SourceTable order by RowId;
Great! We’ve got data, the computed columns are working, let’s copy it over to a new table in another database. We’ll just going to click Next, Next, Next through the wizard this time around.
Success! Our table has been copied and the data’s all there.
select top 5 * from Sample2.dbo.SourceTable order by RowId;
Let’s do some work on our new table and check out the results.
select top 5 * from Sample2.dbo.SourceTable order by RowId; update Sample2.dbo.SourceTable set Num2 = Num2 * RowId where RowId < 3; select top 5 * from Sample2.dbo.SourceTable order by RowId;
Woah! That’s not right. That Total column is supposed to be Num1 + Num2, and last time I checked 2 + 4 was not 4. Let’s keep going and try adding a new record the same way it was done earlier.
insert into Sample2.dbo.SourceTable (Num1, Num2) values (100,200);
Cannot insert the value NULL into column 'RowId', table 'Sample2.dbo.SourceTable'; column does not allow nulls. INSERT fails.
Huh. Now that’s really odd, isn’t it? RowId is supposed to be an identity – we shouldn’t have to populate it. What is going on here? Let’s script out the table.
USE [Sample2] GO /****** Object: Table [dbo].[SourceTable] Script Date: 2015-11-10 22:36:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SourceTable]( [RowId] [int] NOT NULL, [Num1] [int] NOT NULL, [Num2] [int] NOT NULL, [Total] [int] NULL ) ON [PRIMARY] GO
This is all kinds of wrong! What happened to the primary key? Or the computed column? Well, it turns out that the wizard isn’t that smart, and if you just take all the default values, you’re going to get burned. Let’s go back to the wizard and click that Edit Mappings button in the Select Source Tables and Views screen.
Well…that looks like what we got above. And it’s not what we wanted. If we click Edit SQL, this is confirmed – the table being created is not defined the same way the source table is being defined.
Fortunately, we can edit the SQL here and make it match the source table definition, then finish the wizard.
OK, data’s copied – what do we have?
select top 5 * from Sample3.dbo.SourceTable order by RowId; update Sample3.dbo.SourceTable set Num2 = Num2 * RowId where RowId < 3; select top 5 * from Sample3.dbo.SourceTable order by RowId; insert into Sample3.dbo.SourceTable (Num1, Num2) values (100,200); select * from sample3.dbo.SourceTable where rowid >= 100 order by RowId;
Everything’s there, and it’s working the way it’s supposed to. Lesson learned: don’t blindly trust the defaults, especially the ones in a wizard. Double-check everything, and then verify that your schema works the way you expect it to before doing any business with it.
Ed Leighton-Dick has renewed his New Blogger Challenge this month. Here are all (I think) the posts for this week after Ed posted his announcement. If I’ve missed any, please let me know and I’ll update.
Spend any time around a 4 year old, and you will inevitably find yourself involved in a conversation which evolves into this:
- Please do this thing
- Reasonable answer
- Restatement of reasonable answer
- Shorter, more frustrated restatement of reasonable answer
- Because that’s what has to be done
- I give up. Go ask your other parent
It’s a simple, but powerful and important question. The trouble is that when it’s a 4 year old asking it, in a lot of cases they can’t understand the answer. More often, they aren’t interested in understanding it.
Fortunately, there aren’t any 4 year olds in the average IT shop (although it may not be too far off).
A while ago, a data issue came to my team. Nothing major, but enough that it caused problems for a user. It’s a small glitch with an application component which pops up maybe once or twice a year, so it’s been decided that it’s better to just fix the data in those rare cases as opposed to spending 20 hours tracking down the root cause & fixing it there (I’m the SME for this component).
The requested correction was to delete the entire record, based on a previous fix to a similar but unrelated data problem. By the time I saw the request, a teammate had picked it up & started working on it.
“Wait! Don’t do it that way!” I said. “All we should be doing here is fixing the one erroneous field on that record.” This had come up in the past, but with it happening so rarely it’s easy to forget about.
I paused to catch my breath, then heard it.
I had to pause even longer to collect my thoughts. I don’t often get asked questions on things like this but I wish it happened daily.
This is the moment in which knowledge is gained, even by the answerer.
When you live & breathe a system for years on end, it’s easy to take certain aspects of it for granted. You respond without having to think about why things work the way they do – you just know that’s how it is.
The ensuing conversation was productive and I hope informative for my co-workers. While deleting the record would have the desired short-term result (making the application function properly), in the long term it would break the link between the data and a document which is referenced by that data. A net loss. Fixing the one column (setting it to the value which it should have been in the first place) allows the application to function correctly and retain access to that referenced document.
The conversation also forced me to take a closer look at my own understanding of the issue and re-evaluate what I thought I knew about it. It turns out, I had some bad assumptions in there too, which I was able to correct.
Not only did my teammates learn, I learned too. Everyone wins.
So why was the original solution of deleting the whole record requested? The answer isn’t too far removed from the idea of cargo cult programming. That is, someone saw the solution of deleting the whole record used in a similar case years ago, documented it, and it was seen as the One True Answer from that point forward – regardless of its applicability to the situation at hand. A detailed explanation of “why” isn’t usually written for every issue that comes to our team for resolution, for a few reasons:
- We don’t think to do it.
- There isn’t a good way to distinguish between this bug in the system and others without having a fairly deep knowledge of the system.
- There isn’t a way in our ticketing system to record information that isn’t visible to everyone, and the whole company does not need to see the dirty details of the internals of every system – in fact, it would probably be counterproductive.
In hindsight, a carefully-written, more thorough explanation many years ago may have prevented this particular request from being written as it was.
Asking why became the basis for Toyota’s approach to improving their manufacturing processes, and is built into Six Sigma and many other process improvement methodologies. This one word is the gateway to understanding, and once we understand, we can find ways to do things better.
If you’re curious about something, release your inner 4 year old. Just don’t act like a 4 year old when you do it. Keep asking why, get to the answers – and make sure you understand them.
If someone asks you why, embrace the question. This person is interested, they’re engaged, they want to learn! Take advantage of that opportunity to teach and spread that knowledge. Along the way, you just might learn something yourself.