Monitoring User Roles

Monitoring user setup is important, but it is only the beginning of user monitoring.  A companion script to show roles and administration options is also required. This is shown in Source 11.4. As you can see, it is very important under Oracle to assign roles to users, due to the large number of required grants for the modern environment.

If you assign each privilege to each user as it is required, you will soon find it impossible to manage your user base. Start by assigning only the default roles, then expand those roles as required. For example, for a user who needs to create tables and indexes, a role called CREATOR could be constructed that has the role CONNECT, plus the CREATE_TABLE and CREATE_INDEX privileges. It should also be obvious that the DBA will need to track the roles and have them available at a moment's notice in hard copy to refer to as users are assigned to the system. See Listing 11.3 for an Oracle roles report.

SOURCE 11.4. Example of roles report listing for Oracle7, Oracle8, and Oracle8i.

REM
REM NAME        : sys_role.SQL
REM PURPOSE     : GENERATE SYSTEM GRANTS and ROLES REPORT
REM USE         : CALLED BY SQLPLUS
REM Limitations : None
REM Revisions   :
REM Date           Modified by  Reason for change
REM 08-Apr-1993    MIKE AULT     INITIAL CREATE
REM 10-Jun-1997    Mike Ault     Update to Oracle8
REM 15-May-1999    Mike Ault     No changes for Oracle8i
REM
SET FLUSH OFF TERM OFF PAGESIZE 58  LINESIZE 78
COLUMN grantee           HEADING 'User or Role'
COLUMN admin_option      HEADING Admin?
START title80 'SYSTEM GRANTS AND ROLES REPORT'
DEFINE output = rep_out\&&db\role_report
SPOOL &output
SELECT
     grantee,
     privilege,
     admin_option
FROM
     sys.dba_sys_privs
GROUP BY
     grantee;
SPOOL OFF
SET FLUSH ON TERM ON
CLEAR COLUMNS
TTITLE OFF

LISTING 11.3 Example of output from script in Source 11.4.

Date: 05/22/99           "Your Company Name"                 Page:   1
Time: 03:12 PM              ORACLE ROLES REPORT                   
SYSTEM                       "Your Database"                                                                                    

User or Role            PRIVILEGE                           Adm
---------------------   ---------------------------------   ---
CONNECT                 ALTER SESSION                       NO
                        CREATE CLUSTER                      NO
                        CREATE DATABASE LINK                NO
                        CREATE SEQUENCE                     NO
                        CREATE SESSION                      NO
                        CREATE SYNONYM                      NO
                        CREATE TABLE                        NO
                        CREATE VIEW                         NO

DBA                     ALTER ANY CLUSTER                  YES
                        ALTER ANY INDEX                    YES
                        ALTER ANY PROCEDURE                YES
                        ALTER ANY ROLE                     YES
                        CREATE SEQUENCE                    YES
                        CREATE SESSION                     YES
                        CREATE SNAPSHOT                    YES
                                .
                                .
                                .
                                                                
DEV8_DBA                UNLIMITED TABLESPACE                NO

Monitoring User Profiles

In addition to roles, each user is also assigned profiles. Each user gets the default profile if he or she is not explicitly assigned to a different profile. Profiles control resource usage and, with Oracle8, Oracle8i, and Oracle9i, password security. Source 11.5 is a script that shows the different user profiles. The output of Source 11.5 is shown in Listing 11.4.

SOURCE 11.5 Script to generate a report on Oracle8 and bryond.

REM NAME        : PROFILE_REPORT.SQL
REM PURPOSE     : GENERATE USER PROFILES REPORT
REM Revisions:
REM Date          Modified by     Reason for change
REM 08-Apr-1993     MIKE AULT     INITIAL CREATE
REM 14-May-1999     MIKE AULT     Added resource_type
REM
SET FLUSH OFF TERM OFF PAGESIZE 58 LINESIZE 78 VERIFY OFF FEEDBACK OFF
COLUMN profile           FORMAT a15   HEADING Profile
COLUMN resource_name     FORMAT A25   HEADING 'Resource:'
COLUMN resource_type     FORMAT A9    HEADING 'Resource|Affects'
COLUMN limit             FORMAT a20   HEADING Limit
START title80 'ORACLE PROFILES REPORT'
BREAK ON profile
SPOOL rep_out/&&db/prof_rep
SELECT
     profile,resource_name,
     resource_type,limit
