Re: Help with hosting multiple versions of the same schema (1 per client)

"Jack Addington" <jaddington_at_shaw.ca> wrote in message news:JFGxd.531931$Pl.262659_at_pd7tw1no...
> I had a look at that... seems like a lot of admin overhead as well as
having
> limitations when connecting from another source (sqlplus). One of my
> requirements is that the client can do an ODBC connect from a tool like
SPSS
> and extract their data for analysis.
>
> Also I think that might be a big sell to the customer that their data
isn't
> really isolated from their potential competitor.
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
> news:ADjxd.261498$HA.8722_at_attbi_s01...
> > How about using the virtual private database? Then there is one schema
> > but
> > it looks like a separate schema for each person. Much simpler.
> > Jim
> > "Jack Addington" <jaddington_at_shaw.ca> wrote in message
> > news:iojxd.542806$nl.210875_at_pd7tw3no...
> >> Hi All.
> >>
> >> I have a problem that I need some help/advice on how to manage it. I
have
> >> scoured the forums here but only found bits and pieces of what I'm
trying
> > to
> >> do.
> >>
> >> Basically I have a small application that I am hosting the database
for.
> > As
> >> each client comes online I need to create a schema for them. I have
> > written
> >> the application so that it does not reference the schema name (assumes
> >> ownership or public/private schema). If you log in as the owner then
> >> everything is fine. However I am at the point now that I would like to
> > start
> >> overhauling the security so that I can support and manage the different
> >> users logging in. I currently have after update triggers on all tables
> > that
> >> sets the user_id/timestamp for auditing issues.
> >>
> >> I have a few different scenarios that I need to be able to support as
far
> > as
> >> clientele.
> >>
> >> 1) I host database: 1 client = 1 schema
> >>
> >> 2) Client hosts database: 1 client = 1 schema
> >>
> >> 3) Client organization hosts database: 1 dept = 1 client = 1 schema
(same
> > as
> >> 1 except client organization is hosting database). The department are
so
> >> different (budget, staff, different support contract) that they are
> >> different customers but their organization is so big they have large
> >> outsourced databases.
> >>
> >> 4) There is also the chance that 1 client might allow the same user
> > (single
> >> oracle account) access to two versions of the application.
> >>
> >> I also have two versions of my software, client/server and an under
> >> development distributed/web-based. I need a single solution that works
> > best
> >> across both platforms
> >>
> >> Anyhow I have been warned that creating private synonyms will become a
> >> nightmare once there are lots of users. I also don't want to log in as
> >> the
> >> schema owner as that too will become very difficult to manage
> >> security/rights (especially on a web based system).
> >>
> >> I also need a solution that is simple to manage and will fit with the
> >> 'generic' client that hosts their own database.
> >>
> >> These are the solutions I see right now:
> >>
> >> 1) new instance for each schema - not cost effective and probably not
> >> satisfactory for large clients
> >> 2) Re-write all sql statements to use a master stored proc that then
> >> dynamically forwards to appropriate schema - sounds gross
> >> 3) Somehow altering my sql statements at runtime to 'modify' the schema
> > name
> >> dynamically - lot of re-work and difficult to debug
> >> 4) FGAC - sounds perfect but competing clients aren't going to accept
> >> that
> >> for security/privacy etc no matter how safe I tell them it is.
> >> 5) Using some sort of ALTER SESSION SET CURRENT_SCHEMA and tricking the
> >> database to thinking someone else is logged in? - this is the one I
found
> > in
> >> the forums but haven't found much else on. How is this different than
> >> just
> >> logging in as the owner?
> >>
> >> In my perfect world I think I would want to be able to point roles at
> >> certain schemas. But Oracle doesn't seem to support that. Then I could
> > have
> >> companyOneReadOnly = company1schema + all the read-only privileges.
> >>
> >> Anyhow any help is greatly appreciated.
> >>
> >> Thanks
> >>
> >> jack
> >>
> >>
> >>
> >
> >
>
>

Use a logon trigger to set their context. Then they can use any tool that connects to the database - they have to log on - and they would see their own data.
Jim Received on Mon Dec 20 2004 - 20:05:06 CST