'; 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
Creating User Accounts
Modifying User Accounts

The necessity of user accounts
Options for authenticating database users
Adding and deleting users from the database
Changing existing users
Managing user storage limits

The Key Purposes of a User Account

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

They provide security so that you can control who has access to what portions of your database and what operations can be performed.
Even if someone is allowed access to data, you may want to audit his or her activities as part of an overall security plan or to help diagnose performance problems by identifying who is holding locks, using system resources, or simply logged into the database.
An Oracle8 account includes a schema wherein all segments (tables, indexes, stored procedures, and so on) owned by the user are stored. The schema name matches the user's userid.

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:

What will the new user's userid be?
Will the user be externally authenticated? If not, you must choose an initial password for the user.
If the user will be creating segments, what tablespace will he or she be placed in by default?
When temporary space is needed for operations such as sorting, which tablespace will be used? In many systems, a tablespace named TEMP will be dedicated for this purpose.
How much space, or quota, will the user be allowed to use on tablespaces in the system?
If the user will be assigned a security profile, what profile will be used?

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

CREATE USER userid IDENTIFIED BY password | EXTERNALLY
        [DEFAULT TABLESPACE tablespace]
        [TEMPORARY TABLESPACE tablespace]
        [QUOTA value | UNLIMITED] ON tablespace] ...
        [PROFILE profile]
        [PASSWORD EXPIRE] [ACCOUNT <LOCK | UNLOCK>]

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.

CREATE USER userid This specifies the userid of the new user you're creating. Using any existing username standards in place at your site is advisable to minimize confusion in your user community. If this user will be externally authenticated, be sure to prefix the userid with the value of the os_authent_prefix parameter (typically, OPS$).
BY password | EXTERNALLY If you aren't using external authentication, you must provide an initial password for the new user. This password should begin with an alphabetical character (a-z) and consist of alphabetical or numeric characters. Be aware that this password will be visible onscreen. You should avoid creating users at a workstation where others could easily see the password(s) you're entering. If you're using external authentication, you must enter EXTERNALLY after the IDENTIFIED clause.

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.

DEFAULT TABLESPACE tablespace If this new user account will be allowed to create segments, be sure to set a default tablespace for where these segments will go. This won't restrict the user from creating segments in other tablespaces; it just provides a default location for storage if the user doesn't specify otherwise. If you don't specify a default tablespace, the default is to place user segments in the SYSTEM tablespace.

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!

TEMPORARY TABLESPACE tablespace In the course of sorting functions, Oracle8 will require some scratch space in which it can create temporary segments until a transaction completes. You should dedicate at least one tablespace to this function. Typically, this tablespace will be named TEMP. If you don't specify a location for temporary segments, Oracle8 defaults to the SYSTEM tablespace.
QUOTA value | UNLIMITED ON tablespace Oracle8's quota system allows you to limit the amount of space a user's segments may use on any given tablespace. By duplicating the QUOTA clause as many times as necessary within the CREATE USER command, you can set up quotas on all tablespaces in which the user will be authorized to store segments. You should generally assign an appropriate quota on the user's default tablespace.
The value parameter is an integer followed by K or M (no space) to denote kilobytes or megabytes. If you don't use K or M, Oracle8 will interpret the integer you provide as bytes, which probably isn't what you intended Oracle8 to interpret. For instance, 40M, 40K, 40 will be interpreted as 40MB, 40KB, and 40 bytes, respectively.
PROFILE profile Oracle8 allows you to create standard security profiles that can be assigned to users. This greatly simplifies and standardizes the security policies to be enforced. You can specify a security profile by adding PROFILE followed by the profile name in the CREATE USER command. Oracle8 provides many new security features that can enhance the security of your Oracle8 database.

SEE ALSO
For a more in-depth discussion of profiles,

PASSWORD EXPIRE Oracle8 allows for the initial password to expire immediately. To do this, add PASSWORD EXPIRE to the end of the CREATE USER command. This forces the user to change the password when he or she first logs in to Oracle8.
ACCOUNT LOCK | UNLOCK In some environments, it's desirable to create accounts for new employees but not allow access (by LOCKing them) until they're actually ready to use them (at which time they will be UNLOCKed). By adding ACCOUNT LOCK to the end of your CREATE USER command, the user account will be created but the user can't log in until you explicitly unlock the account.

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:

