CODEWITHSUNDEEP
sqldatabaseoracle-databasesecurityprocess
ptownbro
ptownbro

Reputation: 1284

Is Oracle Db Query Information Processed on Server or Client?

We are having a discussion about data security and an issue came up that I need confirmation on. This is the scenario:

We have two Oracle databases: one on a source server and one a client server. The source server has sensitive information and has been completely secured and deemed compliant. We want to run a group by query to aggregate the data from the source and store it on the client where, once it's aggregated, the data will no longer be sensitive.

Simply put... there are three parts to this 1) the data on the source server, 2) the transmission of data over the network, and 3) the data on client server.

Can some explain where the aggregation occurs? And what data is being transmitted?

In others words, which way is it:

  1. The data is first aggregated on the source server and what is sent over the network is just the aggregated data

  2. The data is sent in detail from the source server over the network and then aggregated on the client server

  3. It's a hybrid of both #1 or #2

  4. Or ... something else I'm missing?

Upvotes: 0

Views: 147

Answers (2)

APC
APC

Reputation: 146269

Oracle optimizes distributed queries to execute them as efficiently as possible, and especially it seeks to minimize the amount of data sent across the network. It will always try to run queries on a remote server and send only the result set to the local server. (The situation is more complicated when joining remote data with local data, but that doesn't seem to be your scenario). Find out more.

So you could run the query on your client server and be sure that the sensitive data remains on the source server and only the aggregate result set is transmitted (your option #1). However, if I were your security officer I would still be unhappy with this solution. It still means granting select access on sensitive tables to a user outside the secure database. That's risky.

A safer solution would be aggregate the data on the secure server, say with a materialized view. Your client server accesses that materialized view to get the aggregated data, either just through a query or view, or with another materialized view, depending on what you need.

The full-on solution would be to have a separate schema on the source database to host the materialized views, so that no outside user even has access to the schema which owns the sensitive data. Obviously maintaining a DMZ is additional overhead, but all security is overhead.

Upvotes: 1

LingYan Meng
LingYan Meng

Reputation: 774

If the policy is that never ever view the sensitive data in client server, then option one is your choice. As to implement option 2, client server will need to have the ability to query the original data from the source server.

To implement option 1, you can aggregate data by using Materialized Views on the source server and then use db link to copy aggregated data to client server.

Upvotes: 1

Related Questions