Security is often an afterthought. Even for OLTP. In DSS, where afterthoughts are as common as Cartesian products, it is wise to try to address as much as possible of any issue as early as possible, planning with foresight so that mention of change later on will not cause grim nightmares, serious indigestion and a flood of resumes across the corporate firewall.

It is easier to put security systems in a building during its construction. It's more expensive to do it afterwards. Put the security in with the building, make changes as needed. If security is done during the design stage, a better security system can be achieved at significantly lower costs. Although it may not be obvious, the basic principles of security are the same for OLTP and DSS.

Security of Data

The level and nature of security are often the function of arbitrary decisions by IT. Or worse: the result of neglect.

The underlying question that should determine the nature of security for a business is a simple one: "What is in the best interest of the business?"

Let's take the more "concrete" example of an office building. What are some of the security decisions made?

The principle of security decisions driven by business may restrict non-employees accessing the building or parts of the building. Employees may be restricted from certain areas. Access to equipment may be restricted by placement or by an access code. There may be guards (inattentive or otherwise) at key locations. Cameras can monitor access and entry, and exit can be logged automatically or manually.

It is naive to think that implementation of security for a database is handled like implementation of security for a building. Nonetheless, in both cases, security should be driven by business rules; and the security should be considered during design and may even affect design, no matter how well thought out initially. Security will have to be adjusted and improved over time based on practical considerations and changing needs.

Although, good security in a building or a database is achieved by the same basic principles, it is easier to implement security in a database. A database table can be protected from deletion easier than office equipment can be protected from destruction or damage.

Both have issues of politics and status involved.

OLTP versus DSS

It is not a particularly worthwhile exercise to figure out how OLTP and DSS security differ. The security of any database should be determined by what makes business sense. One must understand the business (which determines the needs of the users), the function of the database, the inter-office and intra-office politics and, in addition, have that blessed gift often bestowed but rarely used ­ common sense.

Without this understanding, security will fall into the categories of nuisance, nonsense or non-existence.

Security for the Warehouse

The first steps of warehouse security planning occur at the gathering of requirements and the first stage of design. One must understand that good security is an iterative process based on usage, feedback and business requirements.

Security should be addressed at the column level (and, in some cases, at the row level), at the table level, at the database level, at the tools level, at the client and server level and at the network level.

Don't try to tie the value of analytical data back to its operational sources. Value it as it exists. Realize that data of a given danger or importance when aggregated or combined with other data will now have a different value or potential liability.

Consider issues of customer confidence, legal liability, strategic value, cost of down time, cost of corruption, ease of identifying corruption and backup strategy.

Security can be broken down into two interrelated components: access and monitoring.


Some access restrictions will be determined by corporate policy. Consider who can access the machine and how. Will most users go through a firewall before being able to log on? What access is there to the console?

Although access to the computer room may be tightly restricted and monitored, it often isn't. I have been in one location where despite strict access control at traditional points of entry, one could get into the main computer room simply by lifting a ceiling tile and crawling one's way to an appropriate landing spot.

At many sites, one can get into the computer room not only with the proper badge, but by entering as someone else leaves or, after-hours, by walking in with someone from the cleaning crew.

Don't underestimate the number of potential loopholes that exist at every stage from console access to database access.

Database Access

How are the users going to be accessing the database? Is it through a software product that provides only read access to the data? Does the query tool have security features to specify what users can access what tables? Even so, redundantly implement the same security restrictions at the database level. Always assume that users can (somehow, someway) directly enter the database through "sqlplus" or "dbaccess" or whatever tool the database user provides for entry to the database.

Determine what users can access what data and in what way; group the users so that the DBA can manage roles instead of individuals. DO NOT ALLOW users to have delete, update or insert privileges except to scoring tables or tables existing for a similar front-end use. Protect all other meta data.

Create views to limit access to particular columns or, in unusual circumstances, rows. Views also allow the substitutions of a fixed value for a given column, (for example, masking a phone number with spaces or substituting an average value for individual values of a salary column).

Don't rely on anything to protect the database but the database's security.


Encryption is a means of access control. Data can be encrypted in a variety of ways and at a variety of stages.

