設定SQLServer資料庫中某些表為只讀的多種方法

翻譯自：http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012614

在某些情況下需要把SQLServer的表設為只讀，下面舉出幾種方法：

一般情況下會有幾種情況需要你把資料庫設為只讀：

1.        Insert,Update,Delete 觸發器

2.        Check 約束 和 Delete 觸發器

3.        設定資料庫為只讀

4.        把表放到只讀檔案組中

5.        拒絕物件級別許可權

6.        建立檢視

在開始之前，先建立一個資料庫及表作為示例：

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觸發器：

請注意這裡使用的是INSTEADOF trigger，因為如果你使用了AFTER trigger，會在執行DELETE, UPDATE和INSERT語句時請求鎖，會對寫事務日誌和回滾操作造成效能上的影響

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

當使用者執行insert/update/delete時，將提示以下錯誤：

Msg 50000, Level 16, State 1, Procedure trReadOnly_tblEvents, Line 7

tblEvents table is read only.

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

使用 Check 約束和Delete 觸發器：

現在先在表中新增一個check 約束“1=0”，意味著總是失敗。它禁止你在任何行執行INSERT或者Delete操作。

首先，先禁用在上一步建立的觸發器：disable trigger trReadOnly_tblEvents on tblevents

然後，新增約束：ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINT chk_read_only_tblEvent CHECK( 1 = 0 )

執行以後，無論你執行任何一個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操作造成影響，為此，需要再建立一個DDL觸發器：

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

設定資料庫為只讀：

你可以設定資料庫為只讀，這樣就禁止對整個資料庫的DDL/DML操作。可以使用以下語句：

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

把表放到只讀檔案組：

可以在一個只讀檔案組中建立一個表：

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.

拒絕物件級別許可權

可以通過DCL命令控制使用者許可權，但此步無法限制高階許可權使用者（如system admin,DatabaseOwner）：

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

建立檢視

為了替代直接訪問表，可以使用檢視：

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

在這個檢視中，使用了UNION，只有在你確保有對應數量的列時才使用。在這個例子中，表有兩列，所以使用兩個輸出列。同時，你也應該確保資料型別也一致。

當一個使用者嘗試通過INSERT/UPDATE/DELETE運算元據時，將得到以下錯誤資訊：

Msg 4406, Level 16, State 1, Line 1

Update or insert of view or function 'vwtblEvents1' failed because it contains a derived or constant field.

Msg 4426, Level 16, State 1, Line 1

View'vwtblEvents1' is not updatable because the definition contains a UNIONoperator.

最後一步：

確認是否有必要用這些步驟來設定表為只讀。

如果一個表總是隻讀，那麼你應該放到只讀檔案組中。