Section Access - logical OR on different fields

[brunodec]

Hi,

I wondered if there is a way to have section access with a logical OR on two different fields. For instance, people out of different countries enter their time in a time writer application, and they book their time on projects. Each project is also linked to a country. Countrymanagers should be able to see all the time postings of their people (let's say where country = US) and also all time entries against projects from US. So they should also see the time spent from French people on US projects, and also from US people on French projects.

I tried as follows but it gives no data when user 'USMANAGER' logs on. When ADMINISTRATOR logs on, he gets everything (as expected).

Any ideas ?

Thanks !

Section Access;
Load * inline
 [ACCESS, NTNAME, SEC_CODE
 ADMIN, ADMINISTRATOR, SEC_ALL
 USER, USMANAGER, SEC_US
];
Section Application;
star is *;
load * inline
 [SEC_CODE , Employee_Country, Project_Country
 SEC_ALL, *, *
 SEC_US, US, *
 SEC_US, *, US
 ];

[prieper]

Think that the script should do. Have you tried to change the fieldnames Employee_Country resp. Project_Country in capitals?

HTH
Peter

[brunodec]

Hi,

I changed the fieldnames to capitals, but this gives the same result.
In the manual I found a close saying

So I guess this is the reason why it doesn't work ?

Any other suggestions ...?

regards,

     Bruno.

The star symbol is not allowed in information files. Also, it cannot be used in

key fields, i.e. fields used to join tables.

[pat_agen]

hi Bruno,

i was intrigued when reading your first post because you said that the Administrator could see everything and it was only the US manager who failed to see what s/he should.

Was this the case? In theory the Administrator was being linked to both Employee_Country and project_country with the value "*" which would normally fail as a link.

Did you have the "Initial data reduction based on section access" and the "strict exclusion" boxes ticked?

[deepakk]

HI Bruno,

You might hve not selected the initial data reduction in your application.

Please find a sample file and the screen shot of the initial data reduction feature.

[brunodec]

Hi Pat,

both "Initial data reduction.." as "Strict exclusion" were selected. I tried without "strict exclusion" but this didn't change anything.

Yes as Administrator I can see everything. I will post a sample file based on Deepak's reply so you can see what happens.

First I thought that the data reduction happened row by row: for user USMANAGER first step is to reduce all data where Employee_Country is not US; second step (on the result of step 1) is to reduce the data where Project_Country is not US.  But this should still give me the data for US employees working on US projects, and even these data are missing...  caused by the star link??

[brunodec]

Hi Deepak,

regarding the Initial Data reduction... see also my reply to Pat.

I would have loved to insert a sample file based on your file, but I can't find how to attach it here...

Anyway, it's just this little script so anyone can just copy-paste it.

If you open it as administrator, you see all hours (totalling 20 hours). As USmanager, you see no data, where I try to find a solution to see 5+6+2=13 hrs

Oh  finally I found the attach button at the bottom

regards,

     Bruno.

Section Access;
Load * inline
 [ACCESS, USERID,PASSWORD, SEC_CODE
 ADMIN, ADMINISTRATOR, ,SEC_ALL
 USER, USMANAGER,,SEC_US
];
Section Application;
//star is *;
load * inline
 [SEC_CODE , EMPLOYEE_COUNTRY, PROJECT_COUNTRY
 SEC_ALL, , 
 SEC_US,US, 
 SEC_US, , US
 ];
/// SEC_US, , US
//Section Application;
LOAD * INLINE [
    Employee, EMPLOYEE_COUNTRY, ProjectID, PROJECT_COUNTRY, HOURS
    John, US, PRJ_01, US, 5
    John, US, PRJ_02, NL, 6
    Kees, NL, PRJ_01, US, 2
    Kees, NL, PRJ_02, NL, 7
];

[pat_agen]

hi Bruno,

i've kicked this around a bit but always come up with the results you posted. That is the administartor sees everything whereas usmanager sees nothing.

Is it the star "*" for linking tables? I don't know because the adminstrator has this. What is certain is that your security table in section application has two fields in common with your fact (project) table. Qv creates a synthetic key to handle this, not in itself a bad thing but shows that the link between your security and the project is a combination of employee_country and project_country. QV accepts this when both parts are "star" but obviously is unhappy when one half is star and the other isn't.

One piece of information I found whilst looking at this is that since qv8.5 r3 if more than one reduction field is used in section access, if any user has more than one record and those choices are mutually exclusive the user will not get into the document.

in theory you have avoided this by only having the one reduction field in section access but maybe the fact qv introduced this restriction is a pointer to why your solution isn't working.

In our environment we have similar security considerations - why I'm interested in how this will pan out, there are some pretty qv savvy people out there who may be reading this and supply the correct answer.

We have dealt with this in two ways. Firstly is to have two documents each with different section access. so you would have a project by country doc and a project by employee doc. The reasoning being a) security is simplified and b) the document defines the analysis being made. For example what you call a country manager is in one instance being a geographical manager (overseeing all projects in the US) but in the other instance is being a people manager (overseeing all projects performed by US employees). The second way is to explicitly generate all the possible combinations of your fields.

In your case it could be solved by having a single field in section access (called say COUNTRY). then link country to a table containing the really existing combinations from your project table. The code to do that isn't terrifying and the performance implications would probably be negligeable.

something like this (added after loading of your project data):

securityLinkTable:

load

     project_country as COUNTRY,

      project_country & '|'  & employee_country as securityKeyProjectTable

resident projects;

load

     employee_country as COUNTRY,

      project_country & '|'  & employee_country as securityKeyProjectTable

resident projects;

ps of course you will need to add the securityKeyProjectTable to your project table.

keep me posted if you come up with another solution.

[brunodec]

Thanks for this reply. It holds very usefull information, and both options are worth consideration. 

The option to explicitely generate all the possible combinations also crossed my mind, but in fact I wanted to do this on 4 fields, and the example with 2 fields was only to show the basic problem. Nevertheless I will see if I can work it out in one way or the other you suggested.

Thanks again,

Bruno.

[swuehl]

Hi Bruno,

that's an interesting problem, but I also think your first approach will not work.

What makes me wonder, I see no data at all within your sample application, even as ADMINISTRATOR.

Thats what I also would expect, since I don't think the NULL or star nomenclature will work as you want.

If you want to play around a bit further, it might be useful to disable the data reduction in document property, thus you can see the effect for the users by simply selecting the SEC_CODE (without the need to log in / out).

If you then create table box with all fields, it looks like your approach doesn't filter the project table as wanted, instead added four lines to the table (i.e. appended the security table to the project table leaving the unset fields empty. If you then filter the SEC_CODE, you will always got no data for the hours).

So if you want to work further on your approach, I think you have to handle the synthetic key, which obviously works not as you want.

Regards,

Stefan