SQL - Data projection - Part 5 - Protecting Data of Views in SQL
|
Haziran 2, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Explanation of Protecting Views in sql
- Explanation of WITH CHECK OPTION in sql
- Example to WITH CHECK OPTION in sql
Articles tries to provide answer to following questions
- How to check the data being inserted or updated through view?
- How to restrict inserts and updates through view in sql
- How to protect views from undesired inserts?
- What is WITH CHECK OPTION?
- Why WITH CHECK OPTION is used in views?
Article covers followings indirectly:
- alter view in sql
Articles pre-requisites following information:
- General knowledge of SQL Server Management Studio
- General knowledge of Views in sql
SQL VIEWs and INSERT restriction
SQL views are a way to build projection, a control to access to data. In an usual view, apart from its being projection, a row can be inserted or updated through the view.There will be cases, we will need to limit inserts and updates in the database through the views.
To understand how a view works, first lets try to insert a row into the database through a view. Following two sql query brings two table. One is underlying table and a view which uses it with a WHERE clause.
Lets try to insert a row through the view to see what happens.
As above sql query results indicates, inserting a new row through the view works. However, there is an issue there. View was filtering out the rows where INSERT is not aware. New row is not visible. Moreover, through the view another logical section of data which was not presented to access to the viewer has been changed.
Lets think about the idea of view here; we were giving access to data about a customer where its id is 2. Assume that was Microsoft as customer. And a customer representative had access to that view. Representative was assigned to Microsoft. Now that representative has added a new data to database which is not related to Microsoft. That can cause security issues in the database.
Protecting data from undesired inserts and updates
To prevent above scenario, WITH CHECK OPTION in sql is used. WITH CHECK OPTION is added to view as a feature to prevent inserts and updates through view which are not desired.
Lets add WITH CHECK OPTION to our view. This is done with altering view as follows:
alter view V_customerOrders
as
select * from orders
where customerID=2
WITH CHECK OPTION
We added WITH CHECK OPTION to the view. Our view contains a WHERE clause that brings out only customers with their ID as 2. Any insert or update through that view that customerID is not 2 will not be executed.
Lets try to insert another row through view into underlying table that customerID is not 2.
We got our error message as expected.
Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans
a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify
under the CHECK OPTION constraint.
The statement has been terminated.
As above error message indicates, INSERT did not work. Since we are trying to insert a new row through view which is protected by WITH CHECK OPTION that contains WHERE clause, insert fails.
If we try to insert that new row with changing customerID to 2, it works.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|