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.
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.