Restrict user accress to a single, predefined record Topic is solved

[Paul]

I have created an access level that is set to Print, No Edits, and Browse Only, but I want to further restrict a user to one specific record only (entire record). How would I do that?

[kev1n]

Do you want to restrict a specific user (x) so they can only see one record (y), while allowing all other users to see the full dataset?

Construct a Browse SQL like this:

Code: Select all

SELECT *
FROM my_table
WHERE 
    ('#USER_ID#' = 'x' AND my_table_id = 'y') 
    OR ('#USER_ID#' <> 'x')

Replace placeholders as follows:

x → the User ID (from the users table, e.g. 123)
my_table → your actual table name
my_table_id → the primary key column of that table
y → the record ID that user x should be restricted to

If instead you want only user x to see record y (and all other users to see nothing), you can adapt the logic.

[steven]

nuBuilder Run Object

[Paul]

Worked like a charm! Excellent!

Thank you!

[Paul]

How would I modify the query when I add additional specific users? Each additional specific user would also be restricted to one specified record (a different record from the original user in the query) and all other users to have access to everything.

[Paul]

Would it be this?

SELECT * FROM cases where
('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '18') OR ('#USER_ID#' = 'x' AND pk_inv_case_no = 'x')
OR ('#USER_ID#' <> '68d858650166554')
OR ('#USER_ID#' <> 'x')

[kev1n]

Try:

Code: Select all

SELECT *
FROM my_table
WHERE 
    (#USER_ID# = 'user1' AND my_table_id = 'record1')
    OR (#USER_ID# = 'user2' AND my_table_id = 'record2')
    OR (#USER_ID# = 'user3' AND my_table_id = 'record3')
    OR (#USER_ID# NOT IN ('user1','user2','user3'))

[Paul]

So my code is:

Code: Select all

SELECT * FROM cases 
WHERE (#USER_ID# = '68d858650166554' AND pk_inv_case_no = '12')
    OR (#USER_ID# = '68d883538d9b830' AND pk_inv_case_no = '13')
    OR (#USER_ID# = '68d885c9ebe0dee' AND pk_inv_case_no = '14')
    OR (#USER_ID# NOT IN ('68d858650166554','68d883538d9b8302','68d885c9ebe0dee'));

I tried that in Browse SQL and logged in as globeadmin. I got 'No data to display' with no records and the following error:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'globeadmin' in 'where clause'

===SQL===========

SELECT pk_inv_case_no,pk_inv_case_no,pk_inv_contact_date,pk_inv_case_name,pk_loc_name,pk_inv_street,pk_inv_city,pk_inv_state,pk_inv_directions,pk_inv_contact,pk_inv_contact_phone,pk_inv_contact_email,pk_resident_own_name,pk_inv_resident_own_phone,pk_inv_owner_contacted,pk_inv_perm_waiv,pk_inv_privacy_n,pk_inv_init_notes,pk_loc_hist,pk_loc_hist_docs_URL,pk_loc_const,pk_loc_desc,pk_loc_rooms,pk_other_struct,pk_reported_activity,pk_recent_remodel,pk_recent_remodel_notes,pk_priest,pk_priest_notes,pk_spirit_itc_inv_hist,pk_wit_1,pk_wit_1_stat,pk_wit_2,pk_wit_2_stat,pk_wit_3,pk_wit_3_stat,pk_inv_start,pk_inv_end,pk_spirit_itc_inv,pk_others,pk_devices,pk_anomalous,pk_anomalous_dt,pk_weather,pk_moon,pk_inv_notes,pk_spirit_itc_inv_pe_1,pk_pe_dt_1,pk_pe_1,pk_spirit_itc_inv_pe_2,pk_pe_dt_2,pk_pe_2,pk_spirit_itc_inv_pe_3,pk_pe_dt_3,pk_pe_3,pk_desc_audio_1,pk_desc_audio_2,pk_desc_audio_3,pk_desc_audio_4,pk_desc_audio_5,pk_desc_audio_6,pk_desc_audio_7,pk_desc_audio_8,pk_audio_url_1,pk_audio_url_2,pk_audio_url_3,pk_audio_url_4,pk_audio_url_5,pk_audio_url_6,pk_audio_url_7,pk_audio_url_8,pk_desc_video_1,pk_desc_video_2,pk_desc_video_3,pk_desc_video_4,pk_desc_video_5,pk_desc_video_6,pk_desc_video_7,pk_desc_video_8,pk_video_url_1,pk_video_url_2,pk_video_url_3,pk_video_url_4,pk_video_url_5,pk_video_url_6,pk_video_url_7,pk_video_url_8,pk_desc_photo_1,pk_desc_photo_2,pk_desc_photo_3,pk_desc_photo_4,pk_desc_photo_5,pk_desc_photo_6,pk_desc_photo_7,pk_desc_photo_8,pk_photo_url_1,pk_photo_url_2,pk_photo_url_3,pk_photo_url_4,pk_photo_url_5,pk_photo_url_6,pk_photo_url_7,pk_photo_url_8,pk_inv_summary
 FROM cases 
 WHERE (globeadmin = '68d858650166554' AND pk_inv_case_no = '12')     OR (globeadmin = '68d883538d9b830' AND pk_inv_case_no = '13')     OR (globeadmin = '68d885c9ebe0dee' AND pk_inv_case_no = '14')     OR (globeadmin NOT IN ('68d858650166554','68d883538d9b8302','68d885c9ebe0dee')) LIMIT 0, 20

===BACK TRACE====

C:\xampp\htdocs\nuBuilder2\core\nuform.php - line 1351 (nuRunQuery)

C:\xampp\htdocs\nuBuilder2\core\nuform.php - line 469 (nuBrowseRows)

C:\xampp\htdocs\nuBuilder2\core\nuform.php - line 167 (nuGetFormFinalize)

C:\xampp\htdocs\nuBuilder2\core\nuapi.php - line 118 (nuGetFormObject)

[kev1n]

I spotted the problem right away. But you can also check with AI—I did, and here’s what it said.
This isn’t really a nuBuilder issue, more about MySQL syntax.

Problem
In nuBuilder Browse SQL, when you use #USER_ID#, the system substitutes the current login name (e.g. globeadmin) directly into the query.
Without quotes, MySQL interprets it as a column name, leading to:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'globeadmin' in 'where clause'

---

Cause
Your query was executed as:

Code: Select all

WHERE (globeadmin = '68d858650166554' AND pk_inv_case_no = '12') 

This fails because globeadmin is not a column.


---

Solution
Enclose #USER_ID# in single quotes so it is treated as a string value:

Code: Select all

 SELECT * FROM cases WHERE ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '12') OR ('#USER_ID#' = '68d883538d9b830' AND pk_inv_case_no = '13') OR ('#USER_ID#' = '68d885c9ebe0dee' AND pk_inv_case_no = '14') OR ('#USER_ID#' NOT IN ('68d858650166554','68d883538d9b830','68d885c9ebe0dee')); 

---

Note
There is a typo in your original NOT IN list:

Code: Select all

68d883538d9b8302

should be

Code: Select all

68d883538d9b830

Also, could you please use code tags when posting code? (You can use the buttons in the toolbar to add them.) It makes the code a lot more readable. Thanks! (I already formatted your last post accordingly)

[Paul]

Thank you for catching those SQL syntax errors and the typo. Much appreciated. I fixed them and all four logins are working as expected.
And I will remind myself to use the special code tags when posting code.