Monitoring Users and Other Database Objects

Chapter 10 covered monitoring of table-related objects in Oracle; this chapter continues to discuss monitoring, but as it applies to virtually all other database objects. And note that because information about users is stored in the database, and because a DBA creates users, I am including users in the ?other? database object category. Like tables, clusters, snapshots, types, and indexes, all other database objects are monitored using the data dictionary tables and views.

Note: Refer to the Reference manual on the documentation website, technet.oracle.com, as you review the scripts provided here. The data dictionary is a powerful tool in the hands of someone who knows how to use it.

Using the V$ and DBA_ Views for Monitoring Users

What exactly do DBAs need to know about the users of their databases? The DBA needs to keep track of many important facts about each user, including privileges, quotas, tables owned, filespace used, and database default locations, just to name a few. The Oracle Administrator toolbar (part of the Oracle Enterprise Manager toolset) has a nice GUI in its Security Manager section (see Figure 11.1), but it has no report capability.

Figure 11.1 Oracle Enterprise Manager Security Manager screen.

The DBA_USERS view is the root of a tree of related DBA_ views that give a full picture of the privileges, resources, and roles granted to users. Figure 11.2 diagrams how all these views in the DBA_ user view cluster relate to each other.

Figure 11.2 DBA_USERS view cluster.

How can the DBA keep track of this information for hundreds or possibly thousands of users? Scribble it down as it displays on the SVRMGR or OEM screen? Use some sort of screen capture or screen print facility? Hardly. To keep track of this information, the DBA needs reports. Whether a DBA store these reports online or uses a three-ring binder, good reports detail exactly what a DBA needs to know. Let's address the relevant topics.

Monitoring User Setup

The first report we will look at implements the DBA_USERS view to provide information on users, user default and temporary tablespace assignments, and user database-level privileges. The script for this report is shown in Source 11.1.

SOURCE 11.1 Example of user report listing.

SOURCE 11.2 Example user report.

REM
REM NAME        : DB_USER.SQL
REM
REM FUNCTION    : GENERATE USER_REPORT
REM Limitations : None
REM
REM Updates     : MRA 6/10/97 added Oracle8 account status
REM               MRA 5/14/99 Added Oracle8i Resource Group
REM
SET PAGESIZE 58  LINESIZE 131 FEEDBACK OFF
rem
COLUMN username                 FORMAT a10 HEADING User
COLUMN account_status           FORMAT a10 HEADING Status
COLUMN default_tablespace       FORMAT a15 HEADING Default
COLUMN temporary_tablespace     FORMAT a15 HEADING Temporary
COLUMN granted_role             FORMAT a21 HEADING Roles
COLUMN default_role             FORMAT a9  HEADING Default?
COLUMN admin_option             FORMAT a7  HEADING Admin?
COLUMN profile                  FORMAT a15 HEADING Profile
COLUMN initial_rsrc_consumer_group FORMAT a10 HEADING 'Resource|Group'
COLUMN lock_date                HEADING 'Date|Locked'
COLUMN expiry_date              HEADING 'Expiry_date'
rem
START title132 'ORACLE USER REPORT'
DEFINE output = rep_out\&db\db_user
BREAK ON username SKIP 1 ON account_status ON default_tablespace
ON temporary_tablespace ON profile
SPOOL &output
rem
SELECT a.username,
       a.account_status,
       TO_CHAR(a.lock_date,'dd-mon-yyyy hh24:mi') lock_date,
       TO_CHAR(a.expiry_date,'dd-mon-yyyy hh24:mi') expiry_date,
       a.default_tablespace,a.temporary_tablespace,
       a.profile,b.granted_role,
       b.admin_option,b.default_role,
       a.initial_rsrc_consumer_group
FROM sys.dba_users a,
     sys.dba_role_privs b
WHERE a.username = b.grantee
ORDER BY username,
         default_tablespace,temporary_tablespace,
         profile, granted_role;
rem
SPOOL OFF
SET TERMOUT ON FLUSH ON FEEDBACK ON VERIFY ON
CLEAR COLUMNS
CLEAR BREAKS
PAUSE Press Enter to continue

LISTING 11.1 Example of user report format.

Date: 05/22/99                                                                          Page:   1
Time: 08:45 AM                         ORACLE USER REPORT                   
SYSTEM                                  ORTEST1 database

                                         Resource
