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,
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:

1. 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