0504-0506

Previous Table of Contents Next

Page 504

Modifying User Accounts

Similar to the way in which you modify profiles, you can modify user accounts by using the ALTER USER command. The basic syntax is the same as for CREATE USER, except that you need to specify only the value being changed.

The following syntax changes the temporary tablespace of the designated user:

 % sqlplus system/manager SQL> alter user cherie temporary tablespace tmptbl; User altered. 

Deleting User Accounts

When attempting to delete (drop) a user account, you must make a consideration similar to the one made earlier for profiles. To drop a user account, you must decide what to do with all objects owned by the user (destroy the objects with the user or keep them). This situation is similar to the UNIX system administrator who is removing a UNIX account and must decide whether to remove all files owned by a user, change the ownership to another user, or leave the files alone with the same ownership.

As shown in the following example, the syntax for removing a user is far less complicated than for adding a user:

 % sqlplus system/manager SQL> drop user ops$lance cascade; User dropped. 

In this example, the DBA removes the database account and, by appending the CASCADE option to the command, removes all objects owned by the database user. If you omit the CASCADE option, any existing database objects are left untouched when the user account is removed; you still can access the objects.

Changing User Passwords

One of the most common tasks users ask administrators (DBAs or system administrators) to perform is to reset user passwords. Quite often, a user of the HelpDesk contacts you with this request. You accomplish this task by using the ALTER USER command:

 % sqlplus system/manager SQL> alter user cherie identified by rhett; User altered. 

Users can reset their own personal passwords, but they often forget how to access their accounts and ask you to do it.

Page 505

Working with INIT.ORA Parameters

A few INIT.ORA parameters deal with the creation of new user accounts, with respect to operating system authentication:

• OS_AUTHENT_PREFIX
• REMOTE_OS_AUTHENT

By setting the value of OS_AUTHENT_PREFIX, you can designate a prefix other than OPS$ for operating system authenticated accounts. Using the following setting, for example, allows a null value to be the operating system authentication prefix:

 os_authent_prefix="" 

This setting allows the same account name at both the operating system level and the database level. The user LANCE may have an account, LANCE, within Oracle that is operating system authenticated.

The value of the parameter REMOTE_OS_AUTHENT is set to TRUE or FALSE. This parameter enables remote clients to perform authentication on the database server. If this value is not set to TRUE, client connections must use password authentication. You should consider the sensitivity of the database information and the security of the network before setting up this type of authentication.

TIP

To use operating system authentication with SQL*Menu 5.0, you must set the value of REMOTE_OS_AUTHENT in the INIT.ORA parameter file to TRUE.

Special Account Considerations

Aside from performing the day-to-day tasks of creating end-user accounts, you should evaluate a few special account considerations. Although you might not encounter these issues in every single site (each site and environment are unique), give these issues some thought during the early phases of database setup.

Setting Up a Generic Database Administrator

The Oracle RDBMS comes equipped with three accounts for Oracle database administration: SYS, SYSTEM, and INTERNAL. None of these accounts is equipped for day-to-day DBA operations, however.

Page 506

The database user SYS is a user who stores the basic tables and views that make up the Oracle data dictionary. Oracle7 Server Administrator's Guide recommends that you not use this account except when Oracle technical support instructs you to do so. Some experts may argue this point, but this recommendation is sound based on the sensitivity of the database objects the SYS user owns. Similarly, you should use SYSTEM only when installing additional software packages that require this user account. The internal connections are dangerous, because they give you the capability to shut down the database as well as unrestricted database access as SYS.

The point to remember with these accounts is that accidents can happen anywhere . Suppose that you're working late at night; a single typo or misplaced character can drop a data dictionary table and corrupt the entire database. Aside from this possibility, none of the accounts is flexible enough to give you a choice between operating system authentication and password authentication (these accounts allow only password authentication). In addition, none of the accounts is set up to enable you to create temporary, ad-hoc tables you may need. Many sites therefore propose a generic DBA account.

A generic DBA account is set up as a user other than SYS or SYSTEM with full DBA privileges. The account generally is used by the DBA(s), depending on the size of the site. The account is created to enable DBA-level access without placing data dictionary objects at risk and to enable a choice of authentication methods .

Setting Up a Generic Applications Administrator

Along the same vein as the generic DBA account, many sites use a generic applications administrator. This type of account can be far harder to define than the generic DBA account, because the applications environment is different between locations (every site has a DBA). The applications administrator has two distinct sides: the applications side and the database side.

On the applications side, a generic operating system account usually owns all programs created for the system. Any developer who wants to make changes to the programs (forms, reports , and so on) needs to have the password to the operating system applications administrator account. A database account (which generally is password authenticated) owns the database objects. You make any changes to database objects by using this account, and the password usually is known only to you and the applications administrator over the applications system. In general, when setting up these types of accounts, make an attempt to minimize traffic on the accounts that own the actual database objects. The less often these accounts are used, the less chance for a minor mistake (such as dropping or truncating a production table).

Maintaining User Data

One of the many concerns when managing users ”one that some DBAs tend to neglect ”is managing data that is specific to each individual user. Although this task might seem simple (and often is), it can be a very tedious and time-consuming task for DBAs of large, widespread corporations. The following two sections discuss considerations for managing user data.

Previous Table of Contents Next