Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Can we revoke select priv from all_users tables??

Re: Can we revoke select priv from all_users tables??

From: Tim Gorman <>
Date: Wed, 08 Aug 2007 10:39:28 -0600
Message-ID: <>


Reminds me of the old JD Edwards "while paper" that stated that the
Oracle parameter "SPIN_COUNT" should be set to a value derived from the
number of disk drives on which the database is housed  multipled by the
RPM of those drives.  For example, if your database is situated on
eight hard drives of 10,000 rpm apiece, then the Oracle parameter
"SPIN_COUNT" (later "_SPIN_COUNT") should be set to 80,000.

When I worked at Oracle Consulting in the 1990s, I worked for the "commercial" consulting group that dealt with non-government, non-military customers -- governmental or military organizations were the province of the "federal" consulting group.  One day, one of the practice managers from the "federal" group stopped by to get something off his chest.  He asked us why our group was dealing with "Federal Express" and why the "federal" group wasn't handling that customer?  Ummm.... hmmmm.... where to start?  I'm surprised he didn't ask why we were dealing with "General Motors", as well... too bad he didn't ask about "corporal punishment"... :-)

You just can't make this stuff up.  <shake-of-head>

Andrew Kerber wrote:
I recently received a copy of a 'best practices' document that says that users should not have access to any 'all_' views.  Its a really stupid idea, I suspect that whoever put it in the document thinks that all actually looks at all, as opposed to just what the user has access to.

On 8/8/07, JOHNSTON, WAYNE <> wrote:



It may be just my nature, but I have to ask why you want to do this?  Is there some audit requirement that a user not query all_tables to see the tables he has been granted access to?    


Wayne Johnston

Adabas Database Administrator

Oracle Database Administrator

FCCJ: 904-997-2907

CELL: 904-607-1889


No one shall be subjected to arbitrary arrest, detention or exile.

Article 9. United Nations Universal Declaration of Human Rights, December 10, 1948



From: [mailto:] On Behalf Of Ram Srinivasan
Sent: Tuesday, August 07, 2007 5:49 PM
Subject: RE: Can we revoke select priv from all_users tables??



  I am creating a user as:


create user test1 identified by fannymayofomaha

default tablespace def001

temporary tablespace temp01;

  This user test1 has access to all the user_  tables and all_  tables.  I believe that when a user is created he automatically receives the select priv to these all_ and user_ tables.


  Is there a way to REVOKE the select priv from the all_ tables for this user?


  Thanks for your input.

Ram Srinivasan
Charlottesville, VA.

Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'
-- Received on Wed Aug 08 2007 - 11:39:28 CDT

Original text of this message