3.1 The User Interface: User Versus Schema
In May, 1998 at the Mid-Atlantic Association of Oracle Professionals' spring conference, one of the sessions played a game of trivia during which many questions about Oracle were asked. One of the "stumper" questions was this: "What was the forerunner of the SQL*Plus language called?" The answer, in case you ever end up in a trivia game yourself, is UFI User Friendly Interface. UFI was an early SQL command interpreter. The DBA would use UFI to create users much as they would use SQL today. Users would log in to the database via UFI. The DBA could also give the user enough privileges to create tables and other objects. SQL has since become the standard language for manipulating data in modern relational databases.
Just as the SQL name has changed over time, so has other nomenclature . For example, let's look next at the changes in the terms "user" versus "schema."
3.1.1 The Schema Concept
Oracle7 introduced the schema concept. Like the "instance" vs. "database" terms we discussed in Chapter 2, "user" and "schema" are frequently used interchangeably. A user is equivalent to a computer account. The DBA will create a user account, assign a password, and define a default working tablespace, a temporary sort area, and quota if the user is to be allowed to create any database objects.
Users can log in to the database and perform work. They can create and own objects such as views, tables, and stored programs. In general, any object a user creates is considered to be owned by that user. However, there are exceptions to this general rule , such as a user who is granted system privileges that allow him or her to create objects on behalf of another user.
From an Oracle perspective, the schema refers to all of the objects owned by a user. There is a CREATE SCHEMA statement, but on closer examination, you will find that the schema name used in this statement must be the same as the Oracle username.
The CREATE SCHEMA statement is used to create all user objects at one time in a single statement. But it allows only three operations:
If any part of the statement fails, the entire statement fails. Since there are many other operations, such as creating constraints, primary and foreign keys, indexes, and the like that cannot be done with the CREATE SCHEMA statement, the use of this statement is very limited.
| || |
In commands where the term "schema," "user," or "owner" is included, the oracle username is meant .
Security within Oracle begins with the concept that non-public objects are owned by the creator of those objects. Only the owner of an object or a user with DBA-type privileges can give access privileges to other users to enable them to interact with the objects. Careful consideration should be given before privileged access is granted to any user within the database system to any object.
3.1.2 About Quotas
In the last section, we mentioned that a quota had to be granted on a tablespace for a user to be able to create objects in a database. Quotas are used to control the amount of space a user will be allowed to "fill up" in the database. Quotas are one way you can monitor and control the allocation of resources in your development database. If you decide that you do not need to monitor the amount of space an application is using, you can set the quota to UNLIMITED for the tablespaces in which the user will be creating objects.
At one facility we're familiar with, the application manager requested that his developers be given fairly restrictive quotas on his application's tablespaces and no quota on the schema's default tablespace. He did this to force the developers to explicitly name the tablespace to which each created object would go. Since this was a conversion of an existing data warehouse over to an Oracle database, the manager also wanted to be able to trace the growth of the application data during conversion so he could gauge the size of the actual production data.
The bad news is that the day the developers exceeded their quotas on all of their tablespaces happened to be the day that both the application manager and the application DBA were out of the office on vacation. The decision made by the alternate DBA was to just go ahead and raise the quotas to UNLIMITED so the developers could continue working and to let the manager and application DBA address any issues when they both got back to the office.
Although the developers were temporarily impacted by not being able to continue working until their tablespace quotas were increased, the quota restrictions the manager imposed on the developers made sense for two reasons. The developers were forced to code more carefully to ensure that the proper tablespaces were used for each object, and the amount of space used for the objects could be more carefully monitored to help size the application for production.
In a production system, the use and amount of quota assigned to each application should be very carefully evaluated and monitored. In many cases, the decision for production has been made to allow the applications to grow as needed and to set quotas to UNLIMITED across the board.