RE: An approach to isolating external users--opinions wanted

Lisa,

I agree with Stephane. I would create the account in the Production database, and grant select access on the views to that account. It is much easier to manage, not having to remember that the production database is being accessed from "another" database via db links. Also, it solves all types of management issues -

• like what if the production database is down, but development is up. it leads to confusion to the user that they can "log in" to the database, but not get at the data
• it is much easier to report to audit-types what users have access to what data if they all are in one database

the only other suggestion I would have is to give these individuals accounts directly - not through a shared Oracle account. when they leave the organization, or their duties change and they no longer should have access to the data, you simply drop their Oracle account, and their access disappears.

hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, June 27, 2001 6:50 PM
To: Multiple recipients of list ORACLE-L

Lisa Clary wrote:
>
> We have approximately 40 or so people external to this office and to our
> operation that require access to confidential information (with that
> population is increasing). The connections to Oracle are made through
> DSN/ODBC using various applications (access, excel,etc) and data are
> restricted through views. Currently, these users log into the development
> database (not production) and access the data from production through a
> dblink. The only data they can see is what is presented to them through
the
> views.
>
> I would like to keep these users separate from our own internal users and
> wondered whether creating another (very small) instance just for them is a
> good idea. The new instance would contain no data, but would contain the
> predefined views and database links to the production database. That way
if
> the development server needs to go down, these people still have access to
> production data via the link & their views.
>
> I don't know if anyone has the same situation, and what the approach was
to
> accomplish the task (providing secure data) without compromising the
> security of a production database. Does this sound feasible?
>
> Thanks,
>
> lc
>

Lisa,

   There is no such thing as 'very small' nowadays. The problem with any new instance is that it will require lots of memory, etc. Of course it sound feasible, and indeed it would probably be better than what you currently have. Two questions though :

• do your external users need 'real-time' access? What about using snapshots? It would make them really independent.
• watch your database link(s). Many people create, somewhat inconsiderately, a database link which connects as the schema owner. Even if you have views layered over this and are careful about privileges, it's IMHO no luxury to create a special account 'EXTERNAL_ACCESS' on your prod database, with only two privileges, create session and create synonym, to grant whatever external users need to EXTERNAL_ACCESS, to create the suitable synonyms in the EXTERNAL_ACCESS account, and then to connect to this account from the other machine - which is not contradictory with anything which can already be in place on the other machine. It has the advantage of documenting what external users can actually see.

-- 
Regards,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:    +44  (0) 7050-696-449 
Performance Tools & Free Scripts
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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