Oracle 10g - An invisible column?

Question

Hello,

Is it possible to 'hide' a column in an Oracle 10g database, or prevent data from being inserted altogether? We'd prefer to have existing INSERT statements still function, but have the information NOT insert for a particular column. Also, column masking or any type of encryption is not preferred.

Not sure if it's possible, but thanks in advance.

Answer 1

With Oracle feature virtual private database (VPD) you can define which users can change and which users can select a column. Virtual private database is also called fine-grained access control (FGAC).

Answer 2

If all you need to do is stop data from being inserted, you could create a BEFORE INSERT FOR EACH ROW trigger that wipes out any value inserted into the column before the row is saved.

There are various other things you can do with security (also views) to prevent inserts/selects from particular users in particular circumstances, but these will probably not let existing inserts continue to work.

Answer 3

Rename original table, create view with original table name but only selecting the columns you want to show.

Recompile code referring to existing table.

Answer 4

what about just setting your grants to each item you allow to update or insert

grant select on emp to scott;

grant update (column1, column2), insert (column1) on emp to scott