'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += '
'; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 9 - Creating and Managing User Accounts

Using Oracle8

Chapter 9

Creating and Managing User Accounts

The Key Purposes of a User Account

User accounts in Oracle8 generally provide three distinct and important purposes in the operation of a database:

User security is one of the more important functions that you perform. Security in Oracle8 is important not only in keeping prying eyes from your sensitive data, but also in helping keep a developer or normal user from interfering with a production database's operations. Oracle8's security allows you to impose a very granular security profile on every user in the system. This profile allows control over which columns, tables, and rows users can select, update, or insert into, as well as whether users can create segments and where they may be created.

Although security is probably one of the more thankless jobs, it's absolutely essential that you understand what types of security protection are available. Every security permission has some risks and benefits associated with it. It's important that the significance of each permission be understood so you can implement the safest and most flexible security policy possible. It's true that nobody will thank you for expiring his or her password every 90 days, but if someone breaks into the corporate data warehouse, you'll probably be answering some even tougher questions from your organization's upper management.

Oracle8 improves user management
User accounts through Oracle7 had been one of Oracle's traditional weaknesses-until Oracle8. Oracle8 truly has an excellent system in place for managing user accounts (and security in general) throughout your enterprise. It's vastly superior to anything Oracle7 DBAs had to work with.

Creating User Accounts

An Oracle8 user account allows a user to be identified by the database and, optionally, to store segments such as tables and indexes. What users do with their individual user accounts is controlled by permissions that you grant. A user account by itself doesn't allow anyone to even log on to the database. No implicit permissions are given when a user account is initially created.

Oracle, like most RDBMS systems, uses privileges granted to a user account to determine whether a user is allowed to perform a certain function on a particular segment (or in the system at all). A user account without any privileges granted can't do anything at all.

Granting users privilege is covered in depth in Chapter 10, "Controlling User Access with Privileges."

User Authentication

The only means of identifying users to Oracle is their user identification, known as a userid in Oracle8. Anyone could deliberately or accidentally claim to be someone he isn't. Without some means of authenticating who a user is, you could potentially allow someone access to information; that access could be damaging to the company, employees, or worse.

From the earliest of computer days, the password has been a universally accepted means of authenticating who a user is. Central to password authentication is the belief that only a person claiming to be userid GASPERT would know what GASPERT's password is. By making the user produce this secret, you believe that if a user knows the password, he is who he says he is.

The popularity of password authentication is also one of its greatest drawbacks. Most users despise having to keep a different password to each system they use during the day. Users are usually urged to use different passwords on different systems, which leads them to write down passwords or use a single password on all systems anyway. In either case, the password can no longer be considered a secret. You have no way of knowing if someone else had access to the password either in another system or to the piece of paper on which the password was written. Without a doubt, it's at least an irritant to have to log on to multiple systems separately each time you want to use them.

Clearly, it would be most convenient if you could just log on to one system (such as the operating system) and then have all subsystems (such as a database) base their authentication on the first logon. Fortunately, Oracle8 provides this exact capability. Oracle8 allows external authentication, which basically links a particular Oracle8 user account to a particular operating system account.

External authentication's drawback
The only major drawback to external authentication is that you have to trust the operating system to authenticate users properly. Don't take this issue lightly. PC environments in particular are notorious for their lack of credible security. By using external authentication, your database's level of security is no greater than your operating system's. If you can trust your operating system to provide adequate security for your database, external authentication offers an attractive and viable alternative to saddling your users with yet another password.

In the modern client/server world, users often aren't actually using a tool such as SQL*Plus on the same machine that the Oracle8 server runs on. External authentication then adds another question to ponder: Can we trust the remote operating system to properly authenticate a user? Often, external authentication is practical only if you can trust the database server's OS and the client machine's OS's authentication methods. PCs, again, present a particular problem.

Remote authentication and Microsoft Windows
Windows 3.x and, arguably, Windows 95 don't offer nearly the level of security that Windows NT or a UNIX workstation offer. If your clients will be using Windows 3.x or Windows 95, you probably should avoid using external authentication.

