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

As an additional technique what about:

1. Logging into application as a user but logging into Oracle as the schema owner.
2. On login I add a row to temporary global table with the user id.
3. Also do a quick lookup in the allowed users roles and set other flags

Then I point all triggers or security functions that use the USER environement variable to the temp table?

My biggest concern is with security on a web based system.

"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:36:10 CST