限制用户加入两个SQL视图 [英] Restrict the users to join two SQL views

问题描述

大家好，



我们在SQL中面临性能问题，后来发现这是因为用户正在运行客户查询。用户对数据库具有受限访问权限，并且只能运行SQL视图。



后来分析我们注意到用户正在加入多个视图并将其作为一个大查询并导致性能问题。



现在，我们想限制用户只运行视图，并且不能加入视图。如果您对此有任何想法，请分享。



提前致谢。

问候

Prashant



我尝试了什么：



1.用Google搜索如果我们可以限制对用户的访问，但是理解无法限制业务。



2.如果他们遇到同样情况，请与其他人核实但是没有运气。

Hi All,

We were facing the performance issue in SQL and later identified that it is because of the users running customer queries. The users are having restricted access on the DB and they can only run the SQL views.

Later on analysing we noticed that users are joining the multiple views and making it a big query and causing performance issue.

Now, we wanted to restrict the users to run only the views and should not be able to join the views. Can you please share if you have any ideas on achieving this.

Thanks in advance.
Regards
Prashant

What I have tried:

1. Googled to find if we can restrict the access to the users but understood that there is no way to restrict the business.

2. Checked with other people if they had come across same situation but no luck.

推荐答案

一般情况下，优化器很难在单个查询中组合多个视图，尤其是在视图外部使用条件的情况下。



如果用户需要访问这两个视图，那么我担心你不能限制视图的使用方式（加入或不加入）。您应该确保的是：

- 两个视图中的连接列都已正确编入索引

- 使用常用的过滤条件

- 确保ad-hoc查询经过某种逻辑验证，以防止错误的查询

- 考虑使用固定条件集，也许是实现它们的表值函数

- 如果可能的话，不要使用视图，而是使用基础表。

In general combining multiple views in a single query is difficult for the optimizer especially if the conditions are used from outside the views.

If the users need access to both views, then I'm afraid you cannot restrict how the views are used (joined or not). What you should ensure is:
- the joining columns in both views are properly indexed
- commonly used filter conditions are used
- ensure that the ad-hoc queries go through some kind of logical validation in order to prevent erroneous queries
- consider using fixed condition sets, perhaps table valued functions implementing them
- if possible, don't use view, instead use the underlying tables.

这篇关于限制用户加入两个SQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！