Can't assign two design time roles to users in Oracle Database

Support
#1

Hi,
I tried to use design time roles for an application, but I was unable to assign more than one role.

The problem seems to be the index IDX_SEC_USER_ROLE_UNIQ_ROLE:

image

As the field ROLE_ID is null for design time roles, it is not possible to assign more than one at the same time.

I wasn’t able to reproduce this problem when using PostgreSQL or HSQL. Probably because of different treatment of null values in these databases (I think oracle treats nulls as equals in unique constraints). I’m not sure, but I think this situation will to happen to MSSQL as well.

I was able to workaround the problem changing the index:

DROP INDEX "IDX_SEC_USER_ROLE_UNIQ_ROLE";

CREATE UNIQUE INDEX "IDX_SEC_USER_ROLE_UNIQ_ROLE" ON "SEC_USER_ROLE" ("USER_ID", "ROLE_ID", "ROLE_NAME", "DELETE_TS");

Is this a valid workaround? Or is there another solution?
Thanks!

Regards,
Peterson.

#3

Hi Peterson,
It’s a valid workaround.
We have created GitHub issue: Exception when assigning more than one predefined role to user using Oracle datastore · Issue #2923 · cuba-platform/cuba · GitHub. We are going to fix it in the next update release.

1 Like