RE: DBLINKs in critical production system

Is "security hole" still applicable in 10g assuming no extra privileges are given. In 10g password is encrypted in sys.link$

-----Original Message-----
From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg] Sent: Tuesday, May 01, 2007 8:06 AM
To: Thotangare, Ajay (GTI); oracle-l_at_freelists.org Subject: Re: DBLINKs in critical production system

A DBLink is required for

1. Accessing CURRENT data from a remote database.
2. Refreshing Materialized Views (scheduled or on demand) which fetch a subset or a joined set of data from a remote database[MVs were also known as "Snapshots"]
3. The inherent transfer mechanism in Advanced Replication.

The argument that DBLink requirement is obviated by MVs or Replication is not strong
because MVs and Replication, themselves, need DBLinks. What is important is
what data is opened through the DBLink and how it is accessed (what queries are run and when).

1. If you create a DBLink connecting to the base schema (the schema actually owning the tables being referenced) then that is a big NO NO (read "Security Hole"). The DBLink should be connecting to a "shadow" account which has only limited (ie "SELECT" only) privileges on only the subset of tables that are actually required to be accessed. See http://hemantoracledba.blogspot.com/2007/02/creating-database-links.html
2. That DBLinks are a performance issue is like saying "User Queries are a Performance Issue". You, as the DBA, must ask first "Why do you need the DBLink ? What queries will be running across the DBLink ? How frequently will the queries be running ?" You must also turn to the Application Manager owning the Database being accessed if he approves of those queries being made against his database -- queries coming from another database / another application.

DBLinks must obtain approval from the Application team on both sides , not just the DBA alone,
for two reasons

1. Knowing *which* data is being accessed (it should open only a specific list of tables/views to access)
2. Understanding the performance impact of those queries being run -- on _both_ databases, particularly where the DBLink is being used to run distributed queries that join tables across databases. Ideally, DBLinks should not be exposed to end-user adhoc queries but only through application controls / scheduled jobs.

At 10:43 PM Monday, Thotangare, Ajay \(GTI\) wrote:

>Hi Group,
>
>I have a question about dblink. I always hear that
>
>- dblinks are not good in production system.
>
>- dblink , ohh!! not in critical production system
>
>- dblink are not safe
>
>----------

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com

"First they ignore you, then they laugh at you, then they fight you, then you win" !"
Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to this e-mail. http://www.ml.com/email_terms/

--
http://www.freelists.org/webpage/oracle-l