473,883 Members | 2,619 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Can you filter SELECT results?

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(c urRecord.id) ){ addRecordToResu ltSet() }
else { ommitRecordFrom ResultSet() }
END

Jul 23 '05 #1
11 6080
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********@yah oo.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
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(c urRecord.id) ){ addRecordToResu ltSet() }
else { ommitRecordFrom ResultSet() }
END

Jul 23 '05 #2
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?

Jul 23 '05 #3
On 17 Dec 2004 12:39:22 -0800, wa********@yaho o.com wrote:
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(c urRecord.id) ){ addRecordToResu ltSet() }
else { ommitRecordFrom ResultSet() }
END


If you can define accessGranted as a UDF, then all you have to do is

SELECT * from myTable WHERE accessGranted(m yTable.ID)
Jul 23 '05 #4
Hi

It would be simpler if you dispensed with granting access to an individual
then it would be one query joining the menu, menugrpaccess and usr2grp
tables filtering on the userID in the usr2grp table, something like:

SELECT M.MenuId, M.Name
FROM Menu M
JOIN MenuGrpAccess A ON A.MenuId = M.MenuId
JOIN Usr2Grp G ON G.GroupId = A.GroupId
WHERE G.UserId = @UserId

This does assume that you do not have groups within groups (not another
hierarchy!!!!!) .

John

<wa********@yah oo.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
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?

Jul 23 '05 #5

:) Yeah.

Although I'd never have groups of groups. That's a little much I agree.

Jul 23 '05 #6
OK, (understand I'm just beginning SQL Server, but am an experienced
programmer) now what would the accessGranted() have to return? Would it
return like a string that fills in the where clause or something else?

I'm not sure what is allowed and what's not.
And thanks everyone for the input I've received I really appreciate it.

Jul 23 '05 #7
<wa********@yah oo.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
OK, (understand I'm just beginning SQL Server, but am an experienced
programmer) now what would the accessGranted() have to return? Would it
return like a string that fills in the where clause or something else?
It'd allow you to specify the columns people get returned from the query.
Do you need to vary them as well?
Because.... that's something I'd recommend avoiding if you can.

I'm not sure what is allowed and what's not.
And thanks everyone for the input I've received I really appreciate it.


I like simple designs myself.
I can understand them.
When they go wrong I can fix em easier.

When I've had similar issues I prefer to have users in (windows) groups and
associate SQL security at that level.
People join and leave, it's not my problem. Whoever looks after the windows
security changes that stuff.
With vb.net you can tell what group they're in and hide the button leads to
specific screens by setting visible=false.
I would imagine similar functionality is available in other GUIs, or you
could use sql and write a stored procedure returned the group and call that
instead.

Usually where people are allowed to work with one bit of data and not
another it's because their team ( or whatever ) raises those orders ( or
whatever) and no other team does. Maybe there's another manager or whatever
team deals with the lot and over-rides this....
But generally there's something you can associate with an order (say).
So...
I'd stick a team ( or whatever ) field on some significant table or tables.
Write the team in there as it's created.
Associate the windows group with that team - either directly on a 1:1 basis
or indirectly via a table ( which'd allow for the financial director to see
everything ).
And that'd pretty much be that.

You can work out the windows group in the gui and pass to the stored
procedure or in the stored procedure.
Watch out for jobs in the latter case.

HTH.
--
Regards,
Andy O'Neill
Jul 23 '05 #8
John Bell (jb************ @hotmail.com) writes:
It would be simpler if you dispensed with granting access to an individual
then it would be one query joining the menu, menugrpaccess and usr2grp
tables filtering on the userID in the usr2grp table, something like:

SELECT M.MenuId, M.Name
FROM Menu M
JOIN MenuGrpAccess A ON A.MenuId = M.MenuId
JOIN Usr2Grp G ON G.GroupId = A.GroupId
WHERE G.UserId = @UserId

This does assume that you do not have groups within groups (not another
hierarchy!!!!!) .


As long as there are not groups within groups, there is no major problem
with direct access for users.

SELECT M.MenuId, M.Name
FROM Menu M
JOIN MenuGrpAccess A ON A.MenuId = M.MenuId
JOIN Usr2Grp G ON G.GroupId = A.GroupId
WHERE G.UserId = @UserId
UNION
SELECT M.MenuID, M.name
FROM Menu M
JOIN MenuUserAccess a ON A.MenuID = M.MenuId
WHERE A.UserID = @UserID

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
Hi Erland

