After completing a series of posts on Building Secure Database Applications, the question of how the practices and features highlighted apply to data warehouses arose.

In a traditional database application, the database is a behind-the-scenes element, accessed indirectly by the user through an application front-end or middle-tier. In such an architecture, interaction with the database can be very tightly controlled to ensure security.

In a data warehouse, the database is the application.  Users interact more directly with the tables and views it houses through numerous applications, issuing a wide variety of queries which may or may not be known in advance.  The techniques that would be applied to secure the database behind a traditional application could severely constrain the users of the data warehouse and undermine its success.

Still, security is essential. Without appropriate security, the data warehouse, as the centralized hub of information on the status of the business, becomes just as much a liability as it is an asset. How then should it be secured?

In the previous series of posts, seven high-level considerations were put forward for securing a database application.  Revisiting these considerations in the context of a data warehouse provides some changes in guidance:

NOTE If you haven’t read the original posts associated with each bulletpoint below, please click on the provided link to provide background on the additional commentary.

• Harden the Server - No changes here. The software, operating system, network, and physical environment supporting SQL Server requires the same protection as before.  

• Regulate Network Connectivity – The guidance provided here is intended to make SQL Server harder to locate on the network by all but the intended applications.  As the data warehouse is expected to provide flexible access to a variety of applications, hiding the SQL Server instance, disabling the SQL Browser, and employing application-specific endpoints doesn't exactly support this objective.  Configuring non-standard TCP ports and named pipes some albeit little additional security to the data warehouse.  

• Secure the Authentication Process – The exclusive use of Windows authentication is strongly encouraged so that you may more accurately monitor end-user activity.

• Assign Minimal Permissions – Users of the data warehouse typically require read only permission to the tables and views in their domain. By carving the database up into separate schemas (with differing owners to break ownership chains), custom database roles can be built with appropriate access to individual schemas. Synonyms and views can be employed to make database objects, e.g. conformed dimension tables, accessible across multiple domains.  Another approach to assigning minimal permissions is to introduce an intermediate application between the user and the data warehouse. Such an application should support a fine-grain security model and query the data warehouse on behalf of the user.  Examples of such applications include SQL Server Analysis Services and the soon to be release Business Intelligence Semantic Model (BISM).

NOTE Column-level security is often suggested in data warehousing scenarios.  Please be aware that the has_perms_by_name() function, which is called directly and indirectly by many applications to determine which objects are available to a user, will only identify a table as accessible if all fields in the table are accessible to the user.  As a result, tables on which column-level restrictions have been defined may not appear to the user in their application.  Please test the impact of column-level security on the specific applications in your portfolio before employing this feature.

• Encrypt the Data – Transparent Data Encryption (TDE) is a must if the data warehouse contains any data of significance. This will prevent someone from walking off with a backup of the database which could then be used to learn the ins-and-outs of your business. IPSec and SSL to encrypt over the wire should also be considered even if the database is used exclusively over a secured, internal network.  Encryption over the wire prevents malicious users from intercepting data they might not otherwise have access to and/or by-passing the SQL audit.

• Defend against SQL Injection – If formal applications are built on the data warehouse, the standard guidance on defending against SQL injection applies.  However, the kinds of the tools users typically use to interact with the data warehouse aren't usually very susceptible to this kind of attack. Still, using the SQL audit feature to keep track of who is using which tools and then following up with a brief investigation of those tools is a good idea. 

• Monitor & Enforce Security Policies – The guidance here is relatively unchanged though you may wish to apply the SQL audit a bit more liberally where there is sensitive data.  As mentioned in the related post, there is overhead associated with the audit but the frequency of queries within the data warehouse is often less than an active OLTP application so that the cumulative overhead of the audit should be lower.