 |
|
Oracle Tips by Burleson |
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.
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...
See code depot
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
