Query to check schema sys privs
- From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 2 Dec 2005 13:07:15 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
