NuBuilder @ Front Office (restrict access to table rows)

[Drache]

Dear NuBuilders,

Obviously, NuBuilder is a backoffice application. Does it work for customers, too ?

Aim
Some well-known customers want to access their own data via Browser.
As an example, a customer might login and browse his list of invoices.

Howto
We can, of course, maintain logins for these customers and tell them their name+password. I know we can restrict access to forms and objects, too.
But can we filter table rows depending on the current user ?
A customer must never see other customers invoices. Even when fiddling around in the browsers URL.

Has NuBuilder a way to accomplish that ?
I'd be *very* glad.

Thank you,
Ruben

[massiws]

Ruben,
first, customers allowed to browse their invoices should have a dedicated Access level and User group: each user must login with his/her username+password.

Second, you need a system to map customers and users.
This can be done:

1. using the same id for user and customer (but you have to insert user manually in database); so, in the SQL of your invoice table you may use:

   Code: Select all

   SELECT * FROM invoice WHERE inv_customer_id = '#nu_user_id#' OR 'globeadmin' = '#nu_access_level#';

2. add a new sus_customer_id field in zzzsys_user table (simple, but you have to hack a system table: I don't know if this can be dangerous for future upgrading); so, in the SQL of your invoice table you may use:

   Code: Select all

   SELECT * FROM invoice INNER JOIN zzzsys_user ON inv_customer_id = sus_customer_id WHERE inv_customer_id = '#nu_user_id#' OR 'globeadmin' = '#nu_access_level#';

3. building a new table customer_user to map customer_id and user_id:
   customer_user
   - customer_user_id
   - cuus_customer_id
   - cuus_user_id
   
   So, in the SQL of your invoice table you may use:

   Code: Select all

   SELECT * FROM invoice INNER JOIN customer_user ON inv_customer_id = cuus_customer_id WHERE cuus_user_id = '#nu_user_id#' OR 'globeadmin' = '#nu_access_level#';

You can use #nu_user_id# hash variable in all SQL or PHP code to get ID of current user and filter what he/she can access.

Hope this helps,
Max

[Drache]

Hope this helps,

Yes, absolutely! The 3rd version even allows to keep eventually existing (Integer) customer_id. Additionally, it's a good idea to privilege globeadmin
Thank you very much,
Ruben

[massiws]

.