SAM External SQL Tables
- Jean-Philippe Golay
- Jaabir Ho
- Joseph Mikhail
- Hemangi Kurapati
SAM Is able to use external SQL tables to get section access data and apply it to Qlik applications.
SAM can use a table or a view defined in the same database than SAM repository
By default the table / view name is “SAM_ExtRules” for SAM external rules but the name can be changed inside the appsettings.json file located in “C:\ProgramData\ebiexperts\SAM Web Server2\wip”
the key is : "SamExternalRulesTableOrView": "SAM_ExtRules",
The table must have the following columns defined
AppName : the name of the Qlik Sense applicationActorName : domain\username for the Qlik Sense UserSaACCESS : USER or ADMIN
The folloving columns are optional and will be used for further use
RuleId: row identifier used in the update triggerSaOMIT : for further use
SaRESTRICT : for further use
CreateDate: datetime of record creationModifDate : datetime of record update (updated by a trigger in the sample)
We provide a sample for SQL Server that you can use and fill with your own data
---------------------------------------------------------
-- SQLSERVER DDL SCRIPT
-- Create Direct table for SAM Access
-- Ebiexperts 2024-03-08 JPG
-- SAM_DEMO_NEW.dbo.SAM_ExtRules01 definition
---------------------------------------------------------
-- Drop table
-- DROP TABLE SAM_DEMO_NEW.dbo.SAM_ExtRules;
CREATE TABLE SAM_ExtRules (
RuleId int IDENTITY(1,1) NOT NULL,
AppName nvarchar(2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
ActorName nvarchar(2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
FullName nvarchar(2000) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NULL,
SaACCESS nvarchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT 'USER' NOT NULL,
SaOMIT nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NULL,
SaRESTRICT text COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NULL,
CreateDate datetime DEFAULT getdate() NULL,
ModifDate datetime DEFAULT getdate() NULL,
CONSTRAINT SAM_ExtRules01_PK PRIMARY KEY (RuleId)
);
CREATE NONCLUSTERED INDEX SAM_ExtRules_AppName_IDX ON SAM_ExtRules ( AppName ASC , ActorName ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY ] ;
CREATE TRIGGER trgAfterUpdate ON SAM_ExtRules
AFTER UPDATE
AS
UPDATE SAM_ExtRules
SET ModifDate = GETDATE()
FROM Inserted i
WHERE dbo.SAM_ExtRules.RuleId = i.RuleId
NOTE:
Rules will not work correctly- when there are two external app with same names as we don't get the AppID from external sources it's too difficult to do for that so we work on distinct/unique names of the apps.
UI Changes for the External Apps
We have provided Allow Only External Checkbox on the SAM options page of global configuration
When Allow Only External = Checked
user will be able to scan only external apps.
Also user wont be able to change QMC to table for any app
and when Allow Only External = Unchecked
user will be able to scan both external apps and non external apps
User will be able to change QMC to table for external app but not for manual apps
NOTE: When Allow Only External = ON, any non external apps(i..e apps not present in external_rules table) needs to be switched to table manually.