Oracle hide columns from certain users

Question

The scenario : an Oracle 11g database containing some sensitive user data that could result legal liabilities if disclosed to the wrong party.

The desired effect : only a certain user, connecting from a certain IP, can see the column that contains this sensitive user data

I am not sure that hidden columns or virtual columns are the right ways to do this. It seems that Fine-Grained Access Control could help. I am not sure of what is the best solution. The restriction by IP is probably done at the listener level?

The question : How can we restrict the visibility of a column so it is only available only to a specific user? All the other users would never see the column, not even when doing a "DESC TABLE_WITH_SENSITIVE_DATA"

Thanks for any tips.

Orthohydrogen

Virtual column is totally not that. It is used to have a calculated column directly inside a table rather than using a view – Pentachlorophenol

FGAC can hide column DATA (or the whole row) but not the column itself, nor can it hide it in DESC. you'd have to have views to fully hide a column and then only grant the users access to the view. – Offense

When you say "only a certain user, connecting from a certain IP, " do you really want the database to enforce the connecting IP? Have you consulted your network adminstrators on this point? – Klystron

Another question: do you trust your DBAs or do you want to shield the data from them (and similar power users)? – Klystron

I think my answer to a related question covers all the bases: https://mcmap.net/q/1477961/-oracle-transparent-data-encryption-undecrypted-access – Klystron

Thanks APC for feedback, we do not want to enforce IP restrictions at the host level (e.g. using firewall rules or router ACL) but at the TNS listener level. In MySQL it is trivial to do, there has to be a way on Oracle. This should probably be a separate question. – Orthohydrogen

Answer 1

Simplest way to do this is to create a view on the table that does not contain all of the columns. Don't grant select on the table, but only on the view.

Inhalator

This is such a simple and elegant solution I kind of feel dumb to have missed it. – Orthohydrogen

Answer 2

The "proper" way to do this is with Fine-Grained Access Control (Virtual Private Database), which can replace the contents of columns with a NULL if certain conditions are not met.

See the example here: http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#autoId17

You can probably build this sort of functionality yourself if you're feeling both impoverished and skilled.

Ladyinwaiting

After reading a bit on VPD and FGAC it became clear that this could be used to enforce data hiding to some extent, however it is probably overly complicated (and potentially costly) to use those techniques for our purpose. – Orthohydrogen

Answer 3

Do you the ability to modify roles and create views? Perhaps you could create two separate views and grant access to two different roles for that table. All users that are restricted from seeing the sensitive data would belong to a "restricted" role and the others would have access to the "unrestricted" role. You would need to grant privileges on each view to the appropriate role.

It is important to note that there are restrictions on updating the underlying data associated with a view. As explained here, views that contain set operators, aggregates and GROUP BY DISTINCT and joins and not modifiable.

Nostrum