Affiliated with:

Security Threats in the Data Warehouse Environment

Warehousing 28

Data warehouse implementations are vulnerable to internal as well as external security threats.  Follow these mitigating steps to reduce the risks.


Security threats exist against your information resources, whether the systems are accessible through the Internet or buried deep within your internal network, available only to authorized users, including the enterprise data warehouse  and related analytics systems.  The increasing prevalence of web-enabled applications accessing information resources compounds this threat by spanning the enterprise infrastructure and the Internet.  Vendors supplying applications for information technology (IT) environments are pressured by users to quickly provide more functionality and performance to the marketplace, outpacing the competition, which can sacrifice security.  Do you really think your data warehouse implementation is still secure?

One common misconception is that all security attacks originate from outside of the company.  Not all information security  threats originate from outside hackers trying to penetrate your company’s network and application security barriers.  Take for example the following two possible scenarios:

Internal DW Security Threat

A department manager logs onto to the corporate intranet site to review time and attendance (T&A) reports from the corporate data warehouse for his particular department.  He navigates to the month end T&A summary and selects the particular report for execution.  After a few seconds the report appears in his browser with data pre-selected based on his logon id.  The returning URL in the browser window contains a long string of mixed characters and numbers.  Through simple decoding of this string, the manager’s user id and report number is extracted from this string.  Through systematic trial and error, the manager substitutes in the user id of the business unit’s vice president, a different report id number, and encode them into the string portion of the URL.  The eventual returning report displays the vice president’s view of the current month end revenue sharing forecast showing the manager, his manager, and their peer’s confidential information.

External DW Security Threat

A user logs onto to their personal account on a financial web site.  The user selects access to their personal account summaries.  The user is challenged by the site to enter valid authentication information.  The user enters their personal id and password, which gains them access to the summary section of the site.  The user selects a summary of the preceding month’s activity for their account.  The site returns with a summary listing showing transactions that occurred against the account.  The browser’s URL address shows a long directory path name from the web server, an execution query string, and a numeric filename and extension.  Through systematic substitution of the filename with alternate number strings, the user is able to obtain account summaries and other personal information for other users of the site.  Through further URL manipulation and testing, the user is eventually able to retrieve a listing of all processed summary reports for all users stored on the web server.

Information Security Challenges in DW Development

Many information system administrators believe that properly controlling and maintaining user and access privileges of the information system applications alone provide adequate security around this strategic information asset.  Commonly, these applications, built or bought, were evaluated primarily based on providing functionality and performance, not security.  For example, some information system applications still employ encoding techniques in their URL strings and/or header variables to prevent unauthorized access. Encoding methods, such as base64, do not provide the same security as encrypting and can easily be reversed engineered to reveal contents.  Another example is delivery application’s that allows application information to exist in a persistent state on the hard drives of a web server as an unencrypted cache or temporary file.  This data is used to increase performance response of recurring user requests to the information system at the expense of security.  This also applies to the supporting components of the infrastructure (web, application, database servers) which are connected to the IT environment.  These components are shipped to provide the most functionality out of the box not necessarily the most security.  This leaves other possible avenues of exploitation to your IT environment and its information resource unless configurations are modified to maximize security.

Often, the groups in a company who are qualified to evaluate applications and infrastructures implementations for security are excluded from the decision process until the application is about to go into production.  In the best case, this can lead to delays in implementation or redesign of front-end delivery methods due to security risks.  Worst case, due to insufficient time for security analysis, implementations go live without fully recognizing the possible compromise that has been made to corporate or personal information from the application.  Even if an application implementation has been through an internal or external security vulnerability assessment, any change to any underlying component requires notification to appropriate support staff for analysis of the component modification, and possible effect to the overall infrastructure.  Increasing use of portals, messaging, web and cloud services, and wireless in data warehouse applications will provide additional points of possible exploitation against this information.

DW Security Vulnerabilities

The most common application vulnerabilities found in front-end information system applications, involves input validation.  How well an application defends itself against malicious or erroneous user input directly correlates to its level of susceptibility and ease of exploit.  Often the first activity an intruder will perform is to look for information leakage, i.e. any clue that could help in mounting a successful attack.  The most obvious place to look is in Web pages themselves.  HTML and JavaScript comments often reveal useful information such as application version numbers, hidden variables, code, or links that have been commented out but still function if called.  For example, one product reveals the full directory path where data files were stored, enabling an attacker to use another exploit to download them directly, bypassing application-level checks.

Parameter manipulation is a rather broad description of a very simple fact: every piece of information and every parameter sent by the browser to the server can be tampered with.  Hence security practitioners’ frequent admonitions never to trust user input.  In its simplest form, the parameters being changed are typically found in URLs.  One of the most notorious security attacks, cross-site scripting (CSS), results in an attacker’s ability to execute code, typically JavaScript, on another user’s browser.

