SQL Hacks

Chapter 11. Users and Administration

You may have one set of user accounts in your application, another set in your database system, and yet another set in your operating system. Sometimes it might be convenient to make some of these accounts identical in each system, and sometimes you might want different sets in different systems. There are a number of ways to support each approach. Several hacks in this chapter look at the creation of administrator user accounts, and how to audit database user activity.

This chapter also considers the problem of how to design an application for easy installation into a database system. In some systems, the person installing the application won’t have administrator rights, so your application has to be flexible enough to support different styles of installation.

Implement Application-Level Accounts

When you are managing user details in your own applications, you need to keep in mind ease of design and security.

You should never record important passwords in plain text in an SQL table. People who have operating-system-level access to your server may be able to access other people’s passwords. All of your backups include this sensitive information and anyone handling the backups could be tempted to look at the passwords. As well, if you copy backups over the network, anyone can sniff the traffic on the network and get the passwords, so operating-system-level access on the database server is not the only way to compromise your security.

Storing User-Specific ...