DML privileges on selective columns .

We can selectively grant privileges on any columns in a table . This applies for INSERT , DELETE , UPDATE statements.

Here is an example , where the schema owner ( in this case , dbuser ) wants to restrict the
appuser to insert only two columns in a table . Oracle throws an error message if the appuser tries to insert any other columns other than provisioned .

See below for an example.

SQL> show user
USER is "DBUSER"

SQL> create table t as select object_id , object_name , object_type from all_objects;
Table created.

SQL> grant insert (object_id , object_name ) on t to appuser ;
Grant succeeded.


SQL> insert into dbuser.t ( object_id , object_name , object_type) values ( 500 , 'MOHIDEEN' , 'DBA');
1 row created.

SQL> conn appuser/app
Connected.

SQL> show user
USER is "APPUSER"


SQL> insert into dbuser.t ( object_id , object_name , object_type) values ( 561 , 'ZAHIR' , 'DBA') ;
insert into dbuser.t ( object_id , object_name , object_type) values ( 561 , 'ZAHIR' , 'DBA')
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> insert into dbuser.t ( object_id , object_name ) values ( 561 , 'ZAHIR' ) ;
1 row created.