Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Oracle Redaction with OBIEE
Hello,
We have a requirement in which we are to hide sensitive data (on the column level) from specific OBIEE users based on roles assigned in EBS.
Our current setup: EBS (ERP source, holds all users) > Singly Sign On to OBIEE 11g (used for reporting).
We want to mask/redact data at the database level. I know OBIEE stores SQL queries that are run against the application, but does each query also have User information? Do these reside in the logs? If we have user information, we should be able to apply Redaction on the database side, correct?
Wanted to ask the Community if anyone has applied anything similar in their environments. Feel free to share your approaches to masking/redacting data. Any thoughts are appreciated.
Thanks!
Answers
-
Sure, previously I have used a parallel process to row level security implemented in the business model layer against the physical column via a formula, essentially giving users true or false and putting it through a case statement to the extent of;
case when user.col1key = true then underlying.columnHere else 'Redacted' end
Obviously you need to reference the user to initialise the user.key for its various implementations.
Cheap and cheerful, provided you have the underlying user / can see column data.... and provided there aren't too many columns involved!
0 -
Thanks for the response Robert. We tried this approach, and it works really well actually. But you're right, when we have a large number of columns and user role combinations, then the case statement gets pretty large and complex and could cause a heavy load on the server.
Unfortunately, that is exactly the case for our situation. Several possible combinations of user roles and a growing list of columns (500+). So this is why I'm thinking of applying coming up with a method to conduct redaction on the database side instead. The ideal solution would be: as soon as a user from OBIEE fires a query, the database recognizes the user, applies the appropriate policy, and redacts the data before sending it to OBIEE to read.
0 -
Have you looked into =>
https://www.oracle.com/uk/database/database-vault/index.html
It sounds like your solution scale has reached the point where the 'cheap' solution is no longer cost effective!
0 -
On the connection pool, you can add a physical query to update application context with user id(:USER).
Then use dbms_rls package to apply policy on the database table.
https://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm#DBSEG011
Check if the documentation helps.
Thanks
Arijit
0 -
Aside from using RLS, you can also use roles within OBIEE to restrict access on specific presentation columns. For example if you have a column "NI Number" you would double-click that column in the presentation area and select permissions.
Then you would set your Authenticated User to No Access and specify only the specific roles you wish to access it. That way they don't even see the column available for selection. If you want to show the column but change the text then I'd recommend doing this at the database level - otherwise you're messing up your data model with security - they should ideally be separate. I posted an example of doing this in R12 here:
Redacting Sensitive Data in Oracle 12c using Dbms_Redact - Beyond Blog
0 -
+1 simple role-based access to presentation columns is MUCH more straight-forward
Also @kamrandb : "I know OBIEE stores SQL queries that are run against the application" - No. OBIEE works with metadata models It is a semantic layer on top of data sources. If produces model-driven queries. It doesn't store any queries
0