If you want to use operating-system authentication, you need to be aware of two Oracle8 parameters and their customary settings:

os_authent_prefix = OPS$
remote_os_authent = true

In Oracle8, externally authenticated userids are typically prefixed with OPS$ to distinguish them from users who must log in with the traditional userid/password combination. os_authent_prefix allows you to customize the prefix of externally authenticated userids.

If you plan to allow a remote system to authenticate a user for Oracle8, you must set the remote_os_authent parameter in your INIT.ORA file to TRUE.

Oracle8 introduced a third option for authentication, known as enterprise authentication. With the Oracle8 Security Service (OSS), this system creates a global central location for authenticating user logins. If your environment consists of many Oracle8 databases, this may be appealing to you. Although you still must create a user account in each individual database a user is allowed to access, the authentication and password is stored in a central area. This allows users to have a single logon to all databases. If users change their passwords, the change affects all database systems that use a common OSS.

The installation and configuration of OSS is beyond the scope of this book. The discussions and examples henceforth assume that you aren't using OSS in your environment.

Creating a User Account

Before creating a new user on your database, you must gather some information first. You need to know the following:

To create a user in Oracle8, use the CREATE USER command with the following format:

        [DEFAULT TABLESPACE tablespace]
        [TEMPORARY TABLESPACE tablespace]
        [QUOTA value | UNLIMITED] ON tablespace] ...
        [PROFILE profile]
Where to use the CREATE USER command
This discussion of creating user accounts uses SQL*Plus and assumes that you're logged in as a user with the necessary privileges to create another user. This typically means that you're logging in as the SYSTEM user or have been granted the DBA role.

Who's looking over your shoulder?
More than one company's security has been breached by someone merely observing a user account being created. After creating a new account, you should always exit SQL*Plus and clear the screen. Remember that SQL*Plus remembers the last command entered. If you create a new user account, clear the screen, and walk off, someone could enter a quick SQL*Plus command and see exactly what password you used to create the new account. Many companies routinely create new accounts with a well-known password, allowing almost anyone access to your system. This is a poor policy from a security standpoint. Always make new user account passwords unique.

Keep the SYSTEM tablespace clean
Never allow users to create segments in the SYSTEM tablespace or use it for temporary sort storage. They could bring your database operations to a complete standstill if the SYSTEM tablespace fills up!

For a more in-depth discussion of profiles,

Basic Example of Creating a User

The following is a basic example of creating a user in Oracle8. Before entering this command, log on to SQL*Plus as a user with DBA privileges (such as the SYSTEM user).

Used to create user RWILSON
Sets password to RH0WIL2
Indicates that USERS table-space will store RWILSON's segments by default
Set to store RWILSON's temporary sort segments in the TEMP tablespace
Set to allow 100MB in USERS tablespace
Set to allow no storage at all on SYSTEM tablespace
Forces RWILSON to change password on first logon attempt
Prevents user from logging on until you unlock account

After you enter this command, the new user account is created. If the user tried to log in right now, Oracle8 would stop her because she would lack the CREATE SESSION privilege. Chapter 10 discusses the privileges needed by new users to connect and work in the database.

Example of Creating a User with External Authentication and a PROFILE

The following example shows how to create a user who will be externally authenticated and has a security profile assigned to him:

Used to create OPS$BRONC (OPS$ is required because this account is externally authenticated.)
Indicates that new account will be externally authenticated
Set to use security settings in STD_USR_PROFILE profile for new account

Allowing Quotas on Different Tablespaces

Oracle8 allows you to control the amount of space users can consume in tablespaces. By default, there is no quota on the amount of space a user's segments may use in the database. Typically, you'll want to restrict users' storage to prevent someone from consuming all available space. Furthermore, you'll probably have at least one or two special-purpose tablespaces that you'll want to reserve for a specific use. This is particularly true of the SYSTEM tablespace, which should never contain user segments.

The QUOTA parameter, while straightforward, has the potential of greatly affecting the success and efficiency of your database. Too loosely set parameters will usually result in bloated, duplicated segments that have been poorly sized, resulting in wasted disk space, or worse-not enough free space for everyone to continue working. If QUOTA settings are set too tightly, users may waste time with unnecessary workarounds to conserve space.

