How can we restrict Users into a QVD file or QVW file to see data related to them?
Hi Guys,
I want to restrict users to see only data related to them.
For Example: I have Exployee Table (With EMP_ID, EMP_NAME,JOINING_DATE etc) and Employee_Details Table (With EMP_ID, PROJECT_DETAILS, SALARY_CREDITED_EACH_MONTH,SALARY_CREDITED_DATE etc)
So I want to restrict uses to see only data related to that employee.
Section Application; /* End section access script */
SELECT A.GroupName,C.BUSINESSENTITYID
FROM HumanResources.Department A
INNER JOIN HumanResources.EmployeeDepartmentHistory B ON A.DepartmentID=B.DepartmentID
INNER JOIN HumanResources.Employee C ON B.BusinessEntityID = C.BusinessEntityID
INNER JOIN HumanResources.EmployeePayHistory D ON C.BusinessEntityID = D.BusinessEntityID;
Here Admin can see all BUSINESSENTITYID but USERA can see only BUSINESSENTITYID=1 and USERB can see only BUSINESSENTITYID=2 and USERC can see both BUSINESSENTITYID 1 & 2.
Note: After writing Above Script save It and then Select or Check Option "Initial data reduction based on section access" and "Strict Exclusion" in Settings-->Document Properties-->Opening and then Reload.
After doing above setting Close the ClikView (Not only .QVW File) application and again reopen the particular QVW file and Enter User and Password and will be able to see data only related to that user.
Qlkview is a Business Intelligence reporting tool and this tool is really gaining momentum in US job market. This tool has many cool features that can be leveraged as per your BI needs. On this blog i am going to share QlikView security feature that might come handy when data cannot be compromised.
example :
Let's assume that we have two users; UserA, UserB. UserA is responsible for product A and UserB is responsible for product B. Our reporting requirement is to write a report for both products. However; we also want to secure data in such a way that product A is not visible to UserB and Product B is not visible to UserA.
In order to secure data, we can use a Qlikview feature called section access. Section access helps you protect data from unauthorized access as well as limit authorized users access to important data.
With the help of section access, you can restrict unauthorized access or limit authorized access in different ways. For instance you can either use basic user access approach or basic NT security approach for access restriction. For our example purpose we will use basic user access approach.
Before writing script for section access, let's make sure “Initial Data Reduction Based on Section Access” and "strict exclusion" is checked under Settings > document properties > Openings.
Now let's start writing section access script.....
In order to maintain security, we will create hidden script for section access. (File > Create hidden script. )
Section Access; /* Start section access script */
LOAD * INLINE [
ACCESS, USERID, PASSWORD, PRODUCT
ADMIN, ADMIN, ADMIN
USER, USERA, U1, A
USER, USERB, U2, B
];
Section Application; /* End section access script */
PRODUCT:
LOAD * INLINE [
PRODUCT, PROFIT
A, 1000
B, 2000
];
In the above example we created row level security that means userA will be able to see profit only for his product. Similar UserB will be able to see profit only for his product.
Section access can be used not only for row level security but also for column level security. The advantage of section access as shown in our example is that it eliminated the need for creating two reports for each product. In a large organization where we have thousands of products and their related data cannot be shared among users, in that case, section access can be very useful.
Section Application; /* End section access script */
SELECT A.GroupName,C.BUSINESSENTITYID
FROM HumanResources.Department A
INNER JOIN HumanResources.EmployeeDepartmentHistory B ON A.DepartmentID=B.DepartmentID
INNER JOIN HumanResources.Employee C ON B.BusinessEntityID = C.BusinessEntityID
INNER JOIN HumanResources.EmployeePayHistory D ON C.BusinessEntityID = D.BusinessEntityID;
Here Admin can see all BUSINESSENTITYID but USERA can see only BUSINESSENTITYID=1 and USERB can see only BUSINESSENTITYID=2 and USERC can see both BUSINESSENTITYID 1 & 2.
Note: After writing Above Script save It and then Select or Check Option "Initial data reduction based on section access" and "Strict Exclusion" in Settings-->Document Properties-->Opening and then Reload.
After doing above setting Close the ClikView (Not only .QVW File) application and again reopen the particular QVW file and Enter User and Password and will be able to see data only related to that user.
