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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 原文链接:http://www.2cto.com/database/201206/136583.html   在某些情况下需要把SQLServer的表设为只读,下面举出几种方法:   一般情况下会有几种情况需要你把数据库设为只读: 1.
原文链接:http://www.2cto.com/database/201206/136583.html
 
在某些情况下需要把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')  
 
nsert/Update/Delete触发器:
 
请注意这里使用的是INSTEADOF trigger,因为如果你使用了AFTER trigger,会在执行DELETE, UPDATE和INSERT语句时请求锁,会对写事务日志和回滚操作造成性能上的影响
[sql]  
CREATE TRIGGER trReadOnly_tblEvents ON tblEvents  
    INSTEAD OF INSERT,    www.2cto.com  
               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 7
tblEvents table is read only.
Msg 3609, Level 16, State 1, Line 1
The 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触发器:
[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-only filegroup ("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 the definition contains a UNIONoperator.
 
最后一步:
确认是否有必要用这些步骤来设置表为只读。
如果一个表总是只读,那么你应该放到只读文件组中。k
相关实践学习
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
3天前
|
SQL 监控 安全
数据库安全:SQL注入防御实践
【7月更文挑战第11天】SQL注入攻击作为一种常见的网络攻击手段,对数据库的安全性和业务稳定构成了严重威胁。为了有效防御SQL注入攻击,开发者和数据库管理员应采取一系列实践措施,包括输入验证与过滤、使用参数化查询、限制数据库用户权限、使用Web应用程序防火墙、定期更新和打补丁、实施实时监控和审计以及使用HTTPS协议等。通过这些措施的实施,可以显著提升数据库的安全性,降低遭受SQL注入攻击的风险。同时,开发者和数据库管理员应持续关注新的安全威胁和防御技术,不断提升自身的安全防护能力。
|
4天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
5天前
|
Java 数据库连接 应用服务中间件
数据库连接管理与性能优化方法
数据库连接管理与性能优化方法
|
9天前
|
SQL 自然语言处理 网络协议
【Linux开发实战指南】基于TCP、进程数据结构与SQL数据库:构建在线云词典系统(含注册、登录、查询、历史记录管理功能及源码分享)
TCP(Transmission Control Protocol)连接是互联网上最常用的一种面向连接、可靠的、基于字节流的传输层通信协议。建立TCP连接需要经过著名的“三次握手”过程: 1. SYN(同步序列编号):客户端发送一个SYN包给服务器,并进入SYN_SEND状态,等待服务器确认。 2. SYN-ACK:服务器收到SYN包后,回应一个SYN-ACK(SYN+ACKnowledgment)包,告诉客户端其接收到了请求,并同意建立连接,此时服务器进入SYN_RECV状态。 3. ACK(确认字符):客户端收到服务器的SYN-ACK包后,发送一个ACK包给服务器,确认收到了服务器的确
|
4天前
|
SQL Java 关系型数据库
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
13 0
|
4天前
|
SQL 监控 Java
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
8 0
|
8天前
|
SQL 存储 数据库
SQL Server性能优化策略与实践
在使用Microsoft SQL Server(简称MSSQL)进行数据库管理时,性能优化是确保系统高效运行、提升用户体验的关键环节
|
26天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
252 1
|
10天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
21 2