设置SQLServer数据库中某些表为只读的多种方法分享

前端之家收集整理的这篇文章主要介绍了设置SQLServer数据库中某些表为只读的多种方法分享前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

一般情况下会有几种情况需要你把数据库设为只读:
1. Insert,Update,Delete 触发器
2. Check 约束 和 Delete 触发器
3. 设置数据库为只读
4. 把表放到只读文件组中
5. 拒绝对象级别权限
6. 创建视图
在开始之前,先创建一个数据库及表作为示例:
<div class="codetitle"><a style="CURSOR: pointer" data="60903" class="copybut" id="copybut60903" onclick="doCopy('code60903')"> 代码如下:

<div class="codebody" id="code60903">
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语句时请求锁,会对写事务日志和回滚操作造成性能上的影响
<div class="codetitle"><a style="CURSOR: pointer" data="52617" class="copybut" id="copybut52617" onclick="doCopy('code52617')"> 代码如下:
<div class="codebody" id="code52617">
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触发器:
<div class="codetitle"><a style="CURSOR: pointer" data="30591" class="copybut" id="copybut30591" onclick="doCopy('code30591')"> 代码如下:
<div class="codebody" id="code30591">
CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents
INSTEAD OF
DELETE
AS
BEGIN
RAISERROR( 'tblEvents table is read only.',1 )
ROLLBACK TRANSACTION
END

设置数据库为只读:
你可以设置数据库为只读,这样就禁止对整个数据库的DDL/DML操作。可以使用以下语句:
<div class="codetitle"><a style="CURSOR: pointer" data="70112" class="copybut" id="copybut70112" onclick="doCopy('code70112')"> 代码如下:
<div class="codebody" id="code70112">
USE [master]
GO
ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT
GO

把表放到只读文件组:
可以在一个只读文件组中创建一个表:
<div class="codetitle"><a style="CURSOR: pointer" data="51549" class="copybut" id="copybut51549" onclick="doCopy('code51549')"> 代码如下:<div class="codebody" id="code51549">
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,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):
<div class="codetitle"><a style="CURSOR: pointer" data="33795" class="copybut" id="copybut33795" onclick="doCopy('code33795')"> 代码如下:<div class="codebody" id="code33795">
DENY INSERT,UPDATE,DELETE ON tblEvents TO Jugal
DENY INSERT,DELETE ON tblEvents TO Public

创建视图
为了替代直接访问表,可以使用视图:
<div class="codetitle"><a style="CURSOR: pointer" data="60499" class="copybut" id="copybut60499" onclick="doCopy('code60499')"> 代码如下:<div class="codebody" id="code60499">
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.
最后一步:
确认是否有必要用这些步骤来设置表为只读。
如果一个表总是只读,那么你应该放到只读文件组中。

原文链接:https://www.f2er.com/mssql/63804.html
只读只读

猜你在找的MsSQL相关文章