By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Can you filter SELECT results?

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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...
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

Jul 23 '05 #2

P: n/a
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

P: n/a
On 17 Dec 2004 12:39:22 -0800, wa********@yahoo.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(curRecord.id) ){ addRecordToResultSet() }
else { ommitRecordFromResultSet() }
END


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

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

P: n/a
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********@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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

P: n/a

:) Yeah.

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

Jul 23 '05 #6

P: n/a
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

P: n/a
<wa********@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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

P: n/a
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****@sommarskog.se

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

P: n/a
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****@sommarskog.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****@sommarskog.se

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

Jul 23 '05 #10

P: n/a
John Bell (jb************@hotmail.com) writes:
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.


Undoubtedly only giving access to groups and not individual users, leads
to simpler design. Whether this an acceptable solution from a business
perspective, is more difficult for us to tell. If access to group is the
norm, creating a group for a single user solves that problem. But if single
user is the most common scenario, that will make the system difficult to
use.

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

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

P: n/a
wa********@yahoo.com wrote in news:1103334538.943609.119930
@z14g2000cwz.googlegroups.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?

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 made a slight error in my answer; I forgot that there is no boolean data
type in SQL server. The form of the SELECT statement would look like this:

SELECT * FROM myTable WHERE dbo.AccessGranted(myTable.ID,USER_NAME())=1

As a silly example, the following UDF would return even numbered records to
anybody, and odd numbered records only if the username starts with F:

DEFINE FUNCTION dbo.AccessGranted(@id int, @usr varchar(32))
AS
RETURN CASE WHEN @id mod 2 = 0 then 1
WHEN @usr LIKE 'F%' then 1
ELSE 0
END

Jul 23 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.