It's been a while since I had to do this, so I don't have the code handy. Basically, you write an after logon trigger that disallows the database connection where the V$SESSION program isn't in the blessed program name OR the user isn't in the group (sys, system, et al) ________________________________ From: Jared Still <jkstill@xxxxxxxxx> To: jdanton1@xxxxxxxxx; oracledba.williams@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx Sent: Wednesday, April 1, 2009 11:44:37 AM Subject: Re: Removing ALL_ views from users - more info How would you proprose to block unapproved programs? ( not allow login if not Access, sqlplus, etc) On 4/1/09, Joey D'Antoni <jdanton1@xxxxxxxxx> wrote: > With that logic, why not just write a trigger to not allow access to > non-whitelisted user IDs to access the database, except through appropriate > programs (blocking SQL*Plus, or Access). > > I'm not disagreeing with your points about security, but if you are going to > lock down ALL_ views, why not lock down access to the instance. > > Joseph D'Antoni > Synthes USA > > > > > ________________________________ > From: Dennis Williams <oracledba.williams@xxxxxxxxx> > To: Mayen.Shah@xxxxxxxxxx > Cc: Andrew Kerber <andrew.kerber@xxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx" > <oracle-l@xxxxxxxxxxxxx>; oracle-l-bounce@xxxxxxxxxxxxx > Sent: Wednesday, April 1, 2009 10:52:06 AM > Subject: Re: Removing ALL_ views from users - more info > > > > List, > > My auditor pointed to this paragraph in a paper posted on Pete Finnigan's > site, Database Security 101 by Richard D. Newallis, SPRINT > www.geocities.com/ckempster/wpapers/oracle/databasesecurity101.pdf As > innocent as the all_users view may seem, it can allow users to find > potential holes in your defenses by giving the names of accounts which the > DBA may not have protected.At this point I'm considering revoking ALL_USERS > from PUBLIC and based on Mayen's note, maybe ALL_SOURCE. Fortunately we are > just preparing for an application release cycle that will provide an > opportunity to test this a bit. > > I think the philosophy is "defense in depth". Not just placing total > reliance on a password. Reminds me of my days in the nuclear power plant > industry. Prove that no pipe can break. Then assume the worst pipe does > break, prove that the containment vessel (which Chernobyl didn't have) can > contain the mess. The assume the containment vessel breaches, count the > casualties for a given wind direction. And you thought your day was bad :-) > > Dennis > > > On Wed, Apr 1, 2009 at 8:06 AM, <Mayen.Shah@xxxxxxxxxx> wrote: > > > Dennis, > > In my case auditors had objected PUBLIC grant to few of the ALL_ views > (ALL_SOURCE, ALL_VIEWS and few more). Revoked privilege from PUBLIC for > these views. Only problem or issue came up was some of developers using SQL > Navigator could not look at their own source code. Asked them to get source > code using USER_ views from sqlplus. > > I did not have any need for ALL_ views as I use DBA_ views.Instead of me > fighting with auditors, I simply asked developers to have their manager get > approval from auditors. That resolve political battle. > > It was on solaris, 10.2.0.4 > > Thanks > Mayen > > > > > > > "Dennis Williams" <oracledba.williams@xxxxxxxxx> > Mar 31 2009 05:03 PM To Mayen Shah/ITS/Lazard@Lazard NYC > cc "Andrew Kerber" <andrew.kerber@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" > <oracle-l@xxxxxxxxxxxxx>, oracle-l-bounce@xxxxxxxxxxxxx > Subject Re: Removing ALL_ views from users > > > > Mayen, > > Just so I understand you correctly, you took a list of each of the ALL_ > views, and revoked each of them from PUBLIC? Any database problems > afterward? Which database version? > > Thanks, > Dennis > > On Tue, Mar 31, 2009 at 11:10 AM, <Mayen.Shah@xxxxxxxxxx> wrote: > > I had similar request from auditors. I lost half the battle. Instead of > dropping ALL_ views, I revoked PUBLIC privilege to satisfy auditors. When > developers complained, I asked them to get approval from auditors...never > heard back. > > Thanks > Mayen > > > > > > "Dennis Williams" <oracledba.williams@xxxxxxxxx> > Sent by: oracle-l-bounce@xxxxxxxxxxxxx > Mar 31 2009 12:03 PM > > Please respond to > oracledba.williams@xxxxxxxxx > > To "Andrew Kerber" <andrew.kerber@xxxxxxxxx> > cc "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> > Subject Re: Removing ALL_ views from users > > > > Thanks Andrew, > > That was pretty much my first response. Unfortunately this has gone further > than that. What I'm asking is: > > Has anyone removed access to any of the ALL_ views? > > I'm guessing that since the views are PUBLIC, that would need to be revoked > first. > > Thanks, > Dennis > > On Mon, Mar 30, 2009 at 9:40 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx> > wrote: > You are talking to an ignorant auditor who thinks the all views show > everything in the database. If he seriously thinks that knowing other > usernames is a security risk, go ahead and revoke that one, then explain to > him that the all* views actually just show objects that each user has access > to, not everything in the database. I ran into this before, and the problem > was the guy was trained in accounting, not oracle. > > > On Mon, Mar 30, 2009 at 9:32 AM, Dennis Williams > <oracledba.williams@xxxxxxxxx> wrote: > List, > > Some security auditors are stating that the ALL_ views are a security risk > and are recommending that I revoke them. In particular, they are pointing to > ALL_USERS as offering a hacker useful information. My guess is that the ALL_ > views are granted to PUBLIC. Has anyone had this requirement? Has anyone > successfully revoked this access? > > Dennis > > > > -- > Andrew W. Kerber > > 'If at first you dont succeed, dont take up skydiving.' > > > -- Sent from Gmail for mobile | mobile.google.com Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist