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)

SQL Server

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.

MySql / MariaDb

-- MySQL DDL Script
-- Converted from SQL Server by ChatGPT
-- Ebiexperts 2024-03-08 JPG (MySQL Version)

-- Drop table if exists
DROP TABLE IF EXISTS SAM_ExtRules;

-- Create table
CREATE TABLE SAM_ExtRules (
  RuleId INT AUTO_INCREMENT PRIMARY KEY,
  AppName VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  ActorName VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  FullName VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  SaACCESS VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'USER',
  SaOMIT VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  SaRESTRICT TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP,
  ModifDate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create index (MySQL does not have NONCLUSTERED keyword; all indexes are secondary except PK)
CREATE INDEX SAM_ExtRules_AppActorName_IDX ON SAM_ExtRules (AppName ASC, ActorName ASC);
CREATE INDEX SAM_ExtRules_AppName_IDX ON SAM_ExtRules (AppName ASC);
CREATE INDEX SAM_ExtRules_ActorName_IDX ON SAM_ExtRules (ActorName ASC);

-- No need for trigger in MySQL if ON UPDATE is used for ModifDate
-- But if you want explicit trigger logic, here's the equivalent:

-- Optional: If not using ON UPDATE on column
-- DELIMITER $$
-- CREATE TRIGGER trgAfterUpdate
-- BEFORE UPDATE ON SAM_ExtRules
-- FOR EACH ROW
-- BEGIN
--   SET NEW.ModifDate = CURRENT_TIMESTAMP;
-- END$$
-- DELIMITER ;

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.