Chapter 9 *

Managing Database Usage *

Managing Users *

Creating New Database Users *

Exercises *

Altering and Dropping Existing Users *

Exercises *

Monitoring Information About Existing Users *

Exercises *

Terminating User Sessions *

Exercises *

Managing Resource Usage *

Understanding Oracle Resource Usage *

Enabling Resource Limits *

Assigning Individual Resource Limits *

Resource Costs and Composite Limits *

Exercises *

Managing Profiles Created for the System *

Exercises *

Creating and Assigning User Profiles *

Exercises *

Managing Database Access and Roles *

Granting and Controlling System Privileges *

analyze any *

audit any *

Exercises *

Object Privileges Explained *

Exercises *

Creating and Controlling Roles *

Administering Privileges and Roles *

Open to the Public *

Exercises *

Auditing the Database *

Determining the Need for Auditing *

Exercises *

Tracking Statement- and System-Level Access to Data Objects *

Exercises *

Understanding Audit Results with the Data Dictionary *

Exercises *

Managing Audit Information *

Exercises *

Chapter Summary *

Two-Minute Drill *

Chapter 9

Managing Database Usage

In this chapter, you will understand and demonstrate knowledge in the following areas:

This chapter focuses on the functionality Oracle provides in order to limit database access. There are several different aspects to limiting database usage. In many larger organizations, the DBA may find that security is handled by a security administrator—in which case the functionality provided by Oracle in this area may not be handled by the DBA at all. Although in most cases the DBA is not also the developer of database applications, as the resident expert on Oracle software, it helps the DBA to familiarize himself or herself with this subject in order to better manage the Oracle database. Bear in mind that this discussion will use the terms DBA and security administrator interchangeably, and that the main reason it is covered here is that there will be questions about security on the OCP Exam 2. Approximately 27 percent of test content on this exam focuses on database security.

Managing Users

In this section, you will cover the following topics related to managing users:

The management of users in the Oracle database consists of many activities. Usage of a database begins with the creation of new users. Once users are created, their identification methods and default database usage can be altered by the DBA or security administrator in many ways. Information about active sessions run by users can also be monitored, and those sessions can even be killed by the DBA should the need arise. In short, there are many aspects of user management that the DBA or security administrator can control with the Oracle database.

Creating New Database Users

One of the primary tasks early on in the creation of a new database is adding new users. However, user creation is an ongoing task in the Oracle architecture. As users enter and leave the organization, so too must the DBA keep track of access to the database granted to those users. Creation of news users in the Oracle database is handled with the create user statement.

This statement highlights several items of information that comprise the syntax and semantics of user creation. The first aspect of user creation is the name of the user itself. There are few constraints on the name assigned to users in the Oracle architecture. The first constraint is that if the DBA is using operating system authentication for allowing users to access the database, then the usernames should be preceded by default with ops$. In no other case is it recommended that a username contain a nonalphanumeric character, although both _ and # are permitted characters in usernames. The name should also start with a letter. On single-byte character sets, the name can be one to 30 characters long, while on multibyte character sets, the name of a user must be limited to 30 bytes. In addition, the name should contain one single-byte character according to Oracle recommendations. The username is not case sensitive, and cannot be a reserved word.

The second component is the password for the user. This item should contain at least three characters, and preferably six or more. Generally, it is recommended that users change their password once they know their username is created. A user should change his password to something that is not a word or a name, and also preferably contains a numeric character somewhere in it. As is the case with the username, the password can be a maximum length of 30 bytes. Similar to the name, the password cannot be a reserved word.

The next item in the user creation statement plays a crucial role in tablespace management in Oracle. The user’s default tablespace is the location to which all database objects created by that user will be created if the object creation statement does not explicitly name another location. Including this statement in the creation of a username plays an important role in protecting the integrity of the SYSTEM tablespace. If no default tablespace is named for a user, every object the user creates will be placed in the SYSTEM tablespace. Recall that SYSTEM contains many database objects that are critical to database usage. Some of these items include the data dictionary and the SYSTEM tablespace. It can be detrimental to the functioning of an Oracle database if users are allowed to create all their database objects in SYSTEM; therefore, this step of defining a default tablespace is crucial to preserving the integrity of that tablespace.

The act of defining a tablespace in which the user will place all temporary segments is crucial in the same way as defining a default tablespace. If the temporary location is not explicitly specified by the DBA at user creation, then the location for all temporary segments for that user will be the SYSTEM tablespace. To understand fully the impact of this situation, consider the uses of temporary segments. Typically, when a user process executes a long-running query that performs sorting functions, such as group by or order by statements as well as others, Oracle uses a special memory area called the sort area to perform the required sort operation on the information. If the query requires more space than the sort area allows, then Oracle will allocate temporary segments in which to sort the information for the life of the query. This frequent allocation and deallocation can cause some fragmentation in a tablespace. It may take time before the SMON process pulls smaller amounts of free space back into larger contiguous amounts of free space. Again, SYSTEM is a valuable resource that should be used directly by users as infrequently as possible.

The next two lines of the create user statement are the quota allocation parameters. A quota is a limitation on the amount of space the user’s database objects can occupy within the tablespace. If a user attempts to create a database object in a tablespace that exceeds the user’s quota for that tablespace, then the object creation script will receive an error stating that the user’s tablespace quota has been exceeded. In this event, one of two things should happen. Either the user should drop some of the objects he already owns in the tablespace to make room for the new object under his space quota, or the DBA should allocate a higher quota in the tablespace for the user. Quotas can be specified either in kilobytes (K) or megabytes (M). A quota clause should be issued separately for every tablespace other than the temporary tablespace on which the user will have access to create database objects.

TIP: Users need quotas on tablespaces to create database objects only. They do not need a quota on a tablespace to update, insert, or delete data in an existing object in the tablespace, so long as they do have the appropriate privilege on the object for data being inserted, updated or deleted.

Another area to be discussed in a user creation statement is the profile. Profiles are a bundled set of resource usage parameters that the DBA can set in order to limit the user’s ability to use the system. The principle behind profiles is manifold. One driving idea behind their use is that there may be many end users of the system in the course of a day. Since the DBA may want to reduce the number of licenses required for the Oracle database, she might use the concurrent usage licensing arrangement rather than the license-per-user-created arrangement. However, in order to accommodate each user that wants to use the system and reduce the amount of errors users get when they try to log onto a system already at its usage capacity, the DBA may set up profiles for each user that limit the amount of time they can spend on the system.

