Qlik Community

QlikView Publisher

Discussion Board for collaboration on QlikView Publisher.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
autopilot
Contributor III

Is there a way to use Section Access - Active Directory without listing all users in script?

How do I script section access to interpret this "if" type of logic WITHOUT hard coding every user (access, ntname, etc.)?

scenario illustration:

Goal:

- To allow all Sales Reps and Managers access to the dashboard 'Sales.qvw' (containing all sales information) on the Access Point.

- Use Active Directory and Section Access to manage document security and data reduction

Conditions:

- Joe(SalesRep) should see only his Sales data - not the Sales data for any other Sales Reps. This applies to every Sales Rep user.

- Bob(Region1 Sales Manager) should see only Sales data for Sales Rep in Region 1 - not the Sales data for any Sales Reps assigned to different Regions (Regions in this example is simply an aggregate group of Sales Reps)

Ideal resolution would satisfy both conditions without needing to use a separate qvw document.

*****************************

The idea is to achieve a one-to-many document access/distribution deployment via multi-level security. From a simplistic perspective, the system/application would process this logic:

1.-->Who are you?              NTName

2.-->Can you access it?      Y/N

3.-->If you have access, perform data reduction based on who you are.*********This is the key point of interest for this discussion***********

4.-->Present and display dashboard.

I can't find a solution the no. 3.

How do I script section access to interpret this "if" type of logic WITHOUT hard coding every user (access, ntname, etc.)?

Besides being difficult to manage fundamentally, it wouldn't be feasible to basically replicate our entire Active Directory (even just the portions that have immediate application) in order to achieve the desired outcome. Is this possible? Or, is QlikView simple not meant to  be applied in this manner?

I'd greatly appreciate anyone's feedback. This has been quite a challenging barrier to resolve.

1 Solution

Accepted Solutions
autopilot
Contributor III

Re: Is there a way to use Section Access - Active Directory without listing all users in script?

Miguel - Thank you for your help. I really appreciate your feedback.

We had worked on this issue for some time when I finally decided to post a discussion...having not found any comparable situations discussed on the community forum in my search.

In hopes of helping someone else in a similar situation, I'm posting below the sample script we used that finally worked as a viable solution to meet our requirements.

NTGROUPS:
LOAD
  GROUPDN,
  USERDN,
  USERID,
  USERNAME,
  USERDN1,
  EMAIL,
  GROUPNAME
FROM
ADUSERGROUPS.QVD
(qvd);
////////////////////////////////////
////////////////////////////////////
////////////////////////////////////
SECTION ACCESS;
LOAD
      'ADMIN' AS ACCESS,
      '*' AS USERID,
      '*' AS PASSWORD,
     'INTRA\' & UPPER(USERID) as NTNAME,
     '*' AS NTDOMAINSID,
     upper(EMAIL) as SLSREPEMAIL,
     '*' as BRANCHEMAIL,
    upper(EMAIL) as BRANCH_EMAIL,
     '*' as SLSREP_EMAIL
RESIDENT
  NTGROUPS
WHERE
  GROUPNAME = 'Sales';
CONCATENATE
LOAD * INLINE
[
   ACCESS, USERID, PASSWORD, NTNAME, NTDOMAINSID, SLSREPEMAIL, BRANCHEMAIL
   ADMIN,    ADMIN,  ADMIN,         *,           *,                    *,                   *
];
SECTION APPLICATION;
////////////////////////////////////
////////////////////////////////////
////////////////////////////////////
DROP TABLE
  NTGROUPS;
////////////////////////////////////
SAMPLEDATA_SLSREP:
LOAD * INLINE
[
   FIELD1,  FIELD2,  FIELD3,  FIELD4,  FIELD5,   SLSREPEMAIL,                BRANCHEMAIL
   ADMIN,  ADMIN,  ADMIN,   *,          *,           *,                                  *
   ADMIN,  *,          *,           DEMO,    *,          EMAIL@EXAMPLE.COM,    *
];
////////////////////////////////////
SAMPLEDATA_BRANCHMGR:
LOAD * INLINE
[
   FIELDA,  FIELDB,  FIELDC,  FIELDD,  FIELDE,  SLSREP_EMAIL,              BRANCH_EMAIL
   ADMIN,  ADMIN,   ADMIN,   *,           *,          *,                                 *
   ADMIN,  *,           *,           DEMO,    *,          EMAIL@EXAMPLE.COM,   *
];

