:::: MENU ::::

Implement required restrictions on view by using WITH CHECK OPTION

  • Jun 03 / 2009
  • 0
dbDigger, Security and Permissions, T-SQL Tips and Tricks, User Defined Views

Implement required restrictions on view by using WITH CHECK OPTION

I have created views for employees in different departments. Each employee is able to view records for his own department in the view. Also employees can update or insert records for their department through that view. I noticed that employees are even update and insert data that is related to any other department.
Now it was required to restrict that no employee can insert/update data that conflicts with department restriction that i have implemented in each view. For example consider the following imaginary view where records are picked based on a department provided in where clause

 
Create VIEW UVW_DeptRecords  
 AS  
 SELECT name, designation, department  
 FROM employees   
 WHERE department = 'Finance'  
 GO  

Employees from finance department are able to perform DML operations on this view. It is required that these employees may not be able to insert a record with department name other than Finance. First i planned to use a trigger for implementation of this logic. But while discussing this issue with fellow DBA i came to know that there is an option provided with the view definition itself, that will prohibit any DML that conflicts with the conditions provided in where clause of view definition.
So creating the above view with additional WITH CHECK OPTION in following way will not allow any DML operation for which employee does not belong to Finance department.

 
Create VIEW UVW_DeptRecords  
 AS  
 SELECT name, designation, department  
 FROM employees   
 WHERE department = 'Finance'  
 WITH CHECK OPTION  
 GO  
Consult us to explore the Databases. Contact us