SQL - Data projection - Part 6 - Protecting Views in SQL (schema binding)
|
Haziran 3, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Explanation of Protecting Views with schema binding in sql
- Brief explanation of sql schemas5END
- Example to protect view with schema bindingMP85END in sql
Articles tries to provide answer to following questions
- How to protect views in sql server?
- How to restrict access to view via sql schema in sql server?
- What is WITH SCHEMABINDING in sql?
- How to restrict a sql user to update view or delete view?
- How to lock a sql table to prevent the views which are depend on that table to get broken?
Article covers followings indirectly:
- alter view in sql
- sql schema
Articles pre-requisites following information:
- General knowledge of SQL Server Management Studio
- General knowledge of Views in sql
- General knowledge of sql schema
SQL schema and Views
In earlier articles, we had explored views and a raw protection method (WITH CHECK OPTION). With a sql view, we created a projection into an underlying table. Then we explored how to protect view rawly with restricting inserts and updates. However, WITH CHECK OPTION is only a raw method to force the data which is going to be inserted, to match to the WHERE clause contained in SELECT command of View. It does not provide in user level restriction. It does not restrict a user, or a user from a particular command type like ALTER, SELECT or similar.
Moreover, views are depend on underlying real tables as containing a SELECT statement. If underlying table is changed, like changing a column name, views may get broken.
WITH SCHEMABINDING feature steps in here to provide a mechanism to lock view and table together as a seal. Once view is created with SCHEMABINDING, trying to change the underlying sql table that view sends its SELECT query, will cause an error to prevent modification to the underlying table.
To see it in practice without complicating things with theory, lets look into it in a sql example.
we have a sql table as underlying table and a view as follows (view only contains a SELECT statement to filter out result where orders belong only customer which ID is 2:
In normal case, if we change a column name of orders table with an existing sql stored procedure "sp_RENAME" and see what happens if we try to use view again?
After changing column name of underlying table, view gets broken. We got following error:
Msg 207, Level 16, State 1, Procedure V_customerOrders, Line 4
Invalid column name 'customerID'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'V_customerOrders' because of binding errors.
This error is a sql side error from debug. The 3rd party reporting applications which uses that view may give different type of errors.
To prevent that kind of underlying table modifications, WITH SCHEMABINDING is used. That bind method tells sql server that the view and underlying table is bound to each other. Some sort of saying to sql server, there is a view and that view depends on an underlying table.
After we modify our view, and try to modify underlying table, sql will prevent modification to the underlying table. Lets see it in sql example:
After adding schema-bind to view, we get our expected sql error which prevented us to modify underlying table.
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 444
Object 'europe.orders.customerID' cannot be renamed because the object participates in enforced dependencies.
Notice that we changed our SELECT statement in sql query in view slightly. We provided exact column names instead a SELECT *. That is a restriction that applies to schema-bound views. If you try to use *, it will give an error while altering view.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|