Lesson 10	Updating Views and Security
Objective	Understand how permissions affect your ability to use views.

Updating Views and Security Permissions

SQL Security Views

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

read-only,
write-only, and
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.

More on Views

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

Database Engine

The database engine you choose will drive your options as they relate to security. With 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.
What do you do if you can not update a view? 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.