Filtering data access based on user_id of current session

Status
Not open for further replies.
Yeah, things have been a bit crazy since he got back from Holland, with 2.0b being released.

I'll talk to him today.

-- hugh
 
well he forgot but I'm here now!

i've tried reading through all the posts, and I think a db schema would come in handy as a reference here. (or a sql dump so I can replicate).

From what I've read I think you are saying you have created a view in mySQL which you are then trying to filter on. Whilst Fabrik can sort of use views, their implementation hasn't been too well tested so this may in fact cause you additional problems. I can't remember the specifics but i seem to remember it being something to do with editing data that was contained within the view.

Imagining that you had no mysql view, I'm fairly sure that if you created the table joins in fabrik to mirror what the view does, then all the table and joined tables elements would be available in the prefilter section, allowing you to create the required access levels for any given user.
 
Hmmm. I just have a feeling that by reproducing what the view does in Fabrik will break his UI model. I may be wrong - I usually am.

Regardless ... I've run across several instances where people have needed a little more by way of a filter query than can be achieved with the GUI method ... similar to the reasons we provided the DIY option for join elements.

I'm hoping that providing a DIY option for filter queries might be "relatively simple" .. ?

-- hugh
 
What I need - summarized

Sorry, I've been preoccupied with other issues (my paid work :) so am just getting back to this now.

Rob - the problem is not with controlling access levels anymore, even though that was the original subject of the thread. I can control access perfectly using my view - so I don't think the view is the problem either :) I created a Fabrik table based on my view, then joined it to another Fabrik table which is the one I actually want to edit. The second Fabrik table is based on a simple MySQL table. The problem is that I can't edit it in Fabrik, and I suspect the reason is that it is "joined" to a non-editable view. Maybe my hunch is wrong, but there it is ... based on years of Oracle experience :). I do know that I can edit my table if it's not joined to another one - I've already confirmed that in my testing.

So, if I could have a DIY filter option for a Fabrik table, then I wouldn't have to worry about a join at all. I could just create a Fabrik table for my Person MySQL table and use my view in the filter. I would need to be able to enter a complete SELECT statement including the FROM and WHERE clauses. I think Hugh is right - this is the solution I need.

So my question is - can this be made available any time soon ?
 
After a talk with Rob, he'd like to at least try doing this by creating the table join from P to M (set the resulting joined group to hidden), which should then allow you to filter on M.login_id.

However, I have a feeling we may have gone round in circles ... but could you give this a try anyway? I have a feeling that something to do with it being a View causes us problems with this ... but I'll admit I'm getting lost, LOL!

-- hugh
 
TEST RESULTS: join does not do what I need

I tried your suggestion - did joins in both directions, in fact. In this post I am referring to my authentication view as AUTH .. full name is a lot longer :) . Everything in this post is based on actual testing.