The last parameter for the create user statement is the default role. Each user may have specific privileges that are granted to them to allow them to do their job. These privileges include access to view or change data in a table or tables, or to execute stored procedures in a database. The number of available database objects can be quite large, making the management of access to those objects difficult and prone to mistakes. In order to facilitate the management of object or system privileges, a DBA may choose to grant those privileges to special database objects, called roles, instead. These roles can then be granted to users directly.

Exercises

1. What statement is used to create users? Identify the need for tablespace quotas. Do users need tablespace quotas to insert data in existing tables? Explain.
2. What is the purpose of a temporary tablespace?

Altering and Dropping Existing Users

Once a user is created, there should be little reason to modify that user, with the exception that regular password change increases the security of the ID. This password rotation is a feature of Oracle8. However, any aspect of the username can be modified by the DBA, though the aspects of the username that may be changed by the actual user are more limited. A situation may arise in regular database usage where a user must change his or her password. This situation may be that the user’s password has been shared with another user, or if the DBA simply wants all users to rotate their passwords on a regular basis. The alter user identified by statement is used to change the user’s password to something else.

Sometimes the DBA will want to reorganize things on the database in order to make more effective use of the hardware running Oracle. Perhaps this effort involves dropping some tablespaces and creating new ones. If the DBA wants to change the default tablespace used by the users for creating their database objects, the alter user default tablespace statement can be used. By changing a default tablespace for a user, the DBA makes it difficult for users to place objects in the SYSTEM tablespace by accidentally forgetting to specify the appropriate storage clauses. Any objects that were created in the USERS_01 tablespace by user SPANKY will continue to reside in that tablespace until they are dropped. Additionally, if user SPANKY specifies a tablespace in which to place a database object, that specification will override the default tablespace.

By the same token, the DBA may want to reorganize the temporary tablespace used by the users of a database system, and this is done using alter user temporary tablespace. Only the DBA can make these changes; the users cannot change their own temporary or default tablespace.

or

As with default tablespaces, if the user had created an object in the tablespace they had been originally allocated for temporary segments, any object created there will still be there after the temporary tablespace is changed. However, since temporary segments are eliminated after the statement that required their use completes, any object created in the previous temporary tablespace will not stay there for long.

With the exception of the temporary segment tablespace, any tablespace accessible to the user at user creation can have a quota placed on it. A quota can be altered by the DBA with the alter user quota statement. For example, the DBA may want to reduce the quota on the USERS_01 tablespace from 10 megabytes to 5 megabytes for user SPANKY. If the user has already created over 5M worth of database objects in the tablespace, no further data can be added to those objects and no new objects can be created. Only the DBA may change a user’s tablespace quota; the users may not change their own quotas.

The DBA may want to create new profiles corresponding to database usage. After the new profiles are created, the DBA then must allocate the profiles to the users that resource usage will change for. The method for changing a user’s profile is the alter user profile statement. The changes in this new profile that affect resource usage for this user will not take effect until the user drops the current session by disconnecting and reconnects with the new session profile. Only the DBA may change a user quota; the users may not change their own quotas.

The final change the DBA may make to user accounts is specifying a new default role. There is no limit to the number of roles that can be granted to a user; however, if there are privileges granted to a user through a nondefault role, the user may have to switch default roles in order to use those privileges. All roles granted to a user are default roles unless another option is specified by the username creation. The alter user default role all statement sets all roles granted to user SPANKY to be the default role. Other options available for specifying user roles include physically listing one or more roles that are to be the default, or specifying all roles except for ones named using all except (role_name [, …]), or none.

TIP: Except for altering the password, only the DBA can change user configurations.

As personnel for an organization changes, so too should the database users that were created change. To drop a user from the database, the DBA can execute the drop user cascade statement. The cascade option provides a key function in the removal of users. If a user has created database objects, the user cannot be dropped until the objects are dropped as well. In order to drop the user and all related database objects in one fell swoop, Oracle provides the cascade option.

Exercises

1. What statement is used for altering users?
2. What are the features of a user that the users themselves can change? What features can only the DBA change?
3. What statement is used to drop a database user? How can the objects created by the user be eliminated at the same time?

Monitoring Information About Existing Users

The DBA may periodically want to monitor information about users. Several data dictionary views may be used for the purpose of obtaining information about users. Some information a DBA may want to collect includes default and temporary tablespace information, profile information, objects created by that user, and roles granted to that user. The following data dictionary views can be used to determine this information.

Exercises

1. How can the DBA determine which users own which objects?
2. How can the DBA determine which tablespaces whose access is granted to a user have quotas on them?

Terminating User Sessions

In some cases, the DBA may need to cut off a user session from running against the database. The user may not be authorized to perform a certain activity, or the DBA may need to perform some cleanup activity that necessitates eliminating sessions. The statement that a DBA can issue in order to terminate a user session is alter system kill session SID, SERIAL#. In this statement, the user’s session ID (SID) must be specified, along with the serial number for the session. These two pieces of information can be extracted from the V$SESSION view by selecting the values in the SID and SERIAL# columns where the value in USERNAME equals the name of the user whose session the DBA wants to kill.

The effects of issuing an alter system kill session are as follows. First, the user session is terminated, preventing the user from issuing any more SQL operations from the current session. Next, all SQL operations in progress are also discontinued, causing Oracle to roll back any transactions that may have been in progress with that session. As part of discontinuing all transactions related to that session, the locks held by the session are released, freeing up the associated resources. As one can see, terminating a session can be a useful emergency measure for handling a locking situation.

Exercises

1. What statement is used to kill a user session?
2. Where can the DBA look to find information needed to kill a user session?

Managing Resource Usage

In this section, you will cover the following topics related to managing resource usage:

Resource usage can be managed by creating specific user profiles to correspond to the amount of activity anticipated by the average transaction generated by different types of users. The principle of user profiles is not to force the user off the system every time an artificially low resource usage threshold is exceeded. Rather, resource usage should allow the users to do just about everything they have to do in order to effectively use the Oracle database. But, if a mistake should occur, or if the user should try to do something detrimental to database performance, resource usage limitation functionality provided by profiles can help reduce the problem.

Understanding Oracle Resource Usage

