external database links in oracle

I've been reading recently that there are major risks associated with external links into an oracle database (at the database level, i.e. feeder systems), I just wondered how in Oracle 11g you can see what links exist into a live database - and then what exactly would you be looking for in terms of risk? Permissions of the account doing the link? Where would you see those?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
actually, the database links shown by PortletPaul are the links from your current db to OTHER databases.

you cannot see which other databases are linking like that into YOUR database.

anyhow, in regards to db links, the RISK is mainly on PUBLIC db links, which are configured with HIGH permissions on the remote db. any login on your db can then perform any actions of that remote login on the remote db.

say you have DB MASTER, with a login call POWER_USER with DELETE ANY TABLE permissions.

if on DB CHILD you create a public db link to DB MASTER using the POWER_USER user, and you create a login READ_ONLY_USER with locally only SELECT permissions, that login, connected to DB CHILD still can do a DELETE in any tables on the DB MASTER ...
PortletPaulConnect With a Mentor Commented:
>>how in Oracle 11g you can see what links exist into a live database
      , db_link
      , username
      , host
FROM dba_db_links
        , db_link

Open in new window

pma111Author Commented:
Whats the risks? What specifically should we be looking for in terms of risk?
Is it about the permissions the user account has?
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

:) I had to feed the family... & If no-one goes over the remaining questions I'll come back tomorrow - sorry
pma111Author Commented:
So theres no way whatsoever to see what links you have importing data into your database? That is our concern, getting a list of which databases have access to our main database, for import reasons. No queries to determine those accounts?
Geert GConnect With a Mentor Oracle dbaCommented:
checking v$session, v$sql tables periodically is the only way you will be able to see what
happens and what comes in.

it's not the databases connecting to your database which can cause the problems
it's the people doing the wrong things or the mistakes
after you have listed the databases accessing, you still need to find the people behind the cause.

hardening for disasters is by taking precautions > backups, high availabilty, standby, disaster recover etc ...

first level of protection is the network: firewalls, antivirus, etc
one of the next levels is the access to the items like the database
pma111Author Commented:
So when you permit an external database access to your database you grant them an account in your database for the connection, and for them to allow you access to their database they grant you an account in their database? How does it work if for example its 2 different rdbms, ie oracle to mssql and vice versa?
Geert GOracle dbaCommented:
it's not necessary to have both ways

actually to grant them access:
you create an account on your db for them ... with all the priviliges you want to give them
same like you would for any person

in their database they can setup a database link to your database with that account you just gave
pma111Author Commented:
So if you want them to import data directly from their db to yours there's tyically no requirement at all for them to give you any access whatsoever to their db?
pma111Author Commented:
Can you give an indication of what data goes in these v$session, v$sql tables?
Geert GOracle dbaCommented:
requirements are what you make of them
if you indicate then can only get a connection to your db if you have one to theirs, then that's a requirement

a comparison :
> when you go into a shop, you have access to almost everything they have for a certain price ... Does the shop owner ask you for access to almost everything you have at home for a certain price when he let's you in  ?

sys.v$session is info about all the session connected to the database
system users + everytbody else

if you run a select * from v$session, you should see you'r own connection with in column sql_id what query you are execution

if you then select * from v$sql where sql_id = 'your_sql_id'; you'll see what that query is

check the reference for more info on system tables:
>>what links exist into a live database
apologies, my initial comment would not display links into (as has already been noted)

>>feeder systems
>>and then what exactly would you be looking for in terms of risk?
>>Permissions of the account doing the link?
others above have already addressed the "privileges angle" about such links

Other risks exist also. By linking databases together, then building logic in either or both ends of the link; you are embedding strong dependencies between them. These dependencies are often overlooked and/or misunderstood. This might cause a chain of impacts if one db is unavailable for example.

If/when you need to restore or rebuild a "system" (e.g. a remote DR environment) applications can break because some "small" dependency has not been detected and not been replicated.

linking databases can also be a strong deterrent to upgrades (of applications and/or database versions) as the complexity of regression testing increases and impacts potentially ripple into each linked db (which might indeed be a chain of links, with a chain of impacts).

Several of my clients will not permit db to db links on the basis of such risks
(well, that is their guiding principle, exceptions can be made, but any dependencies formed are documented and replicated in supporting environments)

[btw: a mitigation strategy of these risks can be to "loosely couple" the systems]

>>Where would you see those?
Information regarding privileges is held in several tables:

system privileges are held in DBA_SYS_PRIVS
object privileges are held in DBA_TAB_PRIVS
roles held in DBA_ROLE_PRIVS
roles can hold system and/or object privileges
(i.e. back to DBA_SYS_PRIVS & DBA_TAB_PRIVS)

A script I've seen recommended at EE is by Pete Finnigan: find_all_privs.sql
"This scripts lists ROLES, SYSTEM privileges and object privileges granted to a user. If a ROLE is found then it is checked recursively."

Others here may have their own or know of alternatives.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.