设置SQL Server数据库中某些表为只读的多种方法

设置SQL Server数据库中某些表为只读的多种方法

设置SQL Server数据库中某些表为只读的多种方法

 在某些情况下需要把SQLServer的表设为只读，下面举出几种方法：

 

一般情况下会有几种情况需要你把数据库设为只读：

1.        Insert,Update,Delete 触发器

2.        Check 约束 和 Delete 触发器

3.        设置数据库为只读

4.        把表放到只读文件组中

5.        拒绝对象级别权限

6.        创建视图
 

在开始之前，先创建一个数据库及表作为示例：

[sql]  

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')  

 

insert/Update/Delete触发器：
 

请注意这里使用的是INSTEAD OF trigger，因为如果你使用了AFTER trigger，会在执行DELETE, UPDATE和INSERT语句时请求锁，会对写事务日志和回滚操作造成性能上的影响

[sql]  

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, ProceduretrReadOnly_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 beenaborted.

 

2 使用Check 约束和Delete 触发器：
 

现在先在表中添加一个check 约束“1=0”，意味着总是失败。它禁止你在任何行执行INSERT或者Delete操作。

首先，先禁用在上一步创建的触发器：disable trigger trReadOnly_tblEvents on tblevents

然后，添加约束：ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINTchk_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触发器：

[sql]  

CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents 

    INSTEAD OF     www.2cto.com 

              DELETE  

AS  

BEGIN  

    RAISERROR( 'tblEvents table is read only.',16, 1 )  

    ROLLBACK TRANSACTION  

END  

 

设置数据库为只读：

你可以设置数据库为只读，这样就禁止对整个数据库的DDL/DML操作。可以使用以下语句：

[sql]  

USE [master]  

GO  

ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT  

GO  

 

把表放到只读文件组：

可以在一个只读文件组中创建一个表：

[sql]  

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)  

)    www.2cto.com  

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-onlyfilegroup ("READ_ONLY_TBLS"), which cannot be modified.  

 

拒绝对象级别权限

可以通过DCL命令控制用户权限，但此步无法限制高级权限用户（如system admin,DatabaseOwner）：

[sql]  

DENY INSERT, UPDATE, DELETE ON tblEvents TO Jugal  

DENY INSERT, UPDATE, DELETE ON tblEvents TO Public  

 

创建视图

为了替代直接访问表，可以使用视图：

[sql]  

create view vwtblEvents  

as  

select ID, Logevent from tblEvents  

union all  

select 0, '0' where 1=0  

  www.2cto.com  

在这个视图中，使用了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 thedefinition contains a UNIONoperator.

 

最后一步：

确认是否有必要用这些步骤来设置表为只读。

如果一个表总是只读，那么你应该放到只读文件组中。

 

 

作者 黄钊吉