Updating Views and Security Permissions

One of the most frequently asked questions is why a view will not allow updates. One final area to review if your view is non-updateable is the use of formulas. You may recall that you can create a column on the fly by indicating, for example, that a column is derived by multiplying two other columns and providing the result. Since the column is derived by using other values from the table, this will force the view to be read-only. The view can not update the underlying table because the database engine could not know how to apply an update to the values included in the formula for the derived column.

A view can be used in a query that updates data, subject to a few restrictions. Remember that a view is not a table and contains no data, the actual modification always takes place at the table level. Views cannot be used as a mechanism to override any constraints, rules, or referential integrity defined in the base tables.
Restrictions on Updating Data Through Views: You can 1) insert, 2) update, and 3) delete rows in a view, subject to the following limitations:

1. Five Limitations:
   • If the view contains joins between multiple tables, you can only insert and update one table in the view, and you cannot delete rows.
   • You cannot directly modify data in views based on union queries. You cannot modify data in views that use GROUP BY or DISTINCT statements.
   • All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
   • Text and image columns cannot be modified through views.
   • There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.
2. Using WITH CHECK OPTION: The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view. Listing 9.12 creates a view showing customers from Paris using the WITH CHECK OPTION statement.

CREATE VIEW vwCustomersParis
AS
 SELECT CompanyName, ContactName, Phone, City
 FROM Customers
 WHERE City = 'Paris'
WITH CHECK OPTION

Another aspect of views is security. In many database systems, you can set up views so they have specific security levels:

1. read-only,
2. write-only, and
3. fully updateable.

You do not establish this security when you create the view. Rather, once the view is created, the database administrator can apply the permissions you need. It is beyond the scope of this course to explain how to establish security for the various database engines, but it is important you understand that it is possible. You may want to check with your database administrator to see what the policies are for accessing your systems.

The SQL equivalent of an external schema is provided by the ability to declare views. Suppose a user needs information only about the names and employee numbers of employees in room_no 'R4'. A view could be defined which picks out just the required data.

CREATE VIEW roomR4staff
AS SELECT emp_name, emp_no
FROM employee
WHERE room_no = 'R4'

The view definition's FROM clause may refer to base table(s) and/or view(s), so it is possible to have, for example, a view of a view. A view does not physically exist as stored data. Use of a view simplifies data manipulation and improves security. For example, the query on the base table:

SELECT emp_name, emp_no
FROM employee
WHERE room_no = 'R4' AND emp_name = 'Smith'

is simplified when querying the view to:

SELECT emp_name, emp_no
FROM roomR4staff
WHERE emp_name = 'Smith'

SELECT * FROM roomR4staff 
WHERE emp_name = 'Smith'

The database management system translates the query on the view into an equivalent query on the base table.
Column(s) may be given different names in a view to those used in the base table(s). For example, in a view of the telephone table, room_no and extension could be renamed office_no and tel_no, respectively:

CREATE VIEW phone_location (office_no, tel_no)
AS SELECT room_no, extension
FROM telephone

The database engine you choose will drive your options as they relate to security. With Microsoft Access, for example, you have limited capabilities to control who can access a stored query. You will need to set up users, assign overall rights, and let those rights drive the use of the stored queries. With engines such as Oracle and SQL Server, you can assign rights to specific views for specific people. This gives you great flexibility with determining who can query which tables in the system and what information they are allowed to see.

Question: What do you do if you can not update a view?
Answer: There are several reasons this might be the case. First, make sure you have included unique keys for each table referenced in your view. Remember, if the database engine can not specifically determine which row you are trying to update, it will not allow the update to happen. Next, check the security for your account on both the view and your access to the underlying tables. If you have created a view that is trying to access tables for which you have no permissions, your view will not work.