Voice of the DBA

Tag: software development

  • Should we outsource identity management

    My Database Weekly editorial for Jan 30, 2012

    One of the first things many people build when they’re developing an application these days on the web is a login/identity system for their users. This allows them to identify people, customize the features and functions, and separate out the different classes of users from each other. However many developers don’t really understand good, secure design, much less encryption, and end up “storing passwords hair kari in unsecured databases”, a quote from this piece on password security.

    I’d hope that most developers know that passwords should be stored in a digest (one-way hash) form, but that’s like assuming most developers understand the issues with SQL Injection. It’s not true, and not necessarily going to be true until, well, it’s not likely to ever be true. Even if we had minimum standards, there are plenty of developers that would ignore them and plow forward with the arrogance that their custom method is better implemented, and more secure, than any standard.

    Passwords and password security are hard. I’d hope that most people would be looking to move to passphrases instead of passwords, but I don’t see the recommendations being widely disseminated in the world. I recently rebuilt a computer, requiring my kids to implement new credentials, and my recommendation was a simple phrase they can remember with a number. I was hoping it would serve the dual purpose of instilling good security habits as well as improving their typing skills. We’ll see if it helps.

    With large rainbow tables, creative social engineering, and poor application security, it’s even more important now that we use passphrases, and even develop better identity frameworks for applications. Personally I liked the Passport system Microsoft had, and I like the OAuth system even better. Perhaps we can get more of these frameworks implemented in reference applications and frameworks, as a default way of managing identity systems.

    That’s the easy part; convincing developers they can’t manage identity better is probably the hard part and I would love to see some good ideas for that.

    Steve Jones

  • A Quick Export with SQL Packager

    Disclosure: I work for Red Gate Software

    Someone asked me the other day if I’d ever used SQL Packager to export a table to send to another person. I hadn’t, and in fact hadn’t even ever run the tool, but this individual said it worked great.

    Since Red Gate tools are designed to be simple and intuitive, I thought I should give it a try and see what happens. I went through the Start Menu and found SQL Packager in my toolbelt installation:

    packager0

    I documented this as I went, shooting this images as I went through the process for the first time. As soon as Packager started, it began the packing wizard.

    packager1

    SQL Packager is designed to help you bundle up a database, or part of a database, as a part of an installation in your application. It can produce an .exe, a C# project, or a set of scripts that you can include as a distributable item in your application installation (or upgrade). The information can be compressed, so you reduce the requirements for your customers.

    In my case, I decided to just package up a table. I first signed into my local instance, and chose the AdventureWorks database.

    packager2

    Next, I chose just one table, the Customers table. The Red Gate tools tend to follow a similar, intuitive design, and try to do the most common things for most customers. In this case, the entire database was selected (this is a database packaging tool), so I deselected all, and then chose the Customer table.

    packager3

    Once I choose the table and click next, and confirm the selection, the packaging begins. I get some options as to how I might choose to build my package.

    packager6

    The options are shown, and in this case I choose to save the script. Once I clicked next, I had a change to see the final script. First there was the schema tab:

    packager4

    On this tab, all the DDL for my table is there, including a couple dependent tables, and some functions needed for defaults or computer columns. Keys and indexes were included.

    On the data tab, I had the DML for the actual data.

    packager5

    The comment says “Add 1000 rows”, which seems like a default. However I went back and checked in SSMS, and sure enough, my table had 1000 rows.

    I clicked next, and had the chance to specify a save location.

    packager7

    After saving, I opened the script in SSMS, just to check. Sure enough, the DDL was at the top:

    packager8

    and the data at the bottom:

    packager9

    Simple, and easy.

    If you are looking for a way to move certain sections of your database for a deployment, like all the lookup tables, give SQL Packager a try.

    If you need to send some stuff to a client or friend, it might be a simple way as well to export the DDL and DML into one package.

  • Jiggly Code

     

    jiggly, jiggly, jiggly

    This discussion is a little funny, an exchange on a Linux board in which Linus Torvalds writes: “This kind of “I broke things, so now I will jiggle things randomly until they unbreak” is not acceptable.”

    I’d hope that most people would not attempt to write code in this method, but I’ve seen many developers do just that. They start trying other solutions until one works, often implementing the solution without understanding how it works. The fact that it works, even in a limited way, is often good enough for most people.

    I like that the people in the discussion do not get too distracted and instead dive further into understanding the problem. Not enough people dig into the root cause analysis of issues to fully understand the issues. I know time is tight, and I’ve often been too busy to spend much time uncovering every issue. I also think that often subsequent issues, or chronic problems, are often the result of the lack of understanding of the root causes.

    The software we write gets more and more complex. We incorporate new technologies like virtualization, and some of us will even work with distributed applications that might make use of calls to other systems through messaging or even with communications with cloud databases. Learning to trace the data flows, troubleshoot communications,and thoroughly understand the mechanics of how our applications interact becomes more and more important. Especially as our applications seem to reach wider and wider client bases, with high expectations that things “just work”.

    Many people in our SQL Server community preach a continuous improvement of one’s skills and the goal of writing better and better code over time. It takes some effort, but we all ought to be putting forth that effort to improve regularly, at whatever rate we can sustain in our lives. As long as it’s some effort to get better each month.

    Steve Jones

    The Voice of the DBA Podcasts

  • Coding More Carefully

    TRS-80
    Compiling code on one of these was a chore.

    When I wrote computer code in school, programming as we kids called it, I could literally watch my programs compile on the screen. Working on a Radio Shack TRS-80 in PASCAL, we would see the lines of code scroll by, 3 or 4 a second, as they compiled into executable code. When that is the speed of the computer building your work, you learn very quickly to proofread your code from syntax or logic errors. A simple semicolon on the wrong place can result in quite a bit of lost time.

    At some point we transitioned to Turbo Pascal, which compiled hundreds of lines in a split second, on par with the speed of today’s compilers, which are often working through thousands of lines of code. Is that a good thing, however? Has this speed improvement ended up building poor habits in developers?

    I ran across this post on the times when the author had one compile a day. That’s slower than I had, but we did have time limits on the computer, as well as printer limits, when I was in college. The compile/fix a syntax error/recompile/test cycle  that we have now wasn’t an option. Making too many simple mistakes would result in missed assignments.

    Limiting developers to one compile a day wouldn’t work these days, but I wonder if limiting the number of compiles might result in developers spending a little more time thinking about their code, their logic, and writing applications with a little more care?

    Or would they follow the same process they follow now, only slower?

    Steve Jones

    The Voice of the DBA Podcasts