Column Security

[Ed Sudit]

I have an interesting question. We are looking to hide columns for certain users. The simple answer is to use views and user roles, that's the easy solution. The problem is that the columns to be hidden will be controlled by another set of users and will be stored in another table, like a lookup table. I need to be able to pick up the user specified restrictions on which columns are to be hidden and apply them "on the fly" to existing tables or views.
Anyone got any suggestions?

[dbafreak]

There are too many parameters. Can you explain a little plainly. I'll try to help.

[Ed Sudit]

Thanks for you help in advance.

What we have is a table that has columns that are not for public consumption. We want to give the priviledged users a reference table where they can specify which columns are to be hidden. When public users write queries against the main table, we need to read the reference table to get a list of columns to be hidden in the query result.

This is the lowdown. Let me know if you need more info.

[dbafreak]

Hmm, very interesting requirement. Looks like a challenge.

How abt. this logic:

Create a Reference table having three colns:

USER, COLUMN, HIDE_IND

This table will contain the public user names who might be accessing the table, the column names and the indicator for each column which is 'Y' if it has to be hidden or 'N' if it has to be displayed.

Create a GUI interface for this model for the privileged users who are authorized to control the display for the public user to enable them to operate.

When any public user issues a select on the main table, the hiding-control must be fectched from the reference table for that particular user and the columns (that have an indicator 'N') -- pass this info to a procedure that selects only
these columns from the main table.


Don't know if this is feasible, I hope so.

Hope it helps.