Oracle resource usage works on either of two principles: setting individual resource limits, or assigning resource costs and defining a composite limit. Consider the following example. A user with database update capability on the test database and select capability on the production database is attempting to perform a small test of functionality on a database application change. The user determines that he needs 2,500 rows of data populated in a table in order to complete the test. The user attempts to port production data onto the test environment using a database link, but forgets an element in the where clause that will limit the data selected to the 2,500 rows that he needs. As a result, Oracle attempts to execute the statement, which pulls 25,000 rows of data. This statement is running across a link and causes serious performance degradation for other users on the test database. The use of profiles to limit resource usage can help in this type of situation. Either the DBA can assign individual resource costs to limit a user’s CPU time so he can’t issue statements like this one, or the DBA can set a low composite limit and a high resource cost for CPU time to limit use of the CPU.

Enabling Resource Limits

In order for resource limits to be used, the DBA must specify that resource limits are to be used on the database. This step is accomplished by changing the RESOURCE_LIMIT initialization parameter in the init.ora file to TRUE. However, the change there will not take place until the database is shut down and restarted. To enable resource restriction used in conjunction with profiles on the current database session, the DBA should issue the following statement:

Assigning Individual Resource Limits

User profiles bundle together resource limitations into a single data object that can be assigned to a user. User profiles can control usage of specific resource usage in several areas of Oracle. A user profile can be created for the database by the DBA using the create profile statement.

A listing of the areas where usage may be limited in a profile follows:

Resource Costs and Composite Limits

In some cases, the DBA may find the explicit setting of individual parameters for resource usage is not ideal in limiting database resource usage. An alternative known as setting composite limits works on the principle of resource cost. Resource cost is a figure determined by the DBA that reflects the relative value of that resource. Cost is specified as an abstract unit value, not a monetary resource price. For example, setting the resource cost of CPU cycles per session equal to 1.5 does not mean that each CPU cycle costs a user process $1.50 to run. Figure 9-1 demonstrates the resource costs are not necessarily monetary costs.

Figure 1: Resource cost is not a monetary cost

Rather, the cost of a resource corresponds to its importance on the system. For example, a DBA managing a database system with few CPUs available and many disk controllers may consider the cpu_per_session resource to be more expensive than another resource, perhaps logical_reads_per_session. As such, the DBA might allocate resource costs appropriately. The statement used for assigning a resource cost is the alter resource cost statement. Only certain resources can be given a cost, including cpu_per_session, logical_reads_per_session, connect_time, and private_sga. The default value for each resource cost is zero.

Once resource costs are set by the DBA, then the task of assigning a composite limit can be accomplished. Composite limits restrict the usage of database resources by forcing Oracle to keep a running total of how often a resource is used. Each time a resource is used, Oracle increments the total resource usage cost for that session until the time that the session hits the limit set by the composite_limit option. The SALES_EMP profile is altered by the DBA to include a composite limit with the alter profile statement.

The resource costs of cpu_per_session and logical_reads_per_session are set as they appear in the alter resource cost statement. User SPANKY logs onto the system and proceeds to execute a query that takes 10 CPU cycles to execute. As soon as the sixth CPU cycle is complete, the user session terminates because the composite limit of 500 is exceeded by this query. Of course, setting the composite limit this low would artificially restrict the usage of the database by user SPANKY, which from the beginning was identified as not being the ultimate purpose of user profiles.

Exercises

1. What is a user profile? How can they be used to prevent excessive use of the database system?
2. What must happen before resource usage can be limited through the use of user profiles? What are the parameters that can be set to restrict database usage for individual resources?
3. What are resource costs and composite limits? How do these two features work together?

Managing Profiles Created for the System

Before creation of user profiles, the DBA should explore a special user profile that is created by Oracle at database creation. This special user profile is called DEFAULT, and it is assigned to all users on database creation if no other user profile is defined in the create user statement. The DEFAULT profile assigns a special identifier to all resources named above, called unlimited, that allows the user to have unlimited use of the resources specified in the profile. Any of these values can be changed by the DBA in order to ensure that no user can issue SQL statements that arbitrarily consume database resources. However, if the DBA does change a parameter in the DEFAULT profile, she should ensure that no other user profile is adversely impacted by the default resource usage parameter change.

TIP: If no profile is assigned with the profile clause of the create user statement, the DEFAULT profile is assigned to that user.

Setting a user profile to not contain explicit values for every parameter leaves the profile vulnerable to any change made in the DEFAULT profile. If the DBA plans never to change the DEFAULT profile, this scenario will work fine. However, if a change is made in the DEFAULT profile, the effects of the change will be felt in every user profile that did not specify a value explicitly for that aspect of resource usage. If the DBA doesn’t plan to change settings for DEFAULT, then the creation of user profiles that do not contain explicit values set for all parameters will be fine with the default set to unlimited. However, the DBA should ensure that all users created are assigned to a profile other than DEFAULT in order to prevent granting unlimited resource usage to any user. If the DBA plans to allow many users to use the DEFAULT profile, then the resource parameters for DEFAULT should be changed and every user profile created should have resource usage parameters explicitly assigned.

Exercises

1. What is the DEFAULT profile? What are the default values for resource limits in the DEFAULT profile?
2. What profile is assigned to a user if one is not explicitly set by the create user statement?

Creating and Assigning User Profiles

With the DEFAULT profile in mind, the DBA now should consider the task of creating user profiles. This task requires considering each of the potential users in a database, and categorizing those users into several different levels of usage. For example, the DBA of database SALES01 may determine that there are four different classes of users on this system. The first class of users is the salespeople. These users update their sales data on a daily basis, inputting the sales that they have generated and accessing their own profiles to find the status of sales orders when customers call them. Another class of users in this system is managers. These users have several salespeople assigned to each of them. Their job is to log onto the system and track the sales figures for each salesperson against selling goals set forth by the company. Additionally, they assign bonuses to the best salespeople in their group on a weekly basis. Finally, they compile a sales report for their team broken out by team member every two weeks.

The third class of user on this database system is the auditor. There are three auditors on the database. Their job is to review each salesperson individually to verify that the sales are legitimate, and that payment is collected for the sale. The auditor often must run ad hoc reports against the database to determine certain things per salesperson, per team, and for the sales force as a whole. The final class of user on this database system is the payroll administrator. This user executes one process weekly that determines the percentage commission for each salesperson, adds the bonus if there is one, and generates a paycheck for the salesperson for the week’s worth of work. This process can take several hours, depending on how many sales were executed for the week.

