• 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.

  • $organization

    As far as I understand it, using FS_USER() is what you are supposed to do.  That is what I have used in my procedures.

  • $organization

    Hi Evan - John is right about FS_USER() for pre-v15.1 versions. In v15.1, there is a new scalar function called FS_USERNAME() that should be used. Here is the statement from the v15.1 release notes:

    "A new scalar function, FS_USERNAME(), has been added as a replacement to FS_USER() everywhere, including custom tables, views, and procedures.  This allows the update of the function to strip the domain name for windows logins before returning the first 8 characters of the user name for audit columns.  FS_USER() is officially deprecated and it should no longer be used."

  • $organization in reply to David Frederick

    Thanks for that David!  We are actually talking about moving to 15.1 soon, so nice to put that on my radar before we start!

  • $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...?

  • $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.