| only for RuBoard - do not distribute or recompile | |
Earlier in the chapter, we noted how it is the responsibility of individual applications to manage their own users. This user management is a small part of the larger problem of database application security. As an application architect, you are responsible for designing your application to protect its data and ensure that a compromise against it cannot impact other applications.
How you approach application security is heavily dependent on the architecture of your application. Chapter 8 goes into the details of database application architecture. For the purposes of this chapter, however, we will talk about two common components under many architectures: application servers and clients.
For the purposes of this chapter, an application server is any middleware through which clients go to get data. The application server can be as complex as an Enterprise JavaBeans application server or as simple as a web server pulling dynamic content from MySQL. The application server is generally the piece of your application that will perform user management. It is also the final arbiter of who can access what system resources available to the application.
User management is a very complex topic on its own. Not only does it cover how you store users, but it covers such topics as biometric authentication and digital certificates. All of that is well beyond the scope of a book on MySQL. We will take a look at simple user management as an example of how an application server can manage its users without making MySQL handle users in its security scheme.
To manage users, you need a place to store them. There are two common choices:
Some applications even store their user information in files on a server. The flat file approach, however, is very risky and hard to maintain.
A directory server is a database that stores its data hierarchically. Instead of structuring data in tables with foreign key relationships such as a relational database, a directory service stores data in a tree. This structure is useful for data that naturally fits into a hierarchical structure. User data often, but not always, fits well into this paradigm. Applications most commonly use LDAP-compliant directory services to store their user information.
If you have anything but the simplest user-management needs, you should probably use a directory service for user storage. This book, however, is about the relational database MySQL. We will therefore focus on MySQL-based user management.
As we covered earlier in this chapter, MySQL itself uses MySQL to store users. We will therefore use that as a model for simple user management. Specifically, in your application database, you should create a table to manage your user profile. It should contain at least the following information:
A user-friendly unique identifier is any information a user can provide you that will uniquely identify the user to MySQL. This unique identifier can be an email address or a handle. It should not be some hard-to-remember system identifier. You may want to add a unique system identifier to make for quick joins and to enable users to change their user-friendly identifiers.
In general, the authentication token will be a password. It could alternatively be a signed public key if you are using digital certificates. If you are using passwords, you must encrypt them. The PASSWORD( ) function provides a convenient way to encrypt passwords, but it is not terribly secure. It should be secure enough, however, if the table is readable only by your application server.
You will also want to store other basic profile information in this table such as the user's email address, password hints, etc. Unless you have a very well-defined set of resources and privileges as MySQL does, it is probably not a good idea to store permissions in this table. For complex permissions, you should create a separate table.
The first rule of the application server architect is always to assume that every client is malicious and out to cause trouble. This rule implies that you should:
Never trust that the client is who it claims to be. The application server should perform its own authentication.
Never send passwords to the client. Make the client send authentication information to you.
Verify all attempts by a client to access application server resources. Specifically, be sure that the client is accessing things that it is allowed to access.
Luckily, some languages—notably Java—provide security libraries that make it easier to do all these things robustly. You also need to protect the application server against direct compromise. In the previous section, we discussed two critical aspects of protecting against direct compromise: avoiding buffer overflow errors and preventing the arbitrary execution of code. Another related design choice is to avoid keeping passwords in memory on your application server.
If your application crashes and dumps its core, any data in memory is available to anyone who can read the core file. Furthermore, another way to exploit poor memory management is to gain read access to an application's memory. If you have a password stored in memory, your application is potentially vulnerable to such exploits.
The first way to avoid keeping passwords in memory is never to pull them from the database. The following code authenticates a user against an AppUser table without selecting the actual password:
SELECT userID FROM AppUser
WHERE name = 'tim' and password = PASSWORD('clientpass');
This query looks for a userID value from the AppUser table where the username and password equal values the client passed the application server. If the query returns a row, the password was valid. Otherwise, the client gave the application server a bogus password and should be denied access. In short, you are letting the database do the authentication for you.
In the above example, you unfortunately need to store the password the client gives you to read it from the network and to construct your query. You can mitigate this necessary evil through two simple steps:
Avoid copying the password among different storage buffers. Pass the password by reference instead.
Write junk into the password buffer when you are done with it.
These two rules of thumb are simple enough in languages such as C, in which you have direct control over your memory management. In languages such as Java, however, you have to pay more attention to how you handle password strings. Java sometimes implicitly copies String objects, and you cannot overwrite the data in String objects. To get around the special way in which Java and other languages deal with strings, you should instead handle passwords using character arrays. This approach is not generally problematic since you should never need to perform odd string operations on passwords.
Client applications (e.g., Swing GUIs, CGI forms, ASPs, and command-line tools) generally have no inherent access to different resources. In a multitier architecture, they gain access to resources through an application server. In a client/server architecture, they gain access through the database engine. Thus, the key to this security is to avoid compromising the user authentication credentials and to make sure there is a finite time of inactivity during which a client is considered authenticated by the application server or database.
To protect passwords, take the following steps:
Never store passwords in the MySQL client configuration files—or any other client configuration files.
Never echo users' passwords to the screen when they type them in. Many languages even provide a password-safe text box that enables you to avoid storing passwords in memory for too long, as we described earlier in the chapter.
| only for RuBoard - do not distribute or recompile | |