Monday, July 19, 2010

ORA-00942 Querying Tablespaces from Enterprise Manager

In order to meet the minimum security requirements, the SYSTEM user use must be restricted, only the actual DBA must have access to it. Other users requiring access to Enterprise Manager to monitor and "view" must be granted especific minimum privileges.
The SELECT_CATALOG_ROLE and the CONNECT roles are good enough to see most of the E.M. contents, but when trying to access the Tablespaces page from Enterprise Manager the ORA-00942 error shows up.
When tracing I discovered the query used to fill up the Tablespaces Page:

SELECT /*+first_rows */ d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0), DECODE(d.contents,'UNDO', NVL(u.bytes, 0)/1024/1024, NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024), DECODE(d.contents,'UNDO', NVL(u.bytes / a.bytes * 100, 0), NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)), a.autoext, DECODE(d.contents,'UNDO', NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, NVL(f.bytes, 0) / 1024 / 1024), d.status, a.count, d.contents, d.extent_management, d.segment_space_management/*, d.encrypted*/ FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, count(file_id) count, decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext from dba_data_files GROUP BY tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f, (SELECT tablespace_name, SUM(bytes) bytes FROM (SELECT tablespace_name,sum (bytes) bytes,status from dba_undo_extents WHERE status ='ACTIVE' group by tablespace_name,status
UNION ALL
SELECT tablespace_name,sum(bytes) bytes,status from dba_undo_extents WHERE status ='UNEXPIRED' group by tablespace_name,status ) group by tablespace_name ) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') AND d.tablespace_name like '%' /*:1*/ UNION ALL SELECT d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0), NVL(t.bytes, 0)/1024/1024, NVL(t.bytes / a.bytes * 100, 0), a.autoext, (NVL(a.bytes ,0)/1024/1024 - NVL(t.bytes, 0)/1024/1024), d.status, a.count, d.contents, d.extent_management, d.segment_space_management/*, d.encrypted*/ FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, count(file_id) count, decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext from dba_temp_files group by tablespace_name) a, (select ss.tablespace_name , sum((ss.used_blocks*ts.blocksize)) bytes from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name group by ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' and d.tablespace_name like '%' /*:2*/ ORDER BY 1;


Basically all of the views mentioned here are accessible through the SELECT_CATALOG_ROLE, but SYS.TS$, which requires an explicit SELECT privilege granted from SYS on this view to the user.