From oracle-l-bounce@freelists.org Fri Dec 2 13:17:11 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jB2JHBwj010720 for ; Fri, 2 Dec 2005 13:17:11 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id jB2JH6AX010702 for ; Fri, 2 Dec 2005 13:17:06 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C6B9F240F04; Fri, 2 Dec 2005 14:16:58 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11326-01; Fri, 2 Dec 2005 14:16:58 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 40B0E240F41; Fri, 2 Dec 2005 14:16:58 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 02 Dec 2005 14:15:03 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ED84D240E6E for ; Fri, 2 Dec 2005 14:15:02 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11044-09 for ; Fri, 2 Dec 2005 14:15:02 -0500 (EST) Received: from mx1out.acxiom.com (mx1out.acxiom.com [198.160.100.226]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B1F03240DEB for ; Fri, 2 Dec 2005 14:15:02 -0500 (EST) Received: from unknown (HELO ACXMSXFE02.Corp.Acxiom.net) ([10.85.225.22]) by mx1out.acxiom.com with ESMTP; 02 Dec 2005 13:17:02 -0600 X-IronPort-AV: i="3.99,207,1131343200"; d="scan'208"; a="66695663:sNHT27064478" Received: from CWYMSX04.Corp.Acxiom.net ([10.85.225.34]) by ACXMSXFE02.Corp.Acxiom.net with Microsoft SMTPSVC(6.0.3790.211); Fri, 2 Dec 2005 13:15:01 -0600 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: RE: Query to check schema sys privs Date: Fri, 2 Dec 2005 13:14:59 -0600 Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36012ABAA7@CWYMSX04.Corp.Acxiom.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Query to check schema sys privs Thread-Index: AcX3c5uQLgU3FLBkSR62yAVvIYlp5AAAH/eQ From: "Herring Dave - dherri" To: , X-OriginalArrivalTime: 02 Dec 2005 19:15:01.0607 (UTC) FILETIME=[B17EC770:01C5F774] Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Content-Type: text/plain; charset="us-ascii" X-archive-position: 28874 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Dave.Herring@acxiom.com Precedence: normal Reply-To: Dave.Herring@acxiom.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.9 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 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 ------------------------------------- > -----Original Message----- > From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] > On Behalf Of Jesse, Rich > Sent: Friday, December 02, 2005 1:07 PM > To: Oracle-L@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@quadtechworld.com QuadTech, Sussex, WI USA > -- > http://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. ************************************************************************* -- http://www.freelists.org/webpage/oracle-l