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
     owner LIKE UPPER('%&owner&')
     AND privilege !='EXECUTE'
 ORDER BY
     owner,table_name,grantor,grantee;
REM
SPOOL OFF
PAUSE Press Enter to continue
SET LINESIZE 80 PAGES 22 VERIFY ON FEEDBACK ON
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF

Notice that the report in Source 11.10 excludes grants of EXECUTE. The EXECUTE grant is given only on stored objects such as packages, functions, and procedures (also Java stored objects); because this is a table grant report, I excluded them. Also, you will have to remove the call for the hierarchy column if you want to run this report on earlier versions of Oracle. Listing 11.9 shows example output from the script in Source 11.10.

LISTING 11.9 Example of output from table grant script.

Date: 10/13/01                                                                       Page 1
Time: 07:17 PM                     TABLE GRANTS BY OWNER AND TABLE                   SYSTEM                                        galinux1 database
 

With

Grant
Owner      Table                      Grantee             Grantor   
Privilege  Option HRY
---------- -------------------------- ------------------- ----------
OE         CUSTOMERS                  PM                  OE        
SELECT     NO     NO
                                      QS_ADM              OE        
SELECT     NO     NO
REFERENCES NO     NO
           INVENTORIES                PM                  OE        
SELECT     NO     NO
           ORDERS                     PM                  OE        
SELECT     NO     NO
           ORDER_ITEMS                PM                  OE        
SELECT     NO     NO
           PRODUCT_DESCRIPTIONS       PM                  OE        
SELECT     NO     NO
           PRODUCT_INFORMATION        PM                  OE        
SELECT     NO     NO
REFERENCES NO     NO
                                      QS_ADM              OE        
SELECT     NO     NO
REFERENCES NO     NO
           WAREHOUSES                 PM                  OE         
SELECT     NO     NO 

ORDSYS     DBA_CARTRIDGES             SELECT_CATALOG_ROLE ORDSYS    
SELECT     NO     NO
           DBA_CARTRIDGE_COMPONENTS   SELECT_CATALOG_ROLE ORDSYS    
SELECT     NO     NO
OUTLN      OL$                        SELECT_CATALOG_ROLE OUTLN     
SELECT     NO     NO
           OL$HINTS                   SELECT_CATALOG_ROLE OUTLN     
SELECT     NO     NO
           OL$NODES                   SELECT_CATALOG_ROLE OUTLN     
SELECT     NO     NO

Another bit of data to be gathered on user (or role) table grants is whether they have column-level grants. Column-level grants don't seem to be used much in Oracle. Perhaps this is because SELECT and DELETE privileges cannot be granted in this manner (they are considered table-level grants). A script to re-create table column-level grants is shown in Source 11.11. Of course, since Oracle8i, there are row-level grants and security options. These row-level security options are known as policies and are maintained through the use of the DBMS_CONTEXT and DBMS_RLS package procedures.

SOURCE 11.11 Example of script to capture table column grants.

CONNECT TELE_DBA/TELE_DBA                        

GRANT
insert(DELETE_STATUS) ON TELE_DBA.CLIENTS          
 TO system                                         
;                                                  
                                                                                                        
GRANT                                              
update(DELETE_STATUS) ON TELE_DBA.CLIENTS          
 TO system                                         
;                                                  

In most environments, weekly monitoring of users is sufficient. In some high-use, rapidly changing environments, where several DBAs or other types of administrative personnel are adding users, the reports may have to be run more frequently. Source 11.12 shows an example of a script to monitor row-level security policies. Output from Source 11.12 is shown in Listing 11.11.

SOURCE 11.12 Example of row-level security-monitoring policy report script.

