使用视图来保护sql中的实际表 [英] use of views to protect the actual tables in sql

问题描述

视图如何充当实际表和最终用户之间的中介?创建视图时发生的内部过程是什么.我的意思是，当在桌子上创建视图时，它是否像桌子和最终用户之间的一堵墙?视图如何保护实际表，仅使用检查选项?但是如果用户直接插入到表中，那么我如何保护实际的表?

how do views act as a mediator between the actual tables and an end user ? what's the internal process which occurs when a view is created. i mean that when a view is created on a table, then does it stands like a wall between the table and the end user or else? how do views protect the actual tables, only with the check option? but if a user inserts directly into the table then how come do i protect the actual tables?

如果他/她不使用:insert into **vw** values()，但使用:insert into **table_name** values()，那么现在表是如何保护的?

if he/she does not use : insert into **vw** values(), but uses: insert into **table_name** values() , then how is the table protected now?

推荐答案

非物化视图只是预先打包的 SQL 查询.它们的执行方式与任何派生表/内联视图相同.对同一视图的多个引用将运行视图包含的每个引用的查询.IE:

Non-materialized views are just prepackaged SQL queries. They execute the same as any derived table/inline view. Multiple references to the same view will run the query the view contains for every reference. IE:

CREATE VIEW vw_example AS
  SELECT id, column, date_column FROM ITEMS

SELECT x.*, y.*
  FROM vw_example x
  JOIN vw_example y ON y.id = x.id

...转化为存在:

SELECT x.*, y.*
  FROM (SELECT id, column, date_column FROM ITEMS) x
  JOIN (SELECT id, column, date_column FROM ITEMS) y ON y.id = x.id

缓存

主要好处是缓存，因为查询将是相同的.查询被缓存，包括执行计划，以便稍后更快地运行查询，因为执行计划已经生成.缓存通常要求查询与区分大小写的点相同，并且最终会过期.

Caching

The primary benefit is caching because the query will be identical. Queries are cached, including the execution plan, in order to make the query run faster later on because the execution plan has been generated already. Caching often requires queries to be identical to the point of case sensitivity, and expires eventually.

另一个潜在的好处是视图通常允许谓词推送"，其中视图上指定的条件可以推送到优化器表示的视图查询中.这意味着查询可以扫描表一次，而不是扫描表以将结果集呈现给外部/最终查询.

Another potential benefit is that views often allow "predicate pushing", where criteria specified on the view can be pushed into the query the view represents by the optimizer. This means that the query could scan the table once, rather than scan the table in order to present the resultset to the outer/ultimate query.

SELECT x.*
  FROM vw_example x
 WHERE x.column = 'y'

...可以被优化器解释为:

...could be interpreted by the optimizer as:

SELECT id, column, date_column 
  FROM ITEMS
 WHERE x.column = 'y'

谓词推送的决定完全取决于优化器.我不知道开发人员有什么能力强制做出决定，只知道这确实取决于视图使用的查询以及应用的附加条件.

The decision for predicate pushing lies solely with the optimizer. I'm unaware of any ability for a developer to force the decision, only that it really depends on the query the view uses and what additional criteria is being applied.

遗憾的是，看到非物化 SQL 视图仅用于封装以简化编写查询的情况非常常见——这种简化也不是推荐的做法.SQL 是基于 SET 的，使用过程方法不能很好地优化.将视图叠加在一起也不是推荐的做法.

Sadly, it's very common to see a non-materialized SQL view used for nothing more than encapsulation to simplify writing queries -- simplification which isn't a recommended practice either. SQL is SET based, and doesn't optimize well using procedural approaches. Layering views on top of one another is also not a recommended practice.

非物化视图也是可更新的，但有一些限制，因为一个视图可以由多个连接在一起的表组成.可更新的非物化视图将阻止用户插入新记录，但可以更新现有记录.CHECK OPTION 取决于用于创建视图的查询强制执行一定程度的更新限制，但这还不足以确保不会发生任何事情.这表明防止不需要的添加/编辑/删除的唯一可靠方法是 grant适当的权限给用户，最好通过角色.

Non-materialized views are also updatable, but there are restrictions because a view can be made of numerous tables joined together. An updatable, non-materialized view will stop a user from being able to insert new records, but could update existing ones. The CHECK OPTION depends on the query used to create the view for enforcing a degree of update restriction, but it's not enough to ensure none will ever happen. This demonstrates that the only reliable means of securing against unwanted add/editing/deletion is to grant proper privileges to the user, preferably via a role.

这篇关于使用视图来保护sql中的实际表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！