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

Hi Lisa, 

The scalar function [dbo].FS_GET_PARAM_FROM_APPNAME('ug') will return the user group ID, which comes from UG_id in T_METUSERGROUP. 

If you're trying to respect control groups, you should instead use a "secure view" of the table you're querying. These are typically prefixed as "VS_" or "VRS_". They will properly interpret the user group and control groups when executed within the context of the application.

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.

Hello Michael, 

Can you tell me what I'm doing wrong/

In reference to a secure view, what do you mean? What I'm trying to accomplish is a singular report (as these are my requirements). That displays slightly formatted data that's puled from a singular table. However within that table, I've got data marked X that only development user groups can see, or data marked Y that only Ticketing could see. Admin could see both X and Y. 

My hope was to create a bit of logic to say - what is the user group/security access of the user running this report if its DEV show him rows X if its Ticketing show him rows Y 

Thank you, 
-Lisa

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.

Looks like John, Nick and Gawain have answered your questions here but I can mention a few more things.

As Gawain mentioned, using [dbo].FS_GET_PARAM_FROM_APPNAME('ug') inside SSMS directly will produce a null result because you don't have a Tessitura user group established when directly connected to the database. But when executed from a report inside Tessitura it will return the user group ID.

If you decide to introduce control groups into this project (which we would recommend instead of hard-coding user group IDs in your report), you can get an example from the CustomSampleTableCreateScript.sql file in the sample library that ships with Tessitura. I've also attached it to this post. On line 55, a custom system table called LTR_SAMPLE_VOLUNTEER_DEPARTMENT is created and it includes a control group column. On line 116, a custom secure view called LVRS_SAMPLE_VOLUNTEER_DEPARTMENT is created that references the custom table and joins it to a function that interprets the control group security of the currently logged in user. With this in place, any time LVRS_SAMPLE_VOLUNTEER_DEPARTMENT is referenced from a report inside the application, it will respect the control group configuration.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Creating tables for sample volunteer custom screen'

If NOT exists (select * from sys.objects where object_id = object_id(N'[dbo].LT_SAMPLE_VOLUNTEER') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
  Begin
	CREATE TABLE dbo.LT_SAMPLE_VOLUNTEER(
		customer_no int NOT NULL,
		id_key int NOT NULL,
		volunteer_date datetime NOT NULL,
		department_id int NULL,
		activity_id int NULL,
		supervisor_customer_no int NULL,
		hours_worked money NULL,
		gift_credit char(1) NULL,
		reschedule char(1) NULL,
		create_dt datetime NOT NULL CONSTRAINT DF_LT_SAMPLE_VOLUNTEER_create_dt DEFAULT (GetDate()),
		created_by varchar(8) NOT NULL CONSTRAINT DF_LT_SAMPLE_VOLUNTEER_created_by DEFAULT ([dbo].fs_user()),
		last_update_dt datetime NOT NULL  CONSTRAINT DF_LT_SAMPLE_VOLUNTEER_last_update_dt DEFAULT (GetDate()),
		last_updated_by varchar(8) NOT NULL CONSTRAINT DF_LT_SAMPLE_VOLUNTEER_last_updated_by DEFAULT ([dbo].fs_user()),
		create_loc varchar(16) NOT NULL CONSTRAINT DF_LT_SAMPLE_VOLUNTEER_create_loc DEFAULT ([dbo].FS_LOCATION()),
	CONSTRAINT PK_LT_SAMPLE_VOLUNTEER PRIMARY KEY CLUSTERED 
		(
			[id_key] ASC
		)) ON [PRIMARY]
  End
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LT_SAMPLE_VOLUNTEER_T_CUSTOMER_customer_no]') AND parent_object_id = OBJECT_ID(N'[dbo].[LTR_SAMPLE_VOLUNTEER_ACTIVITY]'))
	ALTER TABLE dbo.LT_SAMPLE_VOLUNTEER ADD CONSTRAINT FK_LT_SAMPLE_VOLUNTEER_T_CUSTOMER_customer_no FOREIGN KEY(supervisor_customer_no)
	REFERENCES dbo.T_CUSTOMER (customer_no)
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].LT_SAMPLE_VOLUNTEER TO [ImpUsers]
GO

If Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].TG_SAMPLE_VOLUNTEER_UPDATE') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
	Drop Trigger [dbo].TG_SAMPLE_VOLUNTEER_UPDATE
GO

CREATE TRIGGER [dbo].TG_SAMPLE_VOLUNTEER_UPDATE ON [dbo].LT_SAMPLE_VOLUNTEER FOR UPDATE 
AS
	SET NOCOUNT ON
	UPDATE 	a
	SET 	last_updated_by = [dbo].FS_USER(), last_update_dt = getdate()
	FROM	[dbo].LT_SAMPLE_VOLUNTEER a JOIN inserted b ON a.id_key = b.id_key
GO

