How do I determine the user group of the account running Tessitura

Row-based security filtering should be done using Control Groups if possible.

The way it works is, users in T_METUSER are joined to user groups in T_METUSERGROUP via a join table TX_USER_GROUP. User Groups are also assigned edit or view security rights to control groups. This is all managed via the Tessitura Security app.

When a user logs in to Tessitura, their database connection and API calls are given a context variable containing their current user group. (This is a lesser-known feature of SQL Server, I would say.) That context variable can be retrieved with dbo.FS_GET_PARAM_FROM_APPNAME('ug').

All of the tables supporting control group filtering have a "security view" denoted by the prefix VS_ or VRS_ which filters the underlying table by joining the assigned control group with the result of dbo.FS_GET_PARAM_FROM_APPNAME('ug') for the connection -- this happens via dbo.FT_CONTROL_GROUPS().

Inspecting the SQL for all of these functions and views can be instructive! You should definitely follow the control group pattern if you are trying to do row-based security in Tessitura. Accessing or even setting the context variable with EXEC dbo.AP_SET_CONTEXT 'UG', 'groupname'; can be useful for custom procedures that need to operate within a user context for interoperability with native procedures.

I would go so far as to say that, for any report you write, if there is a secure VIEW of that table available, use it.  The circumstances for which you would intentionally NOT use an available secure VIEW are few and far between (though not non-existent, I have had a couple of them).  But it is certainly safest to use the secure VIEWs.  After all, if you wanted those users to have access to that information... they would probably already have access to that information.

Anyway, just up-voting Nick Reilingh here.

Nick Reilingh and John A. Moskal II sorry for being dense, where can i learn some more about secure views and how are they different then regular views?

As I wrote above my goal is to create a singular report (as it was assigned) that pulls data but only shows appropriate rows based on user security. If a user is logged in as Development they could see certain rows in output if they are Ticketing other rows, etc. This data all lives in one/localized table part of a process being implemented.

My idea was to say based on the parameters selected and the usergroup of the person signed in show him the appropriate results. also as i mentioned above, this query gave me back nothing, for my user group. What am I doing wrong. 

Nick Reilingh and John A. Moskal II sorry for being dense, where can i learn some more about secure views and how are they different then regular views?

As I wrote above my goal is to create a singular report (as it was assigned) that pulls data but only shows appropriate rows based on user security. If a user is logged in as Development they could see certain rows in output if they are Ticketing other rows, etc. This data all lives in one/localized table part of a process being implemented.

My idea was to say based on the parameters selected and the usergroup of the person signed in show him the appropriate results. also as i mentioned above, this query gave me back nothing, for my user group. What am I doing wrong. 

Lisa Rudnitsky,

No problem!  A "Secure View" is Tessitura terminology for a View on a table with the addition a call to get the active user's control group access.  For example, an obvious example for consortia is the season table, TR_SEASON.  One organization has access to the season of group A performances and another organization has access to the season of group B performances.  The "secure view" to TR_SEASON is VRS_SEASON.  You will notice that a view is a "secure view" in Tessitura terminology because it has the "S" after the "V".   So, for example V_CUSTOMER_WITH_HOUSEHOLD is NOT a "secure view".  Because customers are designed to be shared by all.

So, in essence, all you do then, is in your SQL code for your report, instead of joining to TR_SEASON, join to VRS_SEASON instead.  That way, if the user group of the person who is running the report only has access to group A performances, that is all they will see and vice versa for the person who only has access to group B performances.  Someone with access to BOTH groups A and B would see all of the performances.

If you have a local table, you might not have a secure view ready made, but it is easy to do and/or accomplish the equivalent in your procedure's SQL code.  If you glance at the construction of VRS_SEASON, it is simply a view that selects the TR_SEASON table joined on the FT_CONTROL_GROUPS function, which is a standard function that grabs all the control groups to which any given user has.

Of course... your local table would have to have control groups on it.  That would be the equivalent to your X and Y above there.

Let me know if that does not make sense.

John

You can check them out in the database.  Tessitura secure views will have an "S" in the prefix, like "VS_CAMPAIGN".  If the table you are looking at is joining to any Tessitura tables, you might be able to leverage a secure view instead of the table.  It sounds like this is a custom table and that the logic for who should see it is also custom (i.e. not based on control groups).

My next question is are you locally hosted or not?  RAMP-issued SQL accounts are typically linked back to the same Windows Auth account as a Tessitura User account, allowing that function to find out who you are, but if you are locally hosted you may not have your SQL account set up that way, in which case it won't show anything when you run it from there.

Thank you for the quick reply - John A. Moskal II

Yes, it absolutely makes sense, but requires (as you mention) control groups on my table.
I will evaluate if this is a feasible option for this project, but If those don't exist or can't be added - could I still reference a user group currently logged into tessitura? 

Hello, Gawain Lavers the control group logic makes sense and we are locally hosted and not using Ramp. 
Yes, it is a custom table with custom logic and currently no control groups. I'm not sure if they can/will be added to this project. 

The function you mention - do you mean this: fs_get_param_from_appname(''ug) - would it still return a value if its run/called from a report built through Tessitura? I could test it that way but I had hoped to fully test the logic before putting it into Tessitura. 

Yes, when run though a Tessitura report that function will find the user account and user group for the user running the report, but when called from SQL Server Management Studio it won't be able to make a connection.

Alternatively, to adding control groups to THAT table, if there is another table from which some of that data comes that is itself control grouped, you can use the control grouping on that table to filter.  E.g. the VS_PERF view simply joins to the VRS_SEASON view and accomplishes the control grouping in that manner as the T_PERF table also does not have control group as a column anywhere but MUST be assigned a season.  That said, adding a control group column and associated foreign key to TR_CONTROL_GROUP to your local table should not be too terribly difficult.  Assuming it makes sense for that table/project of course.

And yes, Gawain Lavers covered this as well, but the function FS_GET_PARAM_FROM_APPNAME is just one of those things that does not work in SSMS, just like secure views in SSMS will return everything regardless of what control groups are on the user group you normally use to log into Tessitura.

As long as you correctly reference the views and that function, all should be well.