SQL Server 设置数据库中某些表为只读的多种方法
2022-11-12 09:46:42
内容摘要
这篇文章主要为大家详细介绍了SQL Server 设置数据库中某些表为只读的多种方法，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！一般情况下
文章正文
这篇文章主要为大家详细介绍了SQL Server 设置数据库中某些表为只读的多种方法，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
一般情况下会有几种情况需要你把数据库设为只读：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, Level 16, State 1, 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, Level 16, State 0, Line 1The 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 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.最后一步：确认是否有必要用这些步骤来设置表为只读。如果一个表总是只读，那么你应该放到只读文件组中。
注：关于SQL Server 设置数据库中某些表为只读的多种方法的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
