Why isn't the cwd_user.id used as the thing that links users to issues?

[arikevin]

Not trying to tell anyone how to develop a database, not trying to be rude here, but inquiring minds need to know.

Jira uses an app_user table that uses usernames as the unique key to associate users to issues instead of using the ID of the user in CWD_User.

This doesn't make sense to me.  I would think this would lead to records easily orphaned and such.  

Why not link issues using the id field of the cwd_user table? 

Thanks

Kevin

[Craig Castle-Mead]

CCM

[arikevin]

NO!!  I won't go back! ;) 

[Craig Castle-Mead]

Can’t help but think it is this way just cause that’s how it was and the effort to change is significant, but the effort increases  every version - and with the size of the marketplace that’s a big change management and comparability headache. Personally hoping that when (it has to be when, right?) it’s changed through the suite that it goes to UUIDs and not just ints to support multi-master DB which would be a nice step towards geographically dispersed nodes in data center. 

CCM

[arikevin]

I'm trying to gain access back to my Jira instance and I'm noticing that a lot of varchars are being used as unique id's.  For example, groups have unique entries in cwd_group, but those ID's are not used when creating permission schemes, the names of the group.

This may be doing bad things because apparently (unknown to me until today) group names that come in from LDAP, if they happen to be the same name as an internal group, both end up with unique ID's in cwd_group, however, when subsequently used for permission schemes it has not idea if it is using the internal group name or the external group name.

So...makes me wonder if not having USE permission is rooted in this non-unique, unique key thing jira is doing.

[arikevin]

Hey Craig,

Yeah, could be.  Yes, UUID's would be awesome...

Thanks for engaging me :)

Kevin

[Oleksii Skachkov]

Add the following:
if you delete the user using UserManagement page, the user will be deleted only from the SWD_USER table, and the APP_USER table will remain unchanged.

Also, while trying to understand the principle.

[Craig Castle-Mead]

The way the structure has been implemented in cwd_* tables and user mapping tables have actually led to significant flexibility for us (as with everything, there can be downsides...but).

• Content (pages/issues/comments/etc) being "owned" (according to the DB) by a the user_key string (the username of the user based on their original username), and not the current cwd_user.id 
  • we have had to rebuild directories, move users from Directory A to Directory B - both of which cause new entries in cwd_user, yet cause the username string is the same, the content gets associated with the correct user still
  • Changing usernames - there's daily username changes (people changing surnames if they get married/divorced/any other reason they want), but in our world, there's often rebranding (first.last@company.com becomes first.last@newcompany.com), recently this has been combined with those users then moving to a different AD/LDAP - so the combination of the user_key based permissions, and Crowd as our central user directory, we exclude the users from their current AD, create a new Crowd local directory with the same usernames in it (first.last@company.com), rename them in Crowd to their new username (first.last@newcompany.com) - this flows through to the applications and updates the app_user table mapping, reassociating all content to the new username, then delete the temporary Crowd local directory and enable the new usernames to come through from the new AD/LDAP source.
• Permissions based on the group name string, even if the group exists in multiple directories - apart from a UX/auditing nightmare if you had permissions set to "Source 1 :: GroupName" and "Source 2 ::  GroupName", we've got users coming from multiple backend directories and they need the same access. The permissions being based on the group string mean we can create a group with the name "Foobar" in directory 1, and members 1,2,3,4 - same group name in directory 2 with members 5,6,7,8 and the applications just see a group called "Foobar" with members 1,2,3,4,5,6,7,8 - something that you need to know, but can work for you in many circumstances. 
  • The SIGNIFICANT risk here is if you have a directory that non-admins can manage groups/members in, and they know the global permissions setup, they can create a group "jira-administrators", put themselves in that group, and gain elevated access. In our world, we (the platform admins) are the only ones who can create groups, so we limit this concern.
  • That being said, we are crossing our fingers that group rename support (in some way shape or form) is implemented as we have growing technical debt based on legacy group names, but these groups are used in so many locations (permissions schemes, roles, workflow conditions, user list marcros, add-ons etc) and renaming a group from "Foo" to "Bar" in AD/LDAP etc to an application looks like "Foo has been deleted" "Bar is new" "They happen to have the same membership" but the references to the group "Foo" are not touched.

CCM

[Aman]

Hello, I am trying looking at an a issue against which a user logged time but then left the org. I still the user's full name, email etc.. but the cwd_user table does not have any info about this user.

I was able to pull the worklog for this user, the same user_key is available in the app_user table but not the cwd_user table. How can I get full name other details of this user from the data base?

[Craig Castle-Mead]

The users name (first/last/display) is only stored in the cwd_user table, and if the entry from cwd_user 

This is the query to get the user if they exist - replace JIRAUSER12345 with the user key you have - if there's no first/last/etc returned, the user record has been purged.

SELECT a.user_key, a.lower_user_name, u.lower_email_address, u.lower_user_name,first_name,last_name,display_name FROM app_user AS a LEFT JOIN cwd_user AS u ON u.lower_user_name= a.lower_user_name WHERE a,user_key = 'JIRAUSER12345';

The best you might get is a hint at their first/last name based if your username was an email address/or similar object that uses a users name

CCM

[Aman]

Thank you Craig for your response and the query. I replaced the user key in the query and

got the following result. The record is not available in the cwd table.

Strangely, the UI of Jira is showing the full name of this user, any thoughts?