Managing Security

Oracle8 has introduced several long-awaited enhancements to Oracle’s security, such as password aging. This section will discuss how these new features are incorporated into Oracle Security Manager.

Oracle passwords can now expire. If a password has expired, the user must change the password the next time he or she connects to the database. In fact, when you create an account, you can mark the password as expired, so that the user will have to reset the password upon the first logon.

You might also notice that you can give the user a status of locked or unlocked. When a user ID is locked, that ID cannot connect to the database. The user’s profile will determine whether the account is unlocked after a period of time or whether it must be unlocked by an administrator.

Finally, you should notice that Security Manager grants the Connect role unless you select the Roles/Privileges tab and remove this granted role. Remember that Connect is a role that includes not only the Create Session privilege, but system privileges that allow the user to create a number of database objects. If the user is simply going to use an application and you don’t want the user to create any database objects, you should have Security Manager grant Create Session instead of Connect.

If you are using Oracle Security Server, you can use global authentication to provide a single authentication point for all databases in your enterprise. This is a tremendous time saver for organizations with large numbers of users who need access to many databases. In Figure 21.29, you can see that global authentication requires you to enter additional information about the user’s organization and location. See the Oracle Security Server Guide and the Oracle Server Administrator’s Guide for detailed information on global authentication.

Figure 21.29.  Selecting global authentication.

Password aging is implemented through the use of profiles. You can require passwords to expire in a certain number of days. You can also specify that an account with a expired password should be locked after a certain number of days because the account is no longer being used (see Figure 21.30). This makes it much easier to identify accounts that are no longer in use. There are also options for managing the complexity of passwords and for locking accounts after a number of failed logon attempts. Because all these options are set within profiles, you can create combinations of password aging and account locking that are appropriate for the various types of users in your database.

Figure 21.30.  Password options for user profiles.

Managing Storage

Enterprise Manager includes a tool called Storage Manager that enables you to add datafiles, create rollback segments, put rollback segments online and take them offline, and analyze dependencies on your tablespaces. Many of these functions can be performed directly from the OEM console without starting Storage Manager.

The Tuning Pack includes a product called Tablespace Manager that enables you to coalesce free space in your tablespaces and deallocate unused space from objects, freeing the space for use once again.

It isn’t necessary to deallocate all free space in a tablespace. You can choose to leave some of the unused space allocated to its current segment. You can then select an entire user or specific objects for deallocation, as show in Figure 21.31.

Figure 21.31.  Selecting users and objects for deallocation.

You are then given the option to override the default amount of free space to be left on an object-by-object basis. When you select Finish, a job will be submitted to perform the deallocation.

Diagnostic and Performance Tools

Most of the diagnostic and tuning tools provided by Enterprise Manager are, not surprisingly, located in the Diagnostic and Tuning Packs. Unfortunately, these are separately licensed products. If you were licensed for the Performance Pack you are in luck, for at the time of this writing, Performance Pack customers were given the opportunity to upgrade to the two new packs.

Oracle Performance Manager

Oracle Performance Manager is one of the tuning tools in the Tuning Pack. It provides a graphical representation of many of the hit ratios and statistics discussed in the performance tuning chapters later in this book. Figure 21.32 shows some of the memory-related graphs that can be displayed within Performance Manager. An example of the BUFFER CACHE HIT% graph is shown in Figure 21.33.

Figure 21.32.  Choosing a graph in Performance Manager.

Figure 21.33.  Displaying the buffer cache hit percentage.

Performance Manager can be a good place to start if you have users complaining of slow response times or workstations that appear to be frozen. For example, imagine that users are calling you because it seems to be taking a long time to perform some transaction. You can look under the Contention section of the Display menu or you could look in the Database Instance section as in Figure 21.34. You can display the number of users waiting for some event and, more specifically, the number of users waiting for locks, as I did in Figure 21.35. You’ll notice on the very large bar chart that two users appear to be waiting for locks.

Figure 21.34.  Preparing to display database instance information.

Figure 21.35.  Displaying the number of users waiting for locks.

Oracle Lock Manager

Now that you’ve discovered a potential problem, you can use Oracle Lock Manager, another tool in the Diagnostics Pack, to resolve it. Lock Manager gives you a very nice representation of users holding locks and anyone who might be waiting as a result. In Figure 21.36, you can see that the user Michael has a lock on an object and two other users are waiting. Depending on what Michael might have been doing, and whether you report to him or not, you might decide to kill his session and free up the other users. There is a kill session option under the Locks menu, or you can right-click on the session holding the lock, at which time you will be presented with the option of killing the session.

Figure 21.36.  Users waiting for locks.