Register ODK Aggregate users programmatically using SQL insert

Hello to all you awesome people!

I just discovered this table called _registered_users with the following structure

My question is... Is it possible to register ODK Aggregate users programmatically using SQL insert to the _registered_users table?

If so, how can the values for these columns be generated? Especially BASIC_AUTH_PASSWORD, BASIC_AUTH_SALT, and DIGEST_AUTH_PASSWORD?

Also, what are the other things to consider? e.g.: the user permissions, etc.

I'm really hoping you guys can give a clairvoyant on this. Thanks in advance.

i think we can't generate that data

because it's a chain of process we cant step over one of them.

so i would advice you to use the user interface of Aggregate System to add user and grant them Auth.

Best Regards

There currently isn't a great end point for adding users. What others have tried is to use Python scripts with a Selenium web driver.

If you want to do this with SQL, you'll have to look at the Aggregate source. is a good place to start.

I haven't looked at the Aggregate source in a while, but I vaguely remember that DIGEST_AUTH_PASSWORD = MD5(username:salt:username) where salt is the realm string in /WEB-INF/lib/ and I think that's all you should need for user auth.

As far as how to figure this out, start from a fresh install, create a handful of users with known passwords and it should be pretty easy to reverse engineer.


Hi Yaw, that's a great tip! I will definitely start from what you pointed out. Thanks a bunch

Hi @yanokwa, when you say MD5(username:salt:username) does that mean
MD5( "username-string" . ":" . "salt-string" . ":" . "username-string" ) ?
Note: where . (period sign) was used as the concatenator per se (example).

Thanks again

Yes, and the use of colon in that way is what the HTTP basic auth scheme requires. has more.

You should compare what you insert with SQL's MD5 with what you generate with other MD5 algorithms. I vaguely remember that there is a difference in output.

1 Like

Hi @yanokwa, It seems the right digest password formula is MD5( "username-string" . ":" . "realm-string" . ":" . "password-plaintext-string" ). However, how is it possible to find/generate the realm string?

You can find security.server.realm.realmString in the WEB-INF/lib/

Hi @yanokwa , I was able now to synthesize the BASIC_AUTH_PASSWORD, BASIC_AUTH_SALT, and the DIGEST_AUTH_PASSWORD.

I also was able to insert records in _user_granted_authority table. Allowing the user to collect and view data. The programmatically created user works when I tried to use it for logging in the ODK Collect. However, when I try it in the ODK Aggregate, it doesn't work.

Is there something I might have missed?

I'm trying to create a PHP-SDK for the ODK Aggregate because I know there are lots of people who also do PHP on the back-end.

Thanks in advance.

My understanding is that changes to _registered_users and _user_granted_authority are all that is needed.

What specifically is not working?

Hi, Abel,
I'm trying to implementent a programmatically users creation. I read all the posts and I was able now to synthesize the DIGEST_AUTH_PASSWORD but I coud't with BASIC_AUTH_SALT, and the BASIC_AUTH_PASSWORD.

Could you please tell me how you did it with the BASIC_AUTH_SALT, and the BASIC_AUTH_PASSWORD.

@camilo_rodriguez, I sent you a private message regarding your query because I'm not sure if it is good to post the whole formula here.

This programmatic functionality is now a hot topic. A couple of months ago, another user even private-messaged me about the formula. Although... the ODK Aggregate is open-source and it can be reverse-engineered, I do not know if posting it in a public forum will make this topic useful or... harmful

I don't think sharing this is harmful. That is, it's in the source code and it's a fairly standard way to generate passwords.

@yanokwa in that case, below are the formulas for synthesizing the important values.

USERNAME = the desired username
PASSWORD = the desired password
BASIC_AUTH_SALT = any generated 8-char alphanumeric value (e.g. "qwertyui")



The above values are based on a generic programming language. That is...

  1. You will need to query the so called "realm string" from the database of your ODK Aggregate.
  2. The arguments for SHA1-and-MD5 hash functions are concatenated strings using the plus sign (+) as the concatenator.

For you to be able to register a user, you need to do the following:

  1. Insert a record in the table called "_registered_users" to create a user; then
  2. Insert a record in the table called "_user_granted_authority " to be able to grant a privilege to the newly created user

To the one reading this, make sure to study those mentioned tables thoroughly -- I know you will be able to figure it out.


1 Like