Voice of the DBA

Tag: security

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

  • Not on the Same Page

    Are DBAs not on the same page as security staff? DBAs don’t get security? That’s surprising to me, but it’s the first line of this piece on the gap between DBAs and security staffin many companies. Apparently according to a survey, most DBAs don’t apply security patches very often, don’t manage change control, and don’t have tight controls or an understanding of how to detect and respond to unauthorized changes to data.

    That might be the case in the Sybase world, or even the Oracle world, where it seems that I hear the DBAs do more management of corporate fiefdoms than database platforms. In the SQL Server world, however, we haven’t had too many security patches, and I know most DBAs are diligent about applying the service packs, if they’re allowed to by software vendors.

    I think that too often the technology workers that don’t work in security are lax about paying attention to strict security controls. They often tend to have a very loose change control process, and that’s not even counting the fact that the majority of developers and DBAs I know don’t bother to do any obfuscation of production data that is restored on development environments. Many of them don’t even think about implementing any type of security for dev instances at all, much less the same level of protection applied to production systems.

    It’s not all DBAs and developers, however. Most of the fault lies with management, in my opinion. Too often management wants to get work completed quickly, wants rapid changes to configurations in the hope that applications will run quicker, and doesn’t want any resources devoted to changing data in development environments. They often barely want to devote any resources to testing, and don’t understand the value of security for development systems.

    Security has rarely been well implemented by management in many industries and areas. Too often the lack of understanding of risk along with the impatience for the delays associated with security result in a halfhearted effort. I can only hope that at some point the ease with which digital assets can be exposed will force priorities to change and make security more of a requirement rather than an option.

    Steve Jones

    The Voice of the DBA Podcasts

  • A Matter of Life or Death

    In this book a data breach, identity theft, causes a death

    It sounds like something out of a fictional story, but could there be a data breach that might result in a person’s death? I read a book recently called Medical error in which a patient dies because of an allergy. The physician checked their records and noted the patient had been prescribed the drug earlier that year at the hospital, administered it, and the patient reacted and died. It turns out the patient was a victim of identity theft and the thief was the one that was seen at the hospital. The thief  didn’t have an allergy, but the victim did and died.

    According to this story, there hasn’t been a case of a data breach resulting in someone’s death, but the recent attacks against law enforcement agencies could change that. Informants and undercover detectives depend on their anonymity. A release of details from active cases could result in a death since the people that would be most interested in the information might be willing to kill those named.

    We seem to have no shortage of people that hack and attack systems for fun, often without much forethought about the potential effects of their exploits. As criminals get more sophisticated, I would expect more attacks to occur against law enforcement agencies. We’ve already had attacks that cross national borders, potentially sponsored by governments and I suspect we will see even more in the future.

    The data professionals working in the industries where life and death are dependent on technology should be worried. In law enforcement and medical fields, data professionals should be constantly educating themselves about security and working to regularly ensure their systems are protected, and audited . Auditing may even be more important in order to respond to breaches quickly.  It is asking a lot, but the potential results from a security lapse could haunt you for a long time if you haven’t done your best.

    Steve Jones

    The Voice of the DBA Podcasts