Data restriction and security in Qlikview

[#1]

Hi

We are facing big issue related to data restriction and security in Qlikview application which I would like to share with you, please assist us urgently, below are the details

Issue1:

In the sample data file you can see CLAUDIO user has access to multiple regions and companies, but for HO Company he is Not authorized to look at cost value and sales quantity, but he can look at stock level

In the OMIT column we have given the column names which we wants to omit for a company, but system is restricting him for all the companies

Issue2:

Why user AYAZ not able to log in?

Regards

Naseer Ahmed

IT Manager

SAPG

[#2]

Hello,

First the issue 2: move STAR IS ALL just after SECTION ACCESS.

Issue one: you cannot use omit like that. Omit deletes the whole field/column so in your case CLAUDIO will not see those two fields at all (and AYAZ too)

I think that you also misunderstand * (ALL) properly. * replaces all values that appear under a given column in section access. As an example:

tmp:
userid,company
JOHN,aa1
DAVID,aa2
KAROL,*

KAROL will be able to see data related to company aa1 and aa2 but if there was also data related to aa3, KAROL would not see them!

SEE ATTACHED SOLUTION FOR YOUR ISSUES

Good luck:)

[#3]

I have a problem with attachment so I am going to paste the solution 🙂

First your USER ACCESS table should look like that:

USERID	PASSWORD	USER_ACCESS	REGION	COMPANY	KEY
CLAUDIO	CLYDE	USER	UAE	RETAIL	UAE-RETAIL-STANDARD_COST
CLAUDIO	CLYDE	USER	UAE	RETAIL	UAE-RETAIL-SHOPSALES_QUANTITY
CLAUDIO	CLYDE	USER	UAE	RETAIL	UAE-RETAIL-VALUE
CLAUDIO	CLYDE	USER	UAE	HO	UAE-HO-VALUE
CLAUDIO	CLYDE	USER	KSA	RETAIL	KSA-RETAIL-STANDARD_COST
CLAUDIO	CLYDE	USER	KSA	RETAIL	KSA-RETAIL-SHOPSALES_QUANTITY
CLAUDIO	CLYDE	USER	KSA	RETAIL	KSA-RETAIL-VALUE
CLAUDIO	CLYDE	USER	KWT	DISTRIBUTION	KWT-DISTRIBUTION-STANDARD_COST
CLAUDIO	CLYDE	USER	KWT	DISTRIBUTION	KWT-DISTRIBUTION-SHOPSALES_QUANTITY
CLAUDIO	CLYDE	USER	KWT	DISTRIBUTION	KWT-DISTRIBUTION-VALUE
CLAUDIO	CLYDE	USER	OMAN	DISTRIBUTION	OMAN-DISTRIBUTION-STANDARD_COST
CLAUDIO	CLYDE	USER	OMAN	DISTRIBUTION	OMAN-DISTRIBUTION-SHOPSALES_QUANTITY
CLAUDIO	CLYDE	USER	OMAN	DISTRIBUTION	OMAN-DISTRIBUTION-VALUE
CLAUDIO	CLYDE	USER	KWT	RETAIL	KWT-RETAIL-STANDARD_COST
CLAUDIO	CLYDE	USER	KWT	RETAIL	KWT-RETAIL-SHOPSALES_QUANTITY
CLAUDIO	CLYDE	USER	KWT	RETAIL	KWT-RETAIL-VALUE
CLAUDIO	CLYDE	USER	OMAN	RETAIL	OMAN-RETAIL-STANDARD_COST
CLAUDIO	CLYDE	USER	OMAN	RETAIL	OMAN-RETAIL-SHOPSALES_QUANTITY
CLAUDIO	CLYDE	USER	OMAN	RETAIL	OMAN-RETAIL-VALUE
CLAUDIO	CLYDE	USER	QTR	RETAIL	QTR-RETAIL-STANDARD_COST
CLAUDIO	CLYDE	USER	QTR	RETAIL	QTR-RETAIL-SHOPSALES_QUANTITY
CLAUDIO	CLYDE	USER	QTR	RETAIL	QTR-RETAIL-VALUE
AYAZ	AYAZ	USER	ALL	ALL	ALL
AYAZ	AYAZ	USER	UAE	HO	UAE-HO-STANDARD_COST
AYAZ	AYAZ	USER	UAE	HO	UAE-HO-SHOPSALES_QUANTITY

Your QVW script:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Section Access;

STAR IS ALL;

User_access:
LOAD [USERID],
[PASSWORD],
USER_ACCESS,
REGION,
COMPANY,
KEY
FROM

(biff, embedded labels, table is [USER ACCESS$]);

Section Application;

TRANSACTION:
LOAD REGION,
COMPANY,
MONTH,
YEAR,
REGION&'-'&COMPANY&'-VALUE' as KEY,
'VALUE' as MEASURE_DESC,
VALUE as MEASURE
FROM

(biff, embedded labels, table is DATE$);
concatenate (TRANSACTION)
LOAD REGION,
COMPANY,
MONTH,
YEAR,
REGION&'-'&COMPANY&'-STANDARD_COST' as KEY,
'STANDARD_COST' as MEASURE_DESC,
VALUE as MEASURE
FROM

(biff, embedded labels, table is DATE$);
concatenate (TRANSACTION)
LOAD REGION,
COMPANY,
MONTH,
YEAR,
REGION&'-'&COMPANY&'-SHOPSALES_QUANTITY' as KEY,
'SHOPSALES_QUANTITY' as MEASURE_DESC,
VALUE as MEASURE
FROM

(biff, embedded labels, table is DATE$);

[#4]

Thanks for your valuable effort, let me try this and get back to you, but I hope this will solve my purpose

But the thing is that I feel loading time will be increased or tripled as I have millions of records in sales table

[#5]

If you want to save the loading time assign the different as per the aceess level and based on the group u provide the access at dashboard level

[#6]

I have just given you a simple solution which should help you understand Security better. I haven't seen your application/schema so I cannot give you the best solution. Hopefully, now you understand Section Access better and you will find what works best for you

Cheers

Karol