5 Replies

Is there a way to use Section Access - Active Directory without listing all users in script?

Hi,

Section access allows you to use NTNAME. You need to hardcode somewhere (database, excel sheet, etc) to link each DOMAIN\USERNAME combination with his corresponding reduction values. So use only fields ACCESS, NTNAME and REDUCTION in section access, and if the user is authorized, the reduction will take place at the same time.

Am I missing something?

Miguel

autopilot
Contributor III

Is there a way to use Section Access - Active Directory without listing all users in script?

Thank you for the reply!

I have a QVW that retrieves the entire Active Directory and stores it into a QVD which is triggered to reload every day.

Is it possible to use that (QVD) in combination with your proposed solution in order to avoid the hardcoding of every DOMAIN\USERNAME??

That would allow me to use a simple 'include' in the document which would correlate the security and reduction via associations drawn from the AD........is that a logical approach assuming there exists a possibility to the above question?

Is there a way to use Section Access - Active Directory without listing all users in script?

Hi,

Sure, you can create a section access table from any valid source, and a QVD file is a good one. But my question is how do you know what customers is DOMAIN\USERNAME able to see? I mean, how do you create the relation between the reduction field, whatever it is, and the proper NTNAME? Is that the NTNAME data is included in the data model?

Regards.

Miguel

autopilot
Contributor III

Is there a way to use Section Access - Active Directory without listing all users in script?

We have the field EMAIL in both the active directory qvd and document that can be used to create a relation.

autopilot
Contributor III

Re: Is there a way to use Section Access - Active Directory without listing all users in script?

Miguel - Thank you for your help. I really appreciate your feedback.

We had worked on this issue for some time when I finally decided to post a discussion...having not found any comparable situations discussed on the community forum in my search.

In hopes of helping someone else in a similar situation, I'm posting below the sample script we used that finally worked as a viable solution to meet our requirements.

NTGROUPS:
LOAD
  GROUPDN,
  USERDN,
  USERID,
  USERNAME,
  USERDN1,
  EMAIL,
  GROUPNAME
FROM
ADUSERGROUPS.QVD
(qvd);
////////////////////////////////////
////////////////////////////////////
////////////////////////////////////
SECTION ACCESS;
LOAD
      'ADMIN' AS ACCESS,
      '*' AS USERID,
      '*' AS PASSWORD,
     'INTRA\' & UPPER(USERID) as NTNAME,
     '*' AS NTDOMAINSID,
     upper(EMAIL) as SLSREPEMAIL,
     '*' as BRANCHEMAIL,
    upper(EMAIL) as BRANCH_EMAIL,
     '*' as SLSREP_EMAIL
RESIDENT
  NTGROUPS
WHERE
  GROUPNAME = 'Sales';
CONCATENATE
LOAD * INLINE
[
   ACCESS, USERID, PASSWORD, NTNAME, NTDOMAINSID, SLSREPEMAIL, BRANCHEMAIL
   ADMIN,    ADMIN,  ADMIN,         *,           *,                    *,                   *
];
SECTION APPLICATION;
////////////////////////////////////
////////////////////////////////////
////////////////////////////////////
DROP TABLE
  NTGROUPS;
////////////////////////////////////
SAMPLEDATA_SLSREP:
LOAD * INLINE
[
   FIELD1,  FIELD2,  FIELD3,  FIELD4,  FIELD5,   SLSREPEMAIL,                BRANCHEMAIL
   ADMIN,  ADMIN,  ADMIN,   *,          *,           *,                                  *
   ADMIN,  *,          *,           DEMO,    *,          EMAIL@EXAMPLE.COM,    *
];
////////////////////////////////////
SAMPLEDATA_BRANCHMGR:
LOAD * INLINE
[
   FIELDA,  FIELDB,  FIELDC,  FIELDD,  FIELDE,  SLSREP_EMAIL,              BRANCH_EMAIL
   ADMIN,  ADMIN,   ADMIN,   *,           *,          *,                                 *
   ADMIN,  *,           *,           DEMO,    *,          EMAIL@EXAMPLE.COM,   *
];

Community Browser