Re: question about user access to tables

From: Mark D Powell <>
Date: Tue, 4 Aug 2009 07:35:35 -0700 (PDT)
Message-ID: <>

On Aug 3, 4:27 pm, ddf <> wrote:
> Comments embedded.
> On Aug 3, 11:28 am, Jonathan Schilling <>
> wrote:
> > I have a situation that's stumping me for some reason.
> > I have an Oracle 9i system, with a database called abcweb.
> > One user of it, called abcuser, accesses it via something like
> > "sqlplus abcuser_at_abcweb_dev.WORLD/pswd1", with ORACLE_SID=abcweb.
> > They see all the tables in the database, per the
> > "select owner, table_name from all_tables;" command; the tables
> > are all owned by the ABCADM account.
> This user probably should have such access.
> > Another user of it, called defuser, accesses it via something like
> > "sqlplus defuser_at_abcweb_dev.WORLD/pswd2", with the same ORACLE_SID.
> > They see only some of the tables in the database that the
> > first user sees, again per the
> > "select owner, table_name from all_tables;" command; the tables
> > they do see are also owned by the ABCADM account.
> There MAY be some valid reason why defuser does not possess the same
> access as abcuser; possibly you should consult the documentation for
> the application before you go changing any privileges.
> > I want to make it so that the second user, defuser, can see the same
> > tables that the first user abcuser sees.  
> That may not be how this particular user account is to be configured.
> Have you read the product documentation on proper setup?
> > How do I do this?  What
> > table
> > or tool or command controls this?  (Obviously, I didn't set up this
> > arrangement, and don't have deep Oracle knowledge.)
> Then possibly you should leave it alone until those who did set this
> up can be consulted?
> > I've searched and looked at a bunch of things, but none of them
> > really address this ... I must be missing something simple ... thanks
> > for any help.
> It isn't rocket science but without knowing HOW these accounts are
> intended to be configured it's probably best to leave well enough
> alone until you find out more details.
> > Jonathan Schilling
> David Fitzjarrell

David has a point. Be careful about changing something without first trying to gain an understanding of how the environment is set up. That being said the following article has a list of useful security related views for seeing such things as what roles a user has, what grants a role has, and the system privileges assigned to a user.

How do I find out which users have the rights, or privileges, to access a given object ?

HTH -- Mark D Powell -- Received on Tue Aug 04 2009 - 09:35:35 CDT

Original text of this message