April 9, 2009 at 3:59 am
Hi
Can anyone please tell me how to make a view read only in Sql server 2000 and Sql server 2005.
for example suppose I make a view in the database "Northwind" as
create view abc as
select * from Employees
then if we execute the sql command
delete from abc where employeeid=1
it deleted the entry from the employee table too.
how can i prevent the view abc from updating ,deleting and inserting into the employee table.
Thanks in advance.
Nothing Is Impossible
April 9, 2009 at 4:33 am
Hi
What about permissions?
Greets
Flo
April 9, 2009 at 4:46 am
Hi while creating the view you can use CHECK_OPTION to prevent insert and update option.
Check out the example:
http://www.rampant-books.com/t_super_sql_129_check_option_view.htm
Tanx 😀
April 9, 2009 at 5:03 am
Eswin (4/9/2009)
Hi while creating the view you can use CHECK_OPTION to prevent insert and update option.Check out the example:
http://www.rampant-books.com/t_super_sql_129_check_option_view.htm
Hi Eswin
I didn't know that option. Thanks also from my side!
Greets
Flo
April 9, 2009 at 5:32 am
Eswin (4/9/2009)
Hi while creating the view you can use CHECK_OPTION to prevent insert and update option.Check out the example:
http://www.rampant-books.com/t_super_sql_129_check_option_view.htm
from BOL:
CHECK OPTION
Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
Meaning you can do anything (that is granted to you) with the rows of a view, as long as these rows will remain in the result set of the view itself.
e.g.
create view V_myview
as
select col1, col2
from myschema.mytable
go
You can do anything (you are granted for) with the rows of this view because there a no criteria specified for the select.
Now consider this:
create database DSSCTest
go
use DSSCTest
go
Create table mytable (col1 int not null, col2 varchar(128) not null)
go
set nocount on
go
declare @ctr int
Set @ctr = 1
while @ctr < 1000
begin
insert into mytable values (@ctr, @ctr * 1111)
Select @ctr = @ctr + 1
end
go
Set nocount off
go
create view V_myview
as
select col1, col2
from mytable
Where Col1 between 50 and 100
go
Select count(*) nRows_in_V_myview
from V_myview
go
Update V_myview
set col1 = 110
where col1 = 50
go
Select count(*) nRows_in_V_myview_after
from V_myview
go
create view V_myview_CheckOption
as
select col1, col2
from mytable
Where Col1 between 50 and 100
with check option
go
Update V_myview_CheckOption
set col1 = 112
where col1 = 52
go
/* This update on this view gives this error
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.
*/
-- Restricting trigger for view V_myview
Create trigger tr_v
on V_myview
INSTEAD OF insert, update, delete
as
raiserror ('Action not allowed on V_myview', 10, 1)
rollback transaction
go
Update V_myview
set col1 = 110
where col1 = 50
go
/*
Action not allowed on V_myview
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
-- You cannot define a trigger on a view if the view is declared using the WITH CHECK OPTION !!
/* Clean up */
Print 'Clean up after testing !!'
-- use master
-- go
-- drop database DSSCTest
When using this view ( V_myview_CheckOption ) , you can only update the data (of col1 and col2) as long as the data of col1 still fits the where clause
If you want to restrict insert, update, delete, only grant "select" for the object.
You could also start writing triggers, to prevent all this, but that may lead to who knows what administrative mess.....
If you want your database to be a read only copy ... perform an alter database xyz set read_only.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 9, 2009 at 8:37 am
You Can even try out with "INSTEAD OF TRIGGER" to avoid anu updates/inserts thru views.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply