As already stated in Chapter 11, 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.
Examples 12.32, 12.33, and 12.34 show the use of views to restrict the access to data.
Example 12.32
USE sample;
GO
CREATE VIEW v_without_budget
AS SELECT project_no, project_name
FROM project;
Using the v_without_budget view, as shown in Example 12.32, 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.
Example 12.33
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;
The schema of the employee table is modified in Example 12.33 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.
Example 12.34
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’;
The v_analyst view in Example 12.34 represents a horizontal subset and a vertical subset (in other words, it limits the rows and columns that can be accessed) of the employee table.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.