- Oracle Virtual Private Database (VPD)
- Oracle Data Redaction features.
Oracle Data Redaction
I don't want to replace documentation of the Oracle Data Redaction within this blog, all available information you could find there, but just let me remind you the main idea of this feature. Very often databases contain sensitive data, like credit card number, SSN or other personal information. It could be useful to have this data in unchanged format for resolving different issue with billing department, but at the same time another department (like call center) may need only part of this information (like 4 last digits of the credit cards) and for security compliance you are not able to show them original data.
For meet this requirement you may:
- Create tokenized clone of the data
- Use Data Redaction feature, which will transform data on the fly, whenever your unprivileged user query the data.
let me give you an example.
1) on HDFS I store sensitive data, which contain personal data (email)
2) In Oracle RDBMS I created the external table, that link on this HDFS directory (through hive table):
3) Now let's try to query data from this table:
so, we are able to see all data about a user, including emails. Let's mask it.
4) For this only one what I need is create policy and add the rule (fortunately, email masking is the pre-created function).
5) and after this try to query data again:
Emails are hidden. It's only one example how to use data redaction feature. All details you could find in the documentation, but it's important to understand that all Oracle Data Redaction features could be used over HDFS data and you could use all mature and proven feature of Oracle database for protecting your Hadoop data.
Virtual Private Database.
Again I'm not pretending to replace VPD documentation, all necessary details you could find there, I just repeat that this feature also available for an external table (which means over HDFS) as well as over permanent table. The main idea of VPD is to show different part of the table and different data set depend on user privileges. For example, if I have few departments and few roles (like manager/not manager) I can show different data from a table, that contains salary of the employees, depending on their role. If user Bob and Anne I'll show salaries of their directs. For Tim, who are not a manager I'll show only his salary.
let's have another example with our previous table customer. Let's imagine, that we have two guys - Mark, who could see only the data of Sweden customers and Jose, who are able to see only Mexican customers.
Both of this guys want to work over the single table without any extra movements. Only what they want to do is log in as Mark or Jose and see only Sweden or Mexican customers. Virtual Private Database could do this. let me give you an example how it possible to implement this
1) Create support table, that will store a list of the users and filter conditions for each one:
2) Add data there (Mark and Jose information)
3) Create Oracle users and give them all necessary permissions:
4) Create PL/SQL package that will generate predicate depend on the user login. if the user missing in the table (employee) will be generated condition "1=1", that means show all data. Depends on application logics, you could generate "1=0" predicate to hide all data.
5) Create VPD policy over protected table, where we specify previous PL/SQL package as predicate generator:
6) Create login trigger, that allows us to catch the user name and define proper context:
7) And now time to test! Let's run the same query as different users and compare results:
And we got exactly what we expected. Mark could see only Sweden customers (and total our company has 33168 customers from Sweden), Jose could see only Mexican customers (total 32939) and other users could see all customers. VPD generate context predicate depend on who are login into a database, for example when Mark runs query:
VPD exchange it to:
Hopefully today's introduction was valuable and let you understand that you have pretty wide security capabilities with Big Data SQL