Re: Removing ALL_ views from users - more info

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_at_gmail.com>
To: jdanton1_at_yahoo.com; oracledba.williams_at_gmail.com; oracle-l_at_freelists.org
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_at_yahoo.com> 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_at_gmail.com>
> To: Mayen.Shah_at_lazard.com
> Cc: Andrew Kerber <andrew.kerber_at_gmail.com>; "oracle-l_at_freelists.org"
> <oracle-l_at_freelists.org>; oracle-l-bounce_at_freelists.org
> 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_at_lazard.com> 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_at_gmail.com>
> Mar 31 2009 05:03 PM� To Mayen Shah/ITS/Lazard_at_Lazard NYC
> cc "Andrew Kerber" <andrew.kerber_at_gmail.com>, "oracle-l_at_freelists.org"
> <oracle-l_at_freelists.org>, oracle-l-bounce_at_freelists.org
> 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_at_lazard.com> 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_at_gmail.com>
> Sent by: oracle-l-bounce_at_freelists.org
> Mar 31 2009 12:03 PM
>
> Please respond to
> oracledba.williams_at_gmail.com
>
> To "Andrew Kerber" <andrew.kerber_at_gmail.com>
> cc "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
> 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_at_gmail.com>
> 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_at_gmail.com> 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



      

--
http://www.freelists.org/webpage/oracle-l