RE: Query to check schema sys privs
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <Rich.Jesse@xxxxxxxxxxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 2 Dec 2005 13:14:59 -0600
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
-------------------------------------
Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri@xxxxxxxxxx>
-------------------------------------
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Jesse, Rich
> Sent: Friday, December 02, 2005 1:07 PM
> To: Oracle-L@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
> --
> //www.freelists.org/webpage/oracle-l
*************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.
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.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
