By StarkMike - 4/18/2008
For a little backstory you can review this link... if ya want...
http://forum.strataframe.net/Topic4501-21-1.aspx
Here is my question... I'll do my best to explain it clearly ... ;-)
I have a table called Locations... Here is a sample of the records
LocationID LocationName RBSKey
1 Canton RBS.Canton
2 New Philly RBS.NewPhilly
3 Champioin RBS.Champion
We are controlling the locations our users have through RBS. That is why i included the above link... You guys gave me the idea earlier...
Now my question is this... I want to create a view that will only show the Products available in the locations the user has permission to. This would be easy if they could only have permission to one location... but they can have permission to multiple locations... so rather than front load all the records and then run a for...next loop and delete all the records they dont have access to ... i'm hoping I can create a view that will use the RBS tables linked by the RBS keys in the locations table in my database.
Is this clear?
|
By Greg McGuffey - 4/18/2008
I'm not sure a view is going to be much help. Typically a view will handle some data manipulation for you, to make life easier for the consumer, like flatten a data structure (put parents and children together using JOINs) or combine related data from different tables (using a UNION).
Sometimes it helps to start at the result and work backward. I.e. I imagine you want a query something like this:
-- Select the locations a user has based on permissions
Select LocationID, LocationName
From Location
Where RBSKey In('key1','key2','key3',...,'keyn')
Where the 'key1','key2','key3',...,'keyn' needs to be replaced with a list of the locations the user has access to at runtime. Now the hard part...getting that list of permission keys for the user.
There are going to be two problems. First, I'm sure you are using other permission keys in the app (that don't have anything to do with locations), so you will need to somehow know what the valid permission keys are that pertain to locations. You might store them in a table or in a List() or Array.
The second problem is determining which of these permissions the user has. Once you know the list of valid location permission keys, you can use two views in the RBS, to figure this out:
SFSUserPermissionInfo - permissions assigned directly to a user
SFSUserRolePermissionInfo - permissions assigned to a user via a role
Now, if you happen to always be assigning permissions via just one way (i.e. you always assign these location permissions to the user directly XOR you always assign the permissions to the user via a role) then you can just query the appropriate table directly within your original query (assuming the security tables are in the same db as the Location table):
-- Just use a sub query to handle the locations
-- In this case, assuming that all location
-- permissions are assigned directly to users
Select LocationID, LocationName
From Location
Where RBSKey In(Select pm_key
From SFSUserPermissionInfo
Where us_pk = @userID
And up_action = 1 -- use whatever the value for GRANT is...it's an enum
And pm_key In('locKey1','locKey2',...,'locKeyN')
where 'locKey1','locKey2',...,'locKeyN' is the list of valid location keys.
If you put the list of valid location permission keys into a table, you could use another sub query for the pm_key In() to limit the permissions to just location permission. Otherwise, you'll likely need to build the string that will be placed inside the query from you List()/Array/whatever in code. Then you could simply use this query in a Fill method, were the user ID is passed in.
If you are allowing location permissions to be set either via roles or directly, then it gets harder. The rule applied is that permissions set for a user always override any role based permissions. So you would have to figure out the locations based on roles, then see if the user has any permissions that overrule these, specifically if the user has GRANT for a location via a role and DENY set directly.
Hopefully this will help/get you thinking. It took so long to write this, somebody else has probably already posted a better solution...ah well, it's Friday afternoon, and I needed a distraction anyway...thanks! 
|
By Trent L. Taylor - 4/18/2008
Great post, Greg. There is not a whole lot to add 
|
|