Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Granting Insert privilege to specified column
On 22 Nov 1998 00:04:14 GMT, gennick_at_worldnet.att.net (Jonathan
Gennick) wrote:
>On Thu, 19 Nov 1998 11:03:55 +0530, Rajesh Bhave
><rajeshb_at_india.hp.com> wrote:
>
>>When is it required to grant INSERT privilege on specified column of
>>table or view. Please give example. (I think INSERT privilege should be
>>available to be granted to full table rather than on specific column)
>
>I do not believe that you can grant insert only on a
>specific column. As far as I know, INSERT applies to the
>entire table.
Sure you can grant insert only on particule columns. I belive this option is available since the begining of Oracle7. And it is not so unreasonable as some of the repliers suggest.
Consider an employee table (SCOTT.EMP for example), containing personal data of each employee, and perhaps also the data about their salaries. It would not be so unusual security policy to allow regular clerk to insert a new record for a new employee, filling all his/her personal data, but at the same time to prevent this regular clerk to enter the data about the employee's salary. To achive this, a clerk is given the insert privilege on all the columns of the employee table except on salary column.
Of course, if the columns where insert privilege has not been granted are declared as NOT NULL, then this columns must have dafault values declared or their values must been filled by insert triggers, or else the insert privilege would be useless as users will get the "ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert" messeges when trying to insert a record.
>Jonathan
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)