Act_id_priv_mapping not adding user of group record

In flowable-idm portal when i add user ankit to group claimManager(503), then i found that in act_id_priv_mapping table not record available for user ankit with group claimManager.

Due to this when i add group claimManager to acess modeler privileges, then not able to login to modeler UI portal. See flowable-IDM logs:

2020-04-02 02:04:30.415 DEBUG 1 — [nio-9090-exec-5] stomUsernamePasswordAuthenticationFilter : Request is to process authentication
2020-04-02 02:04:30.436 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.i.LogInterceptor : — starting CreatePrivilegeQueryCmd --------------------------------------------------------
2020-04-02 02:04:30.437 DEBUG 1 — [nio-9090-exec-5] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.437 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.i.LogInterceptor : — CreatePrivilegeQueryCmd finished --------------------------------------------------------
2020-04-02 02:04:30.437 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.i.LogInterceptor : — starting PrivilegeQueryImpl --------------------------------------------------------
2020-04-02 02:04:30.437 DEBUG 1 — [nio-9090-exec-5] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.438 DEBUG 1 — [nio-9090-exec-5] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Preparing: select RES.* from ACT_ID_PRIV RES WHERE exists(select 1 from ACT_ID_PRIV_MAPPING mapping where RES.ID_ = mapping.PRIV_ID_ and mapping.USER_ID_ = ?) order by RES.ID_ asc
2020-04-02 02:04:30.438 DEBUG 1 — [nio-9090-exec-5] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Parameters: ankit(String)
2020-04-02 02:04:30.441 DEBUG 1 — [nio-9090-exec-5] e.i.p.e.P.selectPrivilegeByQueryCriteria : <== Total: 0
2020-04-02 02:04:30.442 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.d.DbSqlSession : Flushing dbSqlSession
2020-04-02 02:04:30.442 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.d.DbSqlSession : flush summary: 0 insert, 0 update, 0 delete.
2020-04-02 02:04:30.442 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.d.DbSqlSession : now executing flush…
2020-04-02 02:04:30.442 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.i.LogInterceptor : — PrivilegeQueryImpl finished --------------------------------------------------------
2020-04-02 02:04:30.449 DEBUG 1 — [nio-9090-exec-5] stomUsernamePasswordAuthenticationFilter : Authentication success. Updating SecurityContextHolder to contain: org.springframework.security.authentication.UsernamePasswordAuthenticationToken@58a34d1: Principal: org.flowable.ui.common.security.FlowableAppUser@58a8789: Username: ankit; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked: true; Not granted any authorities; Credentials: [PROTECTED]; Authenticated: true; Details: org.springframework.security.web.authentication.WebAuthenticationDetails@ffff4c9c: RemoteIpAddress: 27.5.39.186; SessionId: null; Not granted any authorities
2020-04-02 02:04:30.449 DEBUG 1 — [nio-9090-exec-5] u.i.s.CustomPersistentRememberMeServices : Creating new persistent login for user ankit
2020-04-02 02:04:30.449 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.i.LogInterceptor : — starting CreateTokenCmd --------------------------------------------------------
2020-04-02 02:04:30.449 DEBUG 1 — [nio-9090-exec-5] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.i.LogInterceptor : — CreateTokenCmd finished --------------------------------------------------------
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.i.LogInterceptor : — starting SaveTokenCmd --------------------------------------------------------
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.d.DbSqlSession : Flushing dbSqlSession
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.d.DbSqlSession : insert TokenEntity[tokenValue=MpkB1+Pqt5hfiOaaIf+cWA==, userId=ankit]
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.d.DbSqlSession : flush summary: 1 insert, 0 update, 0 delete.
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.d.DbSqlSession : now executing flush…
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.d.DbSqlSession : inserting: TokenEntity[tokenValue=MpkB1+Pqt5hfiOaaIf+cWA==, userId=ankit]
2020-04-02 02:04:30.450 DEBUG 1 — [nio-9090-exec-5] o.f.i.e.i.p.e.T.insertToken : ==> Preparing: insert into ACT_ID_TOKEN ( ID_, REV_, TOKEN_VALUE_, TOKEN_DATE_, IP_ADDRESS_, USER_AGENT_, USER_ID_, TOKEN_DATA_ ) values ( ?, 1, ?, ?, ?, ?, ?, ? )
2020-04-02 02:04:30.451 DEBUG 1 — [nio-9090-exec-5] o.f.i.e.i.p.e.T.insertToken : ==> Parameters: kJNDWYxj+gx1Tj3t4KtXLQ==(String), MpkB1+Pqt5hfiOaaIf+cWA==(String), 2020-04-02 02:04:30.45(Timestamp), 27.5.39.186(String), Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:74.0) Gecko/20100101 Firefox/74.0(String), ankit(String), null
2020-04-02 02:04:30.451 DEBUG 1 — [nio-9090-exec-5] o.f.i.e.i.p.e.T.insertToken : <== Updates: 1
2020-04-02 02:04:30.451 DEBUG 1 — [nio-9090-exec-5] o.f.c.e.i.i.LogInterceptor : — SaveTokenCmd finished --------------------------------------------------------
2020-04-02 02:04:30.558 DEBUG 1 — [nio-9090-exec-9] u.i.s.CustomPersistentRememberMeServices : Remember-me cookie detected
2020-04-02 02:04:30.559 DEBUG 1 — [nio-9090-exec-9] o.f.c.e.i.i.LogInterceptor : — starting CreateTokenQueryCmd --------------------------------------------------------
2020-04-02 02:04:30.559 DEBUG 1 — [nio-9090-exec-9] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.559 DEBUG 1 — [nio-9090-exec-9] o.f.c.e.i.i.LogInterceptor : — CreateTokenQueryCmd finished --------------------------------------------------------
2020-04-02 02:04:30.559 DEBUG 1 — [nio-9090-exec-9] o.f.c.e.i.i.LogInterceptor : — starting TokenQueryImpl --------------------------------------------------------
2020-04-02 02:04:30.559 DEBUG 1 — [nio-9090-exec-9] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.560 DEBUG 1 — [nio-9090-exec-9] f.i.e.i.p.e.T.selectTokenByQueryCriteria : ==> Preparing: select RES.* from ACT_ID_TOKEN RES WHERE RES.ID_ = ? order by RES.ID_ asc
2020-04-02 02:04:30.560 DEBUG 1 — [nio-9090-exec-9] f.i.e.i.p.e.T.selectTokenByQueryCriteria : ==> Parameters: kJNDWYxj+gx1Tj3t4KtXLQ==(String)
2020-04-02 02:04:30.561 DEBUG 1 — [nio-9090-exec-9] f.i.e.i.p.e.T.selectTokenByQueryCriteria : <== Total: 1
2020-04-02 02:04:30.561 DEBUG 1 — [nio-9090-exec-9] o.f.c.e.i.d.DbSqlSession : Flushing dbSqlSession
2020-04-02 02:04:30.561 DEBUG 1 — [nio-9090-exec-9] o.f.c.e.i.d.DbSqlSession : flush summary: 0 insert, 0 update, 0 delete.
2020-04-02 02:04:30.561 DEBUG 1 — [nio-9090-exec-9] o.f.c.e.i.d.DbSqlSession : now executing flush…
2020-04-02 02:04:30.561 DEBUG 1 — [nio-9090-exec-9] o.f.c.e.i.i.LogInterceptor : — TokenQueryImpl finished --------------------------------------------------------
2020-04-02 02:04:30.562 DEBUG 1 — [nio-9090-exec-9] u.i.s.CustomPersistentRememberMeServices : Remember-me cookie accepted
2020-04-02 02:04:30.823 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.i.LogInterceptor : — starting CreatePrivilegeQueryCmd --------------------------------------------------------
2020-04-02 02:04:30.823 DEBUG 1 — [io-9090-exec-10] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.823 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.i.LogInterceptor : — CreatePrivilegeQueryCmd finished --------------------------------------------------------
2020-04-02 02:04:30.823 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.i.LogInterceptor : — starting PrivilegeQueryImpl --------------------------------------------------------
2020-04-02 02:04:30.823 DEBUG 1 — [io-9090-exec-10] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.824 DEBUG 1 — [io-9090-exec-10] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Preparing: select RES.* from ACT_ID_PRIV RES WHERE exists(select 1 from ACT_ID_PRIV_MAPPING mapping where RES.ID_ = mapping.PRIV_ID_ and mapping.USER_ID_ = ?) order by RES.ID_ asc
2020-04-02 02:04:30.824 DEBUG 1 — [io-9090-exec-10] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Parameters: flowLdap(String)
2020-04-02 02:04:30.829 DEBUG 1 — [io-9090-exec-10] e.i.p.e.P.selectPrivilegeByQueryCriteria : <== Total: 5
2020-04-02 02:04:30.829 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.d.DbSqlSession : Flushing dbSqlSession
2020-04-02 02:04:30.829 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.d.DbSqlSession : flush summary: 0 insert, 0 update, 0 delete.
2020-04-02 02:04:30.829 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.d.DbSqlSession : now executing flush…
2020-04-02 02:04:30.829 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.i.LogInterceptor : — PrivilegeQueryImpl finished --------------------------------------------------------
2020-04-02 02:04:30.848 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.i.LogInterceptor : — starting CreateTokenQueryCmd --------------------------------------------------------
2020-04-02 02:04:30.848 DEBUG 1 — [io-9090-exec-10] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.848 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.i.LogInterceptor : — CreateTokenQueryCmd finished --------------------------------------------------------
2020-04-02 02:04:30.848 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.i.LogInterceptor : — starting TokenQueryImpl --------------------------------------------------------
2020-04-02 02:04:30.848 DEBUG 1 — [io-9090-exec-10] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.850 DEBUG 1 — [io-9090-exec-10] f.i.e.i.p.e.T.selectTokenByQueryCriteria : ==> Preparing: select RES.* from ACT_ID_TOKEN RES WHERE RES.ID_ = ? order by RES.ID_ asc
2020-04-02 02:04:30.850 DEBUG 1 — [io-9090-exec-10] f.i.e.i.p.e.T.selectTokenByQueryCriteria : ==> Parameters: kJNDWYxj+gx1Tj3t4KtXLQ==(String)
2020-04-02 02:04:30.851 DEBUG 1 — [io-9090-exec-10] f.i.e.i.p.e.T.selectTokenByQueryCriteria : <== Total: 1
2020-04-02 02:04:30.851 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.d.DbSqlSession : Flushing dbSqlSession
2020-04-02 02:04:30.851 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.d.DbSqlSession : flush summary: 0 insert, 0 update, 0 delete.
2020-04-02 02:04:30.851 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.d.DbSqlSession : now executing flush…
2020-04-02 02:04:30.853 DEBUG 1 — [io-9090-exec-10] o.f.c.e.i.i.LogInterceptor : — TokenQueryImpl finished --------------------------------------------------------
2020-04-02 02:04:30.873 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.i.LogInterceptor : — starting CreatePrivilegeQueryCmd --------------------------------------------------------
2020-04-02 02:04:30.881 DEBUG 1 — [nio-9090-exec-1] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.881 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.i.LogInterceptor : — CreatePrivilegeQueryCmd finished --------------------------------------------------------
2020-04-02 02:04:30.881 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.i.LogInterceptor : — starting PrivilegeQueryImpl --------------------------------------------------------
2020-04-02 02:04:30.881 DEBUG 1 — [nio-9090-exec-1] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.881 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Preparing: select RES.* from ACT_ID_PRIV RES WHERE exists(select 1 from ACT_ID_PRIV_MAPPING mapping where RES.ID_ = mapping.PRIV_ID_ and mapping.USER_ID_ = ?) order by RES.ID_ asc
2020-04-02 02:04:30.882 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Parameters: flowLdap(String)
2020-04-02 02:04:30.883 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : <== Total: 5
2020-04-02 02:04:30.883 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.d.DbSqlSession : Flushing dbSqlSession
2020-04-02 02:04:30.883 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.d.DbSqlSession : flush summary: 0 insert, 0 update, 0 delete.
2020-04-02 02:04:30.883 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.d.DbSqlSession : now executing flush…
2020-04-02 02:04:30.883 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.i.LogInterceptor : — PrivilegeQueryImpl finished --------------------------------------------------------
2020-04-02 02:04:30.888 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.i.LogInterceptor : — starting CreatePrivilegeQueryCmd --------------------------------------------------------
2020-04-02 02:04:30.888 DEBUG 1 — [nio-9090-exec-1] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.888 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.i.LogInterceptor : — CreatePrivilegeQueryCmd finished --------------------------------------------------------
2020-04-02 02:04:30.888 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.i.LogInterceptor : — starting PrivilegeQueryImpl --------------------------------------------------------
2020-04-02 02:04:30.888 DEBUG 1 — [nio-9090-exec-1] o.f.c.s.SpringTransactionInterceptor : Running command with propagation REQUIRED
2020-04-02 02:04:30.888 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Preparing: select RES.* from ACT_ID_PRIV RES WHERE exists(select 1 from ACT_ID_PRIV_MAPPING mapping where RES.ID_ = mapping.PRIV_ID_ and mapping.USER_ID_ = ?) order by RES.ID_ asc
2020-04-02 02:04:30.889 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Parameters: ankit(String)
2020-04-02 02:04:30.890 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : <== Total: 0
2020-04-02 02:04:30.890 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.d.DbSqlSession : Flushing dbSqlSession
2020-04-02 02:04:30.890 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.d.DbSqlSession : flush summary: 0 insert, 0 update, 0 delete.
2020-04-02 02:04:30.890 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.d.DbSqlSession : now executing flush…
2020-04-02 02:04:30.890 DEBUG 1 — [nio-9090-exec-1] o.f.c.e.i.i.LogInterceptor : — PrivilegeQueryImpl finished --------------------------------------------------------

