In the Web Client’s Administration area under teams (In both v7.5x and 8.0x versions):
1)
When adding a user to a team (child) that is a member of another team
(parent) the system does not correctly insert a record in the SECRIGHTS
table for the user against the parent team. Doing this same thing in the
LAN Admin correctly adds the SECRIGHTS record to the table.
When adding a user to a team (child) that is a member of another team
(parent) the system does not correctly insert a record in the SECRIGHTS
table for the user against the parent team. Doing this same thing in the
LAN Admin correctly adds the SECRIGHTS record to the table.
2)
When removing a user from a team (child) that is a member of another
team (parent) the system does not remove the SECRIGHTS row for the user
in the parent team even though they should no longer have access since
they were removed from the child team. Doing this same thing in the LAN Admin correctly removes the SECRIGHTS record in the table.
When removing a user from a team (child) that is a member of another
team (parent) the system does not remove the SECRIGHTS row for the user
in the parent team even though they should no longer have access since
they were removed from the child team. Doing this same thing in the LAN Admin correctly removes the SECRIGHTS record in the table.
These issues are very problematic
since the UI for managing teams and users do not show any problems,
however the users end up seeing too little or too much and you have to
dig into the database to fix.
since the UI for managing teams and users do not show any problems,
however the users end up seeing too little or too much and you have to
dig into the database to fix.
This query shows the missing rows based on the problems described in item 1:
select a.PARENTSECCODEID, c.SECCODEDESC ParentTeam,
a.childseccodeid, d.SECCODEDESC ChildTeam, b.accessid, e.USERNAME,
t.ACCESSID from SECCODEJOINS a left join SECRIGHTS b on
a.CHILDSECCODEID=b.SECCODEID
a.childseccodeid, d.SECCODEDESC ChildTeam, b.accessid, e.USERNAME,
t.ACCESSID from SECCODEJOINS a left join SECRIGHTS b on
a.CHILDSECCODEID=b.SECCODEID
left join (select accessid, seccodeid from secrights ) t on a.PARENTSECCODEID=t.SECCODEID and b.ACCESSID=t.ACCESSID
left join SECCODE c on a.PARENTSECCODEID=c.SECCODEID
left join SECCODE d on a.CHILDSECCODEID=d.SECCODEID
left join USERINFO e on b.ACCESSID=e.USERID
where a.PARENTSECCODEID<>a.CHILDSECCODEID and t.ACCESSID is null
This query shows extra rows based on problem described in item 2:
select a.ACCESSID, a.SECCODEID, a.SECRIGHTSID, b.SECCODEDESC
from secrights a left join
SECCODE b on a.SECCODEID=b.SECCODEID left join
usersecurity c on a.ACCESSID=c.USERID left join
(
–shows who (children) should be in a given team
select
c.userid accessid,
a.parentseccodeid as seccodeid
from
SECCODEJOINS a left join
seccode b on a.CHILDSECCODEID=b.SECCODEID inner join
usersecurity c on a.CHILDSECCODEID=c.DEFAULTSECCODEID left join
seccode d on a.PARENTSECCODEID=d.SECCODEID
where d.SECCODETYPE in (‘G’,’U’)
union all
–shows what users should see a given user
select
b.USERID as accessid,
a.PARENTSECCODEID as seccodeid
from SECCODEJOINS a left join
usersecurity b on a.CHILDSECCODEID=b.DEFAULTSECCODEID left join
usersecurity c on a.PARENTSECCODEID=c.DEFAULTSECCODEID
where c.USERID is not null and CHILDSECCODEID<>PARENTSECCODEID
and a.PARENTSECCODEID not in (‘SYST00000001′,’SYST00000002’)) as t on a.ACCESSID=t.accessid and a.SECCODEID=t.seccodeid
where t.accessid is null and a.ACCESSID<>’ADMIN’ and b.SECCODETYPE not in (‘S’,’D’) and c.TYPE<>’R’
