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

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')

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23895263/viewspace-681055/，如需轉載，請註明出處，否則將追究法律責任。