When i insert record like

INSERT INTO public.act_id_priv_mapping (id_, priv_id_, user_id_, group_id_) VALUES(‘5233657a-7480-11ea-b3f0-0242c0a8b003’, ‘a3f0ff41-f536-11e9-9d60-0242ac1d0003’, ‘ankit’, ‘503’);

then i able to login successfully. Please help me for same.

This one is issue in log where we got no records.

The act_id_priv_mapping table is for mapping privileges to groups and users, not users to groups.

You need to check the ACT_ID_MEMBERSHIP table to see if your mapping is there.

Are you using LDAP? I can’t see the group queries being executed in the log that you provided. I also don’t see the privilege queries for groups, only for users

2020-04-02 02:04:30.888 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Preparing: select RES.* from ACT_ID_PRIV RES WHERE exists(select 1 from ACT_ID_PRIV_MAPPING mapping where RES.ID_ = mapping.PRIV_ID_ and mapping.USER_ID_ = ?) order by RES.ID_ asc
2020-04-02 02:04:30.889 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : ==> Parameters: ankit(String)
2020-04-02 02:04:30.890 DEBUG 1 — [nio-9090-exec-1] e.i.p.e.P.selectPrivilegeByQueryCriteria : <== Total: 0

Cheers,
Filip

As per my understanding users can be assigned to a particular group and then that user of than group can login to flowable-modeler if we provide access to that particular group only. Correct me if i am wrong?