I though about the union, but went for the simpler model as I think
maintainance would be easier, after all there is always the superset! I also
missed out the possible need to use DISTINCT if users were in multiple
groups with access to the same menu.

John
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
John Bell (jb************ @hotmail.com) writes:
It would be simpler if you dispensed with granting access to an
individual
then it would be one query joining the menu, menugrpaccess and usr2grp
tables filtering on the userID in the usr2grp table, something like:

SELECT M.MenuId, M.Name
FROM Menu M
JOIN MenuGrpAccess A ON A.MenuId = M.MenuId
JOIN Usr2Grp G ON G.GroupId = A.GroupId
WHERE G.UserId = @UserId

This does assume that you do not have groups within groups (not another
hierarchy!!!!!) .


As long as there are not groups within groups, there is no major problem
with direct access for users.

SELECT M.MenuId, M.Name
FROM Menu M
JOIN MenuGrpAccess A ON A.MenuId = M.MenuId
JOIN Usr2Grp G ON G.GroupId = A.GroupId
WHERE G.UserId = @UserId
UNION
SELECT M.MenuID, M.name
FROM Menu M
JOIN MenuUserAccess a ON A.MenuID = M.MenuId
WHERE A.UserID = @UserID

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2404
by: Edward Burns | last post by:
I am trying to create an events calendar with a complete month view. I want to be able to get all the events for a particular month, using only one recordset on the page then be able to loop through each day of that month and output the events for a particular day for each day without having to open up 28 to 31 different recordsets. The problem that I am having is with the objRS.Filter on line #12 of the asp code below. I can not figure...
2
2612
by: Terry | last post by:
I have cerated a Form, ExamsFrm, which is used to input details of exams taken at several Centres. It dosplays details from StudentTbl and has a SubForm which displays details from ExamsTble. I have incorporated some VB6 code as well as some Conditional Formatting. The code automatically updates CheckBoxes in the SubForm, depending on exam results, and the Conditional Formatting changes background colours, depending on CheckBox datum. ...
8
6541
by: dick | last post by:
I am just trying to print/report the results of a "filter by selection" which is done by right-clicking a form, filling in values, and "applying the filter." I have searched the newsgroups, and there are many examples. BUT, they fail sometimes. The techique is to pass the form's Me.filter as the "where condition" in a Docmd.openreport statement in code behind a "print button" on the form.
0
1998
by: Faybert | last post by:
Hello, and Thanks in advance for any light you might shed on my troubles. I'm trying to setup a series of checkboxes, or a checkboxlist to control the results that are shown on a gridview control. I have a column in my database that hold the current status of the items in there. If I used a checkboxlist and populate it with my options, and set the SQL statement on my gridview control to: "SELECT , , , FROM WHERE
6
1706
by: supasnail | last post by:
I've been going round in circles here trying to get this to work and my ignorance of ASP is now showing me up. I want to construct a query string to allow me to filter entries to be displayed on a web page with respect to the date-entry contained in the database. i.e. an archived results page will show all entries where "my_date" is less than 'todays date'
11
49280
by: jason.teen | last post by:
Hi, I dont seem to be able to get this Filter right on my RecordSet for some reason. I have this code: Dim rs As Recordset Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
5
2665
by: Ron S | last post by:
After days of searching I finally an example that would work with my application, the only problem is after entering all of the code it is not working. Would someone be kind enough to take a look at this... Exmaple web site: http://allenbrowne.com/ser-62.html My Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results...
2
3208
by: leeperman | last post by:
In Dreaweaver I cannot filter my database results to display only specific data that is retrieved from mulptile drop down list on my search page. The drop down list selections are posted to my display page by GET. How do i write my sql code so to only display info where TOWN = "Town selected from list" AND BEDS ="No of Beds selected from list My search page form is below <form action="tsearchresults.asp" method="get" name="townSearchForm"...
1
4177
by: jcf378 | last post by:
Hi all-- Does anyone have any insight as to how I might create a search form that allows a user to select criteria based on any related table in the whole database. The search form I have now only allows me to filter based on variables in a single table. I would like to have a search form where I can select multiple variables (from various linked tables) to filter by, and return results based on this multi-table filter. Allen Browne...
1
6808
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on. My databse mostly includes bits of code for different examples. I have one last thing to finish. I am trying to create a search form that will allow users to select criteria from multiple sources eg ,multi select list boxes , combo boxes. I have a subform showing all the...
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11125
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9568
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7962
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5794
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4607
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.