|
In The Rhyme of the Ancient Mariner, the poet uses the phrase "water, water everywhere" to describe his plight. If this phrase were changed to "users, users everywhere" it might describe the plight of many DBAs throughout the
world. With that analogy in mind, see if you can correctly answer the following question.
Users are:
A. Demanding
B. Unreasonable
C. In need of constant attention
D. The reason for the DBA's existence
E. All of the above
The correct answer is E. Yes, users are demanding (most of the time), unreasonable (sometimes), and in need of constant attention (at least sometimes). However, try to keep these attributes in perspective. By nature, users are typically non-technical
entities who do not understand such exotic things as tables, tablespaces, blocks, and buffers. When users are having problems, they react in the manner to which they are most accustomed: they call an expert. When the sink is
backed up, call the plumber; when the car is backfiring, call the mechanic; and when the database is not responding properly or an issue is unclear, call the database administrator.
Like it or not, the title of DBA makes one an expert (at least in the eyes of the user community). In fact, at some sites DBAs mystically possess the ability to diagnose applications, system administration, and network problems (which is usually untrue,
but often the perception).
Many times the last thing a DBA needs is an interruption from a user ("I have a fragmented SYSTEM tablespace, two production tables at MAXEXTENTS, a full production tablespace, and a backup that didn't run properly. What do you mean you want your
password reset?"). But these people are the reason the DBA can cash a monthly paycheck. As Peter Parker (the boy who would be Spider-Man) remarks in an early comic, "With great power comes great responsibility." If anyone could be a DBA,
then everyone would be a DBA.
Administering users is more complex than just having the SYS or SYSTEM password (or a similar DBA-privileged account) and creating an account. You face issues surrounding what system privileges to have (such as CREATE TABLE or CREATE VIEW), what
privileges to have on what database objects, and in systems that provide application-level security (such as Oracle*Financials), what application modules a user should access. Paramount to all these issues is better understanding the needs of the user
community that the DBA is supporting. Before moving on into the semantics of user creation and setup, this section briefly describes how to analyze and meet the needs of users.
To better serve users, you need to understand what users want. In short, they usually want the moon ("I need access to the Corporate General Ledger system") when they sometimes need only a telescope ("I need a copy of the report with the
end-of-month sales totals"). Users often are willing to spend great lengths of time, energy, and effort telling the DBA exactly what they need. But beware this path, grasshopper! More often than not the user has only a limited scope on what is
occurring in the overall system; a DBA should rely on methods other than user request to determine needs. Don't ignore user requests, but take them in context of what they are trying to accomplish.
 What a user wants is not always what he or she needs.
Although the job of the database administrator is basically, as the name implies, administrative, DBAs are often involved in the overall design process. Often, the role is of a consulting type, where the DBA evaluates data modeling in relationships or
works with applications administrators to set up security roles and database access. Although a more detailed discussion of database security occurs in Chapter 16, "Database Security," this section offers a brief discussion of the procedure of
evaluating needs for user roles. Then you proceed onto the syntactical elements of user management.
A DBA might pose the following questions (either rhetorically or physically) when creating a role and/or granting user access:
- What does the user want?
- What does the user need?
- Is someone currently set up like this?
- What is the minimum level of access that the user should have to do his or her job?
- What is the maximum level of access the user should reasonably have?
- What constraints (technical or political) exist in setting up this user?
Assume for a moment that a staff accountant approached the DBA, claiming that she required access to the sales database. Assume, also, that the corporation was a conglomerate of several different companies, each running their own databases. The
accountant wants access to a database instance that does not contain any of her company's data. However, she claims that the information is for a corporate-level project on which she is working. Certainly the DBA has the proper level of privileges (the
power, as it were) to add the user. Should the DBA do this (the responsibility part of the equation)?
As mentioned earlier, a user generally tells the DBA exactly what he or she needs; take this request with a grain of salt. These perceptions are important, because they do help shape and affect what the end, and overall, result will be. However, the DBA
should complete a full and overall analysis (generally in cooperation with other technical and non-technical personnel) of the situation before logging into the database and complying.
Note, however, that the DBA should pay attention to what the user says (or at least give the illusion of paying attention) for a couple of reasons. First of all, the DBA may learn something. Often DBAs can become embroiled in the daily concerns of
tuning the Shared Pool or making certain that the SORT_AREA_SIZE is properly set. Users work in their applications all day long and can often add valuable insight that the DBA, on his or her own, may not think of. In addition, listening is important, if
for no other reason, to make the user feel valuable and to enhance future working relationships. It is important to stress that any organization is a team, and DBAs are not the fuhrers. Users and DBAs are both cogs (albeit cogs in different wheels) of the
same corporate machine.
This question is often trickier than "What does the user want?" (or at least trickier to answer properly). At this level, the DBA usually needs to consult with an applications administrator, manager, or someone who understands the application
level of the system. In a few sites, the DBAs also serve as the applications administrators.
A user may need access to the database to run reports, view data, modify existing data, create new database rows, or just have a copy of a report (as in the earlier example). Try and identify what the user is shooting for and what means are required to
accomplish that goal. Also, consider if options are available to accomplish the task without granting access. This is not to suggest that a Spanish Inquisition be conducted every time a user needs access. However, every single Oracle license costs the
organization a hefty sum of cash that no one should fault the DBA for trying to save. A final factor to consider is whether or not the access needs are on-going. If a user needs a copy of September's General Ledger, then it can be done (under most normal
circumstances) by another user (or even the DBA) and passed on. However, if the same user is going to need the report every month, the same access usually would not be withheld.
Do not be afraid to grant a user temporary access to data unless some specific corporate policy prohibits this practice. As a rule of thumb, if it is going to take more time to produce the data than to create, and
later drop, the user account, then issue the temporary account.
A major time-saver in many organizations is to be able to set up a user like an existing user. This step limits the analysis phase to determining what the user needs and then creating the account. In these situations, you generally just need to
coordinate with the appropriate applications administrator or manager to determine if this setup is correct ("Do you really want Katie to be able to do all the things Carlton can do?"). If so, then the DBA can create the account without a
tremendous amount of further effort. If the setup is not correct, then you need to make determinations as with any new user situation.
Sometimes DBAs are perceived as minimalists by their nature. Granting user access qualifies as one of those times. In general, the level of access DBAs give to the database in terms of system and object-level privileges, as well as what applications
modules the user can access, should be only what the user needs to do the job. (For more information on security, see Chapter 16.) Minimal access doesn't mean that users are incompetent, malicious people who will take advantage of every situation. However,
mistakes can (and will) happen.
Take the case of Richard, a power user of an OLTP system. Richard is always making modifications to data in many tables. Because he has a working knowledge of SQL, Richard has access to the database via SQL*Plus. Instead of granting access to the
specific tables, however, the DBA takes a shortcut and grants the following privileges: INSERT ANY TABLE, SELECT ANY TABLE, DELETE ANY TABLE, UPDATE ANY TABLE. Shortly thereafter, Richard finds a book on Oracle (a book like this one) at his local bookstore
and learns about the SYS-owned DBA-views. Richard begins some experimenting and one day issues the following command to see the result:
delete from sys.dba_users;
If committed, the command can cause serious problems for the DBA and everyone else using the database. This situation could have been avoided had the user account been appropriately implemented.
 Be very careful granting privileges that are part of the ANY groups (SELECT ANY TABLE, DROP ANY TABLE, etc.) as they give users a very high level of database access (more discussion on this topic in Chapter
16). As long as these privileges remain in effect, users can perform the action in question with unlimited access.
At the opposite end of the spectrum is the concept of maximization. Where the idea of "minimum level of access" determines what a user must have to do his or her job, the idea of "maximum level of access" determines the cut-off
point. For example, if a corporate policy prohibits users from changing data in certain application tables (except for a certain level, such as manager or MIS, of which the user is not a member) then no user should be granted access to perform this task.
This necessary evil must be defined in all user settings. In some settings, no upper limit may exist; users may be permitted to have any privilege short of DBA. In other environments the data may be extremely sensitive and require investigation to have
access. To understand these limitations, the DBA should have an in-depth knowledge of the applications systems and the rules that drive it, or have access to someone who does.
At times, the DBA may not be able to do what the user asks. Perhaps granting a user access to certain tables would inadvertently give him or her access to change data that should never be changed. On the other hand, perhaps access is permissible but the
comptroller does not want anyone with this access ("Not in my backyard!"). Whatever the reason, this problem falls within the realm of constraints.
Constraints (not to be confused with database constraints which were covered in earlier chapters) take the shape of technical and political constraints. Under technical constraints, some underlying reason prevents setting up a user. Perhaps the username
conflicts with an Oracle reserved word. Here, the only option is to determine another method (a workaround), perhaps giving the user another name. The other side of the constraint house is political. As mentioned earlier, no real reason exists to prevent
something from being done except one or more forces in the company do not want it done. In this case, the DBA can override the users (not the best way to win friends and influence people, and upper management may then override the DBA) or work on a
compromise. Of all constraints, political are by far the worst.
Although constraints may not always be a problem, they are issues you cannot ignore when setting up and managing user accounts.
As a rule, databases do not have an open door policy that allows everyone access (of course, there are exceptions). Therefore, a database needs a way to authenticate the user, determining his or her identity and making certain that he or she has
authorized access. In general, a database uses one of two proven methods: password authentication and operating system authentication.
The concept behind password authentication is the same as the traditional password method used on other databases, operating systems, network servers, and the like. Under this concept, the database (in this case Oracle) issues a challenge
("Password:") followed by a prompt. Each distinct user ID has an alphanumeric string associated with it that the user must enter correctly to gain database access. For example, assume a user account named "CHERIE" with a password of
"SCARLETT":
% sqlplus
Enter user-name: cherie
Enter password: ........
SQL> show user
user is ÒCHERIEÓ
Note in the preceding example that the password is not echoed to the screen. This important safety feature enables users (or even a DBA) to enter a password and not worry about others staring over his or her shoulder.
At the option of the DBA, the username and password may be passed to the application (such as SQL*Plus) on the command line. If you take this step, the password is echoed to the screen:
% sqlplus cherie/scarlett
SQL> show user
user is ÒCHERIEÓ
In certain organizations, a person may be admitted entry if his identity is confirmed by a known person. This same tenet is true for operating system authentication; the user is allowed access to the database if he or she has a valid operating system
account that shares the same username as the database account.
For example, on a UNIX-based system, a user may be set up with a user account named "LANCE" within UNIX (usually done by the UNIX system administrator). The DBA, in turn, creates an account called "OPS$LANCE" within the database.
When Lance connects to UNIX, he need only pass nulls to the database's query for a password to gain access. The database extracts the username from UNIX (LANCE) and checks to see if an operating system authenticated account exists within the database
(OPS$LANCE). If the account is found, then the user is granted access; if the account is not found, the request for access is denied.
% sqlplus /
SQL> show user
User is ÒOPS$LANCEÓ
By passing a slash (/) from the command line, the DBA or user invokes this type of login. Simply put, a slash causes a null to be passed as both the username and the password. This type of authentication is enough to allow access to the database.
After deciding on what type of authentication method to use, the DBA is still responsible for making determinations regarding how to set up the user within the database. Just like creating a UNIX user requires the UNIX system administrator to define
certain things such as the user's shell and home directory, similar things need to be defined in the database environment.
This process is known as setting up the user configuration. Although determining the number and types of privileges within the database can encompass a whole separate analysis process, the setup of the user configuration is relatively straightforward.
(For more details on security, see Chapter 16.) User configuration has three basic elements:
- Profiles
- Default tablespace
- Temporary tablespace
Each of these elements serve a specific function and are discussed in more detail in the sections that follow.
The database profile is Oracle's attempt to enable the DBA to exercise some method of resource management upon the database. According to the Oracle7 Server Administrator's Guide, a profile is "a named set of resource limits." To
better understand this term, take a step back and try to understand where it came from.
Most of the power-user tools that exist under Oracle7 were not around in Oracle6. To a certain extent, many sites were still trapped in a mainframe mode that precluded the type of access that is now considered common. Even SQL*Plus was considered
primarily a developer's tool and was not something available to users. Then, as they say, someone let the jinni out of the bottle. Products were introduced that allowed for client/server access to the database data using graphical tools, and these types of
tools became commonplace. However, the tools presented a problem for the DBA: how to restrict them.
Because Oracle6 used a rule-based optimizer, a change in the table order of a FROM clause or the statement order of a WHERE clause could double or triple (or more) the amount of time required to run a query. Most users were unwilling (or unable) to
learn how to properly build queries, and thus were often guilty of releasing queries that could bring a production system to its knees.
Oracle7 introduced profiles, which were part of two tools to help the DBA administer an RDBMS in an ad-hoc environment (the other tool is the cost-based optimizer). Using profiles, the DBA can designate such things as how much CPU time a user can
receive during a single database session or per SQL statement, how much idle time a user can accumulate, or how much time a user can be connected to the database. The DBA gives each profile a name (such as "CLERK", "MANAGER",
"ACCOUNTANT", etc.), and certain fixed resource limits are associated with the name. This profile is then assigned to a user, who then must function within the designated profile limits.
A user with no profile assigned receives, by default, a profile named DEFAULT. The DEFAULT profile is mandatory and must be present within the database. A more complete discussion of profiles is included later in this chapter.
If you conceptually think of the Oracle database as its own operating system (as some academics have argued), then you would probably consider the default tablespace the "home directory" of the database world. As shown in earlier
chapters of this book, you can create a table, index, or other database object using the TABLESPACE option:
CREATE TABLE order
(
orderno NUMBER(6),
partno NUMBER(10),
qty NUMBER(3),
cost NUMBER(7,2)
)
TABLESPACE users;
In the preceding example, the table ORDER is created in the USERS tablespace. However, if no tablespace is designated, as in the following example, then the table is created in the tablespace designated as that user's default tablespace.
CREATE TABLE order
(
orderno NUMBER(6),
partno NUMBER(10),
qty NUMBER(3),
cost NUMBER(7,2)
);
The default tablespace, simply, is the tablespace where a database object is created if no other tablespace is specified.
If the DBA specifies no default tablespace, a user's default tablespace is the SYSTEM tablespace. This can lead to SYSTEM tablespace, which contains the data dictionary and other information crucial to database
operation, becoming fragmented or full. In some cases, the only way to correct problems is to re-create the entire database.
The temporary tablespace is also a tablespace, but it is different in function from the default tablespace. Let's continue the analogy of the database as an operating system. If the default tablespace is the home directory, then the temporary
tablespace is the UNIX /tmp directory.
Fundamentally, the temporary tablespace functions as a "holding area" for SQL commands that require making sorts to the disk (as opposed to sorts in memory). Common examples of this type of operation are GROUP BY, SORT BY, and UNION ALL. When
these types of operations are performed, the Oracle RDBMS takes contiguous extents on the temporary tablespace (segments) and uses this space to perform the required sorting and/or joining operations. After the operation is completed, the database releases
the segments held within the tablespace.
Although a temporary tablespace is not required to be on a separate tablespace from other database objects, it is recommended. Not only does a separate tablespace reduce contention, but it also avoids fragmentation (for details on both, see Chapter 15,
"Tuning and Optimizing"). Separation also helps keep the tablespace from reaching capacity unexpectedly. Any user who does not have a temporary tablespace set by the DBA has a default temporary tablespace of SYSTEM.
As discussed earlier in this chapter, Oracle7 provides more than the ability to create user accounts within the database and constantly monitor their activity—it provides the ability to restrict activity by managing resources. To do this, Oracle7
offers two distinct features: profiles and tablespace quotas.
The primary difference between these two features is the type of resources they manage: profiles control process/memory utilization, and quotas control disk space. When implemented effectively, both features can help curtail things such as rampant,
runaway queries and excessively large, unnecessary tables. Using these features, the DBA has a proactive tool to help efficiently maintain the database.
As mentioned earlier in this chapter, profiles control the amount of resources a user can have. Although a list of profile resources is given below, it is important to note that you don't need to specify every profile resource in every
profile. Any profile resource the DBA does not specifically set has the value of DEFAULT, corresponding to the value of the DEFAULT profile.
- SESSIONS_PER_USER
- CPU_PER_SESSION
- CPU_PER_CALL
- LOGICAL_READS_PER_SESSION
- LOGICAL_READS_PER_CALL
- IDLE_TIME
- CONNECT_TIME
- PRIVATE_SGA_PER_SESSION
- COMPOSITE_LIMIT
The database does not enforce the values of a profile unless the parameter RESOURCE_LIMIT is set in the INIT.ORA parameter file. This value is FALSE by default, meaning that no profiles are enforced; the DBA should set the value to TRUE if profiles are
desired. In the event that a database cannot be restarted (using shutdown and startup) and profiles are needed, issue the following SQL command from Oracle Server*Manager or SQL*Plus:
alter system set resource_limit = true;
As with creating users, defining a profile is more complex than just issuing a SQL command to create it. Each individual profile is a combination of one or more resources that the database is instructed to manage. Many of these resources contain the
value DEFAULT, which can change depending on the value of the DEFAULT profile, or UNLIMITED, which places no upper limits on the resource.
Just as important as knowing how to create the profile, however, is knowing what to create. For example, a cost accountant and a comptroller may both work out of the accounting group; however, the comptroller may work an additional three to four hours a
night above what the cost accountant works. Therefore, placing a limit of eight hours on the total connect time is not sufficient for the comptroller. In this case, you need to make the overall connect time larger (12 hours) or give the associates separate
profiles. You need to understand the ramifications of the profiles and how they will impact the jobs of each user class before you implement the profiles.
The various profile resources for which limits can be set are described briefly in the following sections.
SESSIONS_PER_USER
The setting for SESSIONS_PER_USER is used to determine the maximum number of sessions (connections to the database) a user can have simultaneously. If a user has reached the limit set in the SESSIONS_PER_USER resource of his or her profile, then the
next login (and any subsequent ones) produce an error condition.
For example, if a user with a SESSIONS_PER_USER of 2 pulls up an application menu via SQL*Forms and is running a report via Oracle*Reports, then the user has reached his or her limit. If that user attempts to create another session via SQL*Plus (or any
other application), the database denies the connection until one of the other connections is terminated.
Be careful when dealing with Oracle CDE tools (such as Oracle*Forms). In many cases, if a tool makes a call to another tool, another connection is established. For example, an Oracle*Forms application that
calls another Oracle*Forms application that calls an Oracle*Reports report is a total of three connections (one for each tool) and not a single connection.
CPU_PER_SESSION
Each query a user issues consumes an amount of CPU time, which varies dependent upon the query. By setting this resource item, the DBA limits the amount of CPU time a user can consume from a single database session. After reaching the CPU limit, the
user can perform no further activity in that session. The user must disconnect from the database and then reconnect to reset this CPU accumulator.
The value of this parameter represents the total amount of CPU time (in minutes) that a user can consume during a single database connection.
CPU_PER_CALL
This resource, like CPU_PER_SESSION, represents the total amount of CPU time (in minutes) available to the user. However, this resource restricts the user on a per-call (SQL statement) basis rather than a per-session basis. Whenever a SQL statement
reaches its limit, it ends with an error condition. Unlike CPU_PER_SESSION, however, the user has no need to disconnect from the database. When using CPU_PER_CALL, the user is free to issue another query as long as it does not exceed the total amount of
time specified in CPU_PER_CALL.
LOGICAL_READS_PER_SESSION
Like CPU_PER_SESSION, this resource element is responsible for determining how much activity can take place during a given database session. In this case, the value is the total number of logical reads (in database blocks) that can be performed in a
given session. If the LOGICAL_READS_PER_SESSION is exceeded, the user can still continue to function as long as he or she does not perform actions that cause reads from the database to be performed (such as a query).
To reset, the user must disconnect from the database and/or establish a new database connection.
LOGICAL_READS_PER_CALL
What CPU_PER_SESSION is to LOGICAL_READS_PER_SESSION, CPU_PER_CALL is to LOGICAL_READS_PER_CALL. The value of this parameter restricts the number of database blocks that can be read during a single CPU call (SQL statement). If the number of blocks that
the database attempts to logically read exceeds the limit set, the operation is abandoned. The user may issue another SQL statement and have no problems unless the logical reads in this statement exceed the value.
IDLE_TIME
Many UNIX systems have a so-called idle demon, which terminates user processes that exceed a certain amount of inactive time. The IDLE_TIME resource is an attempt by Oracle to implement such a technology at the database level.
In essence, a system (in this case) database is considered idle when it has had no activity within a certain period of time. This activity may consist of a user typing information at the keyboard or running a query. By using the IDLE_TIME resource, the
DBA is able to designate how much time (in minutes) a user may allow a database connection to sit idle before terminating the connection.
 A database that is processing a long running query is not considered idle.
