How are usernames on tables encoded/hashed in Confluence 5.2.x or greater

This relates to v5.2.3 of the Download version of Confluence.

I use the SQL Plugin with the SQL macro to query information directly out of our Confluence instance. Somewhere in a recent upgrade (5.0.x to 5.1.x or 5.1.x to 5.2.x) the format of usernames on tables such as CONTENT.LASTMODIFIER or NOTIFICATIONS.USERNAME have changed from a plaintext version to an 32 character hexadecimal hash. Previously these plaintext versions were identical to CWD_USER.USER_NAME.

I want to be able to profile information such as,

  • Contributions per person per period
  • Who is watching/subscribed to different blogs/spaces/pages

I can't seem to do it unless I can reverse this hash, or join it to a table that also has this hash which identifies the plaintext username.

3 answers

1 accepted

It looks like the user_mapping table stores the relation between the key and the username.

This table doesn't seem to exist in my Confluence instance nor is it mentioned in the data model https://confluence.atlassian.com/display/DOC/Confluence+Data+Model

Are you able to elaborate?

Nevermind. I tried to select from the user_mapping table from a SQL macro in Confluence and it wasn't working. I've connected directly to the database and I can see the user_mapping table and the data linking the users.

It might now just be a permission issue.

Thanks!

This helped me track down some users who don't have an email address set but have still subscribed to some notifications. Thanks!

I'd also like to know what that's about, it can't be an efficient way to join tables. Also makes those audit columns less than useful.

I suspect they are hashing the user id somehow because the hexidecimal values seem to be sort of incremental (the first 24 characters of the hashes I've looked at seem to be the same). I'd also just like to point out that since usernames are allowed to be up to 256 alphanumeric characters in length, 32 hexidecimal character hash of such usernames would allow for collisions, so I very much doubt it's a hash of the username unless it's glitched.

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Apr 13, 2018 in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...

374 views 20 10
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you