The first user profile created will be called SALES_EMP. Since this profile corresponds to the salespersons that use the database to insert sales orders or check existing orders, the DBA can assume that most statements issued will be select or insert, and that each statement will take only a short amount of time to fulfill. Therefore, the DBA may impose limits on the CPU used per call. Since a salesperson may spend a great deal of time on the phone trying to sell to users, the DBA may not want to time the user out if the session remains idle. However, the DBA doesn’t want users to simply leave their desks at the end of the day without logging off either. In order to prevent a situation where an unauthorized user submits sales entries, the DBA may limit the number of sessions the salesperson can have to one. The rest of the parameters that can be specified at user profile creation but weren’t are set to the default value for that resource as detailed in the DEFAULT profile. The create profile statement will be used to create this profile.

The next profile created is for the managers. The DBA assesses resource usage by each manager in the following way. Since the manager has to review sales figures for many different users, the manager may want to review multiple salespersons’ records at a time, using multiple sessions with the database. Since the manager has to generate that biweekly report, the DBA wants to make sure he will not be limited with respect to CPU or disk usage. However, since the manager has the ability to assign bonuses, the DBA does not want the manager to leave his sessions open while he is away from his desk for someone to inappropriately assign a bonus in the manager’s name. Hence, the limit is placed on idle time and connect time. All other resources that were not explicitly assigned limits for this profile will take on the default values specified in the DEFAULT profile.

For the third class of user, the auditor, the DBA must allow for a great deal of flexibility in issuing potentially long-running ad hoc queries. The DBA intentionally leaves many options off from the create profile statement. This is due to the fact that the DBA wants these options to default to unlimited as set in the DEFAULT user profile. The only item explicitly set by the statement is the limitation of sessions for the user, which is set to five.

The final user profile the DBA will create for the SALES database example is the profile for the payroll administrator. This profile relies on the specification of unlimited for all resources not explicitly identified in the creation of this statement. However, the resources that are set limit the number of sessions to one. Since the payroll administrator will be executing only one process per week to generate pay information, there is no need to her to have more than one session open at a time. Additionally, once the process is complete there is no further need for her to be on the system. As such, if idle time exceeds five minutes, then the user is disconnected.

Once the profiles above are created, they can be assigned to users that are in the process of being created with the use of the create user statement. In the profile clause of this statement, the profile should be named. As a result of this creation statement, SPANKY now has the resource usage ability to execute the types of statements that he needs in order to audit the sales records. If, however, a change is made to user SPANKY’s job function, such that SPANKY is now a sales associate in the organization, the DBA may want to execute the following statement in order to reflect the change in SPANKY’s resource usage needs:

Finally, if the DBA wants to drop a user profile from the database, she can do so by executing the drop profile statement. As with dropping users, a profile cannot be eliminated without the cascade option if the profile has already been assigned to a user. Once a profile that has been assigned to a user is dropped by issuing the drop profile cascade command, the profile applied to the user’s subsequent sessions is changed to the DEFAULT profile.

The act of changing a user profile may be required, particularly if user profiles in the database rely on default parameter values being set in a certain way in the DEFAULT profile. If the resource usage parameter cpu_per_session in DEFAULT were to be changed from unlimited to 20,000, then the value set implicitly for cpu_per_session in several of the user profiles created in the example above would also be changed. The DBA can issue the following statement to explicitly set this option to unlimited in the auditor profile.

ALTER PROFILE auditor LIMIT
CPU_PER_SESSION UNLIMITED;

Once this statement is executed, then the AUDITOR profile is no longer dependent on the configuration of the DEFAULT profile for its setup. Any option in any profile can be changed by the DBA at any time; however, the change will not take effect in user sessions currently running with the profile just changed. Instead, the profile only applies to future sessions for users with the changed profile assigned to them.

Exercises

1. What analysis tasks are required for creating user profiles? What statement is used to create a user profile? What statement is used to change a user profile?
2. What usage value is assigned to a resource in a user profile when the DBA does not explicitly assign one?
3. What statement is used to drop a profile? Can a profile be dropped if it is granted to a user? Explain.

Managing Database Access and Roles

In this section, you will cover the following topics related to managing database access:

Once a user is allowed access to connect to the database, the actual areas the user is permitted to see and the things that the user is allowed to do are also controlled by Oracle. Use of every database object and system resource is governed by a privilege. There are privileges required to create objects, access objects, change data within tables, execute stored procedures, create users, etc. Since access to every object created is governed by a privilege, security in the Oracle database is highly flexible in terms of what objects are available to the user. There are two categories of privileges: object privileges and system privileges.

Granting and Controlling System Privileges

System privileges control the creation of all database objects, such as rollback segments, synonyms, tables, and triggers. Additionally, the ability to use the analyze command and to use the Oracle database audit capability is also governed by system privileges. Generally speaking, there are several classes of privilege that relate to each object. Those classes determine the scope of ability that the privilege grantee will have. The classes of database privileges are listed below:

System privileges on database objects are a little tricky, however. The categorization listed for create, alter, and drop privileges does not exist for all objects. Furthermore, in some database objects like tables, the alter privilege is an object privilege, not a system privilege. Additionally, the create index privilege is an object privilege. Therefore, it may assist you to review the object privileges available in Oracle and determine first if the privilege required is an object privilege. If not, then the privilege required is probably a system privilege. In addition to the privileges required to create (and sometimes alter and drop) database objects within a user schema, there is a class of system privileges that allow the user to perform certain operations on any object in the database, as listed below:

There are some system privileges that don’t seem to fit anywhere else in the scope of system privileges. One privilege is the restricted session privilege. This privilege allows the grantee to connect to the database any time that the database is in restricted mode. This privilege is typically given only to the DBA. The other privilege considered is the unlimited tablespace privilege. This privilege allows the grantee the ability to create database objects in a tablespace using an unlimited amount of space in the tablespace. This privilege is granted typically to the user schema that will own the database objects for an application or database, the DBA, and no one else. The database objects on which at least the create system privilege applies appear in the following list for reference.

Exercises