1. Joining PERSON to AUTH
For a join to do what I want (allow me to grant a team leader access to only the records of the people on his/her team), the query on my AUTH view has to be filtered before joining to the PERSON table. So I can't make my view the "right side" of the join (i.e. start from PERSON and then join to my AUTH view), because the join condition doesn't allow for a pre-filter on the "right side" table. I have tested this out. I can do the join, and I will be allowed to add records to the People table or edit current records (I've tried it), but the join to the unfiltered AUTH view has the net effect of giving me multiple copies of each record in the PERSON table, depending on how many times this particular PER_ID is referenced in the AUTH view.

If I try to create a Fabrik table based on the AUTH view, and apply the filter ( basically it is LOGON_ID = $my->id ) to this Authentication Fabrik table, then use the underlying view as the basis for the right side of a JOIN to the PERSON table, it still doesn't limit the records the way I want. There doesn't seem to be any way of filtering the right side of a join in Fabrik.

2. Joining AUTH to PERSON
If I start with my AUTH view, and build a Fabrik table on this, I can filter it on [ LOGON_ID = $my->id ] and do a join to the PERSON table (i.e. put AUTH on the left, side, PERSON on the right side of the join), and I see only the PERSON records that the current user is authorized to see. I already knew I could do this; I?d done it before. But because the AUTH view is the left side of the join, I am not able to edit the PEOPLE records or add new ones.

3. The core issue here is that the JOIN is not a true MASTER/DETAIL relationship - i.e. does not seem to allow independent transaction processing for the DETAIL record - in this case the PEOPLE record. So, since the LEFT SIDE of the join is a view, the RIGHT side cannot have records added to it, nor can existing records be updated. The PEOPLE record cannot have records added to it independently of the AUTH view. Since the view does not allow any edits, when I try to add records to the PEOPLE table nothing happens.

But I want to point out that the core problem here is NOT that I am using a view. The core problem is a limitation in what the join allows. To test this theory I did a straight table-to-table join. My goal was to see if I could add a record to ONLY the right side table, without doing anything at all to the LEFT SIDE table (i.e. simply allowing it to supply a key value for the right side table). Fabrik did not allow me to do this; I had to add a record to the left side table as well. If I don't add one, Fabrik/MySQL adds a record to the left side table automatically. If the join column of the left side table has AUTOINCREMENT turned on, a row is added to the table on the left side of the join using the AUTOINCREMENT value for the primary key. If AUTOINCREMENT is not specified, it seems to add a row to the left side table with ID = 0, if this value does not already exist. This is a bit unexpected ... and coming from Oracle (where joins are very tightly controlled) strikes me as a bit flakey - but my point is that the join mechanism does not allow rows to be added to the right side table independently of the left side (parent) table.

This is a serious limitation for anyone who has worked in an environment where true MASTER/DETAIL relationships are possible. I don't want to ask for the moon, stars, and sky here - but since the join mechanism is not flexible enough to accommodate what I need to do (and maybe it doesn?t need to be more flexible for most of your users), it would be nice if a more flexible filtering option (DIY filter as suggested by Hugh) were available for tables in Fabrik. This would allow me to control access to data based on who is logged on, using my view, and to allow editing or creation of new records depending on the access level of the person ... if I can get my PHP script submission issues sorted out - other thread :) But I think the existence of a DIY filter option would add value for other users as well - at least, those who understand relational database concepts.

So - over to you. Are you guys willing to add the DIY filter option ? If not - I don't know whether I can use Fabrik to do what I was hoping to do. Maybe there's another way, but using a JOIN doesn't seem to be what I need, unless you can provide MASTER-DETAIL joins, with new detail records automatically being populated with the value of the join_Id from the current record of the master table. But this is probably a much more complicated development effort than just adding the DIY filter option.

I know you're doing this virtually for free (even with subscription) so I can hardly complain - but if you can let me know realistically whether you will be able to do this, I can make a decision as to whether I can keep working with Fabrik. I hope the answer is yes because I've invested a lot of time and effort already, and I would like to get past the current frustrations to some real productivity. I do appreciate the time you've already taken on this.
 
yes, that's the database concept behind master-detail relationships (e.g. in Oracle Forms which is one tool I have worked with for years). So if it's one-to-many, it ought to be possible to create a new record on the "Many" side of the join, using the same "master" id ... i.e. not needing to create another "master" record to create a "detail" record.

Does this make sense ?

But it is probably simpler just to allow more flexible query filtering ... that's all I need, I think (though the other thing would be really nice, would probably take longer to do).
 
OK, would allowing you to do a subquery work?

Look at the existing pre-filter GUI.

If I added an option to the 'eval' radio buttons called 'subquery', and added "IN" to the conditions list. Then whatever you put in the eval box gets treated as the subquery.

So you prefilter might look like:

Join: WHERE
Field: id
Condition: IN
Value: SELECT id FROM foo LEFT JOIN bar ON (foo.something = bar.something) WHERE bar.whatever = {$my->id}
Eval: subquery

So basically if you specify subquery, I'd run whatever you have in the value box through our standard routine to do variable replacement ... so the {$my->id} would get replaced. Then I'd just wrap it in ()'s and treat it as a subquery to the WHERE clause.

I'm not sure if this will do what you need, but I think it would. And it would be relatively easy to code up.

What would be much harder would be to replace the entire thing with a DIY, i.e. so you don't even use the 'Field' or 'Condition' parts, because of the way these prefilters are constructed and stored internally. Basically they have to be split into 'field', 'condition' and 'value'. So its easy enough to add a subquery into the 'value', but not so easy to bypass having it split into those three chunks.

-- hugh
 
Hugh,
It would work if I could do something like this :

SELECT * FROM PERSON WHERE PER_ID IN (SELECT MBR_ID FROM GRP_MBR_UPD_AUTH_LKP_V WHERE LOGON_ID = $my->id).

I've tested this query and it works fine when I supply a literal value in place of the "$my->id".

In other words, your "IN" option would be fine, with a subquery option on the eval box, if the subquery could take the form "SELECT MBR_ID FROM GRP_MBR_UPD_AUTH_LKP_V WHERE LOGON_ID = $my->id". But I would have to be able to reference $my->id from within the subquery. Can that be done ?
 
Wisdom Hunter said:
Hugh,
In other words, your "IN" option would be fine, with a subquery option on the eval box, if the subquery could take the form "SELECT MBR_ID FROM GRP_MBR_UPD_AUTH_LKP_V WHERE LOGON_ID = $my->id". But I would have to be able to reference $my->id from within the subquery. Can that be done ?
Click to expand...

Yes. As I noted in my previous post:

So basically if you specify subquery, I'd run whatever you have in the value box through our standard routine to do variable replacement ... so the {$my->id} would get replaced. Then I'd just wrap it in ()'s and treat it as a subquery to the WHERE clause.
Click to expand...

OK, well that's done. I went ahead and coded it up last night, then tested and committed it to SVN this morning.

Let me know if it works out for you.

-- hugh
 
OK, I installed Tortoise SVN and have downloaded the latest from the 1.0.x branch to my development area. I am now ready to overwrite the files on my Fabrik installation with the new ones. Before going ahead - should I upload everything ? Probably not necessary, right ? I was working with 1.0.5.1 already ... If only one or two files are affected by your changes it might be easier just to upload those ones - but if you say it's better to just overwrite everything as per the instructions on the site, I'll do that.

Thanks for this - much appreciated !
 
I always recommend uploading everything the first time you do an SVN upgrade.

Make sure you do the 'export' step and upload the 'clean' exported copy, otherwise you'll be uploading a whole lot of SVN metadata files (and I mean a LOT).


-- hugh
 
Hugh,
Done - uploaded with no problem. I see the "subquery" option for the "Value" part of the prefilter, but I don't see "IN" as an option in the dropdown list of options for the "Condition" part of the prefilter.
 
Hmmm, it should be there. Can you check in fabrik_table.class.php (front end component dir), around line 4357 should be a function called getFilterConditionDd(). Towards the end of that func should be the line:

PHP: 
                 $aConditions[] = mosHTML::makeOption( 'in', 'IN' );

If not, grab a fresh copy from SVN, and make sure you re-upload ALL files, front and backend.

-- hugh
 
Hugh,
I didn't take a fresh copy but I did upload all the files again (the ones I had taken from SVN yesterday). The FTP software that I was using created a folder inside a folder if I copied a folder over an existing one... so some of the files had not been refreshed properly. I corrected this, and found the missing "in" condition in getFilterConditionDd() as you described. I can now enter the subquery and choose "IN" from the list of conditions. But alas - the filter is not actually being applied. Maybe I need to start fresh with a new table ? I'll try that next.
 
Hugh,
My mistake - I had not made the filter applicable to "Everyone", and I had ommitted the braces {} around $my->id. When I fixed these two problems, it worked perfectly. Many thanks for your help on this one. I hope it is an improvement that will add value for others as well ...
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 74 (members: 3, guests: 71)
    Back
    Top