数据库只读，但只有几个表可写

问题描述：

我想用下面的命令设置数据库为只读模式，它的工作原理。数据库只读，但只有几个表可写

ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT

但是，我需要只允许像UserSession等几个表是可写的。这可能吗？

我们在数据库中有大约500多个表，我们只需要允许4个表是可写的。

https://stackoverflow.com/questions/2529839/how-to-make-a-table-read-only-in-sql-server – MatSnow

@MatSnow的可能的复制，我们在各地的500多个表数据库，我们只需要允许4个表是可写的。我是否应该添加496只读而不是将整个数据库设置为只读，并且只允许4个可写？ – Developer

如果将数据库设置为只读，则AFAIK无法将仅少数表设置为可写。我建议你设置适合你需求的权限。 – MatSnow

答

你可以否认INSERT，UPDATE，DELETE到public角色，如：

DENY UPDATE ON tab1 TO public 
DENY INSERT ON tab1 TO public 
DENY DELETE ON tab1 TO public

之后，普通用户（非系统管理员）将获得试图插入到TAB1时出现错误：

INSERT INTO tab1 (id) VALUES (1) 

--Msg 229, Level 14, State 5, Line 20 
--The INSERT permission was denied on the object 'tab1', database 'test', schema 'dbo'.

如果您愿意将其添加到数据库中，那么您可以生成脚本来更改所有表格，如：

EXEC sp_msforeachtable ' 
PRINT '' 
    DENY UPDATE ON ? TO public 
    DENY INSERT ON ? TO public 
    DENY DELETE ON ? TO public 
'' 
'

确保你不否认写入那些仍然需要写入的4个表。

不确定您的应用程序，但如果您不想拒绝public角色的权限，您可以考虑创建自己的角色，拒绝对该角色的访问，并让用户成为该角色的一部分。

希望它是有道理的。

如果有人使用DDL命令（TRUNCATE/ALTER/DROP）会怎么样？ – lad2025

普通用户通常不具有这些权利。但是，如果是这种情况，那么您可以使用更多的DENY权限来限制它，或者如果更改数据库模式的选项可用，则可以使用数据库触发器（例如：不是您无法更改的第三方数据库） – Horia

Still你不能否认拥有对象所有者的权利，而且你必须重新考虑为所有新用户这样做。真正只读是在数据库或文件组级别。' – lad2025

答

您可以使用这样的事情：

ALTER TABLE [schemaName].[tableName] READ ONLY ON|OFF

我认为这是特定于Oracle（11g），而问题涉及到MSSQL。 – Horia

答

您可以在数据库中创建新模式，例如upd 现在你可以给阅读&写权限为架构应该有能力向特定的用户修改数据。之后，您可以创建要更新

create view upd.Tab1 
as 
select * from dbo.Tab1 
GO

在模式upd对象只将是可写的用户权限的upd架构基于表更新/插入意见。

答

我的建议是将两种类型的表格存储在不同的模式中 - 比如readable和writable。

当SQL Server在2005年推出的模式，在idea是模式是一个单位的安全和数据库备份和恢复的单位：

的Microsoft SQL Server 2005中引入的数据库对象模式的概念。模式类似于用于存储数据库对象的独立名称空间或容器。安全权限适用于模式，使它们成为根据访问权限分离和保护数据库的重要工具。

这将使两种不同的架构成为您数据架构的有力竞争者。

换句话说，将readable架构设置为read_only。并将其他表格放在writable模式中。

这在某些情况下有效。但数据库中的实际数据永远不会只读。拥有足够权限的用户仍然可以使用“可读”模式更改数据。例如，sysAdmin将能够直接更改数据。 –

@ChamikaGoonetilaka。。。当然，您可以根据应用程序的需要调整权限。 “可读”和“可写”可能不是最好的名字。 –

@ChamikaGoonetilaka你的论点是有缺陷的。系统管理员总是能够使数据库成为可写的。无论你做什么访问的人都不是某人你可以停下来，因此安全是一个不是绝对的计划。 – Namphibian

答

您可以通过将可写表移动到单独的文件组并将其他文件组只读取进行存档。

第一步 - 创建一个不同的文件组

ALTER DATABASE TESTDB ADD FILEGROUP Writable_FG;

第二步 - 数据文件添加到新的文件组

ALTER DATABASE TESTDB ADD FILE (
    NAME = JeanAnn2, 
    FILENAME = 'D:\MSSQL\TESTDB_Writable_FG_01.ndf', 
    SIZE = 6MB, 
    MAXSIZE = 18MB, 
    FILEGROWTH = 1 
) TO FILEGROUP Writable_FG;

第三步 - 将你所需要的表写入新文件组

为此，您需要在新文件组上重新创建表的聚簇索引。

CREATE CLUSTERED INDEX CIX_YourTable 
ON dbo.YourTable(YourClusteringKeyFields) 
WITH DROP_EXISTING 
ON [Writable_FG]

，或者如果您的聚集索引是独特：

CREATE UNIQUE CLUSTERED INDEX CIX_YourTable 
ON dbo.YourTable(YourClusteringKeyFields) 
WITH DROP_EXISTING 
ON [Writable_FG]

执行此操作，你需要是可写的所有四个表。

第四步 - 使其他文件组只读

ALTER DATABASE TESTDB MODIFY FILEGROUP [PRIMARY] READ_ONLY;

这里，假定其他文件组是主。

答

你可以把4个表放在一个不同的数据库上，而你的数据库把一个同义词放到你想要更新的4个表中。

CREATE SYNONYM sessionTables 
SELECT * 
FROM WriteableDatabase.dbo.sessionTables

数据库只读，但只有几个表可写

相关推荐