Re: question about user access to tables
Date: Mon, 3 Aug 2009 10:37:57 -0700 (PDT)
Message-ID: <b90e62bc-778f-4949-a528-b82af9be2acd_at_v23g2000pro.googlegroups.com>
On Aug 3, 9:28 am, Jonathan Schilling <jlselsewh..._at_my-deja.com> 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.
>
> 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.
>
> 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
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
-- _at_home.com is bogus. People lie on the internet? http://www3.signonsandiego.com/stories/2009/aug/03/1b3reviews193210-its-miracle-owner-company-says/?uniontribReceived on Mon Aug 03 2009 - 12:37:57 CDT