Voice of the DBA

Tag: security

  • Wow. Just Wow

    Wow. Just Wow.

    Yes, I meant to use a capital letter there. The Red Cross’ Blood Service in Australia had database backups on a website that anyone could access. That means that anyone could download the backup, which contained PII (Personally Identifiable Information) about donors. This was a mysql dump file, which is shockingly easy to restore, so anyone could have read the file inside of an hour.

    To be fair here, this wasn’t the Red Cross’ fault. A partner had put the file on a website, perhaps for developers or an analyst to download, but the web server had directory browsing available and was serving data on a public IP. That’s three major faults, and perhaps a fourth since the backup file wasn’t protected at all.

    How many of us have innocently taken a backup, put it in a location for someone else to download inside our company, and not thought anything of the action? If you have done this, would you know if the file were accessed and downloaded by the wrong person? In most cases we wouldn’t because the correct person might download the file as well and we wouldn’t think anything had ever gone wrong.

    That’s a problem with data. We don’t get necessarily notified if someone makes a copy. We certainly have no way of knowing if the backup files we create are ever restored by an unauthorized user, much less just copied. We often just assume that data at rest is just at rest and isn’t ever mishandled without our knowing about it.

    This is a good reason why we should be practicing security by default in most every case. All our backups should be protected at the very least with passwords. Even if everyone in the organization knows the password, some random person that might steal a laptop or browse an unsecured web server won’t know it. The password also shouldn’t be easily guessed, and if possible, the data should be encrypted. Always.

    What’s more, I would hope that we would use secure ways of moving information around with our fellow employees and partners. I know Dropbox is a very convenient and tempting mechanism to use, but is it really appropriate? It might be good enough if you’ve protected the data in other ways, but I’d be very, very wary of using any public service that doesn’t allow for some sort of security that can be tied to individual, authenticated users.

    No matter how you choose to protect and transfer database backups, I’d also be sure that you don’t leave the information there indefinitely. Have expiration times where files are removed. All encryption and protection can be cracked, given enough time and resources, so don’t leave your files there forever.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Creating a Logon Trigger–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    Suppose you want to audit logins for your SQL Server instance. There are multiple ways to do this, but Logon Triggers have a few advantages. First, they get data into a table that most of us are familiar with, SQL Server. Second, they guarantee that the event is captured on the instance if the trigger is enabled.

    There are plenty of other uses for these triggers, but beware that you can cause problems if your code doesn’t execute flawlessly.

    I’ll show you how to create a basic logon trigger here. Note, these are server level items, and you’ll need to be able to create the trigger in the master database.

    The structure of this code is similar to other triggers. We’ll use the CREATE TRIGGER DDL. Where this differs from DML triggers is that we use the ON ALL SERVER command. For auditing, I tend to set this as an EXECUTE AS ‘sa’, but you may choose a different type of account.

    Here’s my basic code:

    CREATE TRIGGER LogonTrigger
--ALTER TRIGGER LogonTrigger
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    INSERT DBAAdmin.dbo.LoginAuditing
    SELECT SYSDATETIMEOFFSET(),
        ORIGINAL_LOGIN(),
        HOST_NAME(),
        APP_NAME();
END;
GO
Note in this code  I’m specifying a specific table to store data in. This has to exist.

    I also use the FOR LOGON event. You can scope triggers for other events, at the server or database levels, and read more about DDL triggers in BOL.

    Once a user attempts to logon, the trigger fires and a simple insert takes place. If there are errors in inserting this data, the user may not be able to logon.

    There is a lot to write about logon triggers, but for this short piece, I’m just showing hot to get started. Please, please, please, be careful with these and read the documentation carefully. Be sure you understand how they work and how to disable them. If you implement one, test it extensively.

    SQLNewBlogger

    I had a small issue building a logon trigger, and thought I’d get a few posts written on the topic. This was 5 minutes work since I had the code and just wanted to describe things, but I’ve got a few more posts sketched on this topics  as I’ve learned more.

    Learning something, solving a problem, writing about it. This is a good way to show someone you are learning about a topic and developing some skill.

  • Who’s Touching My Database

    Abstract:

    As databases become more critical to the operation of our organizations, we are being asked to audit and report on access to both data and the configuration of our systems. SQL Server has a number of features that can help you comprehensively audit your system, including SQL Audit, Extended Events and the default trace. Come learn how you can configure and incorporate detailed auditing into your reporting to management.

    Level: 200 – Should have some basic knowledge of SQL Server, database security, and T-SQL.

    Demos:

    • Using Logon Triggers
    • Looking at the default trace
    • Using SQL Audit to track logins
    • Using Extended Events to track logins
    • Checking permissions at the server and database level
    • Watching database activity using DML triggers
    • Watching database activity using SQL Audit
    • Watching database activity using Extended Events

    Downloads:

  • Coming Attacks

    The pieces by Bruce Schneier related to security are fascinating. One of his latest posts looks at potential coming attacks to our Internet infrastructure, which could potentially take down parts of the worldwide network. Whether you think this is a valid concern or not, it bears thinking through the issue a bit. If something did happen, your organization could be affected.

    Imagine what would happen to your production sites if they, or their clients, couldn’t resolve a DNS address. Or if there was a DDOS against your domain, perhaps just as a test by attackers to see where you might be vulnerable. What would be your response? For most of us, there isn’t much we could do, but I’m sure your management would want some answer, so do you have a way to respond? Would you worry if there were a targeted attack against your database servers using SQL Injection, cross site scripting, or some other technique?

    What about your development efforts? So many people have started to use services like Slack, Trello, cloud hosting of repositories and more. Could you continue to develop software if the Internet went down for your company? I’ve certainly thought about this for my work, and some things would be off-line, and I could have potential scheduling issues. However, I don’t work on mission critical systems, so I could work on something off-line and push production schedules by a day or two.

    Certainly control systems, embedded systems, and more are vulnerable to these types of attacks. If they depend on data, could they be attacked with fake or compromised data? I hope that some of these companies that have critical system, get serious about security in the event that an attack is targeted at their infrastructure. I don’t know if that will happen, and I really hope not. The Internet is a wonderful, collaborative resource, and will be for a long time if the criminals don’t fundamentally ruin our trust in it.

    Steve Jones

    The Voice of the DBA Podcast

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