topic Regarding dynamic security(urgent) in QlikView

Regarding dynamic security(urgent)

Wed, 12 Aug 2009 14:53:27 GMT

Hi All,

I need to implement the security to my QV file according to one User table in the database, where the userid, roles, and access information will be stored.

So i need to connect that user table to my section access dynamically.

Dynamically i mean like if there is any updation in the data table ,that should be automatically reflected in the QV file. Suppose i have given usera for North region access, but tomorrow i want to give him North as well south region access. These information will be loaded to the User table in the database according to the requirement. So my point is here i should able to connect to the user table from QV section access and the data should be automatically refreshed in QV inline with the updation of the user table.

1- How can i connect to the database table from section access. IF its through script please provide me some example as am very new to the technology.

2- How the data will be automatically refreshed in QV from the user table that i have refred for the section access.

Please provide me the step to implement the same if the same is possible.

Any help will be appreciated.

Thanks,

Mahaweta

Regarding dynamic security(urgent)

Wed, 12 Aug 2009 17:47:59 GMT

Hi All,

Please provide me some solution.

I need it urgently.

Thanks,

Mahasweta

Regarding dynamic security(urgent)

amars — Wed, 12 Aug 2009 19:38:13 GMT

Hi Mahashweta,

You can load it normally like a table in your qlikview script then

UserTBL:

SQL Select EmpID, pwd from UserAccess;

Section Access;

Load 'USER' as ACCESS, EmpID as USERID, pwd as PASSWORD Resident UserTBL;

When you refer the table all the record's in that table will be considered & since it is previously loaded from your database Table, with every reload it will get updated.

Regarding dynamic security(urgent)

Wed, 12 Aug 2009 20:53:02 GMT

Hi Amar,

Thanks a lot.

I have created one table named USERINFO.

and as you said i have connected to that table. My script is given below.

LOAD ACCESSINFO,
UNAME,
PASSWD;
SQL SELECT *
FROM ADEXAUSER.USERINFO;

Section Access;

Load 'USER' as ACCESS,
UNAME as USERID,
PASSWD as PASSWORD
Resident USERINFO;

But even then when am trying to open the file its asking me the userid ,but after entering the userid it still asking to enter the userid.

