topic Re: Security restrictions based on Data Base in QlikView

Security restrictions based on Data Base

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Security restrictions based on Data Base

Peter_Cammaert — Wed, 19 Oct 2016 21:28:52 GMT

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

Re: Security restrictions based on Data Base

MK9885 — Wed, 19 Oct 2016 22:45:19 GMT

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.

Re: Security restrictions based on Data Base

MK9885 — Thu, 20 Oct 2016 10:46:24 GMT

Anyone?

stalwar1‌loveisfail‌

Re: Security restrictions based on Data Base

sunny_talwar — Thu, 20 Oct 2016 10:48:02 GMT

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

Re: Security restrictions based on Data Base

MK9885 — Thu, 20 Oct 2016 10:51:17 GMT

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.

Re: Security restrictions based on Data Base

Anil_Babu_Samineni — Thu, 20 Oct 2016 10:59:21 GMT

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

Re: Security restrictions based on Data Base

MK9885 — Thu, 20 Oct 2016 11:11:46 GMT

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

Re: Security restrictions based on Data Base

Peter_Cammaert — Thu, 20 Oct 2016 11:45:18 GMT

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.

Re: Security restrictions based on Data Base

Anil_Babu_Samineni — Thu, 20 Oct 2016 11:49:50 GMT

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

Re: Security restrictions based on Data Base

MK9885 — Thu, 20 Oct 2016 11:58:38 GMT

Thanks Peter for clearing.

But when I follow what you've said I'm getting an Error942: Table or view not found in Oracle.

I have access to that schema and can view the scheme and browse it as well.

And yes, I want to restrict user based upon CODEID which are assigned to users

EX:

USER CODEID

1 1

2 2

3 1

4 1

......

So if a user with CODEID 1 logins, he should only view his data. The same CODEID field is in data model. But I'm loading this CODEID from a qvd and not added into Data Model.

Only running this QVD in source qvw & also section access in source file.

I did understand what you told in very first reply and concatenated as well but I was getting this Error message. So I thought it's not working.

Re: Security restrictions based on Data Base

MK9885 — Thu, 20 Oct 2016 11:59:08 GMT

I tired but I get Error 924 Oracle table not found.

Re: Security restrictions based on Data Base

Peter_Cammaert — Thu, 20 Oct 2016 13:46:45 GMT

Your error points to an Oracle table load malfunctioning, not to Section Access.

Try by removing or commenting out the Section Access; statement. You will still get the Oracle error, but there will be no mistake as to who is causing problems. The quickest way to get a correct SQL SELECT for an Oracle table in that particular database is to use the Select... button in Script Editor, and let the Wizard assemble the correct SELECT statement for you. Enable the Preceding Load option if you don't feel like renaming your columns in SQL. You can now rename the fields in the preceding load which is a regular QlikView load. Add the CONCATENATE prefix to the preceding load instead of the SQL Select and you should be on your way.

If this eliminates the error, and produces the expected data, then you can uncomment the Section Access; statement.

Best,

Peter