Re: Removing ALL_ views from users - more info

  • From: Joey D'Antoni <jdanton1@xxxxxxxxx>
  • To: oracledba.williams@xxxxxxxxx
  • Date: Wed, 1 Apr 2009 07:57:41 -0700 (PDT)

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


My auditor pointed to this paragraph in a paper posted on Pete Finnigan's site, 
Database Security 101 by Richard D. Newallis, SPRINT 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 

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 :-)


On Wed, Apr 1, 2009 at 8:06 AM, <Mayen.Shah@xxxxxxxxxx> wrote:


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, 


"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  

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? 

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 


"Dennis Williams" <oracledba.williams@xxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
Mar 31 2009 12:03 PM

Please respond to

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 

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> 
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? 

Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'


Other related posts: