29
Database Security
Like database maintenance, database security is an important topic with details that vary from database to database. This chapter doesn't try to cover everything there is to know about database security. Instead, it explains some of the general concepts that you should understand.
In this chapter, you learn how to:
- Pick a reasonable level of security for the database.
- Choose good passwords.
- Give users necessary privileges.
- Promote a database's physical security.
THE RIGHT LEVEL OF SECURITY
Database security can range from nonexistent to tighter than Fort Knox. You can allow any user or application to connect to the database, or you can use encryption to prevent even the database itself from looking at data that it shouldn't see.
Though many people think more security is better, that's not always the case. Some databases can encrypt the data they contain, so it's very hard for bad guys to peek at your data. Unfortunately, it takes extra time to encrypt and decrypt data as you read and write it in the database, and that slows things down. For most applications, that level of security is overkill.
Although you may not need as much security as the White House, Bank of America, or the Tokyo Stock Exchange, it does make sense to take advantage of whatever security features your database does provide. The following sections describe some of the security features that you should look for in a database product.
Rather than getting the most powerful security system money can buy, you should consider the needs of your application and the security features that are available. Then you can decide how tightly to lock things down.
PASSWORDS
Passwords are the most obvious form of security in most applications. Different databases handle passwords differently and with different levels of safety. The following sections describe some of the password issues that you should consider when you build a database application.
Single-Password Databases
Different databases provide different kinds of password protection. At the weaker end of the spectrum, some databases provide only a single password for the entire database. A database may be protected by a password or not, but those are your only options.
The single password provides access to the entire database. That means a cyber villain who learns the password can get into the database and do pretty much anything. It also means that anyone who should use the database must share that password. One consequence of that is that you cannot easily tell which user makes which changes to the data.
In practice that often means the program that provides a user interface to the database knows the password, and then it may provide its own extra layer of password protection. For example, the application might store usernames and passwords (hopefully encrypted, not in their plain-text form) in a table. When the user runs the program, it uses its hard-coded password to open the database and verifies the user's name and password in the table. It then decides whether to allow the user in (and decides what privileges the user deserves) or whether it should display a nasty message, shut itself down, send threatening email to the user's boss, and so forth.
There are a couple of reasons why this is a weak approach. First, the program must contain the database password in some form, so it can open the database. Even if you encrypt the password within the code, a determined hacker will be able to get it back out. At worst, a tenacious bit-monkey could examine the program's memory while it was executing and figure out what password the database used.
A second reason why this approach can be risky is that it relies on the correctness of the user interface. Every nontrivial program contains bugs, so there's a chance that users will find some way to bypass the homemade security system and sneak in somewhere they shouldn't be.
Individual Passwords
More sophisticated databases give each user a separate password, and that has several advantages over a single password database.
If the database logs activity, you can tell who logged into the database and when. If there are problems, the log may help you narrow down who caused the problem. If the database logs every interaction with the database (or if your application does), you can tell exactly who messed up.
Another advantage to individual passwords is that the user interface program doesn't ever need to store a password. When the program starts, the user enters a username and password, and the program tries to use them to open the database. The database either opens or not and the program doesn't need to worry about why. Even a “seriously dope uberhacker with mad ninja skillz” can't dig a password out of the application if the password isn't there.
Because the database takes care of password validation, you can focus on what the program is supposed to help the users do instead of worrying about whether you made a mistake in the password validation code.
If your database allows individual user passwords, use them. They provide a lot of benefits with relatively little extra work on your part.
Operating System Passwords
Some databases don't manage passwords very well. They may use little or no encryption, may not enforce any password standards (allowing weak passwords such as “12345” and “password”), and may even write passwords into log files where a hacker can find them relatively easily.
If your database can integrate its own security with the security provided by the operating system, make it do so. In any case, take advantage of the operating system's security. Make sure users pick good operating system passwords and don't share them. Hackers won't get a chance to attack your database if they can't even log in to the operating system.
Good Passwords
Picking good passwords is something of an art. You need to pick something obscure enough that an evil hacker (or your prankster coworkers) can't guess but that's also easy enough for you to remember. It's easy to become overloaded when you're expected to remember the database password in addition to your computer username and password, bank PIN number, voicemail password, online banking password, PayPal password, eBay password, locker combination, anniversary, and children's names.
And, you don't want to use the same password for all of these because then if someone ever steals your gym membership password, they know all of your passwords.
Many companies have policies that require you to use certain characters in your password (must include letters, numbers, and a special character such as $ or #, and you need to type every other character with your left hand and your eyes crossed). They also force you to change your password so often it's pretty much guaranteed that you'll forget it. (I've never quite understood that. Do they assume that a hacker will guess your password and then say, “Whew! That was hard. I think I'll wait a month or two before I take advantage of this password and trash the database?” Okay, I know they're really worried about someone just prowling through the database unnoticed and they want to change the password to shut them out as quickly as possible, but I'm not sure which is more common, an eavesdropper or someone who wreaks havoc as soon as they break in.)
So what do users do when faced with dozens of passwords that must pass complex checks? They write their passwords down where they are easy to find. They pick sequential passwords such as Secret1, Secret2, and so forth. They use names and dates that are easy to remember and guess. (Once as a security check I attacked our own password database to see how many passwords I could guess. By throwing names, dates, and common words at the database, I was able to guess more than half of the 300 or so passwords in just a few hours.)
It's much better to give the users a little extra training, so they can figure out how to pick a really good password and then not require changes so often. For example, a series of unrelated words is a lot better than a single word but is usually just as memorable. The password beeR&Pizza%suckS is pretty easy to remember, tricky to guess, and what self-respecting hacker would ever want to type that? Replacing letters in the password with other symbols can further obscure the message. Replacing “z” with “2” and “e” with “3” turns this password into b33R&Pi22a%suckS. (Search online for “leet” to learn about a hacker wannabe language that uses this kind of substitution to make plain and simple text practically unintelligible. Or look at congressional legislation or a legal contract for some serious incomprehensibility.)
A technique that is particularly useful for touch typists is to shift your fingers before typing. For example, if you type “Potatoe” (with the “optional” extra “e”) with your fingers shifted one key to the right you get “[pysypr” on a standard qwerty keyboard. Combine a few of these tricks and you can build a fairly tough password that's still reasonably easy to remember.
There are a few “don'ts” when it comes to making good passwords. Don't use names, dates, places, ID numbers (such as Social Security numbers or driver's licenses), or anything else that would be easy to guess or figure out by rummaging through your email or trash. In fact, don't use words at all, unless you do something to obscure them such as replacing letters with other symbols or keyboard shifting. A few words together, even if they're logically incompatible (such as “Politician” and “Honest” or “Inexpensive” and “Plumber”) are easy to guess. Remember that modern computers are really fast, so guessing a few million or even a few billion password combinations is child's play.
PRIVILEGES
Most relational databases allow you to restrict each user's access to specific tables, views, and even columns within a table. Typically, you would define groups such as Clerks or Managers, and then grant permission for users in those groups to view certain data. You may also be able to grant exceptions for individual users. (You can perform similar feats of cleverness yourself in your application even if you're using a single password database, but it's a lot more work.)
For example, suppose your medical database contains three levels of patient data. Data such as patient name, phone number, and appointment schedule would be available to almost everyone in the hospital. A summer intern could use that data to make and confirm appointments.
Medical history, appointment notes, prescriptions, and test results would be available only to medical staff such as nurses, physician assistants, doctors, and specialists like x-ray technicians.
Insurance and billing information would be available to the billing department and the person who takes your $150 copay when you enter the lobby.
You could get into serious trouble if some of that data were to slip out to hackers or the wrong users.
If you use the database's security features to prevent certain users from viewing sensitive data, you don't need to worry about the wrong people seeing the wrong data. If Ann is an appointment specialist, she will be able to view Bob's phone number so she can call him but the database won't let her view Bob's medical history.
Some databases also provide row-level security that allows you to restrict access to particular rows in a table. For example, suppose a table contains government documents that are labeled with one of the security levels Public, Secret, Top Secret, and Radioactive (you get thrown in jail if anyone finds those). When a program queries this table, it compares the user's privileges with the records' security labels and returns only those that the user should be able to see.
Other databases provide access control at a less-refined level. They may let you restrict access to a table but not to particular columns or rows within a table. Fortunately, you can provide similar behavior by using views.
A view is the result of a query. It looks a lot like a table, but it may contain only some of the columns or records in one or more tables. If the database doesn't provide column-level security, you can deny access to the table, and then create different views for the different groups of users. For the Patients table, you would create separate views that include contact data, medical data, and billing data. Now you can grant access for the views to let users see the types of data they should be able to view.
The SQL GRANT and REVOKE statements let you give and withdraw privileges. It is generally safest to give users the fewest privileges possible to do their jobs. Then if the user interface contains a bug and tries to do something stupid, such as dropping a table or showing the user sensitive information, the database won't allow it.
Rather than remembering to remove every extraneous privilege from a new user, many database administrators revoke all privileges, and then explicitly grant those that are needed. That way the administrator cannot forget to remove some critical privilege like DROP TABLE or LAUNCH NUCLEAR MISSILES.
The following three MySQL scripts demonstrate user privileges. You can execute the first and third scripts in the MySQL Command Line Client. You need to start the Command Line Client in a special way (described shortly) to use the second script properly.
The following script prepares a test database for use:
CREATE DATABASE UserDb;USE UserDb;-- Create a table.CREATE TABLE People (FirstName VARCHAR(5) NOT NULL,LastName VARCHAR(40) NOT NULL,Salary DECIMAL(10,2) NULL,PRIMARY KEY (LastName, FirstName));-- Create a new user with an initial password.-- Note that this password may appear in the logs.CREATE USER Rod IDENTIFIED BY 'secret';-- Revoke all privileges for the user.REVOKE ALL PRIVILEGES, GRANT OPTION FROM Rod;-- Grant privileges that the user really needs.--GRANT INSERT ON UserDb.People TO Rod;GRANT INSERT (FirstName, LastName, Salary) ON UserDb.People TO Rod;GRANT SELECT (FirstName, LastName) ON UserDb.People TO Rod;GRANT DELETE ON UserDb.People TO Rod;
This script creates the database UserDB and gives it a People table. It then creates a user named Rod, giving it the password “secret.” (Yes, that is a terrible password. Don't do something like this in your database!)
Next, the script drops all privileges, including the GRANT privilege (which would allow users to grant privileges to themselves). It then grants privileges that allow the user to insert FirstName, LastName, and Salary values into the People table, select only the FirstName and LastName values, and delete records from the table.
Before you can execute the next script, you need to start the MySQL Command Line Client as the user Rod. To do that, start a command window (in Windows, open the Start menu, type Command Prompt, and press Enter). At the command prompt, change to the directory that contains the MySQL Command Line Client, mysql.exe. After you move to that directory, start the MySQL Command Line Client by executing this command:
mysql -u Rod -pYou might need to use mysql --u Rod --p on Linux systems.
Note that the username is case-sensitive, so type Rod, not rod or ROD. When prompted, enter the password secret. The Command Line Client should run in the operating system command window and you should see the mysql prompt.
Now, you can execute the following script to test the user's privileges:
USE UserDB;-- Make some records.INSERT INTO People VALUES('Annie', 'Lennox', 50000);INSERT INTO People VALUES('Where', 'Waldo', 60000);INSERT INTO People VALUES('Frank', 'Stein', 70000);-- Select the records.-- This fails because we don't have SELECT privilege on the Salary column.SELECT * FROM People ORDER BY FirstName, LastName;-- Select the records.-- This works because we have SELECT privilege on FirstName and LastName.SELECT FirstName, LastName FROM People ORDER BY FirstName, LastName;-- Create a new table.-- This fails because we don't have CREATE TABLE privileges.CREATE TABLE MorePeople (FirstName VARCHAR(5) NOT NULL,LastName VARCHAR(40) NOT NULL,PRIMARY KEY (LastName, FirstName));-- Delete the records.DELETE FROM People;
This script sets UserDB as the default database and inserts some records into the People table. This works because the user Rod has privileges to insert FirstName, LastName, and Salary values into this table.
Next, the script tries to select all the fields in this table. That operation fails because Rod doesn't have the privilege to select the Salary field. Even if the user-interface application managing this database contains a bug and tries to select salary data, the database won't let the user see the Salary field.
The script then tries to select the FirstName and LastName values from the People table. That works because Rod does have privileges to select those fields.
Next, the script tries to create a table and fails because Rod doesn't have that privilege.
Finally, the script deletes all the records from the table. That works because Rod has that privilege.
After you test the user's privileges, you can close the Command Line Client by entering the command exit. You can then close the operating system window by typing exit again.
Back in the original MySQL Command Line Client that created the database and the user, you can execute the third script to clean up:
DROP USER Rod;DROP DATABASE UserDb;
The technique of removing all privileges and then granting only those that are absolutely necessary is very useful for preventing mistakes. In fact, many database administrators deny even the administrator accounts all of the dangerous privileges that they don't need on a daily basis (such as DROP TABLE and CREATE USER). The account still has the GRANT privilege so it can grant itself more power if necessary, but that takes an extra step so it's harder to accidentally make dangerous mistakes such as dropping critical tables.
A similar technique is for administrators to log in as a less powerful “mortal” user normally and only log into an administrator account when they really need to do something special and potentially dangerous.
INITIAL CONFIGURATION AND PRIVILEGES
Databases (and many other software tools) often come preconfigured to make it easy for you to get started. Find out how the database is initially configured and modify the default settings to make the database more secure.
For example, databases often come with an administrator account that has a default username and password. It is amazing how many people build a database and don't bother changing those default settings. Anyone who knows the defaults cannot only open your database but can do so with administrator privileges, so they can do anything they want to your data. Hackers are very aware of these default accounts, and not surprisingly, trying to open those accounts is often the first attack a hacker tries.
TOO MUCH SECURITY
Ironically, one of the most common security problems I've seen in large applications is caused by too much security. The user interface application tries to restrict users, so they cannot do things they're not supposed to do accidentally or otherwise. When it's done properly, that type of checking is quite important, but if the system is too restrictive and too hard to change, the users will find ways to circumvent your security.
For example, suppose an application manages telephone accounts. Customer representatives can disconnect a customer for outstanding bills, answer customer questions, and reconnect service when payments are received. They can also reconnect service if the customer comes up with a really good sob story. (“My doggy Mr. Tiddles ate the bill. I sent it in anyway half chewed up, but the Post Office returned it for insufficient postage. It would have been a day late, but I was on a cruise and the ship crossed the International Date Line. I can pay now but it would be in a third-party check written in Florins from a bank in a country that no longer exists, etc.”) At this point, the representative hands the customer to a shift supervisor who reconnects services for 15 days in self-defense just to shut the customer up.
Unfortunately, a lot of customers have sob stories that are more believable than this one (it's hard to imagine one less believable), so the shift supervisors waste a lot of time approving service reconnections. To save time, the supervisor writes their user ID and password in huge letters on the whiteboard at the front of the room so every representative can approve reconnections without interrupting the supervisor's online shopping.
Your expression of amusement should change to one of horror when you learn that this is actually a true story. (Not the one about Mr. Tiddles, the one about the password on the whiteboard.) I once visited a telephone billing center where anyone could log on as a supervisor at any time to approve special actions without wasting the supervisor's time.
At this point, a reasonable security feature, making supervisors approve special reconnections, has completely backfired. Not only can anyone approve special reconnections, but they could log on as a supervisor and perform all sorts of other unauthorized actions without leaving any trace of who actually did them. Fortunately, physical security at that center was tight, so a hacker couldn't just look in a window and grab the supervisor's password.
The moral is, restrict access to various database features appropriately, but make it easy for the customers to change the settings. If the supervisors could have changed the program to allow representatives to approve special reconnections, this would never have been a problem.
PHYSICAL SECURITY
Many system administrators spend a great deal of effort on software and network security and do nothing about physical security. It makes sense to focus on network attacks because an open Internet connection makes you potentially vulnerable to millions of would-be hackers and cybersnoops from around the globe.
However, focusing exclusively on software security and ignoring physical security is like installing a cat door on Fort Knox. While an unsecured Internet connection does expose you to a huge number of potential hackers, you shouldn't completely ignore local villains.
Though most employees are honest and hardworking, there have been several spectacular cases where employees have stolen data. There have also been many cases where employees and contractors have lost data through carelessness.
In one case, a former Boeing employee was accused of stealing 320,000 files using a flash drive. Boeing estimated that the files could cause $5 billion to $15 billion in damages if they fell into the wrong hands. (See https://networkworld.com/article/2292651/ex-boeing-worker-accused-of-stealing-documents.html.)
I generally prefer to assume that people are basically honest, but that doesn't mean you should make it easier for them to make bad decisions and silly mistakes.
For about $30, you can put a 1 TB flash drive in your wallet or a 256 GB micro SD card in your phone. For a bit more, you can fit a few terabytes in your phone.
I'm not suggesting that you frisk employees before they leave for home, but if your database contains financial data, credit card numbers, and other proprietary secrets (such as numerological algorithms for picking lottery numbers), you should at least provide some supervision to discourage employees from walking out with the database.
Many powerful computers are also relatively small so, in some cases, it may be possible for someone to simply pick up your server and walk away with it. If the computer is too large to carry away, a few minutes with a screwdriver will allow just about anyone to remove the hard drive. Keeping your database server in a locked office that's accessible by an internal network provides some extra security.
Even if you lock the network down so cyber villains can't find a seam to open, you should also consider outgoing connections. An employee can probably email data outside of your system or surf to a website that allows file uploading.
Laptop security is a particularly tricky issue lately. Laptops are designed for portability. If you didn't need that portability, you would probably buy a less expensive desktop computer, so you must assume the laptop will go off-site. Laptop theft is a huge and growing problem, so you should assume that any data you have on your laptop may be stolen. If you absolutely must store sensitive data on a laptop, encrypt it. Don't assume the laptop's operating system security will stop a thief from reading the hard disk. The web contains lots of sites with advice for preventing laptop theft, so look around and adopt whatever measures you can.
I once worked at a company that didn't allow cameras or cell phones with cameras because they were afraid someone might steal their corporate secrets (not that we had any worth stealing). However, they didn't prohibit flash drives, USB drives, laptops, MP3 players (which have drives that can hold computer files—see https://en.wikipedia.org/wiki/Pod_slurping), outgoing email, or web surfing to sites where you could upload files. They had plugged one possible channel for misdeeds but had left many others open. (My theory is that management was a bit behind the times and wasn't familiar enough with the other methods to realize that they were a potential problem.)
This all begs the question of whether the company has any data worth stealing. In the time I worked there, I saw lots of company confidential material but nothing that had any real financial or strategic value. Before you start installing security cameras and metal detectors, you should ask yourself how likely it is that someone would want to steal your data and how expensive the loss would be. Then you can take appropriate measures to reduce the likelihood of loss.
Though ignoring physical security is a mistake, obsessing over it can make you paranoid. Not everyone is an undercover agent for your competition or looking to sell credit card numbers to drug dealers. Take reasonable measures but try not to go unnecessarily overboard.
SUMMARY
Database security doesn't happen all by itself. Many databases provide sophisticated security features, but it's up to you to take advantage of them. This chapter described some of the issues you should consider to protect your data against accidental and malicious damage. It explained how to:
- Decide on a reasonable level of security for the database.
- Restrict privileges so users cannot harm the database accidentally or intentionally.
- Protect the database physically.
The chapters in this book explain how to determine customers' data needs. They explain how to build data models to study those needs, how to use the models to form a design, and how to refine the design to make it more efficient. Finally, the chapters explain how to implement the database, and how to examine the database's maintenance and security needs.
Having studied these chapters, you are ready to design and build effective databases, but there's a lot more to learn. Although I've tried to cover the most important topics of database design in some depth, database design is a huge topic, and you can always learn more. You may want to increase your knowledge by surfing the web or reading other database books that focus on different aspects of database design and development. In particular, you may want to seek out books that deal with specific issues for whichever database product you are using. You may also want to sign up for free trials of a few database products. The ever-growing cloud has made it extremely easy to try different products to see what they have to offer.
Before you leave these pages, however, use the following exercises to test your understanding of the material covered in this chapter. You can find the solutions to these exercises in Appendix A.