SAM External SQL Tables

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 application
ActorName : domain\username for the Qlik Sense User
SaACCESS : USER or ADMIN

The folloving columns are optional and will be used for further use

RuleId : row identifier used in the update trigger
SaOMIT : for further use
SaRESTRICT : for further use
CreateDate : datetime of record creation
ModifDate : 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.