Key points of warehouse database Essay

For example, a warehouse of sales data can enforce access based on customer number, and whether the user is a customer, a sales representative or a marketing analyst. In this way, customers can view their order history over the Web (but only for their own orders), while sales representatives can view multiple orders, but only for their own customers, and analysts can analyze all sales from the previous two quarters. An application context acts as a secure cache of data that may be applied to a finegrained access control policy on a particular object. Upon user login to the database, Oracle Database sets up an application context to cache information in the user's session. Information in the application context is defined by a developer based on information relevant to the particular application. For example, a reporting application that will query regional sales data can base its access control on the user's position and division. The application, in this case, could initially set up an application context for each user as he logs in and populate the context with data queried from the employees and departments tables for the user's position and division, respectively. The package implementing the VPD policy on the regional sales table references this application context to populate the user's position and division for each query. As such, an application context makes executing sub queries unnecessary, which might otherwise hinder performance. Security affects the application code development and the development timescales in general. Security may affect the following activities.

Application development

Database design

Testing

Security affects the overall application development and it also affects the design of the important components of the data warehouse. They are load manager, warehouse manager, and query manager.

The load manager requires checking code to filter record and place them in ware house at different locations. More transformation rules will also be required to hide certain data. Also there may be requirements of extra metadata to handle any extra objects.

To create and maintain extra views, the warehouse manager requires extra codes to enforce security. Extra checks need to be coded into the data warehouse to prevent it from being fooled into moving data into a location where it should not be available.

The query manager requires the changes to handle any access restrictions. The query manager will need to be aware of all extra views and aggregations.

The estimates of developing and integrating the application with Security features to be accounted as complex tasks in estimating efforts and compute the cost and time.

The database layout is also affected because when security measures are to be implemented, there is an increase in the number of views and tables. Adding security features increases the size of the database and hence increases the complexity of the database design and management. It will also add complexity to the backup management and recovery plan. Testing the data warehouse is a complex and lengthy process. Adding security to the data warehouse also affects