As already stated in Chapter “Views“, views can be used for the following purposes:
- To restrict the use of particular columns and/or rows of tables
- To hide the details of complicated queries
- To restrict inserted and updated values to certain ranges
Restricting the use of particular columns and/or rows means that the view mechanism provides itself with the control of data access. For example, if the employee table also contains the salaries of each employee, then access to these salaries can be restricted using a view that accesses all columns of the table except the salary column. Subsequently, retrieval of data from the table can be granted to all users of the database using the view, while only a small number of (privileged) users will have the same permission for all data of the table.
The following three examples show the use of views to restrict the access to data.
USE sample;
GO
CREATE VIEW v_without_budget
AS SELECT project_no, project_name
FROM project;
Code language: PHP (php)
Using the v_without_budget view, as shown in Example 12.28, it is possible to divide users into two groups: the group of privileged users who can access the budget of all projects, and the group of common users who can access all rows from the projects table but not the data from the budget column.
USE sample;
GO
ALTER TABLE employee
ADD user_name CHAR(60) DEFAULT SYSTEM_USER;
GO
CREATE VIEW v_my_rows
AS SELECT emp_no, emp_fname, emp_lname, dept_no
FROM employee
WHERE user_name = SYSTEM_USER;
Code language: PHP (php)
The schema of the employee table is modified in Example 12.29 by adding the new column user_name. Every time a new row is inserted into the employee table, the system login is inserted into the user_name column. After the creation of the corresponding views, every user, who uses this view, can retrieve only the rows that he or she inserted into the table.
USE sample;
GO
CREATE VIEW v_analyst
AS SELECT employee.emp_no, emp_fname, emp_lname
FROM employee, works_on
WHERE employee.emp_no = works_on.emp_no
AND job = 'Analyst';
Code language: PHP (php)
The v_analyst view in Example 12.30 represents a horizontal and a vertical subset (in other words, it limits the rows and columns that can be accessed) of the employee table.