Voice of the DBA

Tag: security

  • A Nice Refund

    Why don't these kinds of mistakes happen to me?

    This editorial was originally published on Sept 12, 2006.

    How’d you like to be the DBA for a system thatmistakenly gives out $318 million? It probably wasn’t the database, but more likely the code for some application that caused this to occur in the IRS system, but it’s something that becomes more likely in SQL Server all the time.

    SQL Server 2005 is the first time that SQL Server can be easily used as a complete application development platform. With the addition of the CLR, the Service Broker, Database Mail, web services, and more, SQL Server 2005 could be your database server, application server, and only server, with no other software running.

    While application developers will likely write most of the code, including lots of the CLR functions, procedures, and other constructs, who do you think will be responsible for the system? That’s right, the DBA. You’ll be the first and last line of defense and the one that will likely shoulder a lot of the blame for issues. Fair or not, I expect that DBAs will become more likely the scapegoats for issues.

    This means that as a DBA, you really want to beef up your skills as well as clearly delineate lines of responsibility. You should be able to read the code behind assemblies you add to your server, as well as understand how the SQL Server subsystems fit into an application. But you also need to be sure that you let management know when you are overloaded, when you can’t review code, or when you are concerned about possible issues with code.

    I’ve felt for years that it was a matter of time before some computer professionals will need to become bonded because of their responsibilities. I’m not sure when or where this will start happening, but I wouldn’t be surprised if it starts with DBAs.

  • SQL Authentication – Forcing Password Changes

    When you create a SQL Server login (with SQL authentication), you have the option of enforcing password policies from Windows (in SQL Server 2005 and above).

    chagepwd3

    The recommendation is that you check all three and force strong passwords. You also force a password change so the person has a private password not known by the administrator.

    If you go back into this account later, and look at the boxes, only 2 are available to be checked. The “User must change password at next login” is grated out.

    chagepwd1

    In order to access this box and force a password change, you need to change the password. The reason is that if the account is compromised, the hacker should not be the one to set a new password. The security model assumes the administrator can contact the legitimate owner offline and give them the new password.

    Start typing in the password box, and you can check the box:

    chagepwd2

    Of course you need to set a password that conforms to the policies, and it needs to match the confirm edit box Winking smile

  • Data Quality on the Open Web

    Input from customers like this must include data quality checks

    I used to hear that one of the strengths of Linux was the thousands of volunteers that would help you get a patch or a fix in record time when you reported an issue. That worked well, but not well enough for many companies that really wanted a company to stand behind patches. A few companies, like Red Hat, sold support agreements with the “free as in beer” OS that ended up costing companies almost as much as a regular license of another OS. While Linux is a great system, it hasn’t taken over the world like many people thought.

    Lately there’s been a different flavor of open-ness on the Internet. It seems that so much of what we read and is pushed out to us as news or information is based on the crowd-sourcing of what’s popular. Facebook shows a “most active” view, Twitter has trending topics and re-tweets, and many news sites like Reddit use a crowd voting mechanism to help determine what you see first on their front page.

    However there’s a downside to using these open systems. There’s the potential for abuse when a group of people get together. Google started using the open model on it’s map services to allow people to add businesses to maps. A very handy feature, butthe addition of a “mark this as closed” button allowed people to abuse this privilege. Whether it was competitors, vandals, or some criminal element isn’t known, but apparently the quality of data Google is providing on maps isn’t necessarily accurate. With many people using maps on iPhones and Android devices, this could damage businesses that add themselves to the mapping service. I think Google is playing a little fast and loose with their crowd voting on data points, but with so many companies looking to capitalize on the social networking phenomenon, I’m not surprised it’s being abused.

    Whenever we build systems that take input from users, we have a maxim: garbage in, garbage out. Essentially we aren’t responsible for bad data, but many companies won’t feel that way. They will still feel that we ought to be better policing the data quality and not showing bad data in reports or downstream systems. As more and more companies look to incorporate data from customers into their systems, it becomes more important that data professionals incorporate automated scans and manual workflow checks before data moves from staging areas to prevent incorrect data from affecting our production systems.

    Steve Jones

    The Voice of the DBA Podcasts

  • Map a Login – Basic Skill #3

    This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.

    I wrote about the basic security model for SQL Server, and got a question about mapping users to logins. It actually is done automatically for you in the dialog when you create a login, but I thought I’d cover the basic process here in more detail.

    Logins allow access to an instance of SQL Server. Users are the construct in a database that can be assigned permissions (explicitly or through a role). The mapping between a user and a login is what allows SQL Server to determine which logins get which rights.

    Let’s look at an example. On one of my instance, I have a test login called “JoeLogin”. If I connect to the instance, I enter the credentials of “JoeLogin” and the password to connect.

    loginmap

    Once I connect, however, the first thing the SQL Server database engine does is set my context to either the database I’ve specified, or my default database. This immediately maps me to a user in that database and allows me the connection to run commands, or it returns an error if I don’t have access.

    In this case, I have access to my default database, and I can check on my user credentials with this code:

    SELECT USER_NAME()

    This returns “JoeUser”, which is my user name. You can read about user_name() here, but it is a system function that returns your current database user name.

    My login has essentially been bypassed, and would only be used if I needed to check permissions to execute an instance level function, like setting a configuration value or viewing the error log. However I can check my login by using the SYSTEM_USER function. 

    SELECT SYSTEM_USER

    This will return my login name, and it returns “JoeLogin” on my instance. I can easily see this in SSMS, in the right corner of the status bar at the bottom of the window.

    loginmap2

    The Mapping

    We can view the mapping between users and logins in two ways. If you want to see where a user is mapped, you can right click the user and select properties.

    loginmap3

    This will bring up a dialog for the user, and at the top you can see the login mapped to this user:

    loginmap4

    If you want to see where a login is mapped, you can right click the login in the server level Security folder and select properties. This brings up the login dialog, and if you select the “User Mapping” item from the left pane, you will see the list of databases and the user mappings.

    loginmap5

    In this example, my JoeLogin has been mapped to two database. In db1, the default behavior is applied and the login is mapped to a user with the same name. In db4, I have changed the default and mapped to a user called “JoeUser”.

    I haven’t run across a good reason to change the user name from the login name, and I don’t recommend it, but if you think you might have some issues, this is how you check things.