The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Table where Team membership is stored
Posted: 04 May 07 1:08 PM
|
fiogf49gjkf0d Can't seem to determine which table contains the relationship between Users and the Team(s) they are a member of. Anyone know? |
|
|
|
Re: Table where Team membership is stored
Posted: 04 May 07 1:17 PM
|
fiogf49gjkf0d Clarification - I see there are some not-easy-to-comprehend relationships between SECCODEJOINS, SECCODEDEFS and others. But where is the actual Team name? |
|
|
| |
| |
|
Re: Table where Team membership is stored
Posted: 07 May 07 12:29 PM
|
fiogf49gjkf0d Ryan,
Looked over your code was pretty neat, How do you handle the team wihtin Team concept..?
How do you find if User X is in Team A and Team A is in Team D..?
|
|
|
| |
|
Re: Table where Team membership is stored
Posted: 07 May 07 2:34 PM
|
fiogf49gjkf0d If a user is in Team A and Team A is in Team B, SecRights will list the user as a member of both teams. You dont have to worry about traversing "team in team" membership.
If it matters to you that a user is a member of Team B indirectly, you can use the SecCodeJoins table. Note that this table does not sync to remotes.
Timmus |
|
|
| |
|
Re: Table where Team membership is stored
Posted: 11 May 07 1:27 PM
|
fiogf49gjkf0d Timmus: Here is the SQl I am working with, this shows me the teams that the user belongs to however it doesn't show the team inside a team reference.. so if User A is in Team A and Team A is part of Team C I don't see User A as being part of Team C..: any ideas..?
SELECT distinct SECCODE.SECCODEDESC, USERSECURITY.USERID, UserInfo.UserName
FROM SECCODE
INNER JOIN SECCODEJOINS ON SECCODE.SECCODEID = SECCODEJOINS.PARENTSECCODEID
INNER JOIN USERSECURITY ON SECCODEJOINS.CHILDSECCODEID = USERSECURITY.DEFAULTSECCODEID
INNER JOIN USERINFO ON USERSECURITY.USERID = USERINFO.USERID
WHERE SECCODETYPE = 'G' AND USERSECURITY.TYPE <> 'R' and USERSECURITY.UserCode <> 'ADMIN'
|
|
|
|
Re: Table where Team membership is stored
Posted: 11 May 07 3:34 PM
|
fiogf49gjkf0d 1. Security hierarchy is stored in SecCodeJoins. It only stores the parent to child relationship. To see the entire family you have to traverse the relationships yourself.
2. Actual Security access is determined by the SecRights table. In your example, you will find that the user will have two records - one giving them access to Team A and the second record giving them access to Team C. You will find absolutely nothing in that table that informs you that the user has access to Team C because of their access to Team A. That is what seccodejoins is for.
Make sense?
Timmus |
|
|
|
Re: Table where Team membership is stored
Posted: 12 May 07 5:09 PM
|
fiogf49gjkf0d Timmus yes it makes sense.. however when I run the query above I don't get the user listed as belonging to the team in the above example
User A on Team A which is in Team C is not pulling a row showing that they are in TEam C only the team A row is showing |
|
|
|
Re: Table where Team membership is stored
Posted: 12 May 07 10:29 PM
|
fiogf49gjkf0d RJ,
Your query is using the wrong table. You need to use SecRights. This is explained in more detail in my previous post.
SELECT
U.UserName, S.SecCodeDesc, SP.ProfileDescription
FROM
UserInfo U
INNER JOIN SecRights SR
ON SR.AccessID = U.UserID
INNER JOIN SecProfile SP
ON SP.ProfileID = SR.ProfileID
INNER JOIN SecCode S
ON S.SecCodeID = SR.SecCodeID
Hope that helps!
Timmus |
|
|
|
Re: Table where Team membership is stored
Posted: 13 May 07 8:49 AM
|
fiogf49gjkf0d Unless you're interested in seeing the Retired users, I'd suggesting modifying the query as follows:
SELECT
U.UserName, S.SecCodeDesc, SP.ProfileDescription
FROM
UserInfo U
INNER JOIN SecRights SR
ON SR.AccessID = U.UserID
INNER JOIN SecProfile SP
ON SP.ProfileID = SR.ProfileID
INNER JOIN SecCode S
ON S.SecCodeID = SR.SecCodeID
/* Join on UserSecurity table that contains the Type field */
/*R=Retired; N=Network; C=Concurrent*/
INNER JOIN UserSecurity US
ON
U.UserID = US.UserID
WHERE
US.Type <> 'R' |
|
|
|
Re: Table where Team membership is stored
Posted: 14 May 07 7:55 AM
|
fiogf49gjkf0d Rick and Timmus, Thanks for all the help.. interesting data this pulls back..
You'll see that it shows retired people in the seccode desc if a current user had access to a retired users accounts.. Now I'll work on clearing that up and I should have a current listing.. Which I'll post thanks all |
|
|
|
Re: Table where Team membership is stored
Posted: 14 May 07 8:11 AM
|
fiogf49gjkf0d Thanks All here is what came about of the modifications.. This shows access by team and has all the teams listed.. Thanks again.. maybe this will help someone:
==================== CODE =====================
SELECT U.UserId, U.UserName, S.SecCodeDesc, SP.ProfileDescription
FROM UserInfo U INNER JOIN SecRights SR ON SR.AccessID = U.UserID
INNER JOIN SecProfile SP ON SP.ProfileID = SR.ProfileID
INNER JOIN SecCode S ON S.SecCodeID = SR.SecCodeID
/* Join on UserSecurity table that contains the Type field */
/*R=Retired; N=Network; C=Concurrent*/
INNER JOIN UserSecurity US ON U.UserID = US.UserID
WHERE S.SECCODETYPE = 'G' AND US.TYPE <> 'R'
Order by U.UserName
==================================================
Rich |
|
|
|
Re: Table where Team membership is stored
Posted: 22 Jan 09 12:45 PM
|
Ryan,
I have the same question and very glad to found something here, however the post does not exist any more. Could you please re-post it somewhere. Thanks a lot!!!
Emily |
|
|
| |
|