April 7, 2008 at 8:02 pm
Hello and thanks for taking the time. The table structures involved are:
APP_USER
----------
USR_ID
USER_ROLES
------------
USR_ID
ROL_ID
ROLE
-----
ROL_ID
ACCESS_RIGHTS
----------------
PROTECTED_OBJECT_TYPE
PROTECTED_OBJECT_ID
MEMBERSHIP_OBJECT_TYPE
MEMBERSHIP_OBJECT_ID
READ (bit)
UPDATE (bit)
DENY (bit)
- PROTECTED_OBJECT_TYPE can be: 'property', 'department', or 'entity'
- if PROTECTED_OBJECT_TYPE is 'property', the PROTECTED_OBJECT_ID refers to a PROPERTY.PROP_ID (not shown for brevity), and the same is true for 'department' and 'entity' (DEPARTMENT and ENTITY tables)
- MEMBERSHIP_OBJECT_TYPE can be: 'user', 'role'
- if MEMBERSHIP_OBJECT_TYPE is 'user', the MEMBERSHIP_OBJECT_ID refers to APP_USER.USR_ID, and if it is 'role' it refers to ROLE.ROL_ID
- A user inherits all of the access rights for all of the roles he belongs to
The issue I am trying to solve is to easily, and efficiently, get access to all of the property Ids (PROTECTED_OBJECT_ID when PROTECTED_OBJECT_TYPE = 'property') a particular user has UPDATE access to (UPDATE = 1).
I am currently executing queries for the user, then each role, and then merging the results together in my application (C#) layer. Then I am passing the list of IDs into subsequent queries to "filter" the list of properties this user has access to. The list is getting large, and I think there must be a better way to get to this data.
Any ideas? Please let me know if I can provide more information. Thank you very much in advance!
-Aaron
April 8, 2008 at 12:16 am
Does this help? I can't test it, since you haven't given the table schemas or any sample data.
Select Usr_ID
from User_roles
inner join ACCESS_RIGHTS ON
((Access_Rights.MEMBERSHIP_OBJECT_ID = User_Roles.Rol_ID AND MEMBERSHIP_OBJECT_TYPE = 'Role') OR
(Access_Rights.MEMBERSHIP_OBJECT_ID = User_Roles.Usr_ID AND MEMBERSHIP_OBJECT_TYPE = 'User'))
WHERE PROTECTED_OBJECT_TYPE = 'property' AND [UPDATE] = 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2008 at 6:27 am
adressin (4/7/2008)
- PROTECTED_OBJECT_TYPE can be: 'property', 'department', or 'entity'- if PROTECTED_OBJECT_TYPE is 'property', the PROTECTED_OBJECT_ID refers to a PROPERTY.PROP_ID (not shown for brevity), and the same is true for 'department' and 'entity' (DEPARTMENT and ENTITY tables)
- MEMBERSHIP_OBJECT_TYPE can be: 'user', 'role'
- if MEMBERSHIP_OBJECT_TYPE is 'user', the MEMBERSHIP_OBJECT_ID refers to APP_USER.USR_ID, and if it is 'role' it refers to ROLE.ROL_ID
- A user inherits all of the access rights for all of the roles he belongs to
The issue I am trying to solve is to easily, and efficiently, get access to all of the property Ids (PROTECTED_OBJECT_ID when PROTECTED_OBJECT_TYPE = 'property') a particular user has UPDATE access to (UPDATE = 1).
hi aaron. you need to compare MEMBERSHIP_OBJECT_ID to the user's ID or all role IDs the user has. since you have two types of MMEMBERSHIP_OBJECT_TYPEs, a derived table based on a union should do the trick.
select
AR.PROTECTED_OBJECT_ID
from
ACCESS_RIGHTS as AR
join (select 'user' as object_type, @user_id as object_id
union select 'role', UR.ROL_ID from USER_ROLES where UR.USR_ID = @user_id) as X
on AR.MEMBERSHIP_OBJECT_TYPE = X.object_type
on AR.MEMBERSHIP_OBJECT_ID = X.object_id
where
AR.PROTECTED_OBJECT_TYPE = 'property'
and AR.UPDATE = 1
April 8, 2008 at 3:48 pm
Thank you both... either of those works to get me the list of property ids the user has access to. So in your professional opinion, what would be the best way to "merge" this data into the many queries I have that require the filtered list? Should I join my queries to one of these or use a subquery? Is it a problem to have a long list of "IN" parameters?
Thanks again!
-Aaron
April 8, 2008 at 4:02 pm
I only submitted my alternative because I don't like to use OR conditions in JOINs. They usually prevent the optimizer from using any supporting indices for the join.
April 9, 2008 at 12:29 am
SQL 2005's a lot better at ORs than 2000 was. It now can (and sometimes does) process an or the same way it would process a union.
Try them both, see if either performs better. Otherwise go with the one that looks better to you
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2008 at 11:50 am
How to merge this into your queries?
I would use the derived table technique as a guide for creating a view which described rights for each user. Could then query on USR_ID/UPDATE as described in your question, on USR_ID/DENY, or on PROTECTED_OBJECT/UPDATE to see who could have modified.
(I'm writing this free-hand and not testing, but you'll get my drift.)
CREATE VIEW my_VIEW(
USR_ID, PROTECTED_OBJECT_TYPE, PROTECTED_OBJECT_ID, READ, UPDATE, DENY, ROL_ID)
AS
SELECT
MEMBERSHIP_OBJECT_ID, PROTECTED_OBJECT_TYPE, PROTECTED_OBJECT_ID, READ, UPDATE, DENY, 0
from ACCESS_RIGHTS where MEMBERSHIP_OBJECT_TYPE = 'user'
UNION
SELECT
USR_ID, PROTECTED_OBJECT_TYPE, PROTECTED_OBJECT_ID, READ, UPDATE, DENY, MEMBERSHIP_OBJECT_ID
from ACCESS_RIGHTS INNER JOIN USER_ROLES on MEMBERSHIP_OBJECT_ID=ROL_ID
where MEMBERSHIP_OBJECT_TYPE = 'role'
This view includes ROL_ID to make it easier to research how user acquired a certain priv, if the user/role structure allows a user to inherit opposite UPDATE bits from different roles for same object.
April 9, 2008 at 12:09 pm
Fantastic guys!! Thanks so much... I can say that I learned something from this 😉
Kind Regards,
Aaron
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply