Manage Learn to apply best practices and optimize your operations.

DBMS security: Data warehouse advantages

Are there data warehouse advantages in regard to security? Without question. Michael Cobb explains.

Our organization is looking to scale its current database management system (DBMS) infrastructure and we're considering the best way to deal with legacy data. Why would a business have separate databases and a data warehouse? Why not just have one large database for all data, both current and historical? Are there significant security issues with housing all data in one spot?

There are several reasons why I would strongly advise against moving all your data into one big database. First, if you put all your data into one single database, it becomes an extremely attractive target for malicious hackers. They would only have one database to hack and if they can break into it, then they can access all your legacy data -- not just your current data.

A counter argument to this is that running one database introduces cost savings, which could make more of your budget available to implement best practice security controls, and you could concentrate all your defenses on protecting this one asset. However, in reality, this argument doesn't hold up because your database will be providing transactional as well as analytical services. This means the number of different users, roles and access requirements will make the security configuration overly complex and therefore more open to misconfiguration and abuse. The result would likely be a less secure system.

For example, if your database is used to process orders from your website, access will have to be granted to Internet users to place orders, along with various internal departments, such as customer support, accounts and fulfillment, and third-party suppliers such as delivery services. If you only have one database, you will also have to give some form of access to the sales, marketing and research departments. This will force you in the opposite direction of the security principle of least privilege, which requires you to reduce the number of and types of users accessing your live data.

As you can see, setting up the many different types of user groups, setting their access permissions and controlling connections to a central database is complex and difficult -- not a situation you want when trying to secure your organization's key asset. I recommend moving your legacy data to a data warehouse and having a separate transactional database for current activities; the security configurations of both are a lot simpler. For example, the data warehouse, which will be used for analysis, can be made read only to anyone running a query against it. Because the warehouse can be designed specifically to handle complex queries, it will return results much faster and not slow down the processing of live transactions, for which timely responses are critical.

You organization probably wouldn't be able to survive without its database. By warehousing the old data, disaster recovery times will be quicker for the transactional database because the amount of data to be restored will be far smaller. Also, if data is not archived off to a data warehouse, not only will the database need the resources to handle live transaction processing, but also for complex and resource-hungry business intelligence queries for management reports. You are asking one database and the server it sits on to perform two different tasks. Even if you decide to go with just one database, at some point the amount of historical data will reach a point where you will have to archive it; otherwise, the database will grind to a halt. So why not do it now when you have to move your legacy data anyway?

This was last published in March 2010

Dig Deeper on Data security strategies and governance