If NOT exists (select * from sys.objects where object_id = object_id(N'[dbo].LTR_SAMPLE_VOLUNTEER_DEPARTMENT') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
  Begin
	CREATE TABLE dbo.LTR_SAMPLE_VOLUNTEER_DEPARTMENT(
		id int NOT NULL,
		description varchar(30) NOT NULL,
		inactive char(1) NOT NULL CONSTRAINT DF_LTR_SAMPLE_VOLUNTEER_DEPARTMENT_inactive DEFAULT 'N',
		control_group int NOT NULL CONSTRAINT DF_LTR_SAMPLE_VOLUNTEER_DEPARTMENT_control_group DEFAULT -1,
	CONSTRAINT PK_LTR_SAMPLE_VOLUNTEER_DEPARTMENT PRIMARY KEY CLUSTERED
		(
			id ASC
		)) ON [PRIMARY]
	
	Insert into LTR_SAMPLE_VOLUNTEER_DEPARTMENT Values (1, 'Marketing', 'N', -1)
	Insert into LTR_SAMPLE_VOLUNTEER_DEPARTMENT Values (2, 'Development', 'N', -1)
	Insert into LTR_SAMPLE_VOLUNTEER_DEPARTMENT Values (3, 'Front of House', 'N', -1)
	Insert into LTR_SAMPLE_VOLUNTEER_DEPARTMENT Values (4, 'Inactive Department', 'Y', -1)
  End
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].LTR_SAMPLE_VOLUNTEER_DEPARTMENT TO [ImpUsers]
GO


If NOT exists (select * from sys.objects where object_id = object_id(N'[dbo].LTR_SAMPLE_VOLUNTEER_ACTIVITY') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
  Begin
	CREATE TABLE dbo.LTR_SAMPLE_VOLUNTEER_ACTIVITY(
		id int NOT NULL,
		description varchar(30) NOT NULL,
		department_id int NOT NULL,
		inactive char(1) NOT NULL CONSTRAINT DF_LTR_SAMPLE_VOLUNTEER_ACTIVITY_inactive DEFAULT 'N',
	  CONSTRAINT PK_LTR_SAMPLE_VOLUNTEER_ACTIVITY PRIMARY KEY CLUSTERED
		(
			id ASC
		)) ON [PRIMARY]

	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (1, 'Mailings', 1, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (2, 'Data Entry', 1, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (3, 'Hall Tour Guide', 1, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (4, 'Peer Solicitor', 2, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (5, 'Event Decorations', 2, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (6, 'Mailings', 2, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (7, 'Data Entry', 2, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (8, 'Hall Usher', 3, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (9, 'Balcony Usher', 3, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (10, 'Programs', 3, 'N')
	Insert into LTR_SAMPLE_VOLUNTEER_ACTIVITY Values (11, 'Inactive Activity', 4, 'Y')	
  End
GO

delete from ltr_sample_volunteer_activity where ID = 11
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LTR_SAMPLE_VOLUNTEER_ACTIVITY_LTR_SAMPLE_VOLUNTEER_DEPARTMENT_department_id]') AND parent_object_id = OBJECT_ID(N'[dbo].[LTR_SAMPLE_VOLUNTEER_ACTIVITY]'))
	ALTER TABLE dbo.LTR_SAMPLE_VOLUNTEER_ACTIVITY ADD CONSTRAINT FK_LTR_SAMPLE_VOLUNTEER_ACTIVITY_LTR_SAMPLE_VOLUNTEER_DEPARTMENT_department_id FOREIGN KEY([department_id])
	REFERENCES dbo.LTR_SAMPLE_VOLUNTEER_DEPARTMENT ([id])
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].LTR_SAMPLE_VOLUNTEER_ACTIVITY TO [ImpUsers]
GO


IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[LVRS_SAMPLE_VOLUNTEER_DEPARTMENT]') and OBJECTPROPERTY(id, N'IsView') = 1)
	Drop View [dbo].LVRS_SAMPLE_VOLUNTEER_DEPARTMENT
GO

CREATE View dbo.LVRS_SAMPLE_VOLUNTEER_DEPARTMENT
AS
SELECT a.*, b.edit_ind
FROM dbo.LTR_SAMPLE_VOLUNTEER_DEPARTMENT a
	JOIN [dbo].[FT_CONTROL_GROUPS](NULL) b ON a.control_group = b.control_group
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].LVRS_SAMPLE_VOLUNTEER_DEPARTMENT TO [ImpUsers]
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LT_SAMPLE_VOLUNTEER_LTR_SAMPLE_VOLUNTEER_DEPARTMENT_department_id]') AND parent_object_id = OBJECT_ID(N'[dbo].[LT_SAMPLE_VOLUNTEER]'))
	ALTER TABLE dbo.LT_SAMPLE_VOLUNTEER ADD CONSTRAINT FK_LT_SAMPLE_VOLUNTEER_T_CUSTOMER_department_id FOREIGN KEY(department_id)
	REFERENCES dbo.LTR_SAMPLE_VOLUNTEER_DEPARTMENT(Id)
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LT_SAMPLE_VOLUNTEER_LTR_SAMPLE_VOLUNTEER_ACTIVITY_activity_id]') AND parent_object_id = OBJECT_ID(N'[dbo].[LT_SAMPLE_VOLUNTEER]'))
	ALTER TABLE dbo.LT_SAMPLE_VOLUNTEER ADD CONSTRAINT FK_LT_SAMPLE_VOLUNTEER_T_CUSTOMER_activity_id FOREIGN KEY(activity_id)
	REFERENCES dbo.LTR_SAMPLE_VOLUNTEER_ACTIVITY(Id)
GO

UP_POPULATE_REFERENCE_METADATA

GO