Oracle data access restriction

oracleSecurity

I am working on some data access restriction feature in Oracle 11g and have been struggling since then.

What I want to achieve is, that I want to allow a specific group only to access a particular set of data and the rest should not be able to see them (or have them hashed) …

Let's image two users

USER_JOHN and USER_TIMMY

The role settings for them is following

USER_JOHN
  - it_analyst
  - sales_analyst
  - read_all

USER_TIMMY
  - risk_analyst

The %_ANALYST roles are just for the sake of demonstration only .. the only important role in this case is the READ_ALL role which allows JOHN to access the "privileged" data …

Example of privileged data (columns marked with * are considered "privileged"):

CLIENT_TABLE
-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |     8/1/1990      | Sesame Street |  Male   |
 2  |  Jane   |     1/18/1976     | Corner Road   | Female  |

When a user (with READ_ALL) role queries this table I want him to get the same data set as you can see above

USER_JOHN:$> SELECT * FROM client_table;

-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |     8/1/1990      | Sesame Street |  Male   |
 2  |  Jane   |     1/18/1976     | Corner Road   | Female  |

But if a user without this READ_ALL role queries this table, they should get only non-privileged data in a read-able form … see below

USER_TIMMY:$> SELECT * FROM client_table;

-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |    3978005636     |  4259532014   |  Male   |
 2  |  Jane   |    2282325776     |  1146039631   | Female  |

As you can see, these "privileged" records are hashed … they can even be empty or not select.able at all .. thus the result will look like this 

USER_TIMMY:$> SELECT * FROM client_table;

--------------------------
ID  |  Name   |    Sex   |
--------------------------
 1  | Franco  |   Male   |
 2  |  Jane   |  Female  |

Is there some solution to achieve this? The best result for me would be hashed values …. I have read something about Oracle VPD which should do the same I just do not know, how to apply that on roles globally …

What I have done so far is that I have created the same object within different schema …

SCHEMA_RAW.CLIENT_TABLE -> accessible for READ_ALL; not accessible for others
SCHEMA_HASHED.CLIENT_TABLE -> accessible for all

I know it's not the best solution but at least I do have some data protection implemented .. the major drawback of this solution is the script-sharing .. if there is a user that has an access to SCHEMA_RAW only .. they need to change the script provided by users that have an access to SCHEMA_HASHED only …

Note: there is not just this single table … there are many (hundreds of them) that needs to be secured this way …

EDIT: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit

Best Answer

found it ...

ANSWER: Oracle Data Masking and SubSetting

https://www.oracle.com/technetwork/database/options/data-masking-subsetting/overview/index.html

Related Question