Problem with tokens id in Oracle database

Hi,
I have 2.20.1 PrivacyIDEA version on Oracle database with Oracle instant client and cx_Oracle driver. I’ve noticed id problem when you create tokens.
For example, I have user Alex in Oracle based sql resolver. This user has userid (Number datatype) 1
I create token for him, all is fine, but… There are blank user and realm fields in All tokens view, I see 1.0 in UserId field also.
All_token_view

I try analyze it. I’ve noticed the next things:

  1. USER_ID in TOKEN table has NVARCHAR2(320 CHAR) datatype, because it’s universal for databases user id in sql resolvers and ldap resolvers, for example. But ldap resolver have no any problems, because it is string value, that can be put in table without any convertation. In case of database resolver we have convert function from Number to NVARCHAR2, so the result becomes 1.0 instead 1
  2. If I edit this field manually in table to 1 I can see user and realm fields, but I can’t pass authentification (wrong otp pin)
  3. get_token_owner(serial) function from lib.token not work if I have 1.0 value
  4. inserting log.debug gives result that 1.0 value is added to database on tokenobject.update(upd_params) step in privacyidea/lib/token.py
  5. In my opinion, it depends from database the privacyidea installed. I have a test server with standard internal mysql database, all is good

Do you have any advices to fix this problem?

Regards,
Alexander

I recommend you use another UserID. Maybe a varchar field.
Kind regards
Cornelius

Thanks for your answer, Cornelius!
I should change id in sql resolver table from Number to varchar, isn’t it?

Regards,
Alexander

Hi Alexander,

no, you could simply choose another column to be the identifying element.
The useridresolver is ment to abstract the username. E.g. is a user changes his name…

But if you know, that usernames always stay the same, you can also use the username as “uid”.

Kind regards
Cornelius

Thanks for your help, Cornelius!

All is working now, I’ve changed id type to varchar2 instead Number.

Regards,
Alexander