FreeLists / oracle-l / Query to check schema sys privs

["Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>]

Hey all,

So, I want a query to check if any apps schemas in our 9.2.0.5 DB have
too many or too few sys privs.  After a week of background head
scratching, I came up with this:

SELECT *
FROM
(
        SELECT grantee, MAX(SYS_CONNECT_BY_PATH(privilege, ' | '))
"PATH1"
        FROM
        (
                SELECT ROW_NUMBER() OVER (PARTITION BY dsp.grantee ORDER
BY dsp.privilege) "MYROW" 
                        , dsp.privilege
                        , dsp.grantee
                FROM dba_users du, dba_sys_privs dsp
                WHERE  du.username = dsp.grantee
                        AND du.initial_rsrc_consumer_group =
'LOCAL_APPLICATION_GROUP'
        )
        START WITH myrow = 1
        CONNECT BY PRIOR myrow = myrow - 1 AND PRIOR grantee = grantee
        GROUP BY grantee
)
WHERE PATH1 != ' | CREATE PROCEDURE | CREATE SEQUENCE | CREATE SESSION |
CREATE SYNONYM';

This should run on most/all 9.2 DBs, but the 'LOCAL_APPLICATION_GROUP'
will need to be modified.  For production, I'd probably plop the
hardcoded PATH1 value into a table, add more tables to the report, etc.

I'm looking for some feedback as to the viability of this query.  I
think it works like I want, but I'd like another (hundred) set of eyes
to look.

TIA!
Rich

Rich Jesse                        System/Database Administrator
rich.jesse@xxxxxxxxxxxxxxxxx      QuadTech, Sussex, WI USA
--
http://www.freelists.org/webpage/oracle-l