topic Where in repository database are labels for Action IDs stored? in Management & Governance

Where in repository database are labels for Action IDs stored?

mbespartochnyy — Fri, 21 Jul 2023 17:59:00 GMT

The question is tied to Actions specified in Security Rules. Qlik Sense Repository Database (QSR) has a table named SystemRules. That table holds all of the security rules that exist in Qlik Sense server.

In SystemRules table, there is a column called Actions. Actions column holds numbers like 2, and 15, and 2080, and several others. I'm trying to find a corresponding table that maps Action IDs to Action names.

Simply looking at security rules in QMC and the corresponding records in QSR, I can tell that Action ID 2 is an ID for Read action, at least on my server. Action ID 15 is an ID for a combination of Create, Read, Update, and Delete actions.

I went through the whole repository database, but I can't find a table that matches Action IDs to Action Names.

Does anyone know if a table like that exists?

Re: Where in repository database are labels for Action IDs stored?

mbespartochnyy — Fri, 21 Jul 2023 20:33:10 GMT

I think I figured it out. At least to an extent. There's 13 possible actions with Create seeming to being the first action and Approve being the last. The IDs stored in Action column seem to be a sum of each action's IDs. Furthermore, it looks like each action ID is multiplied by 2 to get to the ID of next action.

Here's a visual representation of that:

Action ID	Action Name
1	Create
2	Read
4	Update
8	Delete
16	Export
32	Publish
64	Change owner
128	Change role
256	Export data
512	Access offline
1024	Distribute
2048	Duplicate
4096	Approve

If I add up Create, Read, Update, and Delete IDs, I do get 15. There are other IDs like 6271 which is used for Create, Read, Update, Delete, Export, Publish, Change owner, Duplicate, and Approve collection of actions. Adding up IDs for these nine actions, will give a sum of 6271.

The remaining thing that I'm not sure about is why when a rule permits access to all possible actions, the ID that is stored in Actions column in SystemRules table is 4294967295.

4294967295 is a perfect totient, it's 2³²-1, in hex it's FFFFFFFF. I don't quite see what that has to do with it representing all possible values in an array? Does anyone here know the answer?

Mikhail B.

Re: Where in repository database are labels for Action IDs stored?

Levi_Turner — Tue, 25 Jul 2023 19:15:30 GMT

It's not obvious what you're trying to do, but the actions are bitmasked (reference https://community.qlik.com/t5/Design/Bit-operators-How-to-operate-with-multiple-values-stored-in-one/ba-p/1466705 for a primer on the topic).

For Security rules you can use logic like this:

LIB CONNECT TO 'PostgreSQL_QSR'; LOAD *, IF([Actions] bitand 1,'True','False') AS [create_flag], IF([Actions] bitand 2,'True','False') AS [read_flag], IF([Actions] bitand 4,'True','False') AS [update_flag], IF([Actions] bitand 8,'True','False') AS [delete_flag], IF([Actions] bitand 16,'True','False') AS [export_flag], IF([Actions] bitand 32,'True','False') AS [publish_flag], IF([Actions] bitand 64,'True','False') AS [change_owner_flag], IF([Actions] bitand 128,'True','False') AS [change_role_flag], IF([Actions] bitand 256,'True','False') AS [export_data_flag], IF([Actions] bitand 512,'True','False') AS [access_offline_flag], IF([Actions] bitand 1024,'True','False') AS [distribute_flag], IF([Actions] bitand 2048,'True','False') AS [duplicate_flag], IF([Actions] bitand 4096,'True','False') AS [approve_flag], ; LOAD ID, Category, Type, Name, Rule, ResourceFilter, Actions, Comment, Disabled, RuleContext, SeedId, Version, CreatedDate, ModifiedDate, ModifiedByUserName, Subcategory; [SystemRules]: SELECT "ID", "Category", "Type", "Name", "Rule", "ResourceFilter", "Actions", "Comment", "Disabled", "RuleContext", "SeedId", "Version", "CreatedDate", "ModifiedDate", "ModifiedByUserName", "Subcategory" FROM "public"."SystemRules" WHERE "Category" = 'Security';

Re: Where in repository database are labels for Action IDs stored?

mbespartochnyy — Wed, 26 Jul 2023 16:40:43 GMT

@Levi_Turner you read my mind! Thank you for the reference article and the script! That's exactly what I was looking for.

I didn't know about bitwise operators but reading through the article you shared and checking out few YouTube videos (this one is good), it became clear.

There are 13 possible Actions, each represented by a bit in a binary number. Converting a value stored in Actions column of SystemRules table into binary will tell which action is allowed based on which bit is set to 1.

bitand function can then be used to find if a particular bit of the value in Actions column is set to 1, effectively showing whether a particular action has been granted of not.

Thanks again for the reply, script, and reference articles!