The first example provided the methodology for developing a security scheme for a new application. I tend to do very few tasks from scratch. Instead, I like to get out reference books and look at scripts that I have created before to use as examples in my current task. For those of you who think in a similar manner, the next example is a security scheme for a very common Oracle application today—the data warehouse. In the typical data warehouse scenario, you have an online or batch transaction processing system (often on a mainframe computer) that serves as the source of data for the warehouse. At some routine interval you receive data from the transaction processing system. I have usually seen nightly downloads, but you could do it hourly, weekly, or whatever interval fits your business needs.

The key difference in a data warehouse is that most of the security rules related to who can update, delete, or insert are not applicable to the user accounts. There are controlled download routines that are run using special Oracle accounts used only for download, or the privileges required by the download scripts are granted to the system operators. The only restrictions for the users concern what data they are allowed to read.

For this example, let’s consider a system that stores information related to orders downloaded from a transaction processing system. The orders themselves are stored in tables not directly accessed by the users. Instead, scripts are run that extract and summarize key data elements into tables designed to answer the questions of production, sales, and shipping. The common users in each of these groups are not allowed to access the data in the other areas. Certain corporate planners are permitted to access data in all systems to provide overall performance and planning reports. Assuming that the rules I have presented here are correct and complete, I would propose the following privilege scheme for this data warehouse (see Figure 14.2):

•  Production—These users are concerned with assessing the impact of orders on what the company is producing. They divert resources, acquire raw materials, and so on, based on the orders that are placed. They require the following privileges:

•  CREATE SESSION

•  SELECT on production tables

•  Sales—These users are concerned with fulfilling sales quotas, profit margins, pricing, and similar things. They use the data to plan where to market the products and the price at which to market the products. They require the following privileges:

•  CREATE SESSION

•  SELECT on the sales tables

•  Shipping—These users are concerned with planning what items will be shipped to what locations. They arrange trucks and so forth for the products. They require the following privileges:

•  CREATE SESSION

•  SELECT on the shipping tables

•  Planner—These users are concerned with coordinating the overall process and, perhaps, providing reports to management. They require the following privileges:

•  CREATE SESSION

•  SELECT on the production tables

•  SELECT on the sales tables

•  SELECT on the shipping tables

•  DBA—These are the standard privileges granted via the Oracle6 DBA grant or the Oracle7 DBA role.

•  data_owner—This is a dummy user ID that owns all the application data tables. This user (and role) requires the following privileges:

•  CREATE SESSION

•  CREATE TABLE

•  CREATE PROCEDURE

•  CREATE PUBLIC SYNONYM

•  DROP PUBLIC SYNONYM

•  CREATE SEQUENCE

•  CREATE SYNONYM

•  CREATE TABLE

•  CREATE TRIGGER

•  CREATE VIEW

•  unlimited tablespace (possibly, if used by the DBA)

•  Operator—In this particular company, the nightly update scripts are run by the operators using their own Oracle user IDs. Therefore, these accounts require the following privileges:

•  CREATE SESSION

•  SELECT, INSERT, UPDATE, DELETE on the order tables

•  SELECT, INSERT, UPDATE, DELETE on the production tables

•  SELECT, INSERT, UPDATE, DELETE on the shipping tables

•  SELECT, INSERT, UPDATE, DELETE on the sales tables

•  ALTER DATABASE (startup and shutdown)

•  ALTER SYSTEM (kill user processes when needed)

Figure 14.2.  Sample data warehouse privilege scheme.

The final configuration is a sample security scheme for a development instance. This is a different environment from either of the two considered previously. Here, data security is not as important as having access to the system privileges required to get the job done. Many development systems go too far in the direction of granting unlimited privileges. Although it may sound good to have people not restricted from being productive, you eventually have to implement the controls you will use in production so that your testing reflects how the system will perform.

Balancing acts like this are always a challenge. The way I usually like to set up a development instance is to have two classes of developer. The first class contains people who develop software but are not trained or involved with the development of database objects. Perhaps they are good with the front-end tools, but are not sure what the word normalized means. The second class of developer develops new table structures, views, and perhaps even stored procedures. By having two separate sets of roles for these developers, you do not give too many privileges to developers who are not ready for them.

As part of the balancing act, you also create the same object owner account that you will have in production. When the developers have settled on the format of a particular set of tables, they transfer the object creation scripts to someone (developer or DBA, depending on how your organization functions) to be run from the object owner account. All the fine points, such as the creation of public synonyms and the grants of privileges to roles, are added to these scripts.