QUOTA 300M ON USERS

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:

QUOTA UNLIMITED ON USERS

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:

QUOTA 0 ON USERS

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:

CREATE USER KHEIL IDENTIFIED BY KH1507
DEFAULT TABLESPACE DEV
TEMPORARY TABLESPACE TEMP
QUOTA 0 ON SYSTEM
QUOTA 1024M ON DEV
QUOTA 0 ON PROD
QUOTA 0 ON TOOLS

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:

CREATE TABLE CUSTOMER
CREATE TABLE SUPPLIER
CREATE VIEW
GRANT

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 SCHEMA AUTHORIZATION schema
        [CREATE TABLE ...]| [CREATE VIEW ...]| [GRANT ...]

For CREATE SCHEMA AUTHORIZATION schema, provide the name of the schema where the new segments are to be created. This must be the name of the user now logged into the database. You can't use CREATE SCHEMA with someone else's schema name (or userid), even if you're a DBA.
Use any valid ANSI CREATE TABLE command within a CREATE SCHEMA command. You can repeat as many CREATE TABLE commands as you want within a single CREATE SCHEMA command.

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.

CREATE VIEW Just as with the CREATE TABLE command, you may use any number of valid ANSI CREATE VIEW commands.
GRANT Any valid GRANT commands can be used with 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:

ALTER USER userid [IDENTIFIED BY password | EXTERNALLY]
  [DEFAULT TABLESPACE tablespace]
  [TEMPORARY TABLESPACE tablespace]
  [QUOTA value | UNLIMITED] ON tablespace] ...
  [PROFILE profile]
  [PASSWORD EXPIRE] [ACCOUNT <LOCK | UNLOCK>]
  [DEFAULT ROLE
  <ROLE [, ROLE]... | ALL [EXCEPT ROLE [, ROLE]...] | NONE >]

ALTER USER userid This will be the userid of the user you want to change. You can't rename a user after he or she is created.
BY password | EXTERNALLY You can change the password of a user who isn't using external authentication. This password should begin with an alphabetical character (a-z) and consist of alphabetical or numeric characters. Be aware that this password will be visible onscreen. You should avoid changing passwords at a workstation where others could easily see the password(s) you're entering.

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 TABLESPACE tablespace You can change the default tablespace for a user at any time. Be aware that this won't move any segments already created. This affects only segments that will be created in the future if the user doesn't explicitly identify a tablespace.
TEMPORARY TABLESPACE tablespace The temporary tablespace can be changed for users at any time.
QUOTA value | UNLIMITED ON tablespace User quota values can be changed at any time. Just as in the CREATE USER command, you can repeat the QUOTA clause as many times as necessary. You need to provide a QUOTA clause only for the tablespaces that you want to change the user's quota on.
PROFILE profile You can change a user's security profile at any time.
PASSWORD EXPIRE A user's password can be expired at any time. This should always be done when a user's password is being changed by a DBA, thereby forcing the user to re-enter a password that only he or she knows.
ACCOUNT LOCK | UNLOCK Accounts can be LOCKed or UNLOCKed at any time. Many sites require that when accounts are created, they must be LOCKed until the user is actually ready to use them. Use the ALTER USER command with ACCOUNT UNLOCK to unlock the user's account and allow him or her access to the database.
DEFAULT ROLE You can use the DEFAULT ROLE clause to make one or more roles enabled by default on a user's account. All roles granted may be enabled by default with the ALL keyword and, optionally, exclude one or more roles from being enabled by default by explicitly listing them. By using the NONE keyword with the DEFAULT ROLE clause, you can specify that no roles are enabled by default.

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:

DROP USER userid [CASCADE]

DROP USER userid Supply the userid of the user to be dropped from the database.
CASCADE If you want to remove all the segments owned by the user you're dropping, use the CASCADE option. This tells Oracle8 to first drop all the user's segments before actually dropping the user. Be careful! There's no rolling back this command.

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.