Sunday, February 10, 2013

How to create a read only view

In this specific case the server application I'm working with are using a database hosted by SQL Server. The database is designed to be accessed by the server application alone, but there exists situations when other systems also want information from the database, such as status information. In those cases there is a good idea to create a view that other systems may use, and leave the database's tables to the server application. But also, the view should be read only, making it impossible for other parties to change the information. This can be done in number of ways and below I will show two:


Create a read only view

CREATE VIEW CustomersView 
AS 
  SELECT ID, FirstName, LastName FROM Customers
  UNION ALL SELECT 0, '0', '0' WHERE 1=0 

Add a union to the create view statement, the columns in the union must match the columns in the select statement. When the user executes an insert, delete or update statement then is the following error received:

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'CustomersView ' failed because it contains a derived or constant field. 

Create a trigger

First create the view:

CREATE VIEW CustomersView 
AS 
  SELCT ID, FirstName, LastName FROM Customers 

Then create a trigger:

CREATE TRIGGER ReadOnly_CustomersView 
ON 
  CustomersView     
    INSTEAD OF INSERT, UPDATE, DELETE 
AS 
BEGIN     
  RAISERROR( 'CustomersView view is read only.', 16, 1 )     
  ROLLBACK TRANSACTION 
END 

If a user executes an insert, delete or update statement then is the following error received:

Msg 50000, Level 16, State 1, Procedure ReadOnly_CustomersView, Line 7
CustomersView view is read only.
Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted.

This is a very nice way to make the view read only. It is also possible to exclude e.g. the UPDATE from the INSTEAD OF list, if the user shall be able to update the view, but not delete or insert rows.

No comments:

Post a Comment