A terminated connection to the database may not be readily obvious to the user, because the resource does not terminate any applications. The user may not realize that the database connection has been terminated until the next time he or she
attempts to perform an operation (such as a query).
For example, take a user who has been sitting idle in SQL*Plus for two hours. Assuming that an IDLE_TIME value of 60 has been selected (one hour), the user's connection to the database is broken after the first hour. However, the user still sees
SQL*Plus, and not until another hour passes, when he or she attempts to issue a query, does an error message inform the user of the terminated connection.
CONNECT_TIME
Limiting the amount of time for which a user can be connected to the database can sometimes be advantageous. Unlike idle time, which measures how much time a user spends performing no actions, the CONNECT_TIME resource is compared against the total
amount of time the user is connected to the database. The CONNECT_TIME resource, like IDLE_TIME, is set in minutes and terminates the database connection after that limit is exceeded. This resource discriminates equally against active and idle connections.
Like IDLE_TIME, the CONNECT_TIME resource terminates only the database connection and not the applications themselves. However, any query running when the CONNECT_TIME is exceeded returns with an error message.
PRIVATE_SGA_PER_SESSION
Earlier chapters in this book described the composition of the Oracle SGA. This parameter limits the maximum size of the private SGA/SQL area for the user. The value of this parameter identifies, in database blocks, how large a user's private SQL area
can be. This resource limit can be of significant importance on systems where memory is at a premium and the DBA and system administrator are working to reduce "paging" and "swapping."
Leaving this value at UNLIMITED (usually DEFAULT) is best unless circumstances warrant otherwise. Make sure the private SQL area is not too small.
COMPOSITE_LIMIT
One of the most complex and advanced resource elements is the COMPOSITE_LIMIT. Using the COMPOSITE_LIMIT, the DBA can set an overall resource limit that is a composite (as opposed to explicit) resource limit. Under this configuration, resource elements
are weighted based on values called resource costs. These resource costs form a cumulative cost based on all resource elements. This cost enables the DBA to determine which resource items are more important than others when setting resource limits.
Only the following resource elements are usable when determining a resource cost:
- CPU_PER_SESSION
- LOGICAL_READS_PER_SESSION
- CONNECT_TIME
- PRIVATE_SGA
If an item has a resource cost of 0, then that resource has no cost. However, assuming that a value other than 0 exists, the DBA can set values to the resource items using the ALTER RESOURCE COST command. The syntax for this command is
alter resource cost connect_time 10;
In this example, each connection minute costs the user 10 points against the overall composite limit. Whenever the sum of the composite limit exceeds the amount set, the database connection terminates.
For example, assume the following resource costs:
- CPU_PER_SESSION 1
- CONNECT_TIME 0
- LOGICAL_READS_PER_SESSION 50
- PRIVATE_SGA_PER_SESSION 10
Now, assume the following composite limit:
The user is allowed any combination of resources that do not exceed the 15,000 COMPOSITE_LIMIT the DBA set. In this case, the user can have 15,000 CPU minutes (CPU_PER_SESSION) or 1,500 (1,500x10 = 15,000) blocks in his or her private SQL area
(PRIVATE_SGA_PER_SESSION). However, the user may also have, for example, only 7,500 CPU minutes and 150 logical reads (LOGICAL_READS_PER_SESSION: 50x150 = 7,500) for a total of 15,000. The session terminates when any combination of resources triggers the
limit.
Please note that the CONNECT_TIME is set to 0, which does not count against the overall COMPOSITE_LIMIT.
You may have both composite and explicit limits. Take the following example:
- IDLE_TIME 180
- CONNECT_TIME 600
- CPU_PER_CALL 750
- COMPOSITE_LIMIT 10,000
In this example, the profile causes session termination if the amount of idle time exceeds 180 minutes, the total connect time for a session exceeds 600 minutes, the amount of CPU time taken to execute a single SQL statement takes more than 750 CPU
minutes, or the composite resource limit exceeds 10,000. In this case, the COMPOSITE_LIMIT is used as the termination point as long as the IDLE_TIME, CONNECT_TIME, or CPU_PER_CALL values are not exceeded. If they are, then the session disconnects despite
the value of the COMPOSITE_LIMIT.
After a DBA has properly defined a profile to suit the needs of the overall database environment, the profiles need to be created. Any user (not necessarily the DBA) with adequate database privileges can create the profiles via SQL through the Oracle
Server*Manager or SQL*Plus.
In the following example, the DBA creates a profile named "BOSS":
% sqlplus system/manager
SQL> create profile boss limit
2> idle_time 30
3> cpu_per_call 600
4> logical_reads_per_session unlimited
5> composite_limit 7500;
Profile created.
This profile is restricted by 30 minutes of idle time, 600 minutes of CPU time per call, or an overall composite limit of 7,500. The LOGICAL_READS_PER_SESSION is set to an UNLIMITED amount. All other resource values are set to DEFAULT. Any user the DBA
associates with this profile is bound by these constraints.
As with most SQL commands, an ALTER command provides the variation on the CREATE command with which to make changes. The profiles are no different, and you may change any resource item in a profile using this command, as the following example shows:
% sqlplus system/manager
SQL> alter profile boss limit
2> sessions_per_user 3
3> composite_limit default
4> cpu_per_call unlimited;
Profile altered.
The resource SESSIONS_PER_USER, which was previously DEFAULT, is now set to 3. Deciding to go only with implicit profiles, the DBA also sets the COMPOSITE_LIMIT back to DEFAULT and gives the BOSS profile UNLIMITED CPU per call. These changes become
effective for all users assigned the BOSS profile.
As the roles of users evolve, you may need to remove profiles from the database. This is done, simply and effectively, by issuing the DROP PROFILE command:
% sqlplus system/manager
SQL> drop profile boss;
Profile dropped.
At this time, the BOSS profile is no longer available for use. If the profile is currently assigned to an existing user, an error condition occurs. You can override the error by using the CASCADE option, which will assign the DEFAULT profile to these
users:
% sqlplus system/manager
SQL> drop profile boss cascade;
Profile dropped.
As discussed earlier in this chapter, the DEFAULT profile is a standard part of each database. The values of each of the resource items in the DEFAULT are the values that all other profiles, by default, use unless another value is set in them. DEFAULT
profile values are UNLIMITED unless otherwise changed.
You can modify the DEFAULT profile, just as you do any other profile, but you cannot drop or remove it. This profile must exist.
What the profile does for process and resource management, the quota does for disk space management. Often, 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 the DBA 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 GL problem because the data "might still be needed."
Quotas provide the DBA with a way to set an upper limit on the amount of disk space that a single user can occupy. This limit prevents a single user from occupying 90% 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.
The use of quotas also allows the DBA to restrict access to certain key tablespaces (such as SYSTEM) to which the users and developers should not have access. Using a quota allows the DBA to choose which tablespaces are accessible to the user/developer
equation, thus possibly reducing fragmentation issues.
Tablespace quotas are set in bytes, kilobytes (K), or megabytes (M). 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 is as follows:
...
quota 1 M on tablespace users
...
This quota enables the user to occupy up to a single megabyte of space on the users tablespace. The DBA should set the quota at a value that is 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 will vary from site to site, but it can sometimes 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).
By assigning a user an UNLIMITED quota, the DBA allows the user to occupy as much room on a tablespace as necessary. The SQL syntax is the same as it is for a regular quota:
...
quota unlimited on tablespace users
...
 Do not confuse the UNLIMITED tablespace quota with the system privilege UNLIMITED TABLESPACE (see Chapter 16). 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/user personnel on designated tablespaces. This enables groups of users who are allowed to add/drop tables to do so, but not on tablespaces that can contain production tables.
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, then this section may have appeared earlier. However, as shown throughout the course of this
chapter, creating user accounts on a database is far more than just logging in and running a script. All things considered, that task is by far one of the easiest.
User account maintenance (creating, modifying, deleting) is typically done by the DBA. However, the DBA may assign appropriate privileges to a junior administrator to handle this task (see Chapter 16).
To create an account, the DBA connects to the database via Oracle Server*Manager or SQL*Plus and issues 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.
Please 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, where 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.
Make note of this important point. The concept of operating system authentication was first created with Oracle6, when all accounts had to be prefixed with the prefix "OPS$" (which designates the account as an operating system authenticated
account). Under Oracle7, the DBA may tune the database so 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, IDENTIFIED EXTERNALLY must be
used to designate an operating system authenticated account.
If an "OPS$" account is created, however, and a password is specified using IDENTIFIED BY, then that user account may be authenticated in either manner. If a null password is provided from the operating system account of the user, then
operating system authentication occurs and connects this person to the database. However, the user may also connect to the database using the "OPS$" username and password. Currently, this method is the only one available for dual authentication.
In versions of the Oracle RDBMS that are 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.
If the INIT.ORA parameter LICENSE_MAX_USERS is set, the DBA cannot create any more user accounts without adjusting the parameter (which requires a shutdown and startup of 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, etc.) are accounted for when determining the value for this parameter.
Similar to modifying profiles, you can modify user accounts 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.
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, the DBA 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 the preceding 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. The DBA can still access the objects.
One of the most common tasks users ask administrators (DBA or system administrator) is to reset user passwords. Quite often, a user of the HelpDesk contacts the DBA with this request. You accomplish this task 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 the account and ask the DBA to do it.
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, the DBA can designate a prefix other than "OPS$" for operating system authenticated accounts. For example, using the following setting allows a null to be the operating system authentication prefix:
os_authent_prefix=ÒÓ
This setting allows the same account name at both the operating system and 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, then client connections must use password authentication. The
DBA should consider the sensitivity of the database information and the security of the network before setting up this type of authentication.
To use operating system authentication with SQL*Menu 5.0, the value of REMOTE_OS_AUTHENT in the INIT.ORA parameter file must be set to TRUE.
Aside from the day-to-day tasks of creating end user accounts, the DBA should evaluate a few special account considerations. Although you may not encounter these issues in every single site (each site and environment are unique), give them some thought
during early phases of database setup.
The Oracle RDBMS comes equipped with three accounts for Oracle database administration: SYS, SYSTEM, and internal. However, none of these accounts are equipped for day-to-day DBA operations.
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 not using 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 this user owns. Likewise, you should use SYSTEM only when installing additional software packages that require this user account. The
internal connections are dangerous in that they give the DBA the ability to shut down the database and they also give unrestricted database access as SYS.
The point to remember with these accounts is that accidents can happen anywhere. During a late night when the DBA is working, 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 are flexible enough to give you a choice between operating system authentication or password authentication (they allow only password authentication). In addition, none of the accounts are set up to enable the
creation of temporary, ad-hoc tables that the DBA may need. To this end, many sites propose a generic DBA account.
This account is set up as a user other than SYS or SYSTEM with full DBA privileges. The account generally is used by the DBA or DBAs, 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.
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 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, an operating system account usually owns all programs created for the system. Any developer who wants to make changes to the programs (forms, reports, etc.) needs to have the password to the operating system applications
administrator account. A database account (which is generally password-authenticated) owns the database objects. You make any changes to database objects using this account, and the password usually is known only to the DBA and the applications
administrator over the applications system. In general, when setting up accounts of this nature, 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 (like dropping or truncating a production table).
Creating a user account for the Oracle RDBMS is one of the most common activities a DBA undertakes. During most business days, the DBA receives a user request of some type, such as creating an account, modifying an account, deleting an account, or
resetting a password. However, managing users requires more than simply logging on as the DBA and entering a command.
First and foremost, you must understand the requirements of the users. If you don't, you may fail in properly setting up the user account. You also must consider things such as authentication methods (password vs. operating system) and user
configuration methods (profiles and quotas), as well as basic user creation options (profiles, default tablespace, and temporary tablespace).
Only after the DBA understands all the elements surrounding creation of a user should he or she proceed with the user creation. To err on the side of prudence when creating users is to err on the proper side.
| 
