Can views be updated/deleted/Insert?

Views: Lets start with getting some info about views

• Object in database
• Is a virtual table, which is based on a table(s)
• They represent the results of custom SQL statements in memory, it doesn't occupy any space.
• On creation of view an entry is made in data dictionary which represents the data fetched by SQL statement.
• Data in view can come from one or more tables.
• Data in view can have all columns of one or more tables or can have select/specific columns from one or more tables.
• The selection of columns in a view helps in masking selective columns of a table from user while helping us provide access to specific columns of a particular table.
• A view can be updated, inserted into & deleted from, this will update the base tables related columns, however a View is not updatable if its query contains JOINS, SET OPERATORS, AGGREGRATE FUNCTIONS, GROUP BY, DISTINCT clause or if a view query contains psuedocolumns, expressions.
• To be able to create view in its own schema user needs to have "CREATE VIEW" privilege & if it needs to be able to create views in other schemas user should have "CREATE ANY VIEW" privilege.
• To be able to select, insert, update or delete user needs to have those privileges on all base objects(tables), these privileges should not be granted through roles.
• Base tables can be protected by restricting the privileges (providing only select privlege)

Can views be updated, deleted, inserted?
Lets take up an example

SQL> conn / as sysdba
Connected.

SQL> grant create view to scott;
Grant succeeded.

SQL> conn scott/xxx;
Connected.
SQL> create or replace view vw_emp
           as
           select ename, deptno, salary
          from employee;

View created.

SQL> select * from vw_emp;
ENAME          DEPTNO   SALARY
---------- ----------           ----------
Carter             10                  25000
Albert             20                  37000
Breen              30                  50500
Gould              20                 23700
Barker             10                 75000
Mubeen           10                 10000
Sajid                20                10005
7 rows selected.

SQL> update vw_emp
  2  set deptno=35 where ename='Carter';
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from vw_emp;
ENAME          DEPTNO     SALARY
---------- ---------- ----------
Carter             35      25000Albert              20      37000
Breen               30      50500
Gould               20      23700
Barker             10      75000
Mubeen           10      10000
Sajid                20      10005
7 rows selected
.
Base table gets updated too

SQL> select * from employee;
     EMPNO ENAME          DEPTNO     PROJNO     SALARY
---------- ---------- ---------- ---------- ----------
       101 Carter             35          1      25000       102 Albert             20          3      37000
       103 Breen              30          6      50500
       104 Gould              20          5      23700
       105 Barker             10          7      75000
       101 Mubeen             10          7      10000
       102 Sajid              20          3      10005
7 rows selected.

SQL> conn radio/xxx;
Connected.

SQL> select * from vw_emp;select * from vw_emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Since user "radio" doesn't have select privileges on view created by scott, it can't do a select on it.

We grant a select privilege to user "radio" on scott's view (vw_emp)

SQL> conn scott/xxxx;
Connected.
SQL> grant select on vw_emp to radio;
Grant succeeded.

SQL> conn radio/xxxx;
Connected.
SQL> select * from scott.vw_emp;
ENAME          DEPTNO     SALARY
---------- ---------- ----------
Carter             35      25000
Albert             20      37000
Breen              30      50500
Gould              20      23700
Barker             10      75000
Mubeen             10      10000
Sajid              20      10005
7 rows selected.

Next we attempt to update view (vw_emp, created in scott schema) as user "radio"
SQL> update scott.vw_emp
          set deptno=45 where ename='Carter';update scott.vw_emp
             *
ERROR at line 1:
ORA-01031: insufficient privileges

User "radio" doesn't have "update" privileges on view

SQL> conn scott/xxxx;
Connected.
SQL> grant update on vw_emp to radio;
Grant succeeded.

SQL> conn radio/xxxx;
Connected.

SQL> update scott.vw_emp
          set deptno=45 where ename='Carter';
1 row updated.

SQL> select * from scott.vw_emp;
ENAME          DEPTNO     SALARY
---------- ---------- ----------
Carter             45      25000
Albert             20      37000
Breen              30      50500
Gould              20      23700
Barker             10      75000
Mubeen             10      10000
Sajid              20      10005
7 rows selected.

SQL> conn scott/xxxx;
Connected.
SQL> grant select on employee to radio;
Grant succeeded.

SQL> conn radio/xxxx;
Connected.

SQL> select * from scott.employee;
     EMPNO ENAME          DEPTNO     PROJNO     SALARY
---------- ---------- ---------- ---------- ----------
       101 Carter             45          1      25000       102 Albert             20          3      37000
       103 Breen              30          6      50500
       104 Gould              20          5      23700
       105 Barker             10          7      75000
       101 Mubeen             10          7      10000
       102 Sajid              20          3      10005
7 rows selected.

Now we can see that when user "radio" after having necessary privileges did an update, view gets updated & consequently underlying table (base table) gets updated too.

Note: Please refer to oracle documentation