1. Name some system privileges on database objects. What are some objects that do not use system privileges to let users change the object definition or create the object?
2. What are some other system privileges used to manage certain operations on any database object?
3. What is the unlimited tablespace privilege? What is the restricted session privilege?

Object Privileges Explained

The other category of privileges granted on the Oracle database is the set of object privileges. Object privileges permit the owner of four types of database objects to administer access to those objects according to the following types of access allowed. The various types of object privileges are listed below:

As given evidence by the alter and index object privilege, there sometimes can be a blurry line between system and object privileges. However, the point of this discussion is not to indicate nuance; rather, it is to give the DBA some general guidelines to understand about user system and object privileges. In order to grant a privilege to a user, the appropriate privilege administrator or the DBA can issue the following statement:

In addition to granting object privileges on database objects, privileges can also be granted on columns within the database object. The privileges that can be administered on the column level are the insert, update, and references privileges. However, the grantor of column privileges must be careful when administering them in order to avoid problems—particularly with the insert privilege. If a user has the insert privilege on several columns in a database but not all columns, the privilege administrator must ensure that no other column in the table that does not have the insert privilege granted is a NOT NULL column. Take the following example under consideration in order to understand the implications of this rule. Table EMP has two columns, NAME and EMPID. Both columns have NOT NULL constraints on them. The insert access is granted for the EMPID column to user SPANKY, but not the NAME column. When user SPANKY attempts to insert an EMPID into the table, Oracle generates a NULL for the NAME column, then produces an error stating that the user cannot insert a NULL value into the NAME column because the column has a NOT NULL constraint on it. Administration of update and insert object privileges at the column level must be handled carefully, while the concept of using the references privilege on a column level seems to be more straightforward.

Some special conditions relate to the use of the execute privilege. If a user has the ability to execute a stored procedure owned by another user, and the procedure accesses some tables, the object privileges required to access those tables must be granted to the owner of the procedure, and not the user to whom execute privileges were granted. When a user executes a stored procedure, the user is able to use whatever privileges are required to execute the procedure. For example, execute privileges are given to user SPANKY on procedure process_deposit( ) owned by user ATHENA, and this procedure performs an update on the BANK_ACCOUNT table using an update privilege granted to ATHENA. User SPANKY will be able to perform that update on BANK_ACCOUNT via the process_deposit( ) procedure even though the update privilege is not granted to SPANKY. However, user SPANKY will not be able to issue an update statement on table BANK_ACCOUNT from SQL*Plus, because the appropriate privilege was not granted to SPANKY directly.

Exercises

1. In what situations is the alter privilege an object privilege? What are other object privileges?
2. What privilege is required to execute a stored procedure? To which user (the owner of the procedure or the executor of the privilege) must any object privilege related to updating a table in the procedure be granted?

Creating and Controlling Roles

Roles have the ability to take the complexity out of administrating user privileges. A role in the database can be thought of as a virtual user. The database object and/or system privileges that are required to perform a group of user functions are gathered together and granted to the role, which then can be granted directly to the users. As functions or database objects become available or required on the database, the object privileges needed for users to perform their functions are granted to the role. Since the role is already granted to the user, there is no further action required. Furthermore, if a user switches to a new job function requiring a different set of privileges, the role with those privileges granted to it can be granted to the user, while the role with the user’s old privileges is revoked.

Roles are created on databases in the following manner. The DBA determines what classes of users exist on the database, and what privileges on the database can be logically grouped together into different user functions. Using the sales force example from the previous section, the DBA may determine that four different roles are required on the database, SALES_PERSON, SALES_MANAGER, SALES_AUDITOR, and PAYROLL_ADMIN. In order to create the roles that will support privilege administration of these classes of users, the following statement can be executed. Once the role is created, there are no privileges assigned to it until privileges are explicitly granted to it.

The use of a password to authenticate users trying to utilize a role is purely optional. If used, however, the password provides an extra level of security over the authentication process at database login. The most effective way to employ a role password is to set the role authenticated by a password to be a nondefault role. If the user needs to access the database with the set of privileges associated with the role, they must first execute the alter user default role role_name command, where the value set for role_name is the name of the role that must be authenticated. In this scenario, the user must provide the password for authentication once at the time the role is set as the default.

Privileges are granted to roles in the following manner. At the same time that the DBA determines the resource usage of various classes of users on the database, the DBA may also want to determine what object and/or system privileges each class of user will require. For example, a salesperson may need access to the CUSTOMER and ORDER tables to create new customers and customer orders, and may require access to the PRODUCTS table in order to determine what products are currently for sale. Instead of granting the privileges directly to the salespeople on an individual basis, however, the DBA can grant the privileges to the role, which then can be granted to several salespeople more easily.

There are some special roles available to the users of a database as well. The roles available at database creation are CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE and IMP_FULL_DATABASE. The uses of each role are listed as follows:

Administering Privileges and Roles

Giving these privileges to users occurs in the following ways. Privileges are first given to the SYS and SYSTEM users, and to any other user created with the grant any privilege permission. As other users are created, they too are given privileges based on their needs with the grant command. For both system and object privileges, there are two ways to grant privileges to others: either as an end user or as an administrator. For example, executing the following grant statements gives access to create a table to user SPANKY, and object privileges on another table in the database:

At the end of execution for these two statements, SPANKY will have the ability to execute commands associated with the two commands above, namely create tables in his user schema and select and update row data on the EMP table in ATHENA’s schema. However, the extent of user SPANKY’s ability to give these privileges away is limited. User SPANKY can only create tables and select and update rows from EMP. He cannot give his privileges to others, nor can he relinquish them without the help of the DBA. In order to give user SPANKY some additional power to administer the rights granted to him to other users, the DBA can execute the following queries:

GRANT CREATE TABLE TO spanky WITH ADMIN OPTION;
GRANT SELECT, UPDATE ON emp TO SPANKY WITH GRANT OPTION;

The clause with admin option, when included in the general system privilege, gives the user SPANKY the ability to give or take away the same privilege to others. Additionally, it gives user SPANKY the ability to make other users administrators of that same privilege. Finally, if the item granted to user SPANKY is a role, user SPANKY can then alter the role or even remove it from the database.

