Some Problems with Admidio (SHA512&Salt, Username - UID)

[KoljaL]

Hello and first of all many thanks for the idea and implementation of this app, it significantly extends the range of functions of Nextclout!

For our club I am currently trying to establish a connection between Admidio and NC. In the end it should be the case that the administration of the user and groups should only be done via admidio. Unfortunately, the corresponding tables are not available in the given scheme. If necessary, this must be solved using an extra table. First of all, I have the problem that an Admidio user cannot log in to the NC because the password is incorrect.

Admidio can use different algorithms to encrypt the passwords, but can also be forced to use SHA512. I tried different hash algorithms (SHA512, Drupal7), also with a salt in front. Unfortunately without success. This is how the password looks in the database: $6$rounds=50000$T5.rO8Fj$nUcEjAUFICI0/rscUZXnBPVUf6zfh.VxXuZj0c0vkZJZ4oVp50e6UAqlWeBSZODzp5h0Qnlqb9j4/EM5nU/GA1

Furthermore, instead of the login name, the user IDs are displayed in the NC user administration. If the user table UID is filled with the user name, the names fit, but they do not belong to the groups. User table: users Group table: user_relation_types User group table: user_relations https://github.com/Admidio/admidio/blob/master/demo_data/db.sql

I would be happy if you can help me with the problems. If more information is needed, please let me know.

Best regards Kolja

edit: i played arround with workbench: admidio_db_diagram.pdf

[nanoflow]

I made a connection between Admidio and NC. If anyone is interested I can explain.

[kainhofer]

I also set up NC access to the admidio user base. To make thinks easier, I created three (read-only) views in the database for (1) Users, (2) UserGroups mapping, (3) Groups.

One issue I encountered was that admidio allows unset login names (i.e. no login user is created), which confuses Nextcloud, so all users without a login name need to be excluded and cannot access NC.

Here are the SQL codes for the views:

1. Users view:

   SELECT
   u.usr_login_name AS user,
   u.usr_password AS hash,
   CONCAT(first_name.usd_value, ' ', last_name.usd_value) AS name,
   first_name.usd_value AS first_name,
   last_name.usd_value AS last_name,
   email.usd_value AS email
   FROM
   adm_users AS u
   LEFT JOIN adm_user_data first_name
       ON
       first_name.usd_usr_id = u.usr_id 
       AND first_name.usd_usf_id = 2
   LEFT JOIN adm_user_data last_name
       ON
       last_name.usd_usr_id = u.usr_id 
       AND last_name.usd_usf_id = 1
   LEFT JOIN adm_user_data email
       ON
       email.usd_usr_id = u.usr_id 
       AND email.usd_usf_id = 12
   WHERE
   u.usr_login_name IS NOT NULL

2. UserGroups View:

   SELECT
   u.usr_login_name as uid,
   r.rol_name as gid
   FROM
   adm_members AS m
   LEFT JOIN adm_users AS u
          ON m.mem_usr_id = u.usr_id
   LEFT JOIN adm_roles AS r
          ON m.mem_rol_id = r.rol_id
   WHERE
   m.mem_begin <= current_date()
   AND m.mem_end >= current_date()
   AND u.usr_login_name IS NOT NULL
   AND r.rol_valid = 1
   AND u.usr_valid = 1

3. Groups View:

   SELECT
   g.rol_name as gid,
   g.rol_name as displayname,
   g.rol_administrator as admin
   FROM
   adm_roles as g
   WHERE 
   g.rol_valid = 1