0

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

4
  • Do the users have direct DB access or is it via a front-end/API?
    – Philᵀᴹ
    Commented Aug 14, 2018 at 8:24
  • direct access.. I would like to avoid using of any kind of "proxy" ... the direct access is essential in this case
    – Mr.P
    Commented Aug 14, 2018 at 8:32
  • Are you running Enterprise Edition?
    – Philᵀᴹ
    Commented Aug 14, 2018 at 8:48
  • yes ... Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    – Mr.P
    Commented Aug 14, 2018 at 9:35

1 Answer 1

0

found it ...

ANSWER: Oracle Data Masking and SubSetting

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.