快捷導航

設(shè)置SQLServer數(shù)據(jù)庫中某些表為只讀的多種方法分享

更新時間：2012年06月26日 16:19:09 作者：

在某些情況下需要把SQLServer的表設(shè)為只讀，下面舉出幾種方法，需要的朋友可以參考下

一般情況下會有幾種情況需要你把數(shù)據(jù)庫設(shè)為只讀：
1. Insert,Update,Delete 觸發(fā)器
2. Check 約束和 Delete 觸發(fā)器
3. 設(shè)置數(shù)據(jù)庫為只讀
4. 把表放到只讀文件組中
5. 拒絕對象級別權(quán)限
6. 創(chuàng)建視圖
在開始之前，先創(chuàng)建一個數(shù)據(jù)庫及表作為示例：

復制代碼代碼如下:

 
create database MyDB 
create table tblEvents 
( 
id int, 
logEvent varchar(1000) 
) 
insert into tblEvents 
values (1, 'Password Changed'), (2, 'User Dropped'), (3, 'Finance Data Changed') 

nsert/Update/Delete觸發(fā)器：
請注意這里使用的是INSTEADOF trigger，因為如果你使用了AFTER trigger，會在執(zhí)行DELETE, UPDATE和INSERT語句時請求鎖，會對寫事務(wù)日志和回滾操作造成性能上的影響

復制代碼代碼如下:

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

當用戶執(zhí)行insert/update/delete時，將提示以下錯誤：
Msg 50000, Level 16, State 1, Procedure trReadOnly_tblEvents, Line 7tblEvents table is read only.Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.

使用 Check 約束和Delete 觸發(fā)器：
現(xiàn)在先在表中添加一個check 約束“1=0”，意味著總是失敗。它禁止你在任何行執(zhí)行INSERT或者Delete操作。
首先，先禁用在上一步創(chuàng)建的觸發(fā)器：disable trigger trReadOnly_tblEvents on tblevents然后，添加約束：ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINT chk_read_only_tblEvent CHECK( 1 = 0 )執(zhí)行以后，無論你執(zhí)行任何一個INSERT/UPDATE語句，都將提示以下錯誤信息：
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECKconstraint "chk_read_only_tblEvent". The conflict occurred indatabase "MyDB", table "dbo.tblEvents".
The statement has been terminated.
但是，該約束不會對DELETE操作造成影響，為此，需要再創(chuàng)建一個DDL觸發(fā)器：

復制代碼代碼如下:

 
CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents 
INSTEAD OF 
DELETE 
AS 
BEGIN 
RAISERROR( 'tblEvents table is read only.', 16, 1 ) 
ROLLBACK TRANSACTION 
END 

設(shè)置數(shù)據(jù)庫為只讀：
你可以設(shè)置數(shù)據(jù)庫為只讀，這樣就禁止對整個數(shù)據(jù)庫的DDL/DML操作?？梢允褂靡韵抡Z句：

復制代碼代碼如下:

 
USE [master] 
GO 
ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT 
GO 

把表放到只讀文件組：
可以在一個只讀文件組中創(chuàng)建一個表：

復制代碼代碼如下:

 
USE [master] 
GO 
ALTER DATABASE [MyDB] ADD FILEGROUP [READ_ONLY_TBLS] 
GO 
ALTER DATABASE [MyDB] ADD FILE ( NAME = N'mydb_readonly_tables', FILENAME = N'C:\JSPACE\myDBReadOnly.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [READ_ONLY_TBLS] 
GO 
DROP table tblEvents 
create table tblEvents 
( 
id int, 
logEvent varchar(1000) 
) 
ON [READ_ONLY_TBLS] 
ALTER DATABASE [MyDB] MODIFY FILEGROUP [READ_ONLY_TBLS] READONLY 
任何對表的DML操作都會被拒絕，并返回以下錯誤信息： 
Msg 652, Level 16, State 1, Line 1 
The index "" for table "dbo.tblEvents" (RowsetId 72057594038845440) resides on a read-only filegroup ("READ_ONLY_TBLS"), which cannot be modified. 

拒絕對象級別權(quán)限
可以通過DCL命令控制用戶權(quán)限，但此步無法限制高級權(quán)限用戶（如system admin,DatabaseOwner）：

復制代碼代碼如下:

 
DENY INSERT, UPDATE, DELETE ON tblEvents TO Jugal 
DENY INSERT, UPDATE, DELETE ON tblEvents TO Public 

創(chuàng)建視圖
為了替代直接訪問表，可以使用視圖：

復制代碼代碼如下:

 
create view vwtblEvents 
as 
select ID, Logevent from tblEvents 
union all 
select 0, '0' where 1=0 

在這個視圖中，使用了UNION，只有在你確保有對應(yīng)數(shù)量的列時才使用。在這個例子中，表有兩列，所以使用兩個輸出列。同時，你也應(yīng)該確保數(shù)據(jù)類型也一致。
當一個用戶嘗試通過INSERT/UPDATE/DELETE操作數(shù)據(jù)時，將得到以下錯誤信息：
Msg 4406, Level 16, State 1, Line 1Update or insert of view or function 'vwtblEvents1' failed because it contains a derived or constant field.Msg 4426, Level 16, State 1, Line 1View'vwtblEvents1' is not updatable because the definition contains a UNIONoperator.

最后一步：
確認是否有必要用這些步驟來設(shè)置表為只讀。
如果一個表總是只讀，那么你應(yīng)該放到只讀文件組中。

您可能感興趣的文章: