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:
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.
Then create a trigger:
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.
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.
Comments
Post a Comment