Home » Developer & Programmer » Application Express & MOD_PLSQL » Business small commercial app using Apex (4.2.2)
Business small commercial app using Apex [message #592126] Sat, 03 August 2013 13:11 Go to next message
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 #592129 is a reply to message #592126] Sat, 03 August 2013 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58929
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how do I implement multi-tenant so that logged in user can see their own enterprise data only.


Views are made for this, if you don't want to have a schema per user.

Regards
Michel
Re: Business small commercial app using Apex [message #592130 is a reply to message #592126] Sat, 03 August 2013 14:25 Go to previous messageGo to next message
Littlefoot
Messages: 19520
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 as
SQL> select * from dept;

   ID_DEPT NAME_DEPT
---------- ----------------------
    400500 London
    400501 Rome
    400502 Oslo

I also have a table of users, such as
SQL> 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 as
select ...
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 messageGo to next message
John Watson
Messages: 4511
Registered: January 2010
Location: Global Village
Senior Member
anil001 wrote on Sat, 03 August 2013 19:11
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.
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).
Re: Business small commercial app using Apex [message #592689 is a reply to message #592132] Sun, 11 August 2013 01:57 Go to previous message
anil001
Messages: 17
Registered: August 2013
Location: United States
Junior Member
Thanks Everyone. I was able to use add where clause on 'APP_USER' in my view to solve this.

app_user = v('APP_USER');
Previous Topic: Deleting a record
Next Topic: Upload desktop files (pdf/doc) using PLSQL
Goto Forum:
  


Current Time: Thu Aug 28 10:11:06 CDT 2014

Total time taken to generate the page: 0.16590 seconds