Filter data at login by SEC_USER.GROUP_ID

[MauroMerlini (Merlinimauro)]

Good morning
I’m trying to learn how to use cuba and I’m watching video tutorials quick-start-part-2.

In my old STRUTS project I had multiple datasources and at login the user had his own database (company) and for example he could see vendor of his company.

In cuba platform I see that you use a single large database, I have this problem. I have created 2 users who belong and 2 different companies (different SEC_USER.GROUP_ID) and I have created a table VENDORS.

With the tutorial I created a simple list and CRUD but at the moment i see all vendors.

How can I see the VENDORS of the logged in user maybe filtered for SEC_USER.GROUP_ID?

I’m sorry for my bad english.

[shiryaeva (Olga Shiryaeva)]

Hi Mauro,

What you actually need is a row-level security Constraint based on the user session: the predefined session$userGroupId JPQL constant can be used in a Filter component query or in a screen’s datasource query (see here and here).

You can also take a look at Access Groups tutorial.

[MauroMerlini (Merlinimauro)]

Thanks for the reply!

I see the example cuba-platform / sample-workshop

5.2.5 row-level Security

in this case Costraint is {E}.mechanic.user.id = :session$userId

in my case I should use {E}.myEntity.user.group.id = :session$userGroupId.

but i have a problem

entity DEMO Mechanic
name: user type: User[sec$User] column: USER_ID
name:hourlyRate type: BigDecimal column: HOURLY_RATE

my entity VENDOR
name: groupUser type: User[sec$User] column: GROUP_USER_ID
name: name type: String column: NAME

in sample-workshop demo user is selected manually through UI, in my case i want save user session data (group_id) automatically and only name manually through UI.

if i create a row in my case i have this error

MySQLIntegrityConstraintViolationException: Column ‘GROUP_USER_ID’ cannot be null

(obviously because through UI I fill in only the name field)

I maybe understood how to read but I did not understand how to create row with user session data

Where am I wrong?

[shiryaeva (Olga Shiryaeva)]

Do you want to filter data in a particular screen? If so, instead of a security constraint you can create a custom Filter condition, define {E}.myEntity.user.group.id = :session$userGroupId as the Where-clause and make this condition hidden. Other conditions (e.g. by name field) you can leave visible in the UI.

[MauroMerlini (Merlinimauro)]

Thanks for your patience,

Filter condition i’s ok but i have a problem with the ui creation. For every entity i think
I should create an attribute like this:

in sample-workshop demo it is assigned manually

instead I would assign the value with my user data logged, in this example case i don’t want “User” field for create row. When i click ok only Hourly Rate will be inserted manually but User[sec$User] I want to valorize it with logged user data)

[MauroMerlini (Merlinimauro)]

OLD PROJECT (multiple database)

for example i have 3 comapany (3 database) and every comapany have 5 users.

User1->5 login to DB1 	User6->10 login to DB2	User11->15 login to DB3 

DB1 have entity boo, foo	DB2 have entity boo, foo	DB3 have entity boo, foo

boo attibute: name, description...
foo attibute: name, description...

If User6 logs on he can only see boo,foo row of DB2 (company2)
If User3 logs on he can only see boo,foo row of DB1 (company1)
If User14 logs on he can only see boo,foo row of DB3 (company3)

CUBA PROJECT (one database)

i create 3 “Access Groups” Company1, Company2, Company3

and I create 15 users and i have assigned 5 users per company

I have create 2 entity (boo,foo) and simple attribute

I create generic UI

if I want to see only the lines of boo filtered by company I think it should create this attribute and create a constraint

Constraint

at this point if I insert a row in the entity boo with generic UI field NAME and field description I find the empty company_id attribute in the database

demoForum.zip (88.5 KB)
DemoForum 20180704 1727.txt (68.3 KB)

[shiryaeva (Olga Shiryaeva)]

As far as I can see, you want to create a reference to an access Group but set the User type instead.
I’ve made it work, but I’m not sure I understood well what you need.
demoForum.zip (91.8 KB)

[MauroMerlini (Merlinimauro)]

Surely it’s me that I can not explain in English.

Unfortunately, this example is the same as the demo sample-workshop, even in this case the user chooses the company manually. NO, I create this group / customers as an administrator before selling the service, the user must not choose his company, he must not even know the existence of other companies that use the web app, he does not have to notice anything, so he creates a line of all the entities of the application the data of his id company must be entered automatically. No user must see lines of entities from other companies.

Reading on the forum my problem is MULTIPLE DATABESE VS cuba traditional approach muti-tenancy - one database.

I usually create a single web application and every my customers/Company that uses it (which has more users) has its own database. I have many completely identical databases. When Company1 user logs “USE …” the company1/customer1 database.

Users of Company1/Customer1 when they log in see only the data of their Company for example entity invoices, suppliers …

Users of Company2/Customers2 when they log in see only the data of their Company for example entity invoices, suppliers …

if I use CUBA with a single database where everyone has access, ok when user READ “use” filters contraints but when the user of Company1 creates an invoice, does not say that belongs to Company1, it is obvious, he does not even know the existence of company2.

if Cuba does not manage or advise against the concept of mutiplidatabase, how can I solve this problem?

Before moving to the commercial version I have to understand if I can keep the data of my customers divided with CUBA. I have to convince my boss.

I have
one web application
N * Customers who have N * users

if I have only one database and for example a user has to enter a supplier of his company, he does not have to tell which company he belongs to. in what way it creates the supplier I must have in memory his company / customer ID to be saved in the new row.

Thank you.

[krivopustov (Konstantin Krivopustov)]

Please see the attached sample application:
demoForum.zip (105.8 KB)

If you run it and login as u1/1, you’ll see a set of Boo records. If you login as u2/1, you’ll see a different set.

The Boo entity is inherited from the BelongsToCompany mapped superclass which has a reference to access group. It is set automatically by BelongsToCompanyEntityListener:

@Component("demoforum_BelongsToCompanyEntityListener")
public class BelongsToCompanyEntityListener implements BeforeInsertEntityListener<BelongsToCompany> {

    @Inject
    private UserSessionSource userSessionSource;

    @Override
    public void onBeforeInsert(BelongsToCompany entity, EntityManager entityManager) {
        entity.setGroup(userSessionSource.getUserSession().getUser().getGroup());
    }
}

The rest is a set of access groups and the constraint on the root group which restricts access to entities based on BelongsToCompany:

See also a more complex example here: GitHub - cuba-platform/sample-saas: Example of a multi-tenant application

[MauroMerlini (Merlinimauro)]

Thank you Olga and Konstantin

“Sample-saas” demo is a great example.

It’s just what I was looking for.