Data Management

Web application security frameworks, part 2: Database lookup

Often, you will want parts of your Web application to be exclusive to certain users. This access distinction requires the use of Web application security frameworks. Continuing our series on Web app security, we explore the database lookup framework.

In our last article, we introduced three distinct types of Web Application Security Frameworks. We briefly went over why they were needed and how they differed from each other. In the next few articles of the series, we'll go over each particular type of framework. This particular article will cover the Database Lookup framework. The following two will explore OS Level Authentication and Digital Certificates respectively.

The purpose of this article is to answer three main questions about the database lookup model:
  • What is it?
  • How does it work?
  • How is it implemented?

While no specific code will be given in the article, it is my hope that you will come away with a deep understanding of this particular security framework.

What is it?
The database lookup framework is a method for authenticating and authorizing a user by validating credentials against a given set of data. This methodology can, and often is, used by applications other than Web applications; however, for the purpose of this article, we'll strictly focus on the Web version of the security framework.

There are three main parts to this framework. The first part is the username/password combination. The second part is the storage container (i.e., the database). The last part is the code to utilize the information provided from the other two parts.

The username, or login, and password form can be found on almost any Web site. This is common on sites that tend to personalize content specifically for you. These sites usually let you pick your username and password so that you won't easily forget them.

Next up is the database. We'll reiterate a point from the previous article. We are not specifically referring to Relational Database Management Systems (RDBM), such as Microsoft's SQL Server or Oracle, even though that is what we discuss in this article. Whether it's a flat file or hard-coded values in the code, it doesn't matter as long as it's available to the code that is checking the credential. The database usually holds additional information about the users, which is then utilized by the Web application.

The last component is the code that controls the whole login process. Without this bit of code, all the data in the database would be useless and unnecessary. The code, while syntactically different from system to system, is based on a basic algorithm. The simple logic is laid out in the next section.

How does it work?
At the most basic level, this is what transpires in this particular framework model: You take a username/password combination and check it against data. If there's a match, you let them in to do things. If there is not a match, then you keep them locked out. You could leave it at just that, and it would equate to a fully working model.

The nice thing about this method is that you have little to no administration duties with the users of the site. Whether there are one or one million users, they will all use the same logic and the same code. Another reason why there is little administrative work is because, with this method, you usually let the users sign themselves up (pick their own username and password). Moving forward, you can provide an area where they can track and modify their personal data as needed.

The biggest concern about this method is how secure it is. That is probably its biggest drawback. To pretend that the inherent security is complete and unbreakable would be a disservice to yourself and to your users. Anyone with a script can easily try thousands of username and passwords in a matter of minutes. With a little patience, a working login will be found and the intruder will have access to your protected content as well as, possibly, access to private data, particular to the compromised user account. This fact is well known, however, and is the price one pays for this method's ease-of-use.

How is it implemented?
The primary method for implementing this security framework is the one that comes to everyone's mind when they hear the term "database lookup." You have your Web server running your application and taking requests from the Internet. This Web application in turn has a way to connect to an RDBMS server. The RDBMS has stored in its tables a wide variety of information about the users, including the username and password.

The Web site will send the username and, most likely, an encrypted version of the password. The database will look for a match on those two items. If it finds a match, the database usually passes back to the Web server not the username and password, but, rather, any matching, related information it has stored in its tables. The Web server then utilizes this information to present it to the user when needed. The Web server, whether through cookies or session-based IDs, has a way to track which users are sending which requests. Working with the database, the Web site then turns a stateless, anonymous visit into a stateful, personalized experience.

The implementation can vary in the type of systems used, but, no matter the hardware, OS, Web Server, or Database, it is all implemented in a way very similar to the one described above. Very small sites will implement the various pieces of software on the same machine. When your visitors are in the hundreds per month, the single server setup works just fine. However, when your visitors are in the hundreds of thousands per month, the single server is no longer an option. At that point, you'll have farms or dedicated Web servers at multiple physical locations with multiple clusters of database servers to house all the data.

Web ideal
The ease of use and light administrative requirements with database lookups make it ideal for the Web, where time is always of the essence. Its security drawbacks, however, limit its use to only non-critical systems or small scale applications. This works fine, though, for the majority of the Web sites around the world, which are non-critical (despite what IT Managers believe).

In the next article in the series, the second type of WASF will be discussed. You'll find out all about OS Level Authentication. You'll learn when you should use it and why. As you'll learn, its applications are limited, but, when the situation calls for it, its power becomes very apparent.

Editor's Picks