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.

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.

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.