Coordinating your quota policies
No matter what policy you adopt, it's essential that you coordinate it with your users during the initial policy-making and in regular follow-up discussions. Remember that your job as a DBA is to provide a service to your users. The purpose of quotas is to ensure equal quality system performance for all.

Setting quotas on a user applies only to segments owned by that user. If the user is allowed to create segments in other users' schemas or insert rows into another user's table, only the segment's owner's quota will be applied.

The QUOTA parameter is normally used to provide a fixed limit, such as the following:


There are two special cases, however: no quota limit at all and not allowing any space at all.

To remove quota restriction in a particular tablespace, you must substitute UNLIMITED for the numeric limit:


This allows a user to use as much space as he or she asks for (as long as there's enough free space to supply the request, of course). If you don't specify a quota limit on a tablespace, Oracle8 will default to an UNLIMITED quota.

To prevent a user from creating segments on a particular tablespace, set the size to 0 as shown in this example:


Because a segment must be larger than 0, it's impossible for a user to create a segment that will satisfy a 0 quota limit.

As explained earlier, you can duplicate Oracle8's QUOTA parameter as many times as necessary within a CREATE USER command. This is useful to easily set quota on many different tablespaces at once. You'll often want to establish quotas on all non-rollback and temporary tablespaces; this is particularly true in production environments.

Suppose that a database has the tablespaces SYSTEM, TEMP, RBS, PROD, DEV, and TOOLS. The PROD tablespace is intended to hold production segments, whereas DEV is used to hold developer's segments. TEMP is used for temporary sort-related segments, and RBS is the tablespace used for rollback segments.

You only want to allow developers to create segments in the DEV tablespace. Remember that they'll need to create segments in the TEMP and RBS tablespaces for normal database activities. A CREATE USER command might look like this:


This allows no segments to be created on SYSTEM, PROD, or TOOLS. 1GB is allowed on DEV. An unlimited amount of space would be allowed on TEMP and RBS (because they aren't listed with any quota limits).

Using the CREATE SCHEMA Command

Sometimes it's useful to group several CREATE TABLE, CREATE VIEW, or GRANT commands into a single operation. You would typically do this to make sure that either everything is created properly or nothing is created. By grouping multiple CREATE TABLE/VIEW and GRANT commands into a single operation, you ensure that even if the first command succeeded, it will be rolled back if the last command fails.

CREATE SCHEMA falls short of expectations
Although CREATE SCHEMA offers some nice capabilities, Oracle8 doesn't support it very well. Because you can't include a STORAGE clause, you most likely can't create optimal tables with this command. Unless you need the capabilities that it offers, you'll probably get along quite well without ever using it.

Suppose that you had a script with these operations:


If the first CREATE TABLE ran successfully but the second didn't due to disk space, your database would have a CUSTOMER table but no SUPPLIER table. When the disk shortage is corrected, the script can't be used without change because it will abort when it tries to create a CUSTOMER table again. By encapsulating all these commands in a single CREATE SCHEMA transaction, you can correct the problem and restart a script if anything fails; you can do so knowing the database was returned to its original condition.

The syntax of the CREATE SCHEMA command is as follows:

        [CREATE TABLE ...]| [CREATE VIEW ...]| [GRANT ...]

Watch out for non-ANSI syntax
Many CREATE TABLE clauses that you use, such as STORAGE, are Oracle extensions to ANSI SQL and aren't supported in the CREATE SCHEMA command.

Suppose that the SUPPLIER table wasn't created due to an error, but that the CUSTOMER table was created successfully. CREATE SCHEMA wouldn't try to create the view CUSTOMERS_AND_SUPPLIERS or grant permissions. It would drop the CUSTOMER table, thereby returning the database to its original state. This is depicted in the following command:

Used in TGASPER schema (and, of course, run by TGASPER user)
Used to create CUSTOMER and SUPPLIER tables in TGASPER schema (complies with ANSI SQL)
Used to create a view based on CUSTOMER and SUPPLIER tables
Applies appropriate permissions on CUSTOMERS and SUPPLIERS tables

Modifying User Accounts

Like most everything else about your database, user accounts and security properties will inevitably need to be changed. Fortunately, Oracle8 provides the capability to change almost every aspect of a user account after it's created.

Using the ALTER USER Command

Oracle8 provides the ALTER USER command to change attributes associated with a user account. You can issue the ALTER USER command with as many clauses as necessary. You need to specify only the attributes you want to change; all other attributes will remain unchanged.

The format of the ALTER USER command is as follows:

  [QUOTA value | UNLIMITED] ON tablespace] ...
  [PROFILE profile]
  <ROLE [, ROLE]... | ALL [EXCEPT ROLE [, ROLE]...] | NONE >]

