From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Luckys <plpgsql@gmail.com>
Cc: pgsql-sql@postgresql.org, <pgsql-admin@postgresql.org>
Cc: pgsql-general@postgresql.org
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [SQL] Thoughts on a Isolation/Security problem.
Date: Tue, 18 Apr 2006 14:32:40 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0604181427100.24984-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <b80d582c0604180258w6de8b262qe4d93894737f676b@mail.gmail.com>
O Luckys Ýãñáøå óôéò Apr 18, 2006 :
> 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
> = 'which company user has logged in'.
> The user has to specify while logging under which company he's going to 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 pattern
> as stated above.
Thanx for your thoughts, but this would require touching
173 tables +
2,594 SQL statements in a sum of 324 programs (which sum into 125,085
lines of code)
>
>
>
> On 4/18/06, Achilleus Mantzios <achill@matrix.gatewaynet.com> 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 number
> > 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 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/<mgmt company>DB to
> > java:/<mgmt company>pgsql, where <mgmt company>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 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 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...
>
--
-Achilleus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, plpgsql@gmail.com, pgsql-admin@postgresql.org, pgsql-general@postgresql.org, pgsql-jdbc@postgresql.org
Subject: Re: [SQL] Thoughts on a Isolation/Security problem.
In-Reply-To: <Pine.LNX.4.44.0604181427100.24984-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox