Voice of the DBA

Author: way0utwest

  • Badly Encrypted Databases

    I ran across a blog about encrypted databases linked from Bruce Schneier’s blog. I follow his musings and writings on security ,and he recommended we read it with this sentence: “Even the summary is too much to summarize, so read it.” Good enough for me, so I clicked the link and read about encrypted databases.

    I like the idea of stronger encryption in databases, and I’ve given a few talks on the subject. At times there are attendees that will debate that encryption in the database doesn’t do a lot of good. Often they dismiss the idea of TDE, since administrators can still read the data and break the encryption, and normal users aren’t affected. Many also note that database encryption does nothing for data on the wire, which is true. Most people want to do the encryption and decryption on the client, which has other challenges and is fairly hard to do well.

    I think that security a series of layers, and as noted by the author of the blog, most criminals are lazy. If they can copy a backup file or data file, they’ll just do that and read the data. TDE isn’t perfect, but it does limit these simple attacks. Always Encrypted was developed to try and make it easy to include encryption from the client side, but in SQL Server 2016, it has lots of limitations. In SQL Server 2019, we get secure enclaves, which should help adoption somewhat, but we will see once developers start to experiment with the feature.

    The blog talks about problems with encryption, spending quite a bit of time on approximate database reconstruction, which is essentially guessing data values with some information and by watching queries and results. It’s somewhat fascinating, and also scary, but complex and likely requiring lots of queries. To me, this is an area we ought to focus, and really an area that all our protocol libraries and possibly database firewalls (or built in limits) ought to focus efforts. We shouldn’t be most clients to make large queries of all data in a table. Really at this point, we ought to have build in limitations of queries to ensure that users are exporting all data from a table. I’d like some throttle that might prevent the return of very large result sets to clients.

    At the same time, there ought to be some way to analyze the queries coming in and see if an attacker is “guessing” values. I won’t pretend to know how we might do this, but it would seem that in the same way we detect lots of login attempts, we could have some alert being raised when we had xxx of the same type of query in yyy time. That might alert us to potential problems.

    Or users running lots of searches.

    I don’t know the best way to protect data, but I do know that too many of us aren’t doing a good job of this. We need to get better, both in production and development environments. We need to be better at protecting databases and the data within them.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The T-SQL Tuesday #112 Cookie

    tsqltuesdayIt’s T-SQL Tuesday again, and I’m writing in response to Shane O’Neil’s invitation about cookies. Read what he asks and this will make more sense.

    As always, if you want to participate, write a post on your blog and follow the rules on the invite.

    If you want to host, ping me on Twitter.

    My Cookie

    I’ve had quite a bit of success in my career. I’ve worked hard, and I was lucky to be born intelligent enough to get things done in this field, but I’ve had a lot of help along the way. I don’t want to discount the things others have done for me, whether that’s a favor, an opportunity, or a lesson. This post is about standing on shoulders, my own and others, in a way that still makes me smile today.

    I had a lot of experience programming in the past. I’m not great at it, but I’m effective. A few years ago someone asked about getting a list of the PASS sessions from the DVD that they’d purchase in a data set. They didn’t want to go through the HTML index on the drive, but wanted a better data set to work with the sessions.

    At the time, I had the USB key sitting on my desk. I was doing some R learning at the time and decided to see if I could do something in R. I remembered a few years earlier when Aaron Nelson had helped me with a PowerShell script to download some videos, and I wanted to see if I could do something in R.

    I ended up building a short script that read in the data, cleaned it up, and exported a short R script that contained the session titles, authors, and the MP4 file. It took my only tens of minutes, and I was quite proud of it.

    Actually, I need to blog that script.

    In any case, when I sometimes face a task that is unfamiliar, I remember how I worked through the process and got things working. When I worry about a new technology, I remember how I accomplished this in R, which was new to me.

    It’s a delicious cookie, even today.

  • Enter our Database #DevOps Contest

    Share your story and you could win. Enter as often as you like before Mar 20.

    https://www.red-gate.com/hub/entrypage/competition

  • Database Brainstorming

    I read a post recently that captured my attention for more time than I expected. The piece fascinated me, and I sent it to my son and a couple friends. In fact, reading it caused me to stop working for a bit as I thought through some of the concepts and ideas. It’s about divergence and convergence, and how you can use these ideas when trying to be creative. I recommend you take a few minutes and go through it.

    Is this something we ought to do a bit in database design? Should we spend first some time on divergent, creative thinking for how to implement some new entity or design some schema? Do you ever do this in your job, especially in a team? Or do you just push forward with your first thoughts, relying on experience and instinct? That’s what I’ve often done.

    We certainly need convergence thinking. We have to get things done and deliver software. Most of us are probably good at the convergent side of thinking and writing code or queries or configuring systems. Most of us probably do some amount of divergent and convergent thinking at the same time.

    However, I’m not sure that’s the best way to tackle creative work, especially when building complex systems or working to solve new types of problems. There is some value in separating out the brainstorming portion from the actual, let’s write some code and see if it works. Getting a good list of potential ideas and spending a few minutes thinking about them before writing code helps us to avoid getting trapped pursuing into the first thing that works and possibly see other approaches.

    In today’s world of DevOps, trying to move faster and get small chunks of work to the customer quicker, we are under pressure to do more convergent work, quicker. If we want to ensure we do model our entities well and follow good database design practices, we need to spend some time with divergent thinking without doing work, perhaps speculating on potential future design challenges in advance. If we periodically spend this time, then we can rely on our previous brainstorming when it is time to converge and get things done.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and Libsyn.