如何允许用户sql访问限于某些行的表? [英] How can I allow users sql access to a table limited to certain rows?

问题描述

我正在构建一个证券交易所模拟游戏.我有一个名为"Market_data"的表，游戏中的玩家模拟了特定的日期，并允许他们使用SQL查询来检索历史数据并计划其行动方案.我的困难是我需要根据他们正在播放的当前日期来限制他们可以访问的行，以使他们看不到日期大于当前日期的行.

I'm building an stock exchange simulation game. I have a table called 'Market_data' and in the game players simulate being in particular dates and are allowed to use SQL queries to retrieve the historical data and plan their course of action. My difficulty is that I need to limit the rows they can access based on the current date they are playing on so they cant see rows with a date greater than the current date.

例如:一个用户正在运行游戏，并且目前处于2010年，如果他执行"SELECT * FROM market_data"这样的简单选择，我不希望他看到Date>'x-x-2010'的行

Eg: An user is running the game and is currently in the year 2010, if he does a simple select like "SELECT * FROM market_data" I don't want him to see rows with Date > 'x-x-2010'

我唯一了解的是解析用户的SQL并添加WHERE子句以删除较新的日期，但是这似乎很耗时且容易出错，我不确定是否有更好的选择.有关如何执行此权利的任何想法都将受到感谢.

The only soution that I know of is to parse the user's SQL and add WHERE clauses to remove newer dates but it seems time consuming and prone to errors and I wasn't sure whether there were better alternatives. Any ideas on how to do this right will be thanked.

推荐答案

解决方案是SQL视图，出于多种原因使用视图:

* 1. *隐藏数据复杂性.与其强迫用户学习T-SQL JOIN语法，不如希望提供一个运行通常要求的SQL语句的视图.

*1.*To hide data complexity. Instead of forcing your users to learn the T-SQL JOIN syntax you might wish to provide a view that runs a commonly requested SQL statement.

* 2. *保护数据.如果您的表在某些列中包含敏感数据，则可能希望对某些用户组隐藏这些列.例如，客户名称，地址及其社会保险号可能都存储在同一张表中；但是，对于较低级别的员工(例如运输文员)，您可以创建仅显示客户名称和地址的视图.您可以授予视图权限，而不允许用户查询基础表.您可能有几种方法可以保护数据安全:

*2.*To protect the data. If you have a table containing sensitive data in certain columns, you might wish to hide those columns from certain groups of users. For instance, customer names, addresses and their social security numbers might all be stored in the same table; however, for lower level employees like shipping clerks, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the underlying tables. There are a couple of ways you might want to secure your data:

a.创建一个视图以仅读取表中的某些列.一个常见的示例是employee表中的薪金列.您可能不希望所有人员都能阅读经理或彼此的薪水.这称为垂直分区表，可以通过在CREATE VIEW语句中仅指定适当的列来完成.

a.Create a view to allow reading of only certain columns from a table. A common example of this would be the salary column in the employee table. You might not want all personnel to be able to read manager's or each other's salary. This is referred to as partitioning a table vertically and is accomplished by specifying only the appropriate columns in the CREATE VIEW statement.

b.创建一个视图以仅读取表中的某些行.例如，您可能有部门经理的视图.这样，每个经理只能向其所在部门的员工提供加薪.这称为水平分区，可以通过在创建视图的SELECT语句中提供WHERE子句来完成.

b.Create a view to allow reading only certain rows from a table. For instance, you might have a view for department managers. This way, each manager can provide raises only to the employees of his or her department. This is referred to as horizontal partitioning and is accomplished by providing a WHERE clause in the SELECT statement that creates a view.

* 3. *执行一些简单的业务规则.例如，如果您希望生成需要接收秋季目录的客户列表，则可以创建以前在秋季购买衬衫的客户的视图.

*3.*Enforcing some simple business rules. For example, if you wish to generate a list of customers that need to receive the fall catalog, you can create a view of customers that have previously bought your shirts during the fall.

* 4. **使用BCP导出数据.如果使用BCP将SQL Server数据导出到文本文件中，则由于BCP的格式化能力非常有限，因此可以通过视图格式化数据.

*4.*Data exports with BCP. If you are using BCP to export your SQL Server data into text files, you can format the data through views since BCP's formatting ability is quite limited.

* 5. *自定义数据.如果要显示某些计算值或列名，其格式与基本表列的格式不同，可以通过创建视图来实现.

*5.*Customizing data. If you wish to display some computed values or column names formatted differently than the base table columns, you can do so by creating views.

引用来自 http://sqlserverpedia.com .

这篇关于如何允许用户sql访问限于某些行的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！