将SQL中几张表设为只读,这是什么奇怪需求?

点击关注上方“SQL数据库开发”,

设为“置顶或星标”,第一时间送达干货

最近接到一个奇怪的需求,要将SQL Server中的几张表设为只读。我的第一反应就是直接将用户设为只读权限就好了,但是仔细一想又不是那么回事。

创建只读账号

SQL Server的只读账号是针对某个具体的数据库,设置的方法很简单:

  1. 进入Sqlserver Management Studio(MSSQL客户端)

  2. 选择安全性->登录名->右键新建登录名

  3. 在常规里输入用户名和密码

  4. 在"用户映射"里“映射到此登录名的用户”选择该用户可以操作的数据库

  5. 在“数据库角色成员身份”里选择"db_datareader",

  6. 这样一个只读用户就创建完成了。

但是这样会造成所有的表都变成只读,这与需求不符。

那该怎么办呢?今天就告诉大家几种常见的方法。

1、使用触发器限制单表只读

有如下一张创建好的表Teacher

我们先往里面插入一条数据来证明它是可写的。

INSERT INTO Teacher VALUES('04','马六');

再查下一下表里面的结果,看是否写进去了。

证明该表是可写的,下面我们开始新建一个触发器将其设为只读。

CREATE TRIGGER trReadOnly_Teacher ON Teacher
    INSTEAD OF INSERT,UPDATE,DELETE  
AS  
BEGIN  
    RAISERROR( 'tblEvents table is read only.',16, 1 )
    ROLLBACK TRANSACTION  
END

(提示:可以左右滑动代码)

创建完成后,我们再往Teacher里面写数据或删数据

INSERT INTO Teacher VALUES('05','赵七');
DELETE FROM Teacher TID='04';

结果会报如下错误提示:

证明我们设置的触发器生效了。

虽然我们一般不提倡在数据库中使用触发器,因为触发器会造成有很多性能上的影响,但是如果用来控制权限还是可以的。

此外该方法只针对少量需要设置为只读的表,如果需要设置表比较多就不太合适了。

如果要禁用该触发器也很简单,使用如下代码即可:

disable trigger trReadOnly_Teacher on Teacher

2、创建只读文件组

先创建一个只读文件组

--创建文件组READ_ONLY_TBLS
ALTER DATABASE School ADD FILEGROUP [READ_ONLY_TBLS]
GO  
ALTER DATABASE School ADD FILE (
NAME =N'School_readonly_tables',
FILENAME = N'D:\Temp\SchoolReadOnly.ndf' ,
SIZE =2048KB ,
FILEGROWTH = 1024KB
) TO FILEGROUP [READ_ONLY_TBLS]

然后将要设为只读的文件先插入临时表再删除

SELECT * INTO #Teacher FROM Teacher
DROP TABLE Teacher

然后创建刚被删除的表名,再将临时表里面的数据插入进去,最后将文件组设为只读即可

CREATE TABLE Teacher
(
TID VARCHAR(10),
TName varchar(100)
)
ON [READ_ONLY_TBLS]

INSERT INTO Teacher
SELECT * FROM #Teacher

--将数据库的文件组设为只读
ALTER DATABASE School MODIFY FILEGROUP
[READ_ONLY_TBLS] READONLY

下面我们试着往Teacher里面插入数据:

INSERT INTO Teacher VALUES('05','赵七');

结果如下:

证明也是不可写的。

这种方法相比触发器复杂一点,但是可以大批量的设置,前提是要备份好数据。

3、拒绝对象级别的操作

可以通过DCL命令控制用户权限,但此步无法限制高级权限用户(如system admin,DatabaseOwner)

DENY INSERT, UPDATE, DELETE ON tblEvents TO Test
DENY INSERT, UPDATE, DELETE ON tblEvents TO Public

4、创建视图

为了替代直接访问表,可以使用视图:

CREATE VIEW V_SC
AS  
SELECT SID,CID,Score FROM SC
UNION ALL
SELECT '0', '0',0 WHERE 1=0

当我们要删除视图中的数据时

DELETE FROM V_SC WHERE score=30.0

会得到如下提示:

如果不加UNION ALL,正常的视图是可以删除数据的。

以上就是几种常见的将表设为只读的方法,可以根据自身实际需求进行选择。

——End——

后台回复关键字:1024,获取一份精心整理的技术干货
后台回复关键字:进群,带你进入高手如云的交流群。
推荐阅读

这是一个能学到技术的公众号,欢迎关注

点击「阅读原文」了解SQL训练营

展开阅读全文

Python数据分析与挖掘

01-08
92讲视频课+16大项目实战+源码+¥800元课程礼包+讲师社群1V1答疑+社群闭门分享会=99元   为什么学习数据分析?       人工智能、大数据时代有什么技能是可以运用在各种行业的?数据分析就是。       从海量数据获得别人看不见的信息,创业者可以通过数据分析来优化产品,营销人员可以通过数据分析改进营销策略,产品经理可以通过数据分析洞察用户习惯,金融从业者可以通过数据分析规避投资风险,程序员可以通过数据分析进一步挖掘出数据价值,它和编程一样,本质上也是一个工具,通过数据来对现实事物进行分析和识别的能力。不管你从事什么行业,掌握了数据分析能力,往往在其岗位上更有竞争力。    本课程共包含五大模块: 一、先导篇: 通过分析数据分析师的一天,让学员了解全面了解成为一个数据分析师的所有必修功法,对数据分析师不在迷惑。   二、基础篇: 围绕Python基础语法介绍、数据预处理、数据可视化以及数据分析与挖掘......这些核心技能模块展开,帮助你快速而全面的掌握和了解成为一个数据分析师的所有必修功法。   三、数据采集篇: 通过网络爬虫实战解决数据分析的必经之路:数据从何来的问题,讲解常见的爬虫套路并利用三大实战帮助学员扎实数据采集能力,避免没有数据可分析的尴尬。   四、分析工具篇: 讲解数据分析避不开的科学计算库Numpy、数据分析工具Pandas及常见可视化工具Matplotlib。   五、算法篇: 算法是数据分析的精华,课程精选10大算法,包括分类、聚类、预测3大类型,每个算法都从原理和案例两个角度学习,让你不仅能用起来,了解原理,还能知道为什么这么做。
©️2020 CSDN 皮肤主题: 技术工厂 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值