0489-0491

Previous Table of Contents Next

Page 489

TIP

Do not be afraid to grant a user temporary access to data unless some specific corporate policy prohibits this practice. As a rule of thumb, if it will take more time to produce the data than it will to create and later drop the user account, you should issue the temporary account.

Is Someone Currently Set Up with the Same Configuration Requested by the User?

A major time-saver in many organizations is the capability to set up a user in a manner similar to that of an existing user. This step limits the analysis phase of determining what the user needs and then creating the account. In these situations, you generally just need to coordinate with the appropriate applications administrator or manager to determine whether this setup is correct. ("Do you really want Katie to be able to do all the things Carlton can do?") If so, you can create the account without a tremendous amount of further effort. If the setup is not correct, you need to make determinations as with any new user situation.

What Is the Minimum Level of Access the User Requires to Do
the Job?

Sometimes DBAs are perceived as minimalists ”for example, when granting user access. In general, the level of access you give to the database in terms of system-level and object-level privileges, as well as which applications modules you allow the user to access, should be only what the user needs to do the job. (For more information on security, see Chapter 24.) Supporting minimal access doesn't assume that users are incompetent, malicious people who will take advantage of every situation. However, mistakes can (and do) happen.

Take the case of Richard ”a power user of an online transactional processing (OLTP) system. Richard is always making modifications to data in many tables. Because he has a working knowledge of SQL, Richard has access to the database via SQL*Plus. Instead of granting access to the specific tables, however, the DBA takes a shortcut and grants the following privileges: INSERT ANY TABLE, SELECT ANY TABLE, DELETE ANY TABLE, and UPDATE ANY TABLE. Shortly thereafter, Richard finds a book on Oracle (a book like this one) at his local bookstore and learns about the SYS-owned DBA views. Richard begins some experimenting and one day issues the following command to see the result:

 delete from sys.dba_users;

If committed, this command can cause serious problems for the DBA and everyone else using the database. This situation could have been avoided if the user account had been implemented appropriately.

Page 490

WARNING

Be very careful when granting privileges that are part of the ANY groups (SELECT ANY TABLE, DROP ANY TABLE, and so on), because they give users a very high level of database access (Chapter 24 discusses this topic in more detail). As long as these privileges remain in effect, users can perform the action in question with unlimited access. Granting ANY privileges is especially dangerous in a multischema database environment, where a user may have access to tables in only one schema.

What Is the Maximum Level of Access the User Should Reasonably Have?

At the opposite end of the spectrum is the concept of maximization. Whereas the idea of minimum level of access determines what users must have to do their jobs, the idea of maximum level of access determines the cutoff point. For example, if a corporate policy prohibits users from changing data in certain application tables (except for a certain level, such as a manager or MIS, of which the user is not a member), no user should be granted access to perform this task.

This necessary evil must be defined in all user settings. In some settings, no upper limit may exist; users may be permitted to have any privilege short of DBA. In other environments, the data may be extremely sensitive and require investigation before you grant access. To understand these limitations, you should have an in-depth knowledge of the applications systems and the rules that drive them ”or access to someone who does.

What Constraints (Technical or Political) Exist When Setting Up
the User?

At times, you may not be able to do what users ask. Perhaps granting users access to certain tables would inadvertently give them access to change data that should never be changed. On the other hand, perhaps access is permissible, but the comptroller does not want anyone with this access, for pure political reasons only, to be making any changes. Whatever the reason, this problem falls within the realm of constraints.

Constraints (not to be confused with database constraints, which are covered in earlier chapters) take the shape of technical and political restrictions. With technical constraints, some underlying reason prevents setting up a user. Perhaps the username conflicts with an Oracle reserved word. Here, the only option is to determine another method (a workaround), such as giving the user another name . The other side of the constraint house is political. As mentioned earlier, no real reason exists to prevent something from being done, except that one or more forces in the company do not want it done. In this case, you can override the users (not the best way to win friends and influence people, and upper management then may override you) or work on a compromise. Of all constraints, political constraints are by far the worst.

Page 491

Although constraints may not always be a problem, they are issues you cannot ignore when setting up and managing user accounts.

User Authentication Methods

As a rule, databases do not have an open-door policy that allows everyone access (of course, there are exceptions). Therefore, a database needs a way to authenticate users ”to determine their identity and make certain that they have authorized access. In general, a database uses one of two proven methods: password authentication and operating system authentication.

Password Authentication

The concept behind password authentication is the same as the traditional password method used on other databases, operating systems, network servers, and so on. Under this concept, the database (in this case, Oracle) issues a challenge (Password:) followed by a prompt. Each distinct user ID has an alphanumeric string associated with it that the user must enter correctly to gain database access. Suppose that a user account named CHERIE exists with a password of SCARLETT:

 % sqlplus Enter user-name: cherie Enter password: ........ SQL> show user user is "CHERIE"

In this example, the password is not echoed to the screen. This important safety feature enables users (or even a DBA) to enter passwords without worrying about others staring over their shoulders.

You have the option of passing the username and password to the application (such as SQL*Plus) on the command line. If you take this step, the password is echoed to the screen:

 % sqlplus cherie/scarlett SQL> show user user is "CHERIE"

Operating System Authentication

In certain organizations, users may be admitted entry if their identities are confirmed by a known person. This same tenet is true for operating system authentication; users are allowed access to the database if they have a valid operating system account that shares the same username as the database account.

Suppose that, on a UNIX-based system, a user is set up with a user account named LANCE within UNIX (usually done by the UNIX system administrator). The DBA, in turn , creates an account called OPS$LANCE within the database. When Lance connects to UNIX, he simply needs to pass nulls to the database's query for a password to gain access. In other words, the entry of an Oracle username and password are not required. The database extracts the username from