Re: [SQL] Thoughts on a Isolation/Security problem.

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(at)matrix(dot)gatewaynet(dot)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(at)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