Best practice for database link

Company owns an ERP oracle database(A). Vendor have installed another product on oracle database(B).
B requires data from staging tables in A on-demand or real time. B also requires to push data into staging table frequently.
Company has got 24*7 support from Vendor. But access is given on case-to-case basis to the vendor to support this database.

What is best practice to enable data exchange? Currently, a database link from Vendor Database to company database is created? Can the vendor exploit the database link to hack into company database?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRMPMSAuthor Commented:
@devind- thanks for sharing the information. It was indeed informative.

Any other inputs are welcome
slightwv (䄆 Netminder) Commented:
>>Can the vendor exploit the database link to hack into company database?
>>link from Vendor Database to company database

It depends.  Which database has the link?  If I read your post properly, the vendor database links to the company database, then yes.  The Vendor can see any tables/views/??? the connecting user can see.

Is there any reason the Vendor database has to 'pull' the records?  Can you set up the link in the company database and 'push' the data?

If not, I would create a user with a view or stored procedure that returns ONLY the data needed.  Create a new user and grant execute on that procedure.  Use that user as the dblink owner.  Then you can control what can be accessed through the link.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:

Please do not just Google and post the first link you find.  If you Google the title of this question the very first link is what you posted.
GRMPMSAuthor Commented:
Thanks for the post.
From the vendor dbase(residing in our company premise and controlled by our company) the link has been created using a database user from our ERP database. The available access for this user is only 5 interface table. 4 tables to pull data into vendor database. 1 table to push data into ERP database.

Hence, I believe security is not compromised. Or am i wrong?
slightwv (䄆 Netminder) Commented:
>>Hence, I believe security is not compromised. Or am i wrong?

Maybe yes, maybe no.  Any connecting user has access to ALL PUBLIC objects.  There are a lot of security issues where SYS and SYSTEM objects have been granted PUBLIC access.

These 'can' be exploited if not properly locked down.

It all comes down to trust.  You cannot plug every hole.  If you feel the Vendor might have malicious intent then don't let them connect.
GRMPMSAuthor Commented:
@sligtwv- thanks for the reply

In our case, company database 'all* objects are secured.
even the power users like senior developers has no access.

If that is the scenario, is it still possible to hack from the vendor database?

slightwv (䄆 Netminder) Commented:
>>In our case, company database 'all* objects are secured.

It all depends on how they did that and the willingness of the attacker.

>>is it still possible to hack from the vendor database?

Its always possible.  New security holes are found daily.  As long as you are sure all 'known' issues have been plugged, it lessens the risk but doesn't remove it.

It goes back to the trust issue.  Since you asked this question it would appear you have trust issues with the vendor.  Since you do: Don't let them in.

Depending on your requirements you might look into options.  For example, maybe the COPY command but that would be a different question.

Sanjeev LabhDatabase ConsultantCommented:
I agree with what Slightwv has suggested. With minumal risk you can provide an isolated user with only privilege on the procedure which extracts the required data. This data can be in csv format which the vendor can use in his DB as external tables and query it directly if he do not want to load into his DB.

But if you don't trust the vendor and do not want to let him access the DB in any case then the extraction can be done by you only and the vendor can read the csv as external table in his DB making things easier and faster.
GRMPMSAuthor Commented:
@sligtwv- thanks for the reply.
  It is not a trust issue. the vendor database is residing in our premises. Vendor can access it only if patches have to be applied. that too under companies supervision. In other words, it is not an 24*7 open connection for the vendor.
 for this instance, I am trying to reverse the db link. ie. from our database to vendor database.

@sanjeevlabh: thanks for the reply.
the current dblink user is a highly restricted user with access to only the 5 interface tables. Nothing more.

BY my question, I wanted to know if methods exist in hacking into the destination database.
slightwv (䄆 Netminder) Commented:
>>I wanted to know if methods exist in hacking into the destination database.

I believe we've covered this.  If a connection exists, there is ALWAYS a possibility.

Since we do not have access to your system, we have no way of knowing what vulnerabilities might exist.  We have to take you at your word they can only access 5 tables and nothing else.

For example:
create user fred identified by flintstone;
grant create session to fred;

Then connect:
sqlplus fred/flintstone

What all can 'fred' do?

By 'default' can he: exec utl_file.fopen;

If so, you have a possible vulnerability that may exist with the DBLINK owner.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.