Business small commercial app using Apex [message #592126] |
Sat, 03 August 2013 13:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/79d243de626b38c344d06670cd1c248d?s=64&d=mm&r=g) |
anil001
Messages: 17 Registered: August 2013 Location: United States
|
Junior Member |
|
|
I am trying to build a small commercial app and wondering how do I implement multi-tenant so that logged in user can see their own enterprise data only.
There will be few tables which will have common data which will be shared among tenants.
Appreciate if you could share any example etc.
|
|
|
|
Re: Business small commercial app using Apex [message #592130 is a reply to message #592126] |
Sat, 03 August 2013 14:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Here's how I did that. I'll try to simplify it, providing only key information so that you'd get the idea. Real application is somewhat different, but the principle is the same. I also modified table and column names; I tried to pay attention and keep it consistent.
Suppose there are several departments, each of them having unique code, such asSQL> select * from dept;
ID_DEPT NAME_DEPT
---------- ----------------------
400500 London
400501 Rome
400502 Oslo
I also have a table of users, such asSQL> select * from emp;
ID_EMP NAME_EMP
---------- --------------------
4005246 Tiger Scott
4005160 Lion Mike
SQL>
Finally, there's the third table that "joins" users and departments they are allowed to work with:SQL> select * from dept_emp;
ID_EMP ID_DEPT
---------- ----------
4005160 400502
4005246 400500
4005246 400501
4005246 400502
SQL> which means that user whose ID = 4005160 is allowed to work with department 400502, while user ID = 4005246 is allowed to work with 3 departments: 400500, 400501, 400502.
That much about the database part of the job.
Within the application, I used the same (part of the) WHERE clause, everywhere, to make sure that every user sees only his own data, such asselect ...
from evid e
where ...
and e.id_dept in (select id_dept from dept_emp
where id_emp = :P0_ID_EMP
) where P0_ID_EMP represents a page 0 item which gets populated after users logs on to the application.
That's all, more or less.
|
|
|
Re: Business small commercial app using Apex [message #592132 is a reply to message #592126] |
Sat, 03 August 2013 15:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
anil001 wrote on Sat, 03 August 2013 19:11I am trying to build a small commercial app and wondering how do I implement multi-tenant so that logged in user can see their own enterprise data only.
There will be few tables which will have common data which will be shared among tenants.
Appreciate if you could share any example etc. If this is a "small commercial app" you probably want to avoid multitenant. The multitenant option costs US$17500 per CPU, on top of US$47500 per CPU for Enterprise Edition licences. Better to stay with views or programmatic techniques, which you can use with a XE licence (which is free).
|
|
|
|