Voice of the DBA

Tag: security

  • 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.

  • Allowing a User to Create Objects in a Schema

    I was testing something the other day and realized this was a security area I didn’t completely understand. I decided to write a few posts to help me understand the issues.

    I want to give a developer rights to create objects in a schema. In this case, I’ll stick with procedures, but the same thing would apply for tables, views, etc. How do I do this, allow someone to create objects in their schema?

    Let’s create a login and user:

    CREATE LOGIN steve WITH PASSWORD = ‘AR3allyStr0ng!P@**Wo9d’;
    GO
    USE Sandbox
    GO
    CREATE USER Steve FOR LOGIN Steve
    GO
    Now I have a user, and want them to be able to create this:

    SETUSER ‘Steve’;

    CREATE PROCEDURE Steve.MyProc
    AS
        SELECT
                1;
    RETURN

    If the user does this, they get:

    Msg 262, Level 14, State 18, Procedure MyProc, Line 3
    CREATE PROCEDURE permission denied in database ‘sandbox’.

    That’s no good.

    We can see from the error that we don’t have writes to create procedures. Let’s fix that. First, we change our context and then we grant permissions.

    SETUSER
    GO

    GRANT CREATE PROCEDURE TO Steve;

    GO

    With this done, let’s now try creating the procedure again with the SETUSER statement and the CREATE PROC statement. We then get:

    Msg 2760, Level 16, State 1, Procedure MyProc, Line 5
    The specified schema name "Steve" either does not exist or you do not have permission to use it.

    This didn’t used to be the case in SQL 2000, where schemas didn’t exist. Now we don’t have any implicit schema for our user. Let’s see if we can make anything.

    CREATE PROCEDURE MyProc
    AS
    SELECT 1;
    RETURN
    GO

    Returns this:

    Msg 2760, Level 16, State 1, Procedure MyProc, Line 11
    The specified schema name "dbo" either does not exist or you do not have permission to use it.

    At this point Steve doesn’t have permissions to any schema. Let’s start by adding a new schema.

    CREATE SCHEMA Steve
    GO

    Once this is done, can I now create a procedure?

    SETUSER ‘Steve’;

    CREATE PROCEDURE Steve.MyProc
    AS
        SELECT
                1;
    RETURN

    I get this:

    Msg 2760, Level 16, State 1, Procedure MyProc, Line 5
    The specified schema name "Steve" either does not exist or you do not have permission to use it.

    The same error as before. This makes perfect sense because although the schema exists, I don’t have permissions to use it.

    That’s the default in SQL Server. You don’t get any permissions by default. You need to explicitly set them.

    In this case, I want Steve to have control of the schema [Steve], so I really want the user, Steve, to own it. How do I do this?

    The key is that I want to use the Authorization clause with CREATE SCHEMA. I can’t use this with ALTER SCHEMA, only with CREATE SCHEMA.. so I need to do this:

    SETUSER
    GO
    DROP SCHEMA Steve;
    GO
    CREATE SCHEMA Steve AUTHORIZATION Steve;
    GO

    Once this is done, I can now let my user create procedures.

    SETUSER ‘Steve’
    GO
    CREATE PROCEDURE Steve.MyProc
    AS
    SELECT 1;
    RETURN
    GO
    This works, and my developer can work in their own schema. Of course I need to ensure the developer has access to other objects, hopefully using a role of some sort that I’ve created for my application users.

     

    SELECT SUSER_NAME();

    DROP SCHEMA Bob
    DROP SCHEMA steve

    REVOKE CREATE SCHEMA FROM Steve

    CREATE SCHEMA Steve AUTHORIZATION Steve

    ALTER SCHEMA Steve AUTHORIZATION Steve

    SETUSER ‘Steve’;
    SELECT SUSER_NAME();

    CREATE PROCEDURE Steve.MyProc
    AS
        SELECT
                1;
    RETURN

    CREATE PROCEDURE MyProc2
    AS
        SELECT
                1;
    RETURN

    SETUSER;
    SELECT SUSER_NAME();

    GRANT CREATE PROCEDURE TO Steve

    SETUSER
    DROP PROC steve.MyProc;
    DROP PROC steve.MyProc2;
    DROP SCHEMA Steve;

  • Data Breach Danger

    Recently a court in the US ruled that there was no imminent danger from a data breach at a Texas hospital. This is good news and bad news for the world, and I’m a little torn about how I feel. On one hand, it’s good for us as data professionals that we aren’t necessarily going to be liable for the immediate effects from lost data. While the losses aren’t always our fault, we certainly could feel pressure from management if companies faced immediately legal or financial penalties.

    However it’s bad news because I think there’s little else that data breaches do than cause harm to those whose information is lost. It can be incredibly hard to link a specific breach to a specific identity theft incident, and I see this as a way of allowing companies to escape liability for their poor security practices.

    In reality, however, I have no solution to propose. As a data professional, I try to keep data safe, but it’s very, very difficult. One small hole in your technical infrastructure or human employees and you can lose a ton of data very, very quickly. I know it’s not lost, but copied, however you have lost control of it.

    We will face more and more security incidents, and as those tasked with protecting data, I’m not sure what we can do, or should do. However, I do think that organizations can’t take all responsibility, nor can they take no responsibility. The balance of how to deal with losses and issues is certainly something I hope we work out.

    Soon.

    Steve Jones