Page 1 of 1
Hiding columns from non-owners
Posted: 2022-06-23 20:46
by jmcgov
This is a great post, thanks for sharing on Twitter.
https://twitter.com/bigprof/status/1538174700135845889
https://bigprof.com/blog/appgini/how-to ... ser-group/
I have a follow up question, please
- I have a table that contains names and addresses
- owner can insert, view and edit entries on the table
- guests and other can view the table
- I dont want any non-owner to see the surname of first line of the address in TV or DV
- this post helps me do everything I need, except next q
- is there a way I can (1) identify whether the current user is the owner of a line of the TV, and (2) identify whether the
TIA, JAmes
Re: Hiding columns from non-owners
Posted: 2022-06-24 06:58
by jmcgov
Last point wasn’t finished, basically same for DV ?
Re: Hiding columns from non-owners
Posted: 2022-06-25 00:32
by jmcgov
Any ideas peeps?
Re: Hiding columns from non-owners
Posted: 2022-06-25 19:51
by pbottcher
Hi,
for the TV ( I assume a user can see all records, so his own records, but also records from others ) you can add to the _init hook:
Code: Select all
$tn='YOURTABLENAME';
$pk=getPKFieldName($tn);
$from_old=array_flip($options->QueryFieldsTV);
$from_old['quotation_no']="if ((select memberID from membership_userrecords where tableName='{$tn}' and pkValue={$tn}.{$pk})='".$memberInfo['username']."', {$from_old['quotation_no']}, 'xxxx')";
$options->QueryFieldsTV=array_flip($from_old);
for the DV it seems to me a bit more complicated.
I would verify if the current user is the owner and replace the current value for the field(s) with 'xxxx'
Code: Select all
$tn='YOURTABLENAME';
$pk=getPKFieldName($tn);
$sql="select memberID from membership_userrecords where tableName='{$tn}' and pkValue={$tn}.{$pk}";
$owner= sqlvalue($sql);
if ($owner != $memberInfo['username']) {
// pseudocode
$data=get_joined_record($tn, $selectedID);
$html=str_replace($data['FIELDTOBEREPLACED'],'xxxx',$html); // this may be to simple, you need to make sure you only replace the correct data here
}
Hope that helps
Re: Hiding columns from non-owners
Posted: 2022-06-30 21:21
by jmcgov
That was cool, Pbottcher, thanks
I implemented the Table view as you recommended, but changed the Detail view piece as below
- both were inserted into the init hook
- the second required me copying the tablename_templateDV.html to the hooks folder, and editing it to remove the hidden fields
Code: Select all
$sql="select memberID from membership_userrecords where tableName='{$tn}' and pkValue={$_POST['SelectedID']}";
$owner= sqlvalue($sql);
if ($owner != $memberInfo['username']) {
$options->TemplateDV = 'hooks/listing_templateDV.html';
$options->TemplateDVP = 'hooks/listing_templateDVP.html';
}
Re: Hiding columns from non-owners
Posted: 2022-09-12 13:37
by jmcgov
Hi Pbottcher and all,
I need to do a slight enhancement on this, if you can help please - just in TV
Your previous code is pasted below, which worked well to completely hide the address.
However, I also have a postcode column, which I need to partially hide for security reasons, but must make partially visible so users understand the area in question - so Id like to display BT33 0** instead of BT33 0LG
I only need this for TV, as I have figured out DV from your previous steer - the TV calls for a sexy query, I think, which I cannot nail
Any ideas, please
?
$tn='YOURTABLENAME';
$pk=getPKFieldName($tn);
$from_old=array_flip($options->QueryFieldsTV);
$from_old['quotation_no']="if ((select memberID from membership_userrecords where tableName='{$tn}' and pkValue={$tn}.{$pk})='".$memberInfo['username']."', {$from_old['quotation_no']}, 'xxxx')";
$options->QueryFieldsTV=array_flip($from_old);
for the DV it seems to me a bit more complicated.
Re: Hiding columns from non-owners
Posted: 2022-09-12 20:16
by pbottcher
Hi,
maybe you try something like
Code: Select all
$from_old['postcode']="if ((select memberID from membership_userrecords where tableName='{$tn}' and pkValue={$tn}.{$pk})='".$memberInfo['username']."', {$from_old['postcode']}, concat(SUBSTRING(postcode, 1, CHAR_LENGTH(postcode) - 2),'**'))";
Re: Hiding columns from non-owners
Posted: 2022-09-14 09:30
by jmcgov
That was good, but I neglected to say that postcode is populated from a lookup
so Im getting the postcodeID. If you see the image, I see
- the full postcode for the listing added by the current logged in user (good)
- the redacted version of the postcodeID for other listings (bad)
I could overcome this by making a static (not looked up) version of the postcode, but is there a better SQL query way - my SQL skills are sitting on 60%!!
https://bikespike.co.uk/image/postcode-lookup.PNG
Re: Hiding columns from non-owners
Posted: 2022-09-17 16:47
by pbottcher
Hi,
can you post once your
$options->QueryFieldsTV['postcode']
So it would be easiery to help
Re: Hiding columns from non-owners
Posted: 2022-09-19 09:39
by jmcgov
Is this what you mean?
if ((select memberID from membership_userrecords where tableName='listing' and pkValue=listing.listingID)='john', IF( CHAR_LENGTH(`postcode1`.`postcode`), CONCAT_WS('', `postcode1`.`postcode`), '') /* Select Postcode */, concat(SUBSTRING(listing.postcode,1,CHAR_LENGTH(listing.postcode) - 2),'**'))
Re: Hiding columns from non-owners
Posted: 2022-09-19 17:04
by pbottcher
Actually not. I would like to see the original statement, not the modified one if possible
Re: Hiding columns from non-owners
Posted: 2022-09-20 08:29
by jmcgov
No prob, thank you
IF( CHAR_LENGTH(`postcode1`.`postcode`), CONCAT_WS('', `postcode1`.`postcode`), '') /* Select Postcode */
Re: Hiding columns from non-owners
Posted: 2022-09-21 14:51
by pbottcher
ok, thanks. so try
if ((select memberID from membership_userrecords where tableName='listing' and pkValue=listing.listingID)='john', IF( CHAR_LENGTH(`postcode1`.`postcode`), CONCAT_WS('', `postcode1`.`postcode`), '') /* Select Postcode */, concat(SUBSTRING(`postcode1`.`postcode`,1,CHAR_LENGTH(`postcode1`.`postcode`) - 2),'**'))
Re: Hiding columns from non-owners
Posted: 2022-09-21 15:41
by jmcgov
No joy there, unfortunately - shows the full unredacted postcode
Re: Hiding columns from non-owners
Posted: 2022-09-21 15:42
by jmcgov
No joy there, unfortunately - shows the full unredacted postcode
I wonder if its because it is a select, that it may not be possible (within hooks)
Re: Hiding columns from non-owners
Posted: 2022-09-21 17:48
by pbottcher
Hi, that is bad....
can you please post the complete code that you use right now.
Re: Hiding columns from non-owners
Posted: 2023-01-08 09:07
by jmcgov
Hi Pbottcher. Apologies about the lateness of reply. The task fell off my priorities. Thank you for your assistance. James
Re: Hiding columns from non-owners
Posted: 2023-01-08 15:19
by pbottcher
Hi,
I just reviewed the code above.
Maybe the erro was with the fixed name "John". Try
Code: Select all
$from_old['postcode']="if ((select memberID from membership_userrecords where tableName='listing' and pkValue=listing.listingID)='".'$memberInfo['username']."', IF( CHAR_LENGTH(`postcode1`.`postcode`), CONCAT_WS('', `postcode1`.`postcode`), '') /* Select Postcode */, concat(SUBSTRING(`postcode1`.`postcode`,1,CHAR_LENGTH(`postcode1`.`postcode`) - 2),'**'))";