Yes users can be assigned to a group. However, what you are showing is assigning user to a privilege.

How are you assigning the user to a group? You are using LDAP, right? The group assignment should come from there

When we add user to group then only entry interted in this table. but i want to login to flowable-modeler(where a gave access to that particular group) and table ACT_ID_MEMBERSHIP not used at time of login with flowable-modeler.

Above log is of while login in flowable-modeler with user of group.Correct me if i am wrong?

Yes i am using LDAP but flowable engine using both LDAP and postgresql.

Have a look at my reply at Using LDAP all users and groups records not sync.

You can’t use both LDAP and the DB for users. You have to pick one over the other

I am using Debug logging level in flowable idm. and above is of while login to flowable-modeler for particular user of a group (that having access to modeler).

@filiphr Can you please provide me sample docker configuration of flowable with LDAP?

When i remove postgresql db source from my configuration and add only LDAP configuration then i am getting following error while adding user to group:

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential integrity constraint violation: “ACT_FK_MEMB_GROUP: PUBLIC.ACT_ID_MEMBERSHIP FOREIGN KEY(GROUP_ID_) REFERENCES PUBLIC.ACT_ID_GROUP(ID_) (‘501’)”; SQL statement:
insert into ACT_ID_MEMBERSHIP (USER_ID_, GROUP_ID_)
values (
?,
?
) [23506-199]

I am not using h2 in any of configuration. Its helpfull if you add sample docker configuration with LDAP to https://github.com/flowable/flowable-engine/tree/master/docker/config