• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 123 views
  • Users 0 members are here
Related

T_METUSER - userid vs. db_userid

Evan Cartwright
$organization

We have some custom functionality that ties directly to a user's Tessitura login/security profile by calling FS_USER and then matching it against the db_userid column in T_METUSER. However, since Windows Authentication rolled out, we've realized that in T_METUSER the db_userid column has switched over to a value of NULL for anyone using authentication to log in, instead of the familiar 8-character login.

At a glance, it looks like we could work around the problem by simply matching FS_USER against the userid column instead of db_userid - for anyone not using authentication, the two columns appear to be identical. However, before we plow forward, I was hoping to consult the braintrust to see if there are implications or reasons why this might be a terrible idea! Thanks as always.

Parents
  • $organization

    Thanks John and David, and great tip on FS_USERNAME()! The join to T_METUSER is used here so that we can associate the output from FS_USER (soon to be upgraded) to a user's email address which is stored in the security module. Sounds like there aren't any immediate red flags between matching on userid rather than db_userid...?

Reply
  • $organization

    Thanks John and David, and great tip on FS_USERNAME()! The join to T_METUSER is used here so that we can associate the output from FS_USER (soon to be upgraded) to a user's email address which is stored in the security module. Sounds like there aren't any immediate red flags between matching on userid rather than db_userid...?

Children
  • $organization in reply to Evan Cartwright

    Hi Evan - joining what FS_USERNAME() returns to T_METUSER.userid might work; however, it largely depends on whether the first 8 characters of the Windows domain username always matches the first 8 characters of the Tessitura userid. There is no technical requirement that they match, so there is a chance it won't work 100% of the time.

    A better match might be to take FS_USERNAME(), which returns the first 8 characters of the Windows username in situations where Windows authentication is used, and matching that to the first 8 characters of T_METUSER.active_directory_username. But that could result in a duplicate as there is nothing that enforces the first 8 characters of the Windows login to be unique (that I'm aware of), which is a potential flaw of Tessitura's approach of truncating that username for auditing purposes. Even better, use the logic in FS_USERNAME() to pull the whole AD username and get a match.

    Here is the FS_USERNAME() logic, minus the part that truncates it down to 8 characters: 

    Coalesce(dbo.fs_get_param_from_appname(''uid''), substring(suser_name(), charindex(''\'', suser_name()) + 1, 256))
    My thinking is that you would match dbo.fs_get_param_from_appname("uid") to T_METUSER.userid in cases where it is not null (which I believe would be for users who do not authenticate via Windows) and substring(suser_name(), charindex(''\'', suser_name()) + 1, 256) to T_METUSER.active_directory_username when dbo.fs_get_param_from_appname("uid") is null. 
    I haven't tested that and there may already be some existing way to consistently match a current user to their record in T_METUSER, but it seems like it could potentially work based on what I currently understand. I hope that helps.