Recipe4.7.Blocking Inserts to Certain Columns

Recipe 4.7. Blocking Inserts to Certain Columns

Problem

You wish to prevent users, or an errant software application, from inserting values into certain table columns. For example, you wish to allow a program to insert into EMP, but only into the EMPNO, ENAME, and JOB columns.

Solution

Create a view on the table exposing only those columns you wish to expose. Then force all inserts to go through that view.

For example, to create a view exposing the three columns in EMP:

 create view new_emps as select empno, ename, job   from emp 

Grant access to this view to those users and programs allowed to populate only the three fields in the view. Do not grant those users insert access to the EMP table. Users may then create new EMP records by inserting into the NEW_EMPS view, but they will not be able to provide values for columns other than the three that are specified in the view definition.

Discussion

When you insert into a simple view such as in the solution, your database server will translate that insert into the underlying table. For example, the following insert:

 insert into new_emps    (empno ename, job)    values (1, 'Jonathan', 'Editor') 

will be translated behind the scenes into:

 insert into emp    (empno ename, job)    values (1, 'Jonathan', 'Editor') 

It is also possible, but perhaps less useful, to insert into an inline view (currently only supported by Oracle):

 insert into   (select empno, ename, job      from emp) values (1, 'Jonathan', 'Editor') 

View insertion is a complex topic. The rules become very complicated very quickly for all but the simplest of views. If you plan to make use of the ability to insert into views, it is imperative that you consult and fully understand your vendor documentation on the matter.