For object privileges, user SPANKY has many of the same abilities as with the admin option of system privileges. In the example above, user SPANKY can not only select and update data from EMP, but he can give that ability to other users as well. Also, user SPANKY can set up other users with the ability to give the grant option to the particular object privilege granted to them with the grant option. However, if user SPANKY has another object privilege, say the ability to select data from the CUSTOMER table, user SPANKY cannot grant that privilege to another user simply because the grant option was extended to user SPANKY as part of some object privileges on another table. Only the object privilege with which the grant option was given to user SPANKY can be given to other users by user SPANKY.

Some final notes about granting privileges. If user SPANKY creates a role after being granted the create role system privilege, then as the creator of that role SPANKY has the admin option on that role. Granting roles to users is done in the same way as granting system privileges. If user SPANKY creates a table in his own schema, he has all object privileges associated with that object as well. Additionally, there is a consolidated method for granting object privileges using the keyword all. Note that all is not a privilege unto itself, it is merely a specification for all object privileges for the database object. There may also come a time when users must have privileges revoked as well. This task is accomplished with the revoke command.

Important to note here is that revocation of the create table privilege also takes away the admin options that may have been given along with the privilege or role. No additional syntax is necessary for revoking either a system privilege granted with admin option or an object privilege granted with grant option. In the same way, roles may be revoked from users, even if the user created the role and thus has the admin option. The ability to revoke any role comes from the grant any role privilege, while the ability to grant or revoke certain system privileges comes from being granted the privilege with the admin option. When a system privilege is revoked, there are no cascading events that take place along with it. That is to say, if SPANKY created several tables while possessing the create table privilege, those tables are not removed if user SPANKY has the create table privilege revoked. Only the drop table command will remove the tables. There may be other cascading effects related to system privileges.

TIP: Understand the following scenario completely before continuing! User X has a system or object privilege granted to her with admin option or grant option. X grants the privilege to user Y, with or without the administrative privileges. User Y does the same for user Z. Then X revokes the privilege from user Y. USER Z WILL STILL HAVE THE PRIVILEGE. WHY? Because there is no cascading effect to revoking privileges, only the fact that the user from whom the privilege was revoked no longer has the privilege.

There are several considerations to make when revoking object privileges. For instance, on individual columns, if a privilege has been granted on two individual columns, the privilege must be revoked entirely and then regranted, if appropriate, on the individual column to the user. Also, if the user has been given the references privilege and used it to create a foreign-key constraint to another table, then there is some cascading that must take place in order to complete the revocation of the references privilege.

In this example, not only is the privilege to create referential integrity revoked, but any instances where that referential integrity was used on the database are also revoked. If a foreign-key constraint was created on the EMP table by user SPANKY and the above statement was issued without the cascade constraints clause, then the revoke statement will fail. Other cascading issues may appear after object privileges are revoked as well. In general, if an object privilege is revoked, then any item created by the user that relied on that object privilege may experience a problem during execution.

Open to the Public

The final aspect of privileges and access to the database that will be discussed involves a special user on the database. This user is called PUBLIC. If a system privilege, object privilege, or role is granted to the PUBLIC user, then every user in the database has that privilege. Typically, it is not advised that the DBA should grant many privileges or roles to PUBLIC, because if the privilege or role ever needs to be revoked, then every stored package, procedure, or function will need to be recompiled.

Exercises

1. What is a role? How are privileges managed using roles?
2. What special options are required for system and object privileges if the user is to have administrative capability along with the privilege?
3. What cascading issues exist related to the references object privilege and the user PUBLIC?

Auditing the Database

In this section, you will cover the following topics related to auditing the database:

Securing the database against inappropriate activity is only one part of the total security package Oracle offers the DBA or security administrator on an Oracle database. The other major component of the Oracle security architecture is the ability to monitor database activity to uncover suspicious or inappropriate use. Oracle provides this functionality via the use of database auditing. This section will cover aspects of database auditing use, including how to determine when auditing is necessary, how to track statement- and system-level access to database objects, using the data dictionary to monitor auditing options, and viewing and managing audit results.

Determining the Need for Auditing

Almost every database will need to be audited at one time or another. Excessive database usage by a particular user, odd changes that appear on the database at suspicious times, or simply the need to have an archive of database usage may all be reasons the database needs to be audited. Generally speaking, a database audit is most effective when the DBA or security administrator knows what he or she is looking for. Auditing is not something the DBA should just "turn on" and assume that the problem will automatically surface. Reality in database auditing is far from that, in fact. The best way to conduct a database audit is to start the audit with a general idea about what may be occurring on the database. Once the goals are established, set the audit to monitor aspects of the database related to those areas of database usage and review the results to either confirm or disprove the hypothesis.

Why must an audit be conducted in this manner? First of all, database auditing has the ability to generate LOTS of information about database access. If the DBA tried to audit everything, the important facts would get mixed into a great deal of unnecessary detail. With a good idea about the general activity that seems suspicious, as well as the knowledge of the types of statements or related objects on the database that should be looked at, the DBA can save a lot of time sorting through excess detail later.

In addition, it is recommended that the DBA use the Oracle auditing options to conduct audits using a "top-down" approach. Auditing from the top down means that the DBA starts with an idea of suspicious database activity. From there, the DBA audits a number of general areas that may be related to the problem at hand. Once this general audit is complete, the DBA may find that auditing one or two areas produced more results than several others. From there, the DBA should conduct an audit that is much narrower in scope, perhaps focusing exclusively on the areas that displayed suspicious activity, or on one or two areas related to the suspicious activity, in order to solidify the hypothesis, but no more. Above all else in the framework of handling database audits for inappropriate activity is the importance of protecting the evidence. In order to protect evidence of improper database activity, the DBA must ensure that no user can remove records from the audit logs undetected. Therefore, a key step in auditing is to audit the audit trail.

The DBA should be mindful also that monitoring general database activity for the purpose of archiving is not the same motive as trying to uncover a problem with inappropriate database usage. In the more general situation of monitoring database activity for archiving purposes, the DBA should approach the task with two things in mind: 1) what are the key areas to focus on for auditing to produce archive information, and 2) place the audit information into an archive and clear out the online audit destination.

Lastly, it should be understood that, especially in large organizations, the DBA should not be the person ultimately responsible for managing database security, including audits. That responsibility should belong to a security administrator or manager. The DBA should, however, be familiar with the security features of the Oracle database in order to fulfill his or her role as the resident expert on Oracle software.

Exercises

1. What is auditing?
2. When might it be necessary to audit database activity?