FROM
     sys.dba_profiles
WHERE
     profile LIKE UPPER('%&profile_name%')
ORDER BY
     profile,resource_type,resource_name;
SPOOL OFF
CLEAR COLUMNS
SET FLUSH ON TERM ON VERIFY ON FEEDBACK ON
TTITLE OFF

LISTING 11.4 Example of output from the report in Source 11.5.

Date: 05/22/99                                               Page:   1
Time: 10:13 AM              ORACLE PROFILES REPORT          
SYS                             ORTEST1 database                           

In Listing 11.4, notice that it does not display the values for the DEFAULT profile. You should know that the default profile has unlimited resources. This is fine for DBA type accounts, but for the majority of general users, you will probably want to restrict some of the quotas and define a new profile for them. Remember to set the RESOURCE_LIMIT parameter in the initialization file to TRUE in order to enable resource quota usage. The RESOURCE_LIMIT parameter does not have to be set to use the password features of the profile.

Monitoring User Consumer Groups and Plans

New in Oracle8i was the concept of user resource groups. A resource group specifies how much of a particular resource a specific user (or role) is assigned. For example, a CEO group may get a %CPU assignment of 100, while a clerk may get 40. A resource group is set up using resource plans. The DBA_RSRC series of views are used to monitor resource groups, and the DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER_PRIVS packages are used to maintain resource consumer groups and plans.

Monitoring resource plans involves several layers of reports. The top layer will report on the overall structure of the resource plans, directives, subplans, and resource groups. The next level reports on the directive level and the different CPU usage specifications. The final level deals with the group and system-level grants and administration privileges associated with them. Figure 11.3 shows how the PLAN cluster DBA_ views relate to each other. Source 11.6 is an example of a report that shows the structure of a resource plan. The output from the script in Source 11.6 is shown in Listing 11.5.

Figure 11.3 DBA_USERS view cluster for resource groups.

SOURCE 11.6 Script to generate a report on Oracle8i and beyond group resource plans.

Date: 10/13/01
Time: 06:23 PM
Plan                                                                                   CPU
Name             Sessions 1                PARALLEL_METH1                 QUEUE_METH1  Method   Man?
---------------- ------------------------- ------------------------------ ------------ -------- ----
INTERNAL_PLAN    ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS YES 
INTERNAL_QUIESCE ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS YES 
                 ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT              
SYSTEM_PLAN      ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS NO  
                 ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT              
                 ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT               

Second Half of report:
                                                                                                       Page  1
Resource Plan Report                                                                               SYSTEM

                                          galinux1 database

 Group or     Group or                                                                       CPU
 Subplan Name Subplan  Sessions 2                PARALLEL_METH2                 QUEUE_METH2  Method2
 ------------ -------- ------------------------- ------------------------------ ------------ --------
 OTHER_GROUPS GROUP    ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS
 OTHER_GROUPS GROUP    ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS
 SYS_GROUP    GROUP    ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS
 LOW_GROUP    GROUP    ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS
 OTHER_GROUPS GROUP    ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS
 SYS_GROUP    GROUP    ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE FIFO_TIMEOUT EMPHASIS

Once the plans and groups have been documented, it would be nice to know how they relate to each other, wouldn't it? These relationships are called the plan directives. The plan directives are documented via a script similar to the one shown in Source 11.7. An example of output from Source 11.7 is shown in Listing 11.6.

SOURCE 11.7 Script to generate a report on group resource plan directives.

