Query to check schema sys privs

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Fri, 2 Dec 2005 13:07:15 -0600
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE6697545C33@QTEX1.qg.com>

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_at_quadtechworld.com      QuadTech, Sussex, WI USA

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 02 2005 - 13:09:26 CST