0009148: Integrated Business Partner Security

Description

A great benefit of web applications is that no software installation is required on the client. The case when this is an absolute requirement is for customers, vendors, distributors, etc. (people outside of your company) that you want to give controlled access to your system (for example to review shipment status, change an order quantity, etc.). These external people must be restricted to only be able to see their data (no one elses).

ERPs that are not web-based cannot do this natively because the business partners do not allow them to install the client software on their PCs (they rely on separate portals, which are extra cost and administrative burden).

So if we can easily set up a business partner user so that he can only see his data, that would be a huge selling differentiator (and a way to show small prospects the difference between a restricted low-end ERP and one that can meet their information needs and their partners' too).

Steps To Reproduce

Try to implement what I describe above. You can set up ad_tab.whereclause rows to be user-specific, but it means that you need to create a separate role for each user (which is a pain and not the intent).

Proposed Solution

It seems that this feature can be implemented in a straight-forward way (from both the end user perspective and the development perspective), because of the Application Dictionary and because Business Partner is such a generic concept in Openbravo. The logic is conceptually similar to the logic associated with ad_tab.whereclause. But this feature makes it very easy for users to set up business partners to access the system in a properly-restricted way--that's why it is called "Integrated" Business Partner Security.

First, add these two single-character columns to the database:

* ad_column.applybpsecurity {'Y'|'N'}, default value is 'N'.
* ad_role_orgaccess.applybpsecurity {'Y'|'N'}, default value is 'N'.

Note that before a table is accessed, both ad_column and ad_role_orgaccess have been queried. So that query has been made, and a result set returned that includes the two new columns above. Based on that result set we will create (or append to) a where clause to limit the user to see only the rows that are stamped with his business partner id. We touch the where clause when BOTH values of the 2 new columns are 'Y'.

Here is the logic behind the first column:

If ad_column.applybpsecurity is set to 'Y', it means that this column of this table contains a business partner id (c_bpartner_id), and we want to be able to optionally require that a where clause is automatically generated (or appended to using the AND operator), to restrict the signed-on user's business partner id (ad_user.c_bpartner_id) to match the value of ad_column.ColumnName (which will be the c_bpartner_id column of the table being queried), only when this column's table is being queried by a signed-on user who is restricted as discussed below. Note that the appropriate where clause must be generated even if the c_bpartner_id column itself is not in the result set.

Here is the logic for the second column (which tells us which roles will be restricted by Business Partner):

A signed-on user may be restricted to rows in database tables that contain his "Business Partner ID" only if (ad_role_orgaccess.applybpsecurity = 'Y' and ad_user.c_bpartner_id is IS NOT NULL). So, "Integrated Business Partner Security" only applies to a user who is actually connected to a Business Partner (makes sense!) and who is signed on to a role that restricts him in this way.

Note that if multiple rows are returned, then the where clause fragment must include an equality test for each one, separated by the OR operator. This is to handle the case of c_bpartner_id being on the table multiple times, e.g. c_order.c_bpartner_id and c_order.dropship_bpartner_id. The user should be shown the row if EITHER of these business partner ids match his (BOTH are NOT required to match).

Now let's determine which ad_column.applybpsecurity rows should be set to 'Y'. Note that if a row is incorrectly set to 'Y' for this column, then the query will either error out (if the datatype is incompatible) or 0 rows will be returned. So, care must be taken to set the correct rows.

Use this query to list the candidates:

select tablename, columnname from ad_table t1, ad_column t2
where
t1.ad_table_id = t2.ad_table_id
and lower(columnname) like '%bpartner_id%'
order by 1,2

If there isn't a reason not to set all of these to 'y', issue this query:

update ad_column
set ad_column.applybpsecurity = 'Y'
where
lower(columnname) like '%bpartner_id%'

Now, as a normal administrator user, set up role called Customer, and give it access to the windows that you want customers to be able to see (and any other constraints like readonly). Then set ad_role_orgaccess.applybpsecurity to 'Y' (just use the screen), and associate a user to this role and to a business partner. If the feature is implemented correctly, you should be able to sign on as that user and you will only be able to see rows associated with your Business Partner! This is very valuable and will make for a sales-impacting demo (which is why I set the severity to major--big impact, small cost). The nice thing is that you can make a role for Vendor and Limited_Employee, and it will act the same way--very simple and understandable from the user setup perspective.

Issue History
Date Modified	Username	Field	Change
2009-05-23 02:39	johnfandl	New Issue
2009-05-23 02:39	johnfandl	Assigned To	=> rafaroda
2009-05-23 02:49	johnfandl	Note Added: 0016594
2009-05-25 10:00	rafaroda	Assigned To	rafaroda => pjuvara
2009-05-25 17:04	pjuvara	Assigned To	pjuvara => iciordia
2009-05-25 17:04	pjuvara	Category	A. Platform => C. Security
2009-05-25 17:04	pjuvara	Steps to Reproduce Updated

Notes
(0016594) johnfandl (reporter) 2009-05-23 02:49	On the commercial side, making it easy for our customers to give their customers, suppliers, and distributors access to Openbravo means: 1. More users using the system, and 2. Viral marketing, as people in other companies use the software and click on our link to find out more about us.

Relationships [ Relation Graph ] [ Dependency Graph ]