REM NAME        : PLAN_DIRECTIVES.SQL
REM PURPOSE     : GENERATE DATABASE RESOURCE PLAN DIRECTIVES REPORT
REM Revisions:
REM Date          Modified by     Reason for change
REM 15-May-1999     MIKE AULT     initial creation
REM 13-Oct-2001     Mike Ault     Update to 9i
REM
COLUMN plan                     FORMAT a17     HEADING 'Plan|Name'
COLUMN cpu_method1              FORMAT a8      HEADING 'CPU|Method'
COLUMN mandatory1               FORMAT a3      HEADING 'Man|?'
COLUMN num_plan_directives      FORMAT 999     HEADING 'Num|Dir'
COLUMN group_or_subplan         FORMAT a17     HEADING 'Group or|Subplan Name'
COLUMN type                     FORMAT a5      HEADING 'Type'
COLUMN cpu_method2              FORMAT a8      HEADING 'CPU|Method'
COLUMN cpu_p1                   FORMAT 999     HEADING 'CPU|1%'
COLUMN cpu_p2                   FORMAT 999     HEADING 'CPU|2%'
COLUMN cpu_p3                   FORMAT 999     HEADING 'CPU|3%'
COLUMN cpu_p4                   FORMAT 999     HEADING 'CPU|4%'
COLUMN cpu_p5                   FORMAT 999     HEADING 'CPU|5%'
COLUMN cpu_p6                   FORMAT 999     HEADING 'CPU|6%'
COLUMN cpu_p7                   FORMAT 999     HEADING 'CPU|7%'
COLUMN cpu_p8                   FORMAT 999     HEADING 'CPU|8%'
COLUMN parallel_degree_limit_p1 FORMAT 9999999 HEADING 'Par|Degree'
COLUMN switch_group             FORMAT a15     HEADING 'Switch|Group'
COLUMN switch_time                             HEADING 'Switch|Time'
COLUMN switch_estimate                         HEADING 'Switch|Estimate'
COLUMN max_est_exec_time                       HEADING 'Max Est|Exec Time'
COLUMN undo_pool                               HEADING 'Undo|Pool'
COLUMN active_sess_pool_p1                     HEADING 'Active|Session|Pool'
COLUMN queueing_p1                             HEADING 'Queueing'
REM
SET LINES 200 PAGES 55 VERIFY OFF FEEDBACK OFF
BREAK ON plan on cpu_method1 on mandatory1 on num_plan_directives
START title132 'Resource Plan Directives Report'
SPOOL rep_out\&&db\plan_directives.lis
REM
SELECT DISTINCT
      a.plan,
      a.cpu_method cpu_method1,
      a.mandatory mandatory1,
      b.group_or_subplan,
      DECODE(b.type,'CONSUMER_GROUP','GROUP',b.type) type,
      c.cpu_method cpu_method2,
      b.cpu_p1,b.cpu_p2,b.cpu_p3,b.cpu_p4,
      b.cpu_p5,b.cpu_p6,b.cpu_p7,b.cpu_p8,
      b.active_sess_pool_p1,b.queueing_p1,
      b.parallel_degree_limit_p1,
      b.switch_group,b.switch_time,
      b.switch_estimate,b.max_est_exec_time,
      b.undo_pool
FROM
     dba_rsrc_plans a, dba_rsrc_plan_directives b, dba_rsrc_plans c,
     dba_rsrc_consumer_groups d
WHERE
     a.plan=b.plan
     AND ((b.group_or_subplan = c.plan OR b.group_or_subplan=d.consumer_group))
AND b.status='ACTIVE'
ORDER BY
     1,4,5;
SPOOL OFF
CLEAR COLUMNS
SET VERIFY ON FEEDBACK ON
TTITLE OFF

LISTING 11.6 Output from script to generate a report on Oracle group resource plan directives.

First Half of Report:
Date: 10/13/01                                                                         
Time: 06:50 PM                                                                      

Plan              CPU      Man Group or                CPU       CPU  CPU  CPU  CPU 
Name              Method   ?   Subplan Name      Type  Method     1%   2%   3%   4% 
----------------- -------- --- ----------------- ----- -------- ---- ---- ---- ---- -
INTERNAL_PLAN     EMPHASIS YES OTHER_GROUPS      GROUP EMPHASIS    0    0    0    0 
INTERNAL_QUIESCE  EMPHASIS YES OTHER_GROUPS      GROUP EMPHASIS    0    0    0    0 
                               SYS_GROUP         GROUP EMPHASIS    0    0    0    0 
SYSTEM_PLAN       EMPHASIS NO  LOW_GROUP         GROUP EMPHASIS    0    0  100    0 
                               OTHER_GROUPS      GROUP EMPHASIS    0  100    0    0 
                               SYS_GROUP         GROUP EMPHASIS  100    0    0    0 
Second Half of Report:
Page:   1
Resource Plan Directives Report                                         
SYSTEM
                                        galinux1 database

                       Active
 CPU  CPU  CPU  CPU   Session                Par Switch             Switch    Switch   Max Est
