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

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 Sun Dec 19 2004 - 12:03:58 CST