Now let’s divide the many user data tables in the instance into five categories—order, shipping, stock, accounting, and pricing. Let’s also assume that there is a dummy user account called “oe” (for order entry) that owns all the user data tables. Based on the scenario described, and giving some leeway to fill in the blanks because this is a fictitious company, I would propose the following schedule of privileges:

Notice in this example that all the users in the system are covered and I did not have to give out the DROP ANY TABLE or DELETE ANY TABLE privileges. These nasty and powerful privileges are usually used only by DBAs and, in some cases, senior developers. You can have a fully functional instance with everyone limited to what they need to get their jobs done.

An environment that contrasts sharply with that of the order entry system is the data warehouse. The data warehouse concept states that you make a copy of data stored in an operational system, such as an order entry system, and store it in a separate database instance. You often summarize the data and store the summary information in new tables that promote rapid answers to common questions.

Consider the situation where the buyers in the order entry system start to work very hard to analyze their product mix, distribution of purchases throughout the year, and other factors so that they always know exactly what is selling at the time. The system administrators and order entry staff notice that the order entry system is slowing down significantly when these buyers run their analyses. The solution chosen is to purchase a new computer with Oracle and transfer the data from order entry to this new Oracle instance nightly for buyer analysis.

Now management throws in a slight wrinkle into this system. They decree that all purchases will be categorized according to the four product groups sold by this company. They do not want buyers to be able to access data that does not fall into their category. The decision is made to split all orders coming down from the order entry system into four sets of tables, each set representing a product group (for simplicity, no order will cross two product groups). All the objects are owned by the dummy user known as “warehouse.” All the nightly data update jobs are run using this user ID. Therefore, all that is needed is to create four roles. Each role will have CREATE SESSION and SELECT privileges on the tables that correspond to a particular product group. It’s a simple and effective setup. No one but the DBAs and the dummy user can update anything.

Development instances present a series of challenges that are not found in production instances. In production, it is easy to restrict users to all but the most basic privileges. Application developers need to have enough power to get their work done, but not enough power to do any real damage. For the next example, let’s choose the Oracle instance used to develop the data warehouse discussed in the previous section. This company is wise enough to use a separate instance for development and testing (I recommend it whenever possible). In this instance, I would recommend the following roles and privilege sets:

•  The default DBA role (of course).

•  Each of the four roles used in production. You should also create dummy users who each have one of these roles. This enables you to test your applications with the same security scheme that is used in production. It would not be valid to test an application as the table owner and expect it to work the same for a different user account.

•  A developer role. Assign the following privileges to this role:

CREATE SESSION

ALTER SESSION

CREATE TABLE

ALTER TABLE

CREATE VIEW

CREATE PROCEDURE

CREATE SYNONYM (not CREATE PUBLIC SYNONYM)

CREATE SEQUENCE

CREATE TRIGGER (perhaps, if they are used)