锁定视图不可编辑

发布于 2024-09-11 16:13:42 字数 1280 浏览 7 评论 0原文

我正在构建一个包含公共、私有（仅限内部）和机密数据（仅限极少数）的数据库。它有非常具体的要求，即在数据库端管理数据的安全性，但我在一个无法直接控制权限的环境中工作，并且请求更改它们将非常耗时（2-3天）。

因此，我创建了一个应该满足我们的需求而不需要大量许可的结构。我在同一台服务器上创建了两个数据库，一个是内部数据库，其表只能由我们网络的某些子网内的某些用户编辑。第二个是公共数据库，我使用管理员帐户创建仅限于内部数据库中表的公共字段的视图以公开公共数据，并且它似乎运行良好。然而，数据应该只以一种方式流动，并且视图不应该能够写入源表。而且我不能只是将公共数据库锁定为只能选择，因为公共数据库用于我们公共网站的各种任务。

因此，我需要创建视图来限制某些脚本对表中某些字段的访问。我需要确保这些视图无法插入、更新或删除源表中的数据。要创建我使用的视图：

CREATE  ALGORITHM = UNDEFINED 
VIEW `table_view` AS 
  SELECT *
  FROM `table`

查看文档以防止更新视图需要具有聚合数据、WHERE 子句中的子查询和 ALGORITHM = TEMPTABLE。我会选择 TEMPTABLE，但手册不清楚它是否会影响性能。在手册的一段中指出：

在以下情况下，它更喜欢 MERGE 而不是 TEMPTABLE：可能的，因为 MERGE 通常是更高效

然后立即指出：

选择 TEMPTABLE 的理由明确的是锁可以是之后在基础表上发布临时表已创建在它被用来完成之前处理该语句。这可能导致比锁释放更快 MERGE 算法，以便其他使用该视图的客户端不是被封锁了那么久。

将在页面加载时查询视图以生成页面内容，MERGE 仍然更高效还是较短的锁定时间会更好地为我服务？不，通过帐户权限处理此问题并不是真正的选择，因为无法对各个字段授予权限以满足法律保密要求。为了满足这些要求，需要将每个表分成 2-3 个表，其中包含具有同质机密性的字段。

如果算法是 UNDEFINED 或 TEMPTABLE，或者视图定义中是否有其他设置会锁定视图。我将会体验到什么性能效果。另外，如果我做一些事情来强制它不可编辑，比如包含 HAVING 1 使其成为聚合函数，则强制它成为 TEMPTABLE 并且算法的选择没有意义。

原文

I am building a database that contains public, private(limited to internal) and confidential data (limited to very few). It has very specific requirements that the security of the the data is managed on the database side, but I am working in an environment where I do not have direct control of the permissions, and requests to change them will be time consuming (2-3 days).

So I created a structure that should meet our needs without requiring a lot of permissioning. I created two databases on the same server, one is the internal one, who's tables can only be edited by certain users within certain subnets of our network. The second is the public database where, using an admin account, I create views limited to public fields of tables in the internal database to expose public data and it seems to work well. However the data should only flow one way and the views should not be able to write to the source tables. And I cannot just lock down the public database to be only SELECTable since the public database is used for various tasks of our public website.

So I need to create views to limit access of some scripts to certain fields in a table. I need to make sure that those views are not able insert, update, or delete data in the source table. To create the view I use:

CREATE  ALGORITHM = UNDEFINED 
VIEW `table_view` AS 
  SELECT *
  FROM `table`

Looking at the documentation to prevent updates the view needs to have aggregate data, sub queries in the WHERE clause, and ALGORITHM = TEMPTABLE. I would go with TEMPTABLE, but the manual is unclear whether it would impact the performance. In one paragraph the manual states:

It prefers MERGE over TEMPTABLE if
possible, because MERGE is usually
more efficient

Then immediately states:

A reason to choose TEMPTABLE
explicitly is that locks can be
released on underlying tables after
the temporary table has been created
and before it is used to finish
processing the statement. This might
result in quicker lock release than
the MERGE algorithm so that other
clients that use the view are not
blocked as long.

The views are going to be queried on page load to generate the contents of the page, would MERGE still be more efficient or would the lower lock time serve me better? And no, handling this through account permissions is not really an option due to the inability to GRANT permissions on individual fields to meet the legal confidentiality requirements. To meet them would require fragmenting each table into 2-3 tables containing fields with homogeneous confidentiality.

Should the algorithm be UNDEFINED or TEMPTABLE, or is there another setting in the view definition that will lock down the view. And what are the performance effects I will experience. Also, if I do something to force it to be uneditable, like including HAVING 1 to make it an aggregate function force it to be TEMPTABLE and the choice of algorithm moot.

分享到QQ

分享到微博