Re: Column security

Debor19071 (debor19071_at_aol.com) wrote:

: We have an application where we need to implement not only row-level
: security but column-level security.

We had a similar requirement. The way we implemented it was to create a view where the columns in question were DECODEd. The DECODE passes back either the original value of the column, or a NULL, depending on the user's authorization. It looks something like this:

....
decode

   (usd.user_school,
    awd.admin_school_code,awd.account_number,     to_char(NULL))
   account_number,
....

The column usd.user_school identifies the user's school, from a security profile table. This value is compared to awd.admin_school_code on the table that is being secured. If they match, the value in awd.account_number is returned as "account_number". If they don't match, NULL is returned as "account_number".

We have done the same thing with both character and numeric columns, using a to_char(NULL) or to_number(NULL). The to_char and to_number seem a little peculiar, but it's the only way we could get it to work. Do note that by doing this, a CHAR column in the underlying table will be changed to a VARCHAR2 column. That may or may not make a difference to you -- it didn't to us.

--
Tad Davis                  215-898-7864 voice
(davist_at_isc.upenn.edu)     215-898-0386 fax