Security restrictions based on Data Base

[MK9885]

Hi,

I'd like to add section access through Data base.

Section Access;

USERADMIN:

LOAD

     (Field1),

     Field2,

     Field3,

     Field4,

    UPPER( Field5) as CODE,

     Field6   

FROM

...\USERADMIN_ROLE.QVD (qvd);

SQL SELECT

   "Field1",

    Field2,

    "Field3",

    "Field4",

  UPPER(  "Field5") as CODE,

    Field6,

    "Field7",

    "Field8"

FROM

  .........";

Section Application;

I want to restrict user based upon CODE and Field5 in SQL DB.

So if a user has a CODE=500, he should only see data related to 500 and all other fields should not appear to him.

I'm loading the USERADMIN table from a qvd and using it in Section Access and I'm using SQL querry as well.

I also want to load a Inline table as I want to add a single user as an Admin, which I cannot do in Data Base. All other users are available in Data base with a particular Code to them.

I tried loading in many different ways and changing the querry in qlik but still doesn't work.

So with a qvd, Inline & SQL, how can I apply section access?

Please tell me what wrong I'm doing here?

stalwar1‌swuehl‌loveisfail‌

Thanks.

[Peter_Cammaert]

Start with creating a RESIDENT table called USERADMIN with a single record loaded from an INLINE spec. That should be the ADMIN user, or the user that does the reloads on your server.

Then CONCATENATE LOAD all QVD entries you need to that first table.

Then CONCATENATE LOAD all DB entries you need to that expanded table. Yes, you can add the QlikView CONCATENATE keyword to a SQL SELECT statement. See here: Concatenate ‒ QlikView

If the fields all have the same names, you will end up with a single table with authorizations from the three sources.

Best,

Peter

[MK9885]

The field names coming from DB query is different compared to field names in Qlik.

I changed the required name Field 5 from Qlik to Code

and In DB the Field 5 name is actually Field 10 (example)

So I just want to restrict it based on CODE

I did not understand what you explained...

Can you show an example script?

Thanks.

[MK9885]

Anyone?

stalwar1‌loveisfail‌

[sunny_talwar]

Very limited expertise in Section Access, besides you are getting help from one of the best, I am sure pcammaert‌ will respond back to any of your queries here

[MK9885]

Thanks.

Peter's reply was confusing with no example. And it is hard to understand when I also do not have much experience in section access based on data base.

[Anil_Babu_Samineni]

Hey, Would you describe more. I am confusing here. You've load data from qvd and then SQL. What is the use of Sql here (I mean Any reason to take those). If yes, Provide me that

Meantime, HAve a look this about your QlikView Section Access Examples

- ANIL

[MK9885]

Hi loveisfail,

I have a table loaded from DB specially made for section access

In those fields are

Extract query:

SQL Select

USERNAME

ACCESS

CODE_ID

ROLE_NAME

From......

Store into.... as .qvd

The next step I did was I transformed this qvd into

USERS:

Load

USERNAME

ACCESS

CODEID

ROLENAME;

From... Extract.qvd

Third step I did is I have a data model where all transform qvd's are loaded but I did not add the USERS.qvd to my data model, instead I added into my source file where I'm taking binary load from my data model.

In my source file I'm trying to achieve section access by loading this USER.qvd and also writing a SQL select statement

Ex:

Section Access;

USERS:

Load

USERNAME

ACCESS

UPPER(CODEID) as CODEID

ROLENAME;

From... Extract.qvd

SQL Select

USERNAME

ACCESS

UPPER(CODE_ID) as CODEID

ROLE_NAME

From....

Section Application;

So I have a field name CODEID as stored in a different qvd which is BUSINESS.qvd and I made a same table name with same IDs inside CODEID in SQL.

I'm trying to match that query with SQL query and apply section access. Can you tell me what wrong I'm doing here?

I also used UPPER(num(CODEID) as CODEID thinking as those are numeric values.

For now I do not want to use Inline table, just from DB restrictions.

Thanks

[Peter_Cammaert · Accepted solution]

Now it's my turn to get confused.

My original post was explaining the basic steps to create a simple table, unrelated to Section Access because you first have to get your data right. Only then can you apply section access. If you follow that practice, debugging eventual mistakes will become much easier. Even for me, Section Access never works perfectly the first time.

The code in Section Access uses the exact same techniques and statements as any other code. If you want to create an authorizations table, composed of rows from a QVD and rows from a SQL Database, do as follows (I'll try to use code from your other post):

Section Access;

CONNECT TO . ... ; //Connect to your SQL database. Omit if the connection is open.

USERS: // Name doesn't matter

LOAD USERNAME

     ACCESS

     UPPER(CODEID) as CODEID // This is the actual link field?

     ROLENAME // What is this used for?

FROM Extract.qvd

CONCATENATE(USERS)

SQL SELECT

    USERNAME

    ACCESS

    UPPER(CODE_ID) as CODEID

    ROLE_NAME

FROM SomeDatabaseTable ;

Section Application;

:

Now check the connected table in Section Application, make sure the proper fields and values are available, and reload this script in a copy of your original document, never in the document itself. Simply because you may risk locking yourself out of your document and you'll need to start over. If you do get errors, or your document doesn't work as expected, please post back to this thread.

Another tip: If you didn't understand my explanation the first time, you may be lacking basic scripting skills. I would advise against starting with Section Access before you master regular load script techniques, as Section Access can be one of the most tricky and treacherous parts of QlikView development.

[Anil_Babu_Samineni]

You might require to use Section Access

T1:

Concatenate(T1)

T2:

Then, Coming to this part

I also used UPPER(num(CODEID) as CODEID thinking as those are numeric values.

If you are thinking about Numeric values, Then how numeric values came for UPPER

Just use Evaluate(Num(CODEIS)) as CODEID