X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 34BF211F627F for ; Tue, 18 Apr 2006 06:58:57 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 1.137 X-Spam-Level: * X-Spam-Status: No, score=1.137 required=5 tests=[AWL=-0.476, BAYES_00=-2.599, FROM_LOCAL_NOVOWEL=2.861, HTML_10_20=1.351, HTML_MESSAGE=0.001, SPF_PASS=-0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id es8WW2iBkcWw for ; Tue, 18 Apr 2006 06:58:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.193]) by postgresql.org (Postfix) with ESMTP id 4C98311F6624 for ; Tue, 18 Apr 2006 06:58:51 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id m7so693038nzf for ; Tue, 18 Apr 2006 02:58:51 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Zj7n1NH3w0fJSvAOLEIUILzLKOud1j2oq4Mn8aZGm+rJ9g3+WHCCKAxX1UYbIn2u2xcHvi/owM/5WNiZqigVK4pTPaRf6xn1sX0VNva1US7W27rTI1SKvy1oQmQOyzKNPQGZbC620y6q5TpQppW7IigjEEUm5LXtLA5PyiFP+3o= Received: by 10.36.227.33 with SMTP id z33mr7791478nzg; Tue, 18 Apr 2006 02:58:51 -0700 (PDT) Received: by 10.36.158.13 with HTTP; Tue, 18 Apr 2006 02:58:51 -0700 (PDT) Message-ID: Date: Tue, 18 Apr 2006 13:58:51 +0400 From: Luckys To: "Achilleus Mantzios" Subject: Re: [SQL] Thoughts on a Isolation/Security problem. Cc: pgsql-sql@postgresql.org, pgsql-admin@postgresql.org, pgsql-general@postgresql.org, pgsql-jdbc@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3018_20696906.1145354331219" References: X-Archive-Number: 200604/155 X-Sequence-Number: 21324 ------=_Part_3018_20696906.1145354331219 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline how about having a company table, and company_code column across all relevant table, although you'll have to modify your application also, which would be an additional clause in the WHERE condition e.g where company_code =3D 'which company user has logged in'. The user has to specify while logging under which company he's going to wor= k on. This way would be ideal even for your Global financial consolidations if th= e mgmt requires in the due course. other option would be of two tables, Company , Organization, where you can have company1, org1, org2 etc., this can also be applied in the same patter= n as stated above. On 4/18/06, Achilleus Mantzios wrote: > > Hi, i have run into the following problem. > Let me describe the context first. > > When i joined the company(ies) i work for (a group of Shipping Mgmt/ > Owenship/Agent companies), the only thing i was told when i started > designing the DB/Apps was just one company. > > So i built everything into one single DB, and i wrote the apps > having one company in mind. > > Our architecture is based on jboss3/postgresql (curenctly 7.4.12). > There is one .ear file, which authenticates users against a lotus > notes ldap server. > > At the time, the corporate organisational model was a little bit wierd: > - Many Indepentent ownership companies > - Many Independent Mgmg companies > (but all busines was with one company in mind). > > Each App user is a member of one or more ldap groups, each group > mapping to a mgmt company. > > So i ended up with > - one DB with 173 tables > - one DB user (postgres) > - one .EAR application with 148,827 lines of code. > > Now the requirements start to change. > The boss now bought some other types of vessels too. > So virtually there must be N separate distinct apps, where N is the numbe= r > of Mgmt companies (roughly one for every type of vessel), where each app > sees and manages only its data. > > Moreover there are some apps that should see global data for some specifi= c > tables. (like the crew data, people in the crew move from one type of > vessel to the other so they are not tied to a Mgmt company). > > These new requirements are of legal nature, as well as of > operational. (People managing a type of vessels dont want to mess with > another type, > and auditors must see each company completely separated from the rest). > > Doing it with extra code would be a real pain, since i would have to > refine > all security/authentication based on the groups (groups@mgmt_companies) > that a person belongs to. Also this way no inherent isolation/security > would hold. > > Now i am thinking of restructuring the whole architecture as: > - Create one EAR app for every mgmt company > - Create one DB USER for every mgmg company > - Create one SCHEMA (same as the USER) for every mgmt company > (mgmtcompany1,mgmtcompany2,etc...) > - Find a way (links/xdoclet/eclipse?) to have *one* common code base for > the N EAR apps. > - Tweak with jboss*.xml to map java:comp/env/jdbc/DB to > java:/pgsql, where pgsql authenticates > with the corresponding DB USER. > - Classify the tables into > - The ones that apply to ALL mgmt companies (and leave them in the > public schema) > - The ones that apply *only* to a mgmt company and so create one under > each SCHEMA > - Load the data in *each* SCHEMA, except the tables that apply to all. > - Define a process of "mgmt company"fying the tables in each schema (e.g. > delete from mgmtcompany1.vessels the vessels that dont belong to > mgmtcompany1, and so forth) > - Resolve FK constraint issues > - The default search_path in psql (whats the the equivalent in jdbc?) is > $user,public, so effectively *each* EAR will hit automagically the correc= t > mgmtcompanyN.* tables, or the public.* tables if these tables apply to al= l > mgmt companies. > > With this way, the hard work is DB oriented, and not APP oriented. > However i wonder whether someone else has gone thru a similar process, > or if someone finds some assumption conceptually flawed. > > Thanx for reading, and for any possible thoughts. > > -- > -Achilleus > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- regards, Luckys... ------=_Part_3018_20696906.1145354331219 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
how about having a company table, and company_code column across all r= elevant table, although you'll have to modify your application also, which = would be an additional clause in the WHERE condition e.g where company_code= =3D 'which company user has logged in'.
The user has to specify while logging under which company he's going t= o work on.
This way would be ideal even for your Global financial consolidations = if the mgmt requires in the due course.
other option would be of two tables, Company , Organization, where you= can have company1, org1, org2 etc., this can also be applied in the same p= attern as stated above.
 
 
 
