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

发布时间：2020-12-12 07:40:45 所属栏目：MsSql教程来源：网络整理

导读：感兴趣的小伙伴，下面一起跟随编程之家 jb51.cc的小编两巴掌来看看吧！一般情况下会有几种情况需要你把数据库设为只读： 1. Insert,Update,Delete 触发器 2. Check 约束和 Delete 触发器 3. 设置数据库为只读 4. 把表放到只读文件组中 5. 拒绝对象级别权限

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

一般情况下会有几种情况需要你把数据库设为只读：
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 7tblEvents table is read only.Msg 3609,Line 1The 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,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.',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:JSPACEmyDBReadOnly.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,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,DELETE ON tblEvents TO Jugal 
DENY INSERT,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,Line 1Update or insert of view or function 'vwtblEvents1' failed because it contains a derived or constant field.Msg 4426,Line 1View'vwtblEvents1' is not updatable because the definition contains a UNIONoperator.

最后一步：
确认是否有必要用这些步骤来设置表为只读。
如果一个表总是只读，那么你应该放到只读文件组中。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!