Tracking Statement- and System-Level Access to Data Objects

As with many areas of the Oracle database, in order to begin capturing audit information, the DBA must set an initialization parameter. In this case, the initialization parameter set is called AUDIT_TRAIL. This parameter can have three values, DB, OS, or NONE. Each of these values corresponds to the method by which the audit will be conducted. DB indicates that the database architecture will be used to store audit records. OS indicates that the audit trail will be stored external to Oracle using some component of the operating system, and NONE indicates that no database auditing will be conducted at all. After changing the value set for this parameter, the database instance must be shut down and started again.

Once the database is prepared for an audit, the DBA or security administrator can set auditing features to monitor database activities related to system-level activity or statement-level activity. Some examples of system-level database activity include starting and stopping the database, and connecting to the database. Statement-level activities include the general creation or removal of database components. Additionally, statement-level activity corresponds more directly with database usage, such as updating or inserting data into a table, working with constraints, etc.

Common to all database audits are several components about which information is always recorded. These areas are directly related to the system-level activity. The areas are instance startup and shutdown, and every connection to the database of a user with database administrator privileges. However, this information must be recorded to the operating system audit trail because the database audit trail is not available until startup is complete, and will not be available after shutdown is complete. If no operating system audit trail is available, Oracle writes a special audit trail file to a location set by the parameter AUDIT_FILE_DEST. The general syntax for initiating an audit is to state the name of the system-level or statement-level activity that is to be audited, the frequency by which to audit, and whether or not the audit should record successful or unsuccessful executions of the activity in question.

This statement has three effects. First, a certain set of activities performed by user SPANKY will be recorded whenever they complete successfully. To answer the question of what activities are audited, consider the following. Any privilege that can be granted can also be audited. However, since there are nearly 100 system and object privileges that can be granted on the Oracle database, the creation of an audit statement can be an excessively long task. As an alternative to naming each and every privilege that goes along with a database object, Oracle allows the administrator to specify the name of an object to audit, and Oracle will audit all privileged operations. Instead of listing all privileged operations related to the type of object that would be audited, the security administrator could instead name the type of object and achieve the desired result.

There are other options available to consolidate the specification of database activities into one easy command for auditing. These commands are listed as follows.

Related to object privileges is the ability Oracle’s audit feature has to record the data change operations that happen on particular tables. This audit process adds one clause to the general audit statement, which appears as follows:

This statement is useful because it points out some other unique items in the audit syntax. The person setting up audits need not name particular users on which to monitor activity. Rather, the activities of this sort can be monitored every time the statement is issued with the by access clause. Additionally, when the not successful option is specified, audit records are generated only when the command executed is unsuccessful. The omission of clauses from the audit syntax causes audit to default to some performance feature. For example, an audit can be conducted on all inserts on table PRODUCTS, regardless of user and completion status.

If the person setting up audit wants to specify a value for the whenever clause but not the by clause, then something else has to be substituted. That substitution is the on default clause. Review the following statement for a clearer understanding:

However, if the last line is left off, then all inserts on the PRODUCTS table will be recorded. Finally, the person setting up auditing can also specify that audit records are to be compiled by session. This means that audit will record data for audited activities in every session, as opposed to by access. Eliminating the when successful clause tells audit to record every table creation, alteration, or drop activity for every session that connects to the database, regardless of whether or not they were successful.

One final area to consider is how to disable auditing. There are two methods used. The first method is to change the initialization parameter AUDIT_TRAIL to NONE. On database shutdown and restart, this option will disable the audit functionality on the Oracle database. The other, less drastic, option used for changing the activities audit will record is called noaudit. This option can be executed in two ways. The first is used to turn off selective areas that are currently being audited.

In some cases, however, the person conducting the audit may want to shut off all auditing processes going on, and simply start auditing over again. Perhaps the auditor has lost track of what audits were occurring on the database. This statement can be further modified to limit the auditing turned off to a particular database object.

Exercises

1. What is the general format for starting an audit? What activities in the database can be audited?
2. What initialization parameter must be changed before auditing can take place on the database?
3. How can auditing be stopped?

Understanding Audit Results with the Data Dictionary

There are some design issues to discuss in order to understand the architecture of auditing. The object in the Oracle architecture designed to handle audits is a data dictionary table called AUD$. The object is owned by the user SYS. In order to access the information provided by auditing, and to better understand the data being stored in this table, there are several views available to show various items in the architecture. The views that are available are listed as follows, along with some description of their contents and/or usage:

These views are created by the cataudit.sql script found in rdbms/admin off the Oracle software home directory. This script is run automatically at database creation by the catalog.sql script. No additional audit information is stored anywhere else in the database, except for the special file written by Oracle that contains startup and shutdown information, unless operating system auditing is used.

Exercises

1. Where is audit data stored in the data dictionary?
2. What data dictionary views are available for viewing audit data?
3. What data dictionary views are available for viewing audit options and parameters?

Managing Audit Information

Once created, all audit information will stay in the AUD$ table owned by SYS. In cases where several auditing options are used to gather information about database activity, the AUD$ table can grow to be large. In order to preserve the integrity of other tables and views in the data dictionary, and to preserve space overall in the SYSTEM tablespace (where all data dictionary objects are stored), the DBA or security administrator must periodically remove data from the AUD$ table, either by deleting or by archiving and then removing the records. Additionally, in the event that audit records on an Oracle database are being kept to determine if there is suspicious activity, the security administrator must take additional steps to ensure that the data in the AUD$ table is protected from tampering.

In order to prevent a problem with storing too much audit data, the general guideline in conducting database audits is to record enough information required to accomplish the auditing goal without storing a lot of unnecessary information. The amount of information that will be gathered by the auditing process is related to the number of options being audited and the frequency of audit collection (namely, by username, by access, by session).

What problems can occur if too much information is being audited? The biggest problems that occur when auditing is used relate to the auditing of user connections. When the connect or session activity is audited, a record is written to the AUD$ table every time a user connects or disconnects. The amount of space in the AUD$ table is limited. The default size for AUD$ is up to 990 kilobytes—or 99 extents, each 10K in size. If the AUD$ table fills to capacity and session connections are being audited, then no user will be able to connect to the database until some room is made in AUD$.

