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

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
>>
>>
>>
>
>
Received on Mon Dec 20 2004 - 14:32:41 CST