 |
|
Oracle Tips by Burleson |
Monitoring User Table and
Column Grants
Keeping track of which users and roles have
access to which objects in the database is a vital part of the
process of monitoring users. Two reports, one on table-level grants
and one on column-level grants, are required to monitor the users’
permissions and grants profile. Source 11.10 shows a script to
generate information on a user’s table-level grants. For the output,
see Listing 11.9.
SOURCE 11.10 Example of script to generate a
table grants report for Oracle9i.
rem
PURPOSE: Produce report of table grants showing
rem
GRANTOR, GRANTEE and specific GRANTS.
rem
LIMITATIONS: User must have access to DBA_TAB_PRIVS
rem
INPUTS: Owner name
rem
OUTPUTS: Report of table grants
rem
rem
HISTORY:
rem
Who: What: Date:
rem Mike
Ault Initial creation 3/2/95
rem Mike
Ault Oracle8 verified 6/10/97
rem Mike
Ault Oracle8i verified 5/15/99
rem Mike
Ault Oracle9i Updated 13/10/01
rem
rem
COLUMN
GRANTEE FORMAT A19 HEADING "Grantee"
COLUMN
OWNER FORMAT A10 HEADING "Owner"
COLUMN
TABLE_NAME FORMAT A26 HEADING "Table"
COLUMN
GRANTOR FORMAT A10 HEADING "Grantor"
COLUMN
PRIVILEGE FORMAT A10 HEADING "Privilege"
COLUMN
GRANTABLE FORMAT A6 HEADING "With|Grant|Option?"
COLUMN
HIERARCHY FORMAT A3 HEADING 'HRY'
REM
BREAK ON
owner SKIP 2 ON table_name ON grantee ON grantor ON REPORT
REM
SET
LINESIZE 100 PAGES 56 VERIFY OFF FEEDBACK OFF
START
title132 "TABLE GRANTS BY OWNER AND TABLE"
SPOOL
rep_out\&db\tab_grants
REM
SELECT
owner,table_name,grantee,grantor,
privilege,grantable,hierarchy
FROM
dba_tab_privs
WHERE...
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. |