Some databases have an encryption feature. This provides protection against someone who has discovered the root password and is particularly skilled at comprehending binary data. For highly valuable commercial or classified government information, this is protection worth implementing.

Legacy data is often transmitted across a network into a staging area. This data is stored as flat files and is much easier to read than the information the database stores on disk. These files are altered by the integration and transformation processes and may be stored, prior to loading into the database, in another staging area. Less easy to understand, but usually decipherable, are the contents of log files, transaction files and audit trails which may or may not contain sensitive or valuable information. The file protection capabilities of the operating system should be adequate for most environments; but if total safety is required (from examination and copying), then data encryption is necessary.

When data is accessed via a corporate intranet or on the Internet, the risk of data being intercepted is increased. A corporate firewall helps to keep less clever outsiders off the intranet, but this is not complete protection. Use of the Internet doesn't even offer firewall protection; a login and password help keep the uninvited out but don't provide protection against the data as it is transmitted. For complete protection, data has to be encoded prior to transmission and decoded upon receipt.

One price paid for data encryption at any level is lowered efficiency. Data has to be unencrypted as well as encrypted, and this takes significant CPU cycles. Other costs of data protection are the maintenance involved, the training of the administrators involved and the cost of evaluating software as well as the purchase price.

Until recently encryption was mostly used for special purposes such as protecting data that was related to national security. But with Web-based access to decision support systems which contain credit card numbers, addresses, phone numbers and detailed financial information about customers, encryption will be necessary to ensure the protection of the customer and the corporation.

There are a variety of ways to sneak an unauthorized glimpse at the contents of a DSS system. Encryption makes that glimpse incomprehensible.


Even if you do a perfect job of implementing security, monitoring is valuable.

Successful monitoring software will capture improper use of the warehouse by authorized individuals. Monitoring will also help track down software bugs, mistakes, lack of proper education and other common childhood (and adult) ills of a given data warehouse environment.

Since it is only theoretically possible to have perfect security, monitoring can also catch breaches of security and provide feedback for adjusting, improving, scaling down or scaling up the existing security.

Monitoring can be done at each point of access. Whether this is needed or not, must be determined by the value of the data, cost of implementing, maintaining and examining monitoring output and other business considerations.

Monitoring the Database

There are a number of ways to monitor the database. The database may have audit features such as Oracle or Sybase. Scripts can be written to take snapshots of query activity and generate reports. Or, software like Pine Cone Systems' Usage Tracker and Cost Tracker can be purchased.

Aggressive monitoring of the database provides benefits not related to security. The information garnered about the use of the DSS system can be used to improve the physical layout of the database, tune the engine configuration file and identify dormant data.

Safety and Sanity

Implementing security is an ongoing process. If a warehouse is a living breathing organism, then security is the immune system and the sensory organs. As our organism evolves, these will need to evolve. As the environment of the organism changes, so will the security.

Realize that from a business perspective, over-implemented security can be worse than under-implemented security. In an OLTP system, it would be crazy to prevent a telemarketing solicitor the ability to insert new rows into a customer or item table. This would dramatically affect revenue. Realize that preventing read-access to tables (or columns in tables) in the data warehouse may affect revenue or income or profit margin. If a user is frustrated by unnecessary restriction, that user may spend less time scanning the warehouse and more time surfing the net or sending memos to your boss.

Do not drive security implementation by theory or a misplaced desire to feel protected. Security should be implemented based on what makes the most sense for both the short-term and long-term health of the business. If implemented properly, security will protect not only the best interests of the business, but also the best interest of the community that the business serves.

Just as function determines the structure of any component of an organism, natural or mechanical, the function of security will determine its structure. Security will be just one part of the system and will need to work with all the other components; health can be considered a measurement of how successfully each individual part of a system works in conjunction with all the other parts to provide optimal functionality. A healthy organism will serve itself as well as contribute to the success of its environment.

Safety and sanity are not mortal enemies, but sanity is a measurement of how well security and all other components of a system are functioning. Judge security not only by its structure, but by how well it supports the entire corporate organism's needs and survival. To restate an old saying, it is better to be safe and sane, than safe and crazy or sorry and sane.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access