Changing user authentication
Theoretically, you could change a user's authentication from internal (entering a userid/password) to external, or vice versa. However, because the userid will typically be different for users being authenticated internally and externally, this is rarely a practical option. You'll usually need to drop the user and recreate his or her account with the proper authentication.

DEFAULT ROLE's limitation
The DEFAULT ROLE clause can't be used to make (as the default) a role that a user hasn't been specifically granted with the relevant GRANT command.

Example of Changing a User's Password

Using ALTER USER requires that you first log into SQL*Plus as a DBA user (such as SYSTEM). The following example shows a user's password being changed:

Indicates that BJONES's user account is being changed
Sets BJONES's password to BJ4236
Forces user to change password the next time she logs in (using new password)

Changing User's Default Tablespace and Quota Example

The following example shows a user's default tablespace being changed and her tablespace quotas changed accordingly:

Changes NSMITH's user account
Changes user's default table-space to USERS3
Denies user any more space on tablespace USERS2 (user's old default table-space)
Allows user 500MB of space on new default tablespace, USERS3 1

Understanding the Results of Changing Quota

Suppose that for the last year, JPOOLE has had a quota limit of 200MB on the USERS tablespace. His segments residing in the USERS tablespace now use around 180MB. If you were to change his quota to 100MB, there would be absolutely no effect on his existing segments-he would continue to use 180MB. He couldn't, however, use any more space.

The effect of changing quota
Although Oracle8 allows you to change a user's quota on any table-space at any time, doing so won't have any effect on space that has already been allocated by a user's segment(s). The only way to change the amount of space already allocated by a user in a tablespace is to copy his or her segment(s) and drop the originals from the data-base.

When a segment needs to allocate an extent (initially or after it exhausts free space in existing extents), Oracle8 checks the respective user's quota against the amount of space he or she is using in the tablespace containing the segment. If allocating another extent for the user won't cause him or her to exceed the quota, Oracle8 will allocate the extent and continue. If not, the transaction will fail and the user will be notified that Oracle8 can't allocate another extent. This is the only time quotas are checked and Oracle8 takes any action based on quotas.

In short, changing a quota affects only future space requests and has no effect on the space a user has already allocated to his segments.

Using the DROP USER Command

During the life of a database you'll inevitably need to remove users from the database. In Oracle8, this is accomplished with the DROP USER command.

Considerations before dropping users
Unfortunately, removing a user from Oracle8 has more implications than removing a user from most operating systems; a segment can't exist without an existing owner. If you must keep the segments in the doomed user's schema, you'll have to keep the schema on the data-base anyway, or move them to another user's schema. If you choose the former, lock the account with ALTER USER to prevent anyone from logging in and posing a security risk. If you want to keep a copy of the segments, you need to use Oracle8's export/import facility.

The format of the DROP USER command is as follows:


Example of Dropping a User Account

The following example shows a user being dropped, along with his objects from the database.

Drops user SCOTT from database
Tells Oracle to drop any tables that SCOTT may have in his schema before actually dropping him from database

If you have any doubt whatsoever about needing segments contained in a schema you're about to drop, consider exporting them to a file or tape before dropping the user. That way you can always reload the data if you need it, but your system won't be bogged down with dead wood.

© Copyright, Macmillan Computer Publishing. All rights reserved.