RE: Solaris 2.6/8.1.6/Security (Kinda Long)

Mike,
Somewhere on SHADOW, the users have SELECT, ... privileges on the admin table. This is either granted directly or through a role or some system privilege. Dig around in DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS and you should find the problem.

Henry

-----Original Message-----
Sent: Thursday, October 25, 2001 3:35 PM To: Multiple recipients of list ORACLE-L

Gurus:

This is a question about security, and query-ability.

I have a remote database; let's call it REMOTE. I have local users who want to query REMOTE, but I cannot create additional users there without incurring undue amounts of heartache (not to mention heartburn!).

I created a local database called SHADOW. The SHADOW database has a DBA-level user who owns a private database link to REMOTE. SHADOW also has local users defined who wish to query REMOTE.

As DBA in SHADOW, I created a view (as SELECT *) of one of the tables on REMOTE. I then created a public synonym to this view, and granted select to the local user.

No worries. So far...so good.

Now an admin-level user wants access to a different table on REMOTE. So I did the same thing as for the local non-admin user. Created a view. Created a public synonym. Granted all to the admin user. Now, however, the local non-admin user can see and query the admin's table! This is not what I want!

All privileges are granted through ROLES, the local user gets the 'RO' role, and the admin user gets 'ADM' role. How can I stop the local non-admin user from seeing (and updating, since that view allows updates) the admin's table?

Thanks,
Mike

---
===========================================================================
Michael P. Vergara
Oracle DBA
Guidant Corporation
(909) 914-2304



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 

Author: Vergara, Michael (TEM)
  INET: mvergara_at_guidant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 

Author: Henry Poras
  INET: Henry.Poras_at_ctp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).