Default Privileges

The next logical question after this discussion of privileges would be “Which privileges do I get by default?” When a user is created, it has no privileges to do anything within Oracle. You have to grant that user the CREATE SESSION privilege (or a role that contains CREATE SESSION) before the user can even connect to the Oracle database. This is just as applicable to client/server connections as it is to local connections.

The same logic applies to database object access. When a user is created, it has no access to any of the objects in the database other than those that have had PUBLIC access grants provided for them. There are a number of common database objects (for example, the USER_ views) that do have this PUBLIC access grant provided. However, if you have tables built for a local application, you have to grant access to those tables either to PUBLIC, the new user or to a role assigned to that new user.

Connecting Internally

One special case for database access is connecting internally. Under UNIX operating systems, you are allowed to connect internally if you belong to the correct operating system group (usually DBA by default). Under Windows NT, you have to supply a special password created for internal connections at database creation time. When you connect internally, you have DBA level access to the database. This allows you to perform startups and shutdowns in addition to other database and object maintenance tasks.

Dummy Object Owners

The previous sections present the list of privileges associated with the various types of database objects. The next couple of sections tie all these concepts of system and object privileges together. You’ll see several examples that represent environments that I have come across in my travels. These may not match your individual needs exactly, but you should be able to take these concepts and adapt them slightly.

The first concept is that of the dummy user. We all have users who just do not seem to be able to grasp the concepts of computers, but I would not call them dummies. We in the computer industry are not known for our understanding of their business functions, and let’s hope that they are not calling us dummies when they write accounting books. A dummy user is an Oracle user ID that is not associated with a person. It exists strictly to own objects within the database. There are several advantages to this scheme:

•  The password to this account can be restricted to DBAs or senior developers. This enables you to control precisely who can perform major modifications to these critical database objects, especially the more serious functions such as DROP TABLE.

•  You or some senior developers need access to this account to perform the object privilege grants on the database objects.

•  Because it is not often possible (due to inadequate space to copy large tables) to transfer database objects when the original owners leave the project and their accounts need to be deleted, these dummy owners are always with the project.

•  No developers can accidentally delete a key production table when they type an incorrect table name or forget that they are working in the production instance versus the test instance. The dummy account is restricted to key individuals and used only for object creation and deletion purposes. Some of you will not allow any developers at all into the production instance with anything other than connect privileges; but for others of us this is a real concern.

A Typical Privilege Scheme

This section sketches out some typical privilege schemes that are a combination of what I have seen in several instances during my travels. Consider whether any of them could be used as a starting place for your instance. For this discussion, you explore four privilege schemes:

•  An order entry system

•  A data warehouse system

•  A data warehouse development instance

•  A laboratory or research environment

The first environment is an order entry system (see Figure 13.8). In these systems, you typically have a group that enters orders into the system. This data is stored and forwarded to groups involved with accounting/billing functions and shipping. Most of the order entry staff is permitted to enter new orders, but supervisor permission is required to change prices and other functions. Finally, there are often groups, such as buyers, who monitor what is selling and what is in stock to ensure that the correct products are being built and acquired.

Figure 13.8.  A sample order entry environment.

The exact details of how this works will vary between companies, but let’s divide the users into six roles:

•  DBA. This is a book on database administration, I would not forget to include us.

•  order_entry. Staff involved with taking the orders and entering the data into the system.

•  order_supervisor. Supervisors in the order entry process who are allowed to update certain data fields that the order_entry staff is not permitted to modify.

•  accounting. These people perform credit checks, billing, and so forth, and update the tables associated with accounting data.

•  shipping. These people put the components of the order together and ship it out. They also update the shipping data tables.

•  buyers. These people are allowed to monitor the flow of goods and orders, but not to change anything. They are strictly observers in this process.