rem  PURPOSE: Produce report of db policies
rem           used to implement row level grants
rem          
rem  LIMITATIONS: User must have access to DBA_POLICIES
rem
rem  HISTORY:
rem Who:           What:                Date:
rem Mike Ault     Initial creation     5/23/99
rem Mike Ault     Updated to Oracle9i  10/13/01
rem
COLUMN object_owner   FORMAT A10   HEADING 'Object|Owner'
COLUMN object_name    FORMAT A19   HEADING 'Object|Name'
COLUMN policy_group   FORMAT A12   HEADING 'Policy|Group'
COLUMN policy_name    FORMAT A16   HEADING 'Policy|Name'
COLUMN pf_owner       FORMAT A10   HEADING 'Policy|Function|Owner'
COLUMN function       FORMAT A15   HEADING 'Function|Name'
COLUMN sel            FORMAT A3   HEADING 'Sel|?'
COLUMN ins            FORMAT A3   HEADING 'Ins|?'
COLUMN upd            FORMAT A3   HEADING 'Upd|?'
COLUMN del            FORMAT A3   HEADING 'Del|?'
COLUMN chk_option     FORMAT A3   HEADING 'Check|Option'
COLUMN enable         FORMAT A3   HEADING 'Enabled?'
COLUMN static_policy  FORMAT A7   HEADING 'Static?'
SET LINES 132 VERIFY OFF FEEDBACK OFF PAGES 47
START title132 'DB Policies Report'
BREAK ON object_owner
SPOOL rep_out\&db\db_policies
SELECT
  object_owner, object_name,policy_group,
  policy_name,pf_owner,function,
  sel,ins,upd,del,chk_option,
  enable,static_policy
FROM
 dba_policies
ORDER BY
 1,2,3;
SPOOL OFF
SET LINES 80 VERIFY ON FEEDBACK ON PAGES 22
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF

LISTING 11.11 Example of output of row-level security-monitoring policy report script.

Date: 10/13/01
Page: 1
Time: 07:35 PM                                  DB Policies Report                                          SYSTEM                                          galinux1 database 

                                                             Policy
Object     Object              Policy       Policy           Function   Function        Sel Ins Upd Del Che
Owner      Name                Group        Name             Owner      Name            ?   ?   ?   ?   Opt Ena Static?
---------- ------------------- ------------ ---------------- ---------- --------------- --- --- --- --- --- -WKSYS      WK$ATTRIBUTE        SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$ATTR_MAPPING     SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$CRAWLER_CONFIG   SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$CRAWLER_SCHED    SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$CRAWLER_STAT     SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$DATA_SOURCE      SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$GROUP_DS_MAPPING SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$JOB_INFO         SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$MAILLIST         SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$SCHED_MAPPING    SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$SOURCE_GROUP     SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$SYSINFO          SYS_DEFAULT  WK$SYSINFO_POL   WKSYS      WK$SYSINFO_PF   YES YES YES YES YES
YES NO
           WK$SYS_ADMIN        SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$TDS_LOG          SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO
           WK$TRACE            SYS_DEFAULT  WK$INSTADMIN_POL WKSYS      WK$INSTADMIN_PF YES YES YES YES YES
YES NO

Monitoring Currently Logged-in User Processes

A final report in this section that I have found useful lists currently logged-in processes, their user IDs, and operating system IDs, as well as any programs they are currently running. Of course, the Q product on the Precise Web site does a better job, but I don't always have time to start it up just to check on users. The script, called pid.sql, is shown in Source 11.13.

SOURCE 11.13 Example of script to show active users.

REM
REM  Name:     pid.sql
REM
REM  FUNCTION: Generate a list of current oracle sids/pids
REM
COLUMN terminal FORMAT a10   HEADING 'Terminal'
COLUMN program FORMAT  a30   HEADING 'Program'
COLUMN pid     FORMAT  9999  HEADING 'Process|ID'
COLUMN sid     FORMAT  9999  HEADING 'Session|ID'
COLUMN osuser  FORMAT  A15   HEADING 'Operating|System|User'
COLUMN spid    FORMAT  A7    HEADING 'OS|Process|ID'
COLUMN serial# FORMAT  99999 HEADING 'Serial|Number'
SET LINES 132 PAGES 58
BREAK ON username
COMPUTE COUNT OF pid ON username
START title132 "Oracle Processes"
SPOOL rep_out\&db\cur_proc
SELECT
     NVL(a.username,'Null') username,
     b.pid,a.sid,
     DECODE(a.terminal,'?','Detached',a.terminal) terminal,
     b.program,b.spid,a.osuser,a.serial#
 FROM
     v$session a,
     v$process b
WHERE
     a.PADDR = b.ADDR
ORDER by
     a.username,
     b.pid
/
SPOOL OFF
CLEAR BREAKS
CLEAR COLUMNS
SET PAGES 22
TTITLE OFF
PAUSE Press Enter to continue

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