Security of Oracle database

[TimBiesiek]

Hey all,

I have a bit of a dilemma around security for our Oracle database. We have a corporate database that has about 40 odd different schemas setup that are used by various applications. The applications provide the method of allowing users to view and modify the data as need be. We also use Business Objects for reporting, along with some web based ASP reports. Some of our applications also allow spreadsheet export of the data to allow users to slice and dice, and analyse the data that they own.

The dilemma is that the users have previously had Read only access to some of this data via a RO user that was setup for them. I am not happy with providing this read only direct access to them, due to security issues and potential issues around inefficient SQL affecting database performance.

I'd like to know what other businesses do to allow users access to the data, for them to analyse the data in spreadsheets, or for storing constants (Specific gravity of products, various other ratios etc) that need to be centrally managed so that if it is updated, it is then updated in all the various computational models etc that use these values. Do you allow RO access direct to the DB? Do you have other ways of allowing access to the data without direct RO access? Or do you just flat out say that the only way to ensure these values are up to date is to manually update them based on the data within the application?

Looking for any helpful information! Thanks!

 

[LKBrwnDBA]

1) Use Virtual private database(s)
2) Spreadsheet export of the data are prohibited -- go through Business Objects for data analysis.
3) Create a "reporting database" to avoid security issues and potential issues around inefficient SQL affecting database performance.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

I second LKBrwnDBA's suggestions. In our organization, we have a Data Warehouse (DW) to which all applications send pertinent updates for each application on a daily basis. We tune and secure the DW for Business Intelligence/Reporting queries so that the objectives/performance of the DW database do not conflict with the objectives/performance and security of our on-line transaction processing (OLTP) applications. There is much to be said for separating the OLTP databases from the management-reporting/management-inquiry databases.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.