In order to remove records from AUD$, a user with delete any table privilege, the SYS user, or a user to whom SYS has granted delete access to AUD$ must log onto the system and remove records from AUD$. Before doing so, however, it is generally advisable to make a copy of the records being deleted for archiving purposes. This task can be accomplished by copying all records from AUD$ to another table defined with the same columns as AUD$, spooling a select statement of all data in AUD$ to a flat file, or using EXPORT to place all AUD$ records into a database dump file. After this step is complete, all or part of the data in the AUD$ table can be removed using either delete from aud$ or truncate table aud$.

Alternately, if the data in AUD$ is being collected in support of suspicious activity, it is in the interest of those performing the audit to ensure that the data in AUD$ is monitored. Although this measure will keep track of the users that make changes to the AUD$ table, the only actual measure of prevention the database has against deletion is to protect the SYS user and password, and limit the number of users who have delete access to AUD$ to the minimum number necessary.

Exercises

1. How can the security administrator remove data from the audit trail?
2. What problems can arise when the audit trail fills? How can data in the audit trail be protected?

Chapter Summary

This chapter covered several aspects of managing database usage that are critical to database administration. The areas discussed were creating and managing users, managing resource usage with profiles, managing database access with privileges and roles, and monitoring database activity with the audit command. The content discussion provided by this chapter comprises 27 percent of material covered in OCP Exam 2.

Managing users is an important area of database administration. Without users, there can be no database change, and thus no need for a database. Creation of new users comprises specifying values for several parameters in the database. They are password, default and temporary tablespaces, quotas on all tablespaces accessible to the user (except the temporary tablespace), user profile, and default roles. Default and temporary tablespaces should be defined in order to preserve the integrity of the SYSTEM tablespace. Quotas are useful in limiting the space that a user can allocate for his or her database objects. Once users are created, the alter user statement can be used to change any aspect of the user’s configuration. The only aspects of the user’s configuration that can be changed by the user are the default role and the password.

Several views exist to display information about the users of the database. DBA_USERS gives information about the default and temporary tablespace specified for the user, while DBA_PROFILES gives information about the specific resource usage allotted to that user. DBA_TS_QUOTAS lists every tablespace quota set for a user, while DBA_ROLES describes all roles granted to the user. DBA_TAB_PRIVS also lists information about each privilege granted to a user or role on the database. Other views are used to monitor session information for current database usage as described in the chapter. An important view for this purpose is V$SESSION. This dynamic performance view gives information required in order to kill a user session with the alter system kill session. The relevant pieces of information required to kill a session are the session ID and the serial# for the session.

In order to restrict database usage, the DBA can create user profiles that detail resource limits. A user cannot exceed these limits if the RESOURCE_LIMIT initialization parameter is set on the database to TRUE. Several database resources are limited as part of a user profile. They include available CPU per call and/or session, disk block I/O reads per session, connection time, idle time, and more. One profile exists on the Oracle database at database creation time, called DEFAULT. The resource usage values in the DEFAULT profile are all set to unlimited. The DBA should create more profiles to correspond to the various types or classes of users on the database. Once created, the profiles of the database can then be granted to the users of the database. An alternative to setting usage limits on individual resources is to set composite limits to all database resources that can be assigned a resource cost. A resource cost is an integer that represents the importance of that resource to the system as a whole. The integer assigned as a resource cost is fairly arbitrary and does not usually represent a monetary cost. The higher the integer used for resource cost, the more valuable the resource. The database resources that can be assigned a resource cost are CPU per session, disk reads per session, connect time, and memory allocated to the private SGA for user SQL statements. After assigning a resource cost, the DBA can then assign a composite limit in the user profile. As the user uses resources, Oracle keeps track of the number of times the user incurs the cost associated with the resource and adjusts the running total. When the composite limit is reached, the user session is ended.

Oracle limits the users’ access to the database objects created in the Oracle database by means of privileges. Database privileges are used to allow the users of the database to perform any function within the database, from creating users to dropping tables to inserting data into a view. There are two general classes of privilege: system privileges and object privileges. System privileges generally pertain to the creation of database objects and users, as well as the ability to connect to the database at all, while object privileges govern the amount of access a user might have to insert, update, delete, or generate foreign keys on data in a database object.

Database privilege management can be tricky if privileges are granted directly to users. In order to alleviate some of the strain on the DBA trying to manage database access, the Oracle architecture provides a special database object called a role. The role is an intermediate step in granting user privileges. The role acts as a "virtual user," allowing the DBA to grant all privileges required for a certain user class to perform its job function. When the role has been granted all privileges required, the role can then be granted to as many users as required. When a new privilege is required for this user group, the privilege is granted to the role, and each user who has the role automatically obtains the privilege. Similarly, when a user is no longer authorized to perform a certain job function, the DBA can revoke the role from the user in one easy step. Roles can be set up to require password authentication before the user can execute an operation that requires a privilege granted via the role.

The activities on a database can also be audited using the Oracle audit capability. Several reasons exist for the DBA or security administrator to perform an audit, including suspicious database activity or a need to maintain an archive of historical database activity. If the need is identified to conduct a database audit, then that audit can happen on system-level or statement-level activities. Regardless of the various objects that may be monitored, the start and stopping of a database as well as any access to the database with administrator privileges is always monitored. To begin an audit, the AUDIT_TRAIL parameter must be set to DB for recording audit information in the database audit trail, OS for recording the audit information in the operating system audit trail, or to NONE if no auditing is to take place. Any aspect of the database that must have a privilege granted to do it can be audited. The information gathered in a database audit is stored in the AUD$ table in the Oracle data dictionary. The AUD$ table is owned by SYS. Special views are also available in the data dictionary to provide views on audit data. Some of these views are DBA_AUDIT_EXISTS, DBA_AUDIT_OBJECT, DBA_AUDIT_SESSION, DBA_AUDIT_STATEMENT, and DBA_AUDIT_TRAIL. It is important to clean records out of the audit trail periodically, as the size of the AUD$ table is finite, and if there is an audit of sessions connecting to the database happening when the AUD$ table fills, then no users will be able to connect to the database until some room is made in the audit trail. Records can only be removed from the AUD$ table by a user who has delete any table privilege, the SYS user, or a user SYS has given delete access to on the AUD$ table. The records in the AUD$ table should be archived before they are deleted. Additionally, the audit trail should be audited to detect inappropriate tampering with the data in the table.

Two-Minute Drill