你能过滤SELECT结果吗？ [英] Can you filter SELECT results?
问题描述
我想创建一个存储过程，从表格中返回
的记录列表。但是根据只给出某些记录的userID值，将返回他们有权访问的
。
我认为这可能很难在单个SELECT语句，因为
用户可能也属于可能拥有权限的组等。
你可以在T-中做这样的伪代码SQL程序？
DECLARE cur CURSOR FOR SELECT * from myTable
OPEN cur
FETCH NEXT FROM cur
WHILE @@ FETCH_STATUS = 0
BEGIN
if（accessGranted（curRecord.id））{addRecordToResultSet（）}
else {ommitRecordFromResultSet（）}
END
I want to create a stored procedure that returns a list of records from
a table. But depending on a userID value given only certain records
will be returned that they have access to.
I think this might be hard to do in a single SELECT statement because
the user might also belong to a group that might have permission, etc.
Can you do something like this pseudo code in a T-SQL procedure?
DECLARE cur CURSOR FOR SELECT * FROM myTable
OPEN cur
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
BEGIN
if( accessGranted(curRecord.id) ){ addRecordToResultSet() }
else { ommitRecordFromResultSet() }
END
推荐答案
嗨
您可以使用IS_MEMBER函数来限制选择
语句或视图返回的结果，或者您可能希望在某些
$ b $中使用位掩码b方式;问题是，你如何确定谁拥有所需的特权
查看给定的记录。
如果你限制访问，你也可以使用PERMISSIONS功能按栏。
John
< wa ******** @ yahoo.com>在消息中写道
news：11 ********************** @ z14g2000cwz.googlegr oups.com ...
Hi
You can use the IS_MEMBER function to limit the results returned by a select
statement or view, or alternatively you may wish to use a bit mask in some
way; the issue is, how you determine who has what privileges are required to
see the given record.
You can also use the PERMISSIONS function if you limit access by columns.
John
<wa********@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
我想创建一个存储过程，从表中返回一个记录列表。但是根据只给出某些记录的userID值将会返回他们有权访问的内容。
我认为在单个SELECT语句中这可能很难做到，因为
用户也可能属于可能拥有权限的组等。
你能在T-SQL程序中做这样的伪代码吗？
DECLARE cur CURSOR FOR SELECT *来自myTable
OPEN
FETCH NEXT FROM
WHILE @@ FETCH_STATUS = 0
BEGIN
if（accessGranted（curRecord.id））{ addRecordToResultSet（）}
else {ommitRecordFromResultSet（）}
END
I want to create a stored procedure that returns a list of records from
a table. But depending on a userID value given only certain records
will be returned that they have access to.
I think this might be hard to do in a single SELECT statement because
the user might also belong to a group that might have permission, etc.
Can you do something like this pseudo code in a T-SQL procedure?
DECLARE cur CURSOR FOR SELECT * FROM myTable
OPEN cur
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
BEGIN
if( accessGranted(curRecord.id) ){ addRecordToResultSet() }
else { ommitRecordFromResultSet() }
END
好吧，它会回到我的菜单问题。你知道我希望能够说出哪些菜单出现在特定的人或团体中。由于这个
相同的菜单信息将由多个程序访问，因此有一个很好的
来过滤掉DB上菜单的逻辑而不是
每个程序。
我确定它是否可见的方式是这样的（也许有一个更好的方式来支付
）
给定一个userID，我检查menuUserAccess表，查看是否有一个
记录macthing UserID和有问题的menuID。如果是这样，用户
可以访问它。更复杂的是团体。如果第一次测试失败，我需要从usr2grp表中获取用户所属的groupID数组
。最后，我将循环遍历每一个，看看是否在menuGrpAccess表中存在
记录，其中包含该groupID和
有问题的menuID。
记录的存在意味着团体/用户可以访问。
也许这是一个有缺陷的设计？
Well, it goes back to my menu problem. You see I''d like to be able to
say which menus show up for a particular person or group. Since this
same menu info will be accessed by multiple programs it would be nice
to have the logic for filtering out the menus on the DB rather than in
each program.
The way I determine if it is visible is like this (Maybe there is a
better way)
Given a userID I check the menuUserAccess table to see if there is a
record macthing that UserID and the menuID in question. If so the user
has access to it. What makes it more complex is the groups. If the 1st
test failed I''d need to get an array of groupIDs the user belongs to
from my usr2grp table. Finally I''d loop thru each of them and see if a
record exists in the menuGrpAccess table containing that groupID and
the menuID in question.
The presence of the record means the group / user has access.
Maybe this is a flawed design?
2004年12月17日12:39:22 -0800， wa ******** @ yahoo。 com 写道：
On 17 Dec 2004 12:39:22 -0800, wa********@yahoo.com wrote:
我想创建一个存储过程，从表中返回一个记录列表。但是根据只给出某些记录的userID值将会返回他们有权访问的内容。
我认为在单个SELECT语句中这可能很难做到，因为
用户也可能属于可能拥有权限的组等。
你能在T-SQL程序中做这样的伪代码吗？
DECLARE cur CURSOR FOR SELECT *来自myTable
OPEN
FETCH NEXT FROM
WHILE @@ FETCH_STATUS = 0
BEGIN
if（accessGranted（curRecord.id））{ addRecordToResultSet（）}
else {ommitRecordFromResultSet（）}
END
I want to create a stored procedure that returns a list of records from
a table. But depending on a userID value given only certain records
will be returned that they have access to.
I think this might be hard to do in a single SELECT statement because
the user might also belong to a group that might have permission, etc.
Can you do something like this pseudo code in a T-SQL procedure?
DECLARE cur CURSOR FOR SELECT * FROM myTable
OPEN cur
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
BEGIN
if( accessGranted(curRecord.id) ){ addRecordToResultSet() }
else { ommitRecordFromResultSet() }
END
如果你可以将accessGranted定义为UDF，那么你需要做的就是
SELECT * from myTable WHERE accessGranted（myTable.ID）
If you can define accessGranted as a UDF, then all you have to do is
SELECT * from myTable WHERE accessGranted(myTable.ID)
这篇关于你能过滤SELECT结果吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