On 4/18/06, = Achilleus Mantzios <= achill@matrix.gatewaynet.com> wrote:

Hi, i have run into the foll= owing problem.
Let me describe the context first.

When i joined t= he company(ies) i work for (a group of Shipping Mgmt/
Owenship/Agent companies), the only thing i was told when i started
= designing the DB/Apps was just one company.

So i built everything in= to one single DB, and i wrote the apps
having one company in mind.

Our architecture is based on jboss3/postgresql (curenctly 7.4.12).
T= here is one .ear file, which authenticates users against a lotus
notes l= dap server.

At the time, the corporate organisational model was a li= ttle bit wierd:
- Many Indepentent ownership companies
- Many Independent Mgmg compa= nies
(but all busines was with one company in mind).

Each App use= r is a member of one or more ldap groups, each group
mapping to a mgmt c= ompany.

So i ended up with
- one DB with 173 tables
- one DB user (po= stgres)
- one .EAR application with 148,827 lines of code.

Now th= e requirements start to change.
The boss now bought some other types of = vessels too.
So virtually there must be N separate distinct apps, where N is the num= ber
of Mgmt companies (roughly one for every type of vessel), where each= app
sees and manages only its data.

Moreover there are some apps= that should see global data for some specific
tables. (like the crew data, people in the crew move from one type ofvessel to the other so they are not tied to a Mgmt company).

These= new requirements are of legal nature, as well as of
operational. (Peopl= e managing a type of vessels dont want to mess with
another type,
and auditors must see each company completely separate= d from the rest).

Doing it with extra code would be a real pain, sin= ce i would have to
refine
all security/authentication based on the gr= oups ( groups@mgmt_companies)
that a person belongs to. Also this way no inhere= nt isolation/security
would hold.

Now i am thinking of restructur= ing the whole architecture as:
- Create one EAR app for every mgmt compa= ny
- Create one DB USER for every mgmg company
- Create one SCHEMA (sam= e as the USER) for every mgmt company
(mgmtcompany1,mgmtcompany2,etc...)=
- Find a way (links/xdoclet/eclipse?) to have *one* common code base fo= r
the N EAR apps.
- Tweak with jboss*.xml to map java:comp/env/jdbc/&l= t;mgmt company>DB to
java:/<mgmt company>pgsql, where <mgmt = company>pgsql authenticates
with the corresponding DB USER.
- Clas= sify the tables into
- The ones that apply to ALL mgmt companies (and leave them in the
p= ublic schema)
- The ones that apply *only* to a mgmt company and so crea= te one under
each SCHEMA
- Load the data in *each* SCHEMA, except the= tables that apply to all.
- Define a process of "mgmt company"fying the tables in each = schema (e.g.
delete from mgmtcompany1.vessels the vessels that dont belo= ng to
mgmtcompany1, and so forth)
- Resolve FK constraint issues
- The default search_path in psql (whats the the equivalent in jdbc?) is$user,public, so effectively *each* EAR will hit automagically the correct=
mgmtcompanyN.* tables, or the public.* tables if these tables apply to = all
mgmt companies.

With this way, the hard work is DB oriented, and= not APP oriented.
However i wonder whether someone else has gone thru a= similar process,
or if someone finds some assumption conceptually flawe= d.

Thanx for reading, and for any possible thoughts.

--
-Ach= illeus



---------------------------(end of broadcast)--------= -------------------
TIP 4: Have you searched our list archives?

            &nb= sp; http://archives.postgre= sql.org



--
regards, Lucky= s...=20 ------=_Part_3018_20696906.1145354331219--