| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query to check schema sys privs
I've got a quick comment about your SQL, not the privs issue: it appears
you're generation a concatenated list of privs using
SYS_CONNECT_BY_PATH. For what its worth, within asktom.com an example
was posted (by Jack Douglas) on using a user-defined aggregate function
as a much faster method.
If you're interested, I made a few changes to it to allow sorting within the string and could forward the code.
Dave
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Jesse, Rich
> Sent: Friday, December 02, 2005 1:07 PM
> To: Oracle-L_at_freelists.org
> Subject: Query to check schema sys privs
>
> 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
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
Thank you.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 02 2005 - 13:17:11 CST
 |
 |