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 (DOT) 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.

Another user of it, called defuser, accesses it via something like
"sqlplus defuser (AT) abcweb_dev (DOT) 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.

I want to make it so that the second user, defuser, can see the same
tables that the first user abcuser sees. 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.)
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.

Jonathan Schilling

On Aug 3, 9:28*am, Jonathan Schilling <jlselsewh... (AT) my-deja (DOT) com>
wrote:
The concept you are looking for is "grant." Someone has granted
privilege for those tables to be seen by abc, but not to def. It is
also possible that the privilege has been granted to roles, and abc
has the role and you just have to grant or set the role to def. It
may be simple or complicated. Synonyms may also be used, for example,
by def to not have to specify abcadm in object name access.

Get thee to tahiti.oracle.com and search for grant. You can do all
this from the command line (which is preferable for learning and
understanding):
select view_name from dba_views where view_name like '%PRIV%';
to see various views you can use, and numerous GUI tools can do it.
In Enterprise Manager (the default Oracle tool for 10g) it's under
Administration --> Users & Privileges, in 9i there's the Oracle
Enterprise Manager (OEM), a separate client installation, it's been a
while I don't remember where to go there, but I don't recall it being
difficult to find once you get the thing running, drill down from
users to privileges or some such thing.

Hopefully, whoever set it up understood and used roles. The four
things you must understand to figure this out: grants, roles,
privileges and synonyms.

Maybe if you are lucky someone left some scripts laying about that
defined it all.

jg
--
@home.com is bogus.
People lie on the internet?
http://www3.signonsandiego.com/stori...ays/?uniontrib

Comments embedded.

On Aug 3, 11:28*am, Jonathan Schilling <jlselsewh... (AT) my-deja (DOT) com>
wrote:
This user probably should have such access.

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.

That may not be how this particular user account is to be configured.
Have you read the product documentation on proper setup?

Then possibly you should leave it alone until those who did set this
up can be consulted?

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.


David Fitzjarrell

On Aug 3, 4:27*pm, ddf <orat... (AT) msn (DOT) com> wrote:
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 ? http://www.jlcomp.demon.co.uk/faq/privileges.html

HTH -- Mark D Powell --

On Aug 4, 10:35*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
Thanks very much for the several responses to this. I'm a veteran guy
and I'm well aware of the pitfalls of monkeying with stuff whose
design one
doesn't really understand. In this case, I was just looking to see if
the one
user could grant access to the other use; I'd used grant equivalents
in
MySQL and Postgres before, but was missing the ALL_TAB_PRIVS that
one of the URLs pointed to. In any event, my user didn't have that
kind
of privilege and I got the sys admin to grant it instead, no harm done.