Undo
5%   6%   7%   8%      Pool  Queueing   Degree Group                Time  Estimate Exec Time      Pool
---- ---- ---- ---- --------- --------- -------- --------------- --------- --------- --------- -------   0    0    0    0   1000000   1000000  1000000                   1000000         0   1000000 1000000
   0    0    0    0         0   1000000  1000000                   1000000         0   1000000 1000000
   0    0    0    0   1000000   1000000  1000000                   1000000         0   1000000 1000000
   0    0    0    0   1000000   1000000  1000000                   1000000         0   1000000 1000000
   0    0    0    0   1000000   1000000  1000000                   1000000         0   1000000 1000000
   0    0    0    0   1000000   1000000  1000000                   1000000         0   1000000 1000000

Oracle8i resource plans are usually maintained by a plan administrator. This administrator is usually a SYSTEM user who has the required privileges through the DBA role. However, you may want to isolate the plan administration functions from the system administration functions by giving other users the required privileges through the use of the DBMS_RESOURCE_MANAGER_PRIVS package procedures. These privilege grants need to be monitored. The script in Source 11.8 is an example of a resource manager system privilege grants monitoring script. Listing 11.7 shows what the output from the script in Source 11.8 looks like.

SOURCE 11.8 Script to generate a report on Oracle group resource plan system-level grants.

REM NAME        : PLAN_SYS_GRANTS.SQL
REM PURPOSE     : GENERATE DATABASE RESOURCE PLAN SYSTEM GRANTS REPORT
REM Revisions:
REM Date          Modified by     Reason for change
REM 15-May-1999     MIKE AULT     initial creation
REM
COLUMN privilege       FORMAT a30    HEADING 'Plan System Privilege'
COLUMN grantee         FORMAT a30    HEADING 'User or Role'
COLUMN admin_option    FORMAT a7     HEADING 'Admin?'
BREAK ON privilege
SET LINES 78 VERIFY OFF FEEDBACK OFF
START title80 'Resource Plan System Grants'
SPOOL rep_out\&&db\plan_sys_grants.lis
REM
SELECT
     privilege, grantee, admin_option
FROM
    Dba_rsrc_manager_system_privs
ORDER BY
    Privilege;
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TTITLE OFF

LISTING 11.7 Output from script to generate a report on Oracle8i and Oracle9i user resource plan system-level grants.

Date: 05/22/99                                               Page:   1
Time: 02:34 PM           Resource Plan System Grants         SYSTEM   
                             ORTEST1 database                           

Each user can also be granted some privileges via the DBMS_RESOURCE_MANAGER_PRIVS package, such as the right to switch resource plans. These user-level plan grants also need to be monitored. The script in Source 11.9 should be used to monitor the user-level privileges granted in your database. An example of output from the script in Source 11.9 is shown in Listing 11.8.

SOURCE 11.9 Script to generate a report on Oracle group resource plan user grants.

REM NAME        : PLAN_GROUP_GRANTS.SQL
REM PURPOSE     : GENERATE DATABASE RESOURCE PLAN GROUP GRANTS REPORT
REM Revisions:
REM Date          Modified by     Reason for change
REM 15-May-1999     MIKE AULT     initial creation
REM
COLUMN granted_group   FORMAT a30    HEADING 'Granted Group'
COLUMN grantee         FORMAT a30    HEADING 'User or Role'
COLUMN grant_option    FORMAT a7     HEADING 'Admin?'
COLUMN initial_group   FORMAT a8     HEADING 'Initial?'
BREAK ON granted_group
SET LINES 78 VERIFY OFF FEEDBACK OFF
START title80 'Resource Plan Group Grants'
SPOOL rep_out\&&db\plan_group_grants.lis
REM
SELECT
     Granted_group, grantee, grant_option, initial_group
FROM
    Dba_rsrc_consumer_group_privs
ORDER BY
    Granted_group;
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TTITLE OFF

LISTING 11.8 Output from script to generate a report on Oracle group resource plan user grants.

Date: 05/22/99                                               Page:   1
Time: 02:49 PM          Resource Plan Group Grants          
SYSTEM                       ORTEST1 database                           

Granted Group          User or Role                   Admin?  Initial?
---------------------- ------------------------------ ------- --------
DEFAULT_CONSUMER_GROUP PUBLIC                         YES     YES
LOW_GROUP              PUBLIC                         NO      NO
ONLINE_USERS           TELE_DBA                       NO      YES
SYS_GROUP              SYSTEM                         NO      YES

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