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

来源:asp之家 时间:2012-07-11 15:41:05 

一般情况下会有几种情况需要你把数据库设为只读:
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 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 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.


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

标签:SQLServer,只读
0
投稿

猜你喜欢

  • python3实现网页版raspberry pi(树莓派)小车控制

    2021-05-05 05:35:18
  • python中的Elasticsearch操作汇总

    2022-01-29 10:44:45
  • opencv实现图像校正

    2023-12-26 02:07:49
  • dl+ol应用

    2008-06-21 17:04:00
  • 基于wxpython开发的简单gui计算器实例

    2023-09-09 04:42:25
  • 带你一文读懂Python垃圾回收机制

    2022-03-03 11:58:03
  • Python编程mac下使用pycharm小技巧

    2022-10-21 19:23:52
  • 详解Python的迭代器、生成器以及相关的itertools包

    2022-07-24 01:03:44
  • Python中非常实用的一些功能和函数分享

    2022-05-28 16:25:06
  • Python 基于TCP 传输协议的网络通信实现方法

    2023-06-18 22:04:01
  • Mysql存储过程如何实现历史数据迁移

    2024-01-24 13:02:33
  • Python 异步之生成器示例详解

    2023-01-12 04:34:26
  • 简单的命令查看安装的python版本号

    2022-03-12 17:23:06
  • python 多进程并行编程 ProcessPoolExecutor的实现

    2023-10-23 14:41:56
  • JavaScript实现获取最近7天的日期的方法详解

    2024-04-27 15:20:59
  • Pytorch之parameters的使用

    2022-05-22 21:06:01
  • Python实现修改图片分辨率(附代码)

    2021-06-30 02:00:10
  • python自动化测试之破解滑动验证码

    2023-12-01 09:36:25
  • Pytorch中Tensor与各种图像格式的相互转化详解

    2023-05-23 01:46:54
  • Python根据成绩分析系统浅析

    2023-08-02 20:25:07
  • asp之家 网络编程 m.aspxhome.com