Many reporting applications allow users to enter SQL queries.  Often, applications that do not support SQL queries are designed to append a user’s input to SQL queries directly.  Both actions can create just the opportunity an attacker needs to perform SQL injection. SQL injection attacks find ways to insert SQL code into applications in order to gain control of a system.  Actual SQL may not be the only code executed on the database.  Many DBMS’s have a whole host of stored procedures installed by default.  Some even enable running any executable already present on the database server itself, which can lead to further points of attack.

Mitigating Risks to DW Security

There are measures that can be used to help minimize or remove security vulnerabilities in your enterprise applications.  Securing the IT environment is more than just administering users and access privileges.  There are a number of actions that can be implemented with varying levels of effort to mitigate security exposures of your firm’s information resources.

Warehousing 29

To begin, remove or at least turn off features and functionality of the site that are not used.  This will reduce the number of access points that an attacker can take advantage of and manipulate.  The more access points the more opportunities for exploitation. This includes any sample code provided by an application vendor, debug functionality used by developers, and administrative functionality that is not going to be used.

Place controls on who has access to the various infrastructure components.  Minimum access should be given to meet the business needs of the user’s role.  For example, only a limited number of database administrators (DBA) should have full access to the entire information stores.  Even then, access logging should be in place to capture what actions were performed by which DBA at which date and time for auditing purposes.  All DBAs and other data management personnel should be fully qualified in their roles.

Assess the security of data and communications flow between the application and the infrastructure plus the application components.  Data coming into a site may be secured under a secure socket layer (SSL) and then sent in clear text or simply encoded between application components.  Make sure the authentication services use encryption when accessing entitlements stores (e.g., LDAP).

Monitor the usage of the application site for variation in patterns.  Unusual usage patterns may be an indication of an attack and worth investigating.

Review how the application stores data extracted from the enterprise environment.  Ideally, data should not exist in a persistent form on disk anywhere in the environment except in a properly secured database or data store.  If persistent application data must be present on disk, it should utilize additional security defenses such as network segmentation (e.g., firewalls) or encryption to limit any exposure threat.

If the site utilizes a single sign-on (SSO) application for controlling access and authentication between application components, determine if it has a cross-site scripting option to deter this particular type of vulnerability.

Develop and use a software development security standard for all internally and externally developed application code.  If you do not already have a security standard search on the Internet for security sites that provide white papers and guides on best practices for building secure applications.

Correct any information leaks found in applications that provide application, security, or infrastructure information.  Error and security messages from applications and databases often provide useful information on servers, networks, directory paths, filenames, programs & scripts, tables, and other infrastructure items, including their metadata.  This information by itself may seem relatively benign but when combined with other techniques can lead to exploits.

Insure that enterprise applications and portals manage the security of sessions typically cookies when a user logs out.  A user’s cookie or other session credentials should be invalidated to avoid session hijacking (e.g., kiosks)

Monitor vendor and security web sites and user groups regularly for security alerts. This includes all components of the infrastructure not just the front-end business intelligence applications.

Additional Efforts – Web-Hardening and IT Security Audit

The mitigation methods described so far were all application specific and important to guard against.  However, security does not stop there.  Even though an application may be secure, if it is running on a Web or application server that has not been hardened then the risk is still high that attacks will succeed against it.  Make sure you follow your application vendors’ instructions on how to secure your server software.  If the vendor does not provide this information in the documentation, be sure to check their Web site or contact their technical support team.

Another option is to engage a third party digital security consulting firm to conduct a security audit of your IT environment.  The assessments can be in the form of an application penetration test to a vulnerability assessment of the front-end applications.  The vulnerability assessment focuses on the risk posed by transaction based exposures to the front-end applications (e.g., cross-site scripting, information leakage, input manipulation, many others).  The application penetration test assesses the application site’s ability to resist attacks from both valid and anonymous users.  This is accomplished by testing the site’s ability to prevent data manipulation, privilege promotion, and authentication bypass.


Finally, once a thorough understanding of the security vulnerabilities of the IT environment is achieved, a reality check needs to occur.  No organization can ensure 100% security of an IT site unless there are unlimited funds and time.  The complexities and dependencies of today’s information technology environments, software, and infrastructure make the testing of every possible permutation virtually impossible.  Imagine performing code reviews of third-party enterprise applications looking for security vulnerabilities.  Choices can be made to resolve the exposure immediately, defer fixing, have vendor remediate, or manage the risk.  Business and security need to work together to assess the real level of risk, probability of business disruption, business liability, and cost of remediation before proceeding down any path.


Michael F. Jennings

Michael F. Jennings is a recognized industry expert in enterprise architecture and information management. He has more than twenty-five years of information management experience in the healthcare, retail, telecommunications, health insurance, and other industries.

© Since 1997 to the present – Enterprise Warehousing Solutions, Inc. (EWSolutions). All Rights Reserved

Subscribe To DMU

Be the first to hear about articles, tips, and opportunities for improving your data management career.