(Note: in my userinfo table the i have three columns as

ACCESSINFO	UNAME	PASSWD
admin	admin	admin
user	user	user

Something am missing here. Could you please correct me?

Thanks,

Mahasweta

Regarding dynamic security(urgent)

Wed, 12 Aug 2009 21:03:46 GMT

Hi Mahaweta,

I think you are trying to use a concept called "View Time Filtering". What this does it filter the data based on the ID you are logged in as. You need to populate the Section Access table from the database, during the load. I think you have most of that. You seem to be missing one part. The relationship between your data table(s) and the section access table. You have to have a field (all caps I think), that is common to both tables in name and content. QV will associate these tables based on this field, and filter the data based on the user id.

Hope that helps.

-Dimitry

Regarding dynamic security(urgent)

Wed, 12 Aug 2009 21:16:13 GMT

Hi Dim,

Thanks a lot for the quick reply.

Exactly i need something like that. Lets say if in my QV file am showing the sales data regionwise then i want to implement the security so that admin can view all the regions data and user can view the data which he has access.

Secondly the access information i want to collect from the database where i have some table ragarding the userid and password ,roles information. So this table need to be connected and automatically refreshed to the section aceess, so that whenever any role / user changes the security need to be implemented accordingly.

Here i have created one table as USERINFo.

and am tryning to access that under section access, but am missing some relations as you said.

Could you please tell me step by step how to resolve my issue?

Please help me out as i need it urgently.

Thanks,

Mahasweta

Regarding dynamic security(urgent)

amars — Wed, 12 Aug 2009 22:00:31 GMT

Attaching a example. See if it help's

Regards

Amar

Regarding dynamic security(urgent)

amars — Wed, 12 Aug 2009 22:01:57 GMT

Sorry , Forgot to attach the source file.

Regarding dynamic security(urgent)

Thu, 13 Aug 2009 11:35:08 GMT

Hi Amar,

Really thanks a ton.

Now am able to connect to data base table.

But one thing am missing here like i have one column scen in the customer table and its one foreign key also.

The same scen column is made to be PK in the user table.

I have pasted my script as below.

Section

Access;

Load

'USER' as ACCESS,

UNAME

as USERID,

PASSWD

as PASSWORD ,

'SCEN'Resident

USERINFO;

SECTION

APPLICATION;

SQL

SELECT CID,

CNAME,

ADRS,

SALES,

SCEN

FROM

system."CUSTOMER_SCENARIO";

CONNECT

TO [Provider=MSDAORA.1;User ID=system;Data Source=adexa1] (XPassword is KaKSeQRMFDeCWQB);

Something surely i am missing here, as when i am login in to qv file with the user name the data selection should hapen according to the user. But am getting all the data irrespective of the user login.

Please suggest me where i am missing.

Thanks,

Mahasweta

Regarding dynamic security(urgent)

Thu, 13 Aug 2009 11:35:09 GMT

Hi Amar,

Really thanks a ton.

Now am able to connect to data base table.

But one thing am missing here like i have one column scen in the customer table and its one foreign key also.

The same scen column is made to be PK in the user table.

I have pasted my script as below.

Section

Access;

Load

'USER' as ACCESS,

UNAME

as USERID,

PASSWD

as PASSWORD ,

'SCEN'Resident

USERINFO;

SECTION

APPLICATION;

SQL

SELECT CID,

CNAME,

ADRS,

SALES,

SCEN

FROM

system."CUSTOMER_SCENARIO";

CONNECT

TO [Provider=MSDAORA.1;User ID=system;Data Source=adexa1] (XPassword is KaKSeQRMFDeCWQB);

Please suggest me where i am missing.

Thanks,

Mahasweta

Regarding dynamic security(urgent)

Thu, 13 Aug 2009 11:35:12 GMT

Hi Amar,

Really thanks a ton.

Now am able to connect to data base table.

But one thing am missing here like i have one column scen in the customer table and its one foreign key also.

The same scen column is made to be PK in the user table.

I have pasted my script as below.

Section

Access;

Load

'USER' as ACCESS,

UNAME

as USERID,

PASSWD

as PASSWORD ,

'SCEN'Resident

USERINFO;

SECTION

APPLICATION;

SQL

SELECT CID,

CNAME,

ADRS,

SALES,

SCEN

FROM

system."CUSTOMER_SCENARIO";

CONNECT

TO [Provider=MSDAORA.1;User ID=system;Data Source=adexa1] (XPassword is KaKSeQRMFDeCWQB);

Please suggest me where i am missing.

Thanks,

Mahasweta

Regarding dynamic security(urgent)

Thu, 13 Aug 2009 11:35:18 GMT

Hi Amar,

Really thanks a ton.

Now am able to connect to data base table.

But one thing am missing here like i have one column scen in the customer table and its one foreign key also.

The same scen column is made to be PK in the user table.

I have pasted my script as below.

Section

Access;

Load

'USER' as ACCESS,

UNAME

as USERID,

PASSWD

as PASSWORD ,

'SCEN'Resident

USERINFO;

SECTION

APPLICATION;

SQL

SELECT CID,

CNAME,

ADRS,

SALES,

SCEN

FROM

system."CUSTOMER_SCENARIO";

CONNECT

TO [Provider=MSDAORA.1;User ID=system;Data Source=adexa1] (XPassword is KaKSeQRMFDeCWQB);

Please suggest me where i am missing.

Thanks,

Mahasweta

Regarding dynamic security(urgent)

Thu, 13 Aug 2009 11:35:22 GMT

Hi Amar,

Really thanks a ton.

Now am able to connect to data base table.

But one thing am missing here like i have one column scen in the customer table and its one foreign key also.

The same scen column is made to be PK in the user table.

I have pasted my script as below.

Section

Access;

Load

'USER' as ACCESS,

UNAME

as USERID,

PASSWD

as PASSWORD ,

'SCEN'Resident

USERINFO;

SECTION

APPLICATION;

SQL

SELECT CID,

CNAME,

ADRS,

SALES,

SCEN

FROM

system."CUSTOMER_SCENARIO";

CONNECT

TO [Provider=MSDAORA.1;User ID=system;Data Source=adexa1] (XPassword is KaKSeQRMFDeCWQB);

Please suggest me where i am missing.

Thanks,

Mahasweta

Regarding dynamic security(urgent)

amars — Thu, 13 Aug 2009 11:59:50 GMT

Hi,

Try it this way

CONNECT TO [Provider=MSDAORA.1;User ID=system;Data Source=adexa1] (XPassword is KaKSeQRMFDeCWQB);

USERINFO:

SQL SELECT UNAME, PASSWD, SCEN from USERTBL

SQL SELECT CID, CNAME, ADRS, SALES, SCEN FROM system."CUSTOMER_SCENARIO";

Section Access;

Load

'USER' as ACCESS,

UNAME

as USERID,

PASSWD

as PASSWORD , SCEN

Resident

USERINFO;

SECTION APPLICATION;

Load SCEN from USERINFO;

Regards

Amar

Regarding dynamic security(urgent)

Thu, 13 Aug 2009 14:39:33 GMT

Hi Amar,

Thanks a lot.

I have tried with the same code as you have provided.

But am getting one error here saying canot open file c:\mahasweta\userinfo

Load SCEN from USERINFO;

Something am missing here, please suggest me where am going wrong.

Thanks,

Mahasweta

Regarding dynamic security(urgent)

amars — Thu, 13 Aug 2009 14:47:45 GMT

Use Load SCEN Resident USERINFO;

Regards

Amar;

Regarding dynamic security(urgent)

Thu, 13 Aug 2009 15:06:31 GMT

Hi Amar,

Thanks a lot, yes its working.

Now i want to hide the script and reload option only for the user, i have gone through the document properties like i checked initial data reduction. But its not helping me out.

Because once am reloading all users data is available in QV file, so i dont want the script to be reloaded or to be seen for the user.

Please suggest me how to implement that.

Thanks,

Mahasweta