0501-0503

Previous Table of Contents Next

Page 501

Using Quotas

What the profile does for process and resource management, the quota does for disk space management. Users (and developers in particular) can be pack rats when it comes to data. They tend to create tables or other database objects and leave them without ever cleaning up after themselves . Inevitably, a tablespace reaches capacity and sends a user or group of users scrambling to you for more disk space. It has been said that "nature abhors a vacuum ," and in many cases, the user/developer community seems insistent on proving that theorem. Despite admonishments from the DBA, users still do not remove tables created during last year's general ledger problem because the data "might still be needed."

Quotas give you a way to set an upper limit on the amount of disk space a single user can occupy. This limit prevents a single user from occupying 90 percent of a tablespace with a personal table. The database allows only a certain amount of disk space to be allocated to a user before it generates an error message. Other users can continue working normally, but the user in question cannot perform further actions until he or she removes some database objects.

Quotas also enable you to restrict access to certain key tablespaces (such as SYSTEM) to which the users and developers should not have access. Using a quota enables you to choose which tablespaces are accessible to the user/developer equation, thus possibly reducing fragmentation issues.

Enforcing Tablespace Quotas

Tablespace quotas are set in bytes, kilobytes (KB), or megabytes (MB). In general, tablespace quotas are established whenever a new user account is created or amended after the fact. If no quota is given, a user has no privilege to create tables within the database (unless he or she has the RESOURCE system privilege). The syntax for the quota portion of the user creation/modification command follows :

 ... quota 1 M on tablespace users ... 

This quota enables the user to occupy up to 1MB of space on his or her tablespace. You should set the quota at a value small enough to keep the user or developer from filling up the entire tablespace but large enough to allow that same user or developer to do his or her work. This value varies from site to site, but it sometimes can be approximated by totaling all the database objects a user would need copies of and padding it with a small amount of overhead (10 percent to 15 percent).

Assigning UNLIMITED Tablespace Quotas

By assigning a user an UNLIMITED quota, you allow the user to occupy as much room on a tablespace as necessary. The SQL syntax is the same as for a regular quota:

 ... quota unlimited on tablespace users ... 

Page 502

CAUTION

Do not confuse the UNLIMITED tablespace quota with the system privilege UNLIMITED TABLESPACE. (See Chapter 24.) The UNLIMITED tablespace quota grants unlimited write access to a single tablespace; the UNLIMITED TABLESPACE privilege is for the entire database.

If tablespace quotas are enforced, you should use the UNLIMITED tablespace option sparingly. In general, this option is given to the owner of the schema objects on the tablespace on which those objects reside. Additionally, this option is given to MIS and user personnel on designated tablespaces. This enables groups of users who are allowed to add and drop tables to do so, but not on tablespaces that may contain production tables.

User Database Accounts

The section you may have expected to be first in this book occurs nearly last. If this text were intended as strictly a laundry list of SQL syntax, this section might have appeared earlier. As shown throughout this chapter, however, creating user accounts on a database is far more than just logging on and running a script. All things considered , that task is by far one of the easiest .

User account maintenance (creating, modifying, and deleting) typically is performed by the DBA. However, you can assign appropriate privileges to a junior administrator to handle this task. (See Chapter 24.)

Creating User Accounts

To create an account, you connect to the database via Oracle Server*Manager or SQL*Plus and issue the SQL command:

 % sqlplus system/manager SQL> create user cherie identified by scarlett   2>    default tablespace users   3>    temporary tablespace temp   4>    quota 10M on users   5>    profile boss; User created. 

Note that you can use Oracle Enterprise Manager in addition to Server*Manager and SQL*Plus to create and manipulate user accounts. The Security Manager tool within Oracle Enterprise Manager enables you to manage users, privileges, roles, and profiles through a GUI interface.

NOTE

One of the major enhancements of Oracle8 is the capability to accommodate more database users. Oracle8 can support more than 10,000 database users, which is a necessity with today's many applications, such as large-scale data warehouses.

Page 503

Be sure to make a distinction between creating a user for password authentication and creating a user for operating system authentication. The key difference between these two methods is that of the IDENTIFIED BY portion of the SQL command. When using password authentication, IDENTIFIED BY is followed by a password (which is echoed to the screen) that identifies what password a user must enter to gain access to the database. This method differs from the operating system authentication, in which a user is identified externally. This specification is a signal to the database that the user account in question will be using operating system authentication, as this example shows:

 % sqlplus system/manager SQL> create user ops$lance identified externally   2>    default tablespace users   3>    temporary tablespace temp   4>    quota unlimited on users   5>    profile boss; User created. 

Note this important point: The concept of operating system authentication was created with Oracle6, when all accounts had to be prefixed with OPS$ (which designates the account as an operating system authenticated account). Under Oracle7, you can tune the database so that it does not require the OPS$ prefix to authenticate. For user accounts that use a prefix other than OPS$, as described later in this section, you must use IDENTIFIED EXTERNALLY to designate an operating system authenticated account.

If you create an OPS$ account and specify a password by using IDENTIFIED BY, however, that user account may be authenticated in either manner. If a null password is provided from the operating system account of the user, operating system authentication occurs and connects this person to the database. The user also may connect to the database by using the OPS$ username and password, however. Currently, this method is the only one available for dual authentication.

In versions of the Oracle RDBMS later than 7.1, however, the IDENTIFIED BY and EXTERNALLY options are totally separate. The capability to create an account that can be authenticated by both the operating system and a password does not exist in the database for Oracle 7.2 or later versions.

CAUTION

If the INIT.ORA parameter LICENSE_MAX_USERS is set, you cannot create any more user accounts without adjusting the parameter (which requires you to shut down and start up the database). Although this tool is excellent for enforcing named licensing restrictions, be certain that all overhead accounts (SYS, SYSTEM, schema object accounts, non-user administrative accounts, and so on) are accounted for when determining the value for this parameter.

Previous Table of Contents Next