User    Default   Temp   Profile Status Group                  Roles
Admin?  Def?
------- --------- ------ ------- ------ ---------------------- ---------------------- DBSNMP  SYSTEM    TEMP   DEFAULT OPEN   DEFAULT_CONSUMER_GROUP CONNECT                NO      YES

                                                               RESOURCE
NO     YES
                                                              
SNMPAGENT         NO     YES
MIGRATE SYSTEM    TEMP   DEFAULT OPEN   DEFAULT_CONSUMER_GROUP DBA
NO     YES
                                                               RESOURCE
NO     YES
ORDSYS  SYSTEM    TEMP   DEFAULT OPEN   DEFAULT_CONSUMER_GROUP CONNECT
NO     YES
                                                               RESOURCE               NO     YES
OUTLN   SYSTEM    TEMP   DEFAULT OPEN   DEFAULT_CONSUMER_GROUP CONNECT                NO     YES
                                                               RESOURCE 
NO     YES
SYS     SYSTEM    TEMP   DEFAULT OPEN   SYS_GROUP             
AQ_ADMINISTRATOR_ROLE    YES     YES
AQ_USER_ROLE             YES     YES                                                                CONNECT                YES    YES
                                                               DBA                    YES    YES
                                                               
DELETE_CATALOG_ROLE      YES     YES
EXECUTE_CATALOG_ROLE     YES     YES
EXP_FULL_DATABASE        YES     YES
IMP_FULL_DATABASE        YES     YES
RECOVERY_CATALOG_OWNER   YES     YES
RESOURCE                 YES     YES
SELECT_CATALOG_ROLE      YES     YES
SNMPAGENT                YES     YES

SYSTEM  TOOLS     TEMP   DEFAULT OPEN   SYS_GROUP             
AQ_ADMINISTRATOR_ROLE    YES     YES
                                                               DBA                    YES    YES
                                                               TEST                   YES    YES

TEL_DBA TELE_DATA TEMP   DEFAULT OPEN   DEFAULT_CONSUMER_GROUP CONNECT                NO     YES
                                                               RESOURCE               NO     YES

As you can see, this report takes care of several of our requirements: user names, default tablespace assignments, temporary tablespace assignments, and database roles. The report is currently sorted, using the ORDER BY command, by user name, tablespace assignments, profile, resource group assignment, and status. If you prefer, it could be sorted by default or temporary tablespace or by individual role. In this script, there will be one row for each role granted to the user.

In Source 11.3. we examine user expiration information. The report columns will be populated only with date information if you are using the password expiration features in Oracle8 and Oracle8i in profiles. The expiry date in a profile will be set based on the last password change. If a user has just been assigned to a profile, I suggest using the ALTER USER command to expire the user's password, forcing him or her to reset it, and set the expiry date. The output from the script in Source 11.3 is shown in Listing 11.2.

SOURCE 11.3 Example of script to report account expiry status.

REM
REM NAME        : USER_EXPIRE.SQL
REM
REM FUNCTION    : GENERATE USER EXPIRY DATA REPORT
REM Limitations : None
REM
REM Updates     : MRA 5/22/99 Created
REM
COLUMN account_status           FORMAT a15 HEADING Status
COLUMN default_tablespace       FORMAT a14 HEADING Default
COLUMN temporary_tablespace     FORMAT a10 HEADING Temporary
COLUMN username                 FORMAT a12 HEADING User
COLUMN lock_date                FORMAT a11 HEADING 'Date|Locked'
COLUMN expiry_date              FORMAT a11 HEADING 'Expiry|Date'
COLUMN profile                  FORMAT a15 HEADING Profile
SET PAGESIZE 58  LINESIZE 131 FEEDBACK OFF
START title132 'ORACLE USER EXPIRATION REPORT'
BREAK ON username SKIP 1 ON default_tablespace ON temporary_tablespace ON profile ON
account_status
SPOOL rep_out\&db\user_expire
rem
SELECT username,
       default_tablespace,temporary_tablespace,
       profile,account_status,
       TO_CHAR(lock_date,'dd-mon-yyyy') lock_date,
       TO_CHAR(expiry_date,'dd-mon-yyyy') expiry_date
FROM sys.dba_users
ORDER BY username,
         default_tablespace,temporary_tablespace,
         profile, account_status;
rem
SPOOL OFF
SET TERMOUT ON FLUSH ON FEEDBACK ON VERIFY ON
CLEAR COLUMNS
CLEAR BREAKS
PAUSE Press Enter to continue

LISTING 11.2 Example of output from the user expiry report.

 
This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".