Voice of the DBA

Tag: security

  • Security Through Chaos

    This editorial was originally published on June 16, 2005. It is being re-run as Steve is away at training.

    I can’t really disclose who told me this, or at which company, but I found it very interesting. Recently there was a worm that rolled through a large number of Windows systems on the Internet. A few friends told me about it since it had rippled fairly quickly through their companies. These were all large organizations, with over 2,000 people employed in each of them. However, one company had almost no infections. The spread between their internal systems was almost non-existant.

    Now I’m sure that you are all wondering what great technique they used so you can deploy it in your environment. I was as well, until I heard the details. I dismissed it at first, but then thought it did make some sense. I’m not sure I’d recommend the technique, but it was interesting.

    Their defense was chaos. They don’t really have a central IT organization, standards are almost non-existent, no central AD setup, not even a standard platform. They do make anti-virus, firewalls, etc. available, but it is up to individual departments, people, and labs/data center areas to deploy them as they see fit. Need a resource from another group? Better start making friends. Want to breach a firewall? I’d recommend buying a Starbucks card or a 6 pack of Red Bull for the admin of that firewall.

    Now this is a technology company and most of the employees are fairly smart technologists. They are each responsible for the most part for their systems. If they break it, they need to fix it or find someone to help because a broken computer is not an excuse for work not being complete. But an individual can have a Mac, PC, Sparc, run Windows, Linux, whatever, as long they get their job done.

    Samba is in use as a file system in many places, but permissions control is distributed. Sarbanes-Oxley, secuirty, auditing, etc. all still apply, but there is no central group that ensures it’s performed in a consistent manner.

    As I mentioned, at first I was shocked. I thought this was ridiculous. But the more I thought about it, the more I realized that it made some sense. You couldn’t easily break into their network because what worked in one place wouldn’t necessarily work in another. Compromise one password and you might not get very far at all, even if you had an administrator password.

    I’m still not completely sure what to think of this, but apparently it works. Probably just like my life with three kids and a wife that works hard. You juggle all the balls and hope none of them drops.

    Steve Jones

  • The Opportunistic Hacker

    You’re sitting in Starbucks, working. You need to use the restroom and you ask the hip, cool looking person sitting coding next to you, with whom you’ve chatted with for the last hour about C# and SQL, to watch your laptop. You leave and come back to an empty table.

    This was a hacker, coding away at their side job to pay a bills in between causing mayhem. They walked away with your laptop, not knowing if you work for a bank or a soy milk manufacturer. The hacker won’t know if you have a copy of the production database on your laptop or simple a few .ASPX files that are useless without a VPN connection to your office. Hopefully that can’t access other machines from yours.

    This is a crime of opportunity, and it happens regularly. Criminals steal laptops from coffee shops, airports, cars, and more. Often just because they can. Some may just wipe the device and sell it, but others may spend some time poking around to see if there is any value in the data on the machine. Or on a connected machine.

    Many of us have far too much data on our machines, with many, many saved passwords and connections. We have copies of code, databases, or backups, and losing our machine to a random criminal might be just as damaging to our organization as losing it to a hacker that targets us.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Source Code Security

    I’m not surprised, and I had expected to read about something like this much sooner. Apparently someone at the ride sharing company, Uber, posted a security key online in a GitHub distribution. I assume this was some sort of code repository for Uber that may or may not have been supposed to be shared publicly, but having used GitHub, I could see someone making a mistake and accidentally putting private code in the public space.

    Uber is worried as the key is a security authorization key used to access their databases. Someone apparently downloaded Uber database files and now Uber is attempting to track them down. The whole situation is a mess, but there are a number of problems here that we could learn from.

    I give a talk on encryption options in SQL Server and one of the main problems I see with symmetric keys in SQL Server is that they can be reproduced with the same parameters passed to CREATE SYMMETRIC KEY. This means that your source code is now a security hole, at least if the production values are stored in a VCS.

    However that’s a no-no. Developers shouldn’t have access to account information or keys that are used in production. There should be separate credentials used in development, precisely for this reason. If someone gets your code, or hacks a dev machine, they shouldn’t be able to jump to production.

    There’s also the issue of using a service like GitHub, or any online VCS. The distinction between private code and public code shouldn’t count on a human checking or un-checking the right box. I’d like to see some better separation, perhaps requiring separate accounts and connections for public and private distributions.

    As we see here, convenience can easily result in poor security. That’s not a trade-off we should be making with our security and applications. Especially not for software developers.

    Steve Jones

    The Voice of the DBA Podcast

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

  • ALTER SCHEMA TO ADD PERMISSIONS

    I’m sure some of you have wanted to do this:

    ALTER SCHEMA Steve AUTHORIZATION Steve

    You realize this doesn’t work, and you can’t grant the user Steve, rights to his schema after it’s created. You can do this:

    CREATE SCHEMA Steve Authorization Steve

    UPDATE: Someone pointed out this works after the fact:

    ALTER AUTHORIZATION ON SCHEMA::Steve TO Steve

    But not alter it. Strange and annoying. In my last post, I showed dropping and recreating the schema. That works well if you are beginning development, but not when you’re in the middle.

    Let’s make this less confusing and see how we actually allow a developer to access a schema to create procedures (or other objects) when the schema exists.

    First, let’s assume we want a developer, Steve, to be able to create procedures in the ETL schema. We have these conditions:

    • The ETL schema exists
    • The ETL schema is owned by another developer.
    • The login and user, Steve, exists in this database with no permissions.

    I want to now allow Steve to build the procedure ETL.MyProc.

    Grant Permissions

    The first thing I do is grant create procedure permissions to Steve.

    CREATE LOGIN steve WITH PASSWORD = ‘Test’;
    GO
    USE Sandbox
    GO
    CREATE USER Steve FOR LOGIN Steve
    GO
    GRANT CREATE PROCEDURE to Steve;

    GO

    With this done, now let’s set up our schema.

    CREATE SCHEMA ETL
    GO
    There are no default permissions, so the user Steve cannot create ETL.MyProc right now. How do we fix this?

    The trick here is that I need to allow Steve to ALTER the schema. I can do this by using this statement.

    GRANT ALTER ON SCHEMA::ETL TO Steve;
    GO

    I could do other things. I could grant CONTROL. to Steve instead, but I might not want to do that. That gives Steve the ability to actually drop the schema, which probably isn’t want. It’s certainly not the “least permissions” to let the developer create objects in a schema.