Series: Project Lockdown

A phased approach to securing your database infrastructure  

Imagine that you have just landed a new job as a DBA. On your first day, you hear rumors of an impending security audit driven by Sarbanes-Oxley requirements. You need to get to know your environment as soon as possible, so you know where to look and understand when to take immediate or preventive action.

Or imagine a less serious (yet still alarming) situation in which you have "inherited" database and server that you know have never been hardened, and an audit is on the horizon. You have to do something quickly to secure them, and there is no one to turn to. You're on your own.

Or, perhaps you are a seasoned DBA and have been looking after a database for a while. No audit is impending, but you are concerned about security in general and want to be assertive about it.

Regardless of the specific situation, you can safely make three assumptions:

1. You will have to work quickly. Whether or not an audit is imminent, you cannot afford to leave your environment in an unsecured state for anything but a short period of time (if at all).
2. You will have to work carefully and methodically because you are modifying the production database.
3. You will have to work on this project while performing other routine activities—taking care of the database, fighting fires, handling concerned customers, and so on.

Based on these presumptions, clearly you will need a phased approach to securing your database infrastructure, and one that makes use of the Oracle technology currently at your disposal. In this series, you will receive a blueprint of such a plan. I call it Project Lockdown.

This project is divided into four distinct phases, each of which are achievable and provide measurable improvements within a specific period of time: one day, one week, one month, and one quarter: 

• Phase 1 (Duration: One Day)
• Phase 2 (Duration: One Week)
• Phase 3 (Duration: One Month)
• Phase 4 (Duration: One Quarter)

(These durations are merely estimates; depending on your installation, you may need more or less than the prescribed amount of time.) At each phase you will learn specifically what you need to do via code samples, examples, and task checklists.

Before beginning this project, I suggest that you read the brief security primer below for common terms and concepts.

Because these activities vary widely by Oracle version, only activities relevant to Oracle versions 9.2.0.x (Oracle9i Database Release 2) through 11.2.x (Oracle Database 11g Release 2) will be discussed. Where possible and appropriate, obvious differences across operating systems will be addressed.

As always, the content provided here is for instructional purposes only and is not validated by Oracle; use it at your own risk! Under no circumstances should you consider it to be part of a consulting or services offering.

A Brief Security Primer 

First, it's important to understand what "security" means in the context of this project.

Database security is akin to a roof over a building. The roof is held in place by four pillars—authentication, authorization, assurance, and accountability—with each pillar contributing to the core principle of security in a different way.

Authentication means establishing identity—confirming that a user is what he or she claims to be. If authentication is weak, an intruder may gain entry into the infrastructure. When the user is authenticated, you allow her to see only the data she is "authorized" to see.

Authorization implies control over what the user can or can't see after she is authenticated; at this stage, identity is not questioned. ("Authentication" and "authorization" are often confused or used interchangeably, which is incorrect.) Basically, authorization establishes the perimeter of the user's access inside the database, and if that perimeter is porous, a legitimate user will be able to see a lot more than she should

As a legitimate, properly authenticated user acting inside the boundaries of authorization manipulates data, the data moves inside and outside the protective "roof" of database security. To guarantee that the data is not contaminated or illegally manipulated during this process, assurance comes into play.

Finally, security is incomplete without the user being accountable for her actions. Without it, anyone can potentially do something that is perfectly legal but might affect the overall stability of the system. So, security is concerned not just with allowing legitimate users to work under an authorization scheme, but also with what they are doing and why. Therefore, the concept of accountability is the final pillar of security infrastructure.

All four pillars are required to hold the roof in place. If any single one of them crumbles, the roof collapses entirely. You must erect and reinforce all four pillars to create a reasonable security structure; a single weakness in this architecture will make your entire infrastructure vulnerable.

A Layered Approach 

Here's another way to look at security. Suppose you are sending a very valuable and delicate piece of item on a regular commercial carrier. Do you simply stuff it inside an envelope and drop it in the mailbox or the carrier's bin? Not likely. Rather, you probably wrap it in bubble wrap or newspaper put it in a protective box. (Depending on the nature of the article, you may want to add a watertight container as well.) You now have layers of protection around the valuable object, with each layer performing a specific function. If someone is intent on getting the item, he has to remove (or penetrate) these layers, one by one, until he reaches the contents of the package.

Database security works in a similar manner; you need layers of security around your data, with each layer protecting a specific area. For example, your organization may have a firewall around the corporate network to prevent external attacks. Inside the organization, you may have a second firewall around the database servers, with ports open only for the application servers. After these layers are penetrated, the intruder must overcome a third obstacle: authentication (that is, proving that she is a legitimate user). If the intruder gets past that layer via password theft, for example, she is still restricted to what she can see—by the layer of correct authorization, with which you can protect key components such as tables containing sensitive information. Finally, if all these layers fail and the intruder gains access to the "inner sanctum," you have one final line of defense: disguising the data (encryption).

The Risks 

Does this model work well? In most cases, yes—but there are exceptions. For example, what if the break-in originate from inside your organization (in fact, most break-ins do), or what if data backups are lost or stolen?

The first case demonstrates that although it is relatively easy to erect a firewall, if the intruder is already inside it, the whole point of a firewall is moot. As insiders such as curious or disgruntled employees are often already properly authenticated—they are legitimate users—your next layer, access control, must be solid. You must verify that only those privileges required for the job are granted; nothing more. The principle of "least privileges," as opposed to redundant privileges to perform a task, is a requirement here.

The second case is even more frightening. Even if you have designed the database like a fortress, there is at least one occasion when the data leaves that fortress: for backup storage. Many organizations send backup tapes to a remote location to protect them from local disasters such as fire, flooding, and so on; in many cases, this remote facility is a different company specializing in that business. Without proper security, tapes can be stolen (or simply lost) from the facility itself or en route to it. An intruder who obtains these tapes can theoretically restore them to a server he has access to and browse the data at his leisure. Scariest of all, in some cases, the tapes can be returned safely to the storage facility without the breach ever being discovered.

These two risks can throw a monkey wrench into your carefully planned security. However, it's possible to protect the database even from these two events via a combination of the following:

• A reliable authentication scheme—if a user is SCOTT, there shouldn't be any doubt that he is indeed SCOTT, not MARTIN
• Prevention of impersonation
• Prevention of eavesdropping by unauthorized users
• Protection of data "at rest" from illegal modification
• Auditing of user activities

...which is what you'll learn to implement in Project Lockdown. Let's get started!

Arup Nanda, an Oracle ACE DIrector, has been an Oracle DBA for more than 12 years, handling all aspects of database administration—from performance tuning to security and disaster recovery. He is a coauthor of PL/SQL for DBAs (O'Reilly Media, 2005). He was Oracle Magazine's DBA of the Year in 2003.