Hello,
How do I code within the report to find/pull the logged in user group.
if I login with System Admin vs. Development Admin vs. Ticketing I see different modules in Tessitura, because different security access is granted. Where does this live in the tables? How can I code it.
What I'm looking to do is - If user logged in with group X then show these rows XYZ, else show rows ABC.
I find the user name, but linking user name to T_METUSER is only sort of kind of helpful since each user can have multiple user groups.
I tried tracing, but with little success
Thank you,
-Lisa
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.
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
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.
Fullscreen CustomSampleTableCreateScript.sql Download 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
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