| |
 |
|
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
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. |
