OMIT multiple fields

[#1]

Hi,

I am successfully protecting a particular data field (PROTECTEDFIELD1) from unauthorised access by certain users, by using section access in my script:

SECTION Access;

LOAD ACCESS,

     NTNAME,

     OMIT

FROM

[My security spreadsheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

In my spreadsheet I have the following table:

ACCESS	NTNAME	OMIT
ADMIN	MYDOMAIN\GOD_USER
USER	MYDOMAIN\LOWLY_EMPLOYEE	PROTECTEDFIELD1

This works fine. When logged in as LOWLY_EMPLOYEE, I can't see PROTECTEDFIELD1.

My problem is: I can't figure out how to restrict access to more than one field. Is there a particular syntax? I have tried:

• PROTECTEDFIELD1 PROTECTEDFIELD2
• PROTECTEDFIELD1, PROTECTEDFIELD2
• PROTECTEDFIELD1; PROTECTEDFIELD2

Any ideas?

Thanks

[swuehl · Accepted solution]

Just duplicate the line with stating the other field value(s):

ACCESS	NTNAME	OMIT
ADMIN	MYDOMAIN\GOD_USER
USER	MYDOMAIN\LOWLY_EMPLOYEE	PROTECTEDFIELD1
USER	MYDOMAIN\LOWLY_EMPLOYEE	PROTECTEDFIELD2

[Michael_Reese]

You can also link to a second table so your user table can be 1:Many without duplication.

User table...

ACCESS,NTNAME,OMITGROUP

Table with just the OMIT fields...

OMITGROUP, OMIT

See an example here...

https://community.qlik.com/t5/Qlik-Sense-Documents-Videos/Example-Section-Access-w-Data-Reduction-an...

[carlcimino]

@Michael_Reese This is much more easily maintained than the response marked as the solution.  Thank you for providing the link.

[carlcimino]

@Michael_Reese  One thing I am having trouble with is when using the OmitGroup method and I don't have a value in that field (that user should have no fields ommitted) it gives me the access denied upon reload.  When OmitGroup is populated it works.  I've included screen shots and my script below.  Is there anything you see that I am doing incorrectly?  I followed your steps as closely as possible.

Omit Group Populated returns "Access Denied after reload..."

With it populated the runs sucessfully.

Here's my section access script.

ALL_MAINAREA:
Load dual(FieldValue('Main Area', RecNo()), FieldValue('Main Area', RecNo())) as ALL_MAINAREA
AutoGenerate FieldValueCount('Main Area');

DISTINCT_MAINAREA:
Load Distinct
Concat(distinct ALL_MAINAREA, ';') as DISTINCT_MAINAREA
resident ALL_MAINAREA;

drop table ALL_MAINAREA;

ALL_TEAM:
Load dual(FieldValue('Team', RecNo()), FieldValue('Team', RecNo())) as ALL_TEAM
AutoGenerate FieldValueCount('Team');

DISTINCT_TEAM:
Load Distinct
Concat(distinct ALL_TEAM, ';') as DISTINCT_TEAM
resident ALL_TEAM;

drop table ALL_TEAM;

ALL_SUBTEAM:
Load dual(FieldValue('Sub-Team', RecNo()), FieldValue('Sub-Team', RecNo())) as ALL_SUBTEAM
AutoGenerate FieldValueCount('Sub-Team');

DISTINCT_SUBTEAM:
Load Distinct
Concat(distinct ALL_SUBTEAM, ';') as DISTINCT_SUBTEAM
resident ALL_SUBTEAM;

drop table ALL_SUBTEAM;

SubField_Security:
Load *
where exists(DATA_KEY)
;
Load
ACCESS
, NTNAME as USERID
, OMITGROUP
, if("Main Area" = 'ALL',
trim(upper(subfield((peek('DISTINCT_MAINAREA', 0, 'DISTINCT_MAINAREA')),';'))),
trim(upper(subfield("Main Area",';'))))

&'-'&

if("Team" = 'ALL',
trim(upper(subfield((peek('DISTINCT_TEAM', 0, 'DISTINCT_TEAM')),';'))),
trim(upper(subfield("Team",';'))))

&'-'&

if("Sub-Team" = 'ALL',
trim(upper(subfield((peek('DISTINCT_SUBTEAM', 0, 'DISTINCT_SUBTEAM')),';'))),
trim(upper(subfield("Sub-Team",';')))) as DATA_KEY
FROM [lib://Global_Finance_BIFileshare/IPA_Proforma_Reporting/Includes/IPA_Pro_Forma_Reporting_Section_Access.xlsx]
(ooxml, embedded labels, table is Sheet1);

drop Tables DISTINCT_MAINAREA, DISTINCT_TEAM, DISTINCT_SUBTEAM;

Section Access;

AUTHORIZATION:

NoConcatenate

Load Distinct
ACCESS,
USERID,
DATA_KEY,
OMITGROUP
resident SubField_Security;

OMIT_FIELDS:
LOAD
OMITGROUPID,
OMITGROUP,
OMIT
FROM [lib://Global_Finance_BIFileshare/IPA_Proforma_Reporting/Includes/IPA_Pro_Forma_Reporting_Section_Access.xlsx]
(ooxml, embedded labels, table is [Omit Group]);

Section Application;

OMIT_SHOWHIDE:
load Distinct
OMITGROUP as OMITGROUP_APP,
OMITGROUPID
Resident OMIT_FIELDS;

[Michael_Reese]

Hi @carlcimino .  Did you get it worked out?  If not, could you show the resulting tables?  I'll try to reproduce it.

You can turn off section access while still loading the security tables.  Make sure you are not reducing records out for those users.

ALL sees everything.  All offices.