SEO

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

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

<wackyphill@yahoo.com> wrote in message
news:1103315962.418936.118250@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
>

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?

On 17 Dec 2004 12:39:22 -0800, wackyphill@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)

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

<wackyphill@yahoo.com> wrote in message
news:1103317871.779405.261970@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?
>

Yeah.

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

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.

<wackyphill@yahoo.com> wrote in message
news:1103334538.943609.119930@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

John Bell (jbellnewsposts@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, esquel@sommarskog.se

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

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" <esquel@sommarskog.se> wrote in message
news:Xns95C3BFD1DEFDDYazorman@127.0.0.1...
> John Bell (jbellnewsposts@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, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp