Voice of the DBA

Tag: security

  • 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 Data Incident

    I was reading a Google whitepaper and I think this was the first time I’d seen a potential loss of data referred to as a data incident. The paper deals with the response from the Google Cloud team if there are potential data loss issues, outlining a four part process for responding and ensuring the customer can get back to work. Azure has a similar plan, though AWS seems to expect the customer to do more of the work.

    This is the first public disclosure of a process, and it’s a good one. In fact, everyone should use this or have something similar in their organization. The way that the world seems to be moving, it’s likely that most of us will suffer a breach at some point, and we ought to know how to respond. Without some plan, it’s easy to have a situation spiral out of control.

    I’ve had formalized DR plans as well as incident responses for events such as a DDOS or virus attack, but the issues with data are somewhat different and probably deserve a dedicated plan of some sort. While I’ve often seen these handled under the security team response, that might not be enough for data services, especially when you have responsibility for data that is different than managing a software system or service.

    Data security is becoming a more important part of the data professional’s job, with legislation and best practices starting to require that we recognize a shared interest in sensitive data that extends beyond our organization. Whether this is something you like or dislike, it is becoming a part of our work.

    Personally I like having a crafted plan that is available for use in crisis situations. It can be hard to remember all the possible issues, and certainly crisis seem to occur when our best people aren’t available. Even more important, a plan means we have something we can practice to be sure that everyone understands how to react, limit the exposure of data, and even ensure that we can get services back up and running as soon as possible.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Schema Security

    How many of you grant access to schemas in your databases? I’m curious because I slowly am starting to see more and more people using schemas in their designs and not defaulting every object to “dbo”. That was the default action for many developers for years, and it’s still a habit of mine. Without SQL Prompt to add the schema to my queries, I’d still be producing code that followed that practice.

    Schemas exist to allow some separation of different types of objects. In early versions of SQL Server, we didn’t have schemas, but considered the third of four part naming to be the owner. In other RDBMSes, the use of a schema was to allow grouping and separation of different objects. This was also, and still is, a security boundary that helps ensure that authorized users can be easily limited to data access for certain objects.

    This is also a nice organizational boundary for related objects that have the same schema, or even name. I have seen auditing or etl schemas that maintain the same named table that exists in a dbo schema, but is separated by placing this in a separate schema. I have even seen some archiving schemas that move data to a related table that exists in a different schema, making it easy for new administrators and developers to find (or remove) older data.

    This week, let us know if you manage objects and security with schemas or if you stick to granting access at a database/object level the two more common ways of working with databases. If you have never used schemas and stuck with the defaults, perhaps you want to learn a bit more about how schemas work and the ways in which they may make your work easier.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Employee Target

    We hear regularly about consumer backlash from data breaches. There are usually legal repercussions, and often hard dollars (euros, yen, etc.) being spent on things like identity protection or credit monitoring. In addition, in the last couple of years, there are plenty of people that try to limit, or cease, doing business with organizations that lose their data. That isn’t always possible, especially when the government is the one getting hacked, but more and more people are taking an active stance against poor internet security by organizations.

    It’s not anything that I think will happen soon, but I bet it will happen. Airbus had a breach that lost employee data. I wonder when we’ll start to see employees initiating lawsuits or other actions against their employers. My suspicion is that this will happen with former employees first, but with the GDPR, current employees in the EU might feel emboldened, and with good reason. Employers have a lot of data about us, and that ought to be well protected.

    There have been hacks to lower a stock price, or affect a company. Why not hacks to attack employees? I wouldn’t have thought of this until I talked with a forensic analyst about other possible second order attacks. Could there be individuals that might seek to attack IT staff through personal information and blackmail or otherwise extort them to copy data? What about partners and spouses of employees at big companies? I don’t worry about Redgate as a target, but what if you work for Google/Apple/Microsoft/Facebook? We already had a potential sleeper employee at Twitter.

    Unfortunately, I see no depths to which criminals might sink. Across the last decade there have been stories of actions that I never would have contemplated. While I hope my fears are unfounded, I worry that sensitive data about employees might be on the radar for some nefarious individuals.

    Steve Jones

    The Voice of the DBA Podcast

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