Protecting databases using encryption is a basic data security best practice and a regulatory compliance requirement in many industries. Databases represent the hub of an information supply chain. However, only securing the hub by encrypting the database leaves security gaps because sensitive data also exists alongside the database in temporary files, Extract-Transform-Load (ETL) data, debug files, log files, and other secondary sources. According to the “Verizon 2011 Payment Card Industry Compliance Report,” unencrypted data that resides outside databases is commonly stolen by hackers because it is easier to access.
Let’s examine the types of files that exist outside three leading database platforms: Oracle, Microsoft SQL Server, and IBM DB2, as well as which ones contain sensitive data and why they should be protected.
Catalog Files (DB2)
In general, catalog files define the IBM DB2 initialization parameters associated with the database. They include information such as database name, ports used, and so on. They should be secured if configuration information is considered sensitive.
Control and Configuration Files (Oracle)
Control Files know the location of Tablespace Files and Online and Archive Redo Logs. Configuration Files define the Oracle initialization parameters associated with the database. This includes information like database name, amount of memory associated with the buffer cache(s) and so on. Configuration files can be SPFILE (which are internally formatted and are easily read via the “strings” command) or PFILE (plain text). These should be protected if configuration information is considered sensitive.
Redo Logs (Oracle)/Transactions Logs (DB2 and SQL Server)
In general, redo/transaction logs contain information about every change made to the database. This includes Data Manipulation Language (DML) changes such as INSERTs, UPDATEs or DELETEs, as well as Data Definition Language (DDL) or structural changes such as table DROPs, CREATEs, ALTERs, and so on. Under certain conditions, changes are not logged, but these are typically exceptions, not the rule. There are two types of these logs— online and archive.
Online logs are always associated with the database, and will contain sensitive data temporarily. Archive logs are only created in “archive-log mode.” When an online log fills, it is copied to an archive log to be backed up later. Sensitive data will reside in these logs both on disk and in backups, possibly for an extended period of time.
Redo/Transaction logs contain copies of sensitive data and should always be secured.
System Databases (SQL Server)
The master database records all of the system level information for a SQL Server system and can contain very sensitive security information about your data in the user databases and should be protected.
The model database is the template for all databases that are created on the instance of SQL Server.
This database does not typically contain sensitive data that needs to be secured but can be encrypted without any impact to the system.
The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and for recording operators. msdb also contains history tables such as the backup and restore history tables.
This database contains information on backup and restore and may list backup location. This is not as critical as other system databases but may also need to be encrypted.
The read-only resource database contains copies of all system objects that ship with SQL Server 2005 or later versions and should be encrypted if those system objects are deemed sensitive.
The tempdb is a workspace for holding temporary or intermediate result sets. This database is re-created every time an instance of SQL Server is started. When the server instance is shut down, any data in tempdb is deleted permanently. Temporary data could potentially contain any data that is accessed in the user tables and should be protected.
Distribution database exists only if the server is configured as a replication distributor. This database stores metadata and history data for all types of replication, and transactions for transactional replication. It will contain the same sensitive information that resides in the user databases and should be secured.
Backups (DB2, Oracle, and SQL Server)
Backups are used for recovery purposes in the event of a database failure and contain the same sensitive information that resides inside of the database. These must be protected if the database contains sensitive information or the data is subject to a compliance regime requiring encryption.
Diagnostic Logs (DB2 and SQL Server) /Trace Files and Alert Logs (Oracle)
These are used to report errors and to provide information about what the database is doing when retrieving their data. Sensitive data can appear in logs when a database is reporting an error. They should be secured.
Tablespace Files (DB2 and Oracle)
In general, these contain objects associated with a database, such as TABLEs, INDEXes, STORED PROCEDUREs and so on. Data Dictionary files “know” where the physical manifestations of the database are located, including USERs, TABLEs, INDEXes, and so on. These should be protected if configuration information is considered sensitive.
User Objects files are the TABLEs, INDEXes, and so on where User or “Application” data is stored. This is “the data” so it will contain sensitive information and should be secured.
UNDO/Rollback files are by Oracle database to maintain a read consistent image of the data when it is being modified by DML, while Temporary files are used by Oracle for sorting, merging, and other activities. Sensitive data is very likely to have a transient existence through these two types of tablespaces. They should be protected.
Scripts (DB2, Oracle, and SQL Server)
These are used to execute one-time and repeat functions against the database. They frequently contain passwords to database users in clear text. These should be secured.
Reports (DB2, Oracle, and SQL Server)
These could be anything from the results of running a SQL*Plus script to a PDF or HTML page that is created on demand. They might contain sensitive data and should be protected.
Exports/Imports (DB2, Oracle, and SQL Server)
In general, they are files either in internal-Oracle format or in other formats that are used to load data into or extract data from a database. These include Datapump export files that need the datapump import program to interpret them; conventional export files that are easily readable via both the conventional import program and the UNIX “strings” command; and SQL*Loader or Extraction, Transformation and Load (ETL) files that can be in a variety of formats, depending on the tool used (SQL*Loader, ETL tool). ETL data is typically extracted from a production database, transformed to fit operational needs, and loaded into target system (typically a data warehouse). Like report files, export/import files may contain sensitive data and should be secured.
Conclusion
It is critical to secure data that resides both inside and outside of the database itself. Encryption and access control policies should be extended outside the database to secure files that contain sensitive information or login credentials. Left in the clear, these data sources could result in regulatory compliance violations and be used to compromise the database itself.
About the author:
Todd Thiemann is senior director of product marketing at Vormetric and co-chair of the Cloud Security Alliance (CSA) Solution Provider Advisory Council.