Looking to hide column data from certain users

keitht · #1 (**permalink**) 10-12-11, 15:11

I have a request to recommended a solution that will allow any user to query a table and based on their authority get back scrambled data or readable data for different rows based on their authority. I've read up on label based access control and they will return an error if a user queries a column that don't have access to. Is it possible to have one user see the following while another user sees the correct data in the examples below?

Restricted User
CLNT_ID DATE ID FNAME LNAME
1703 09/03/2010 77 Qhteb Ujter
1704 08/12/2010 82 Mike Holmes
1703 08/15/2010 98 Toll Jotl

Unrestricted User
CLNT_ID DATE ID FNAME LNAME
1703 09/03/2010 77 Scott James
1704 08/12/2010 82 Mike Holmes
1703 08/15/2010 98 Jeff Hugh

stolze · #2 (**permalink**) 10-12-11, 15:22

You could create a view and in the view definition, you modify the values in the select list based on the current user. Then revoke all privileges on the base table so that all access has to go through the view.

Code:

CREATE VIEW ...
SELECT ..., CASE WHEN checkUserAllowed(USER) = 1 THEN fname ELSE scramble(fname)
FROM ...

You'll have to define the functions checkUserAllowed() and scramble() in whichever way you like.

keitht · #3 (**permalink**) 10-13-11, 13:21

Thanks. This is a good starting step until we come up with a better process.

Internet Services

Web Development

Digital Marketing

Consumer Tech