Home » RDBMS Server » Security » Schema consolidation and user schema mapping based on service? (Oracle 10.2.0.3 (potentially upgrading soon to 11gR2))
Schema consolidation and user schema mapping based on service? [message #517219] Sun, 24 July 2011 20:52 Go to next message
a_schafs
Messages: 3
Registered: July 2011
Location: Melbourne
Junior Member
We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.

Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.

Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.

Any ideas or feedback would be much appreciated...

Thanks,
Andrew
Re: Schema consolidation and user schema mapping based on service? [message #517243 is a reply to message #517219] Mon, 25 July 2011 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at VPD (Virtual Private Database).

Regards
Michel
Re: Schema consolidation and user schema mapping based on service? [message #517248 is a reply to message #517219] Mon, 25 July 2011 01:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Could you use ALTER SESSION SET CURRENT_SCHEMA, perhaps in a logon trigger:
orcl> conn / as sysdba
Connected.
orcl> create trigger jon.setscott after logon on jon.schema
  2  begin
  3  execute immediate 'alter session set current_schema=scott';
  4  end;
  5   /

Trigger created.

orcl> conn jon/jon
Connected.
orcl> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        99 new            uk

orcl>

You wold need some algorithm in the trigger to map sessions to particular schemas.
Re: Schema consolidation and user schema mapping based on service? [message #517420 is a reply to message #517219] Mon, 25 July 2011 23:11 Go to previous messageGo to next message
a_schafs
Messages: 3
Registered: July 2011
Location: Melbourne
Junior Member
Thanks for the thoughtful replies, Michel and Jon.

I looked a VPDs briefly though my (albeit limited) understanding is that they are effectively providing data separation/filtering using user-specific views.

However, we don't have the ability to change the underlying data/schema and were hoping to take it "as-is". Hence us looking at the DB service name as the only mechanism to separate the duplicate user names.

The use of triggers suggestion is an interesting one. Does anyone know if there's a way to view which DB service a particular SQL session is connected via? Or is this more a function of the listener layer and not visible internally to the DB. If the service name could be evaluated, then its possible a login trigger could map the users according to the service they connected through.

It's starting to sound more complicated than it may be worth for the benefits though. I'm not sure what the performance/security implications may be of using a trigger in this way, particularly as the app isn't SQL-efficient and generates a lot of separate connections.

Thanks again,
Andrew
Re: Schema consolidation and user schema mapping based on service? [message #517422 is a reply to message #517420] Tue, 26 July 2011 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does anyone know if there's a way to view which DB service a particular SQL session is connected via?

select sys_context('userenv','service_name') from dual;

or directly the sys_context call in PL/SQL.

Regards
Michel
Re: Schema consolidation and user schema mapping based on service? [message #517746 is a reply to message #517422] Wed, 27 July 2011 21:19 Go to previous message
a_schafs
Messages: 3
Registered: July 2011
Location: Melbourne
Junior Member
Thanks again for that, Michel - that's useful.

We'll do some further testing here as to what is possible. However, I suspect that it's still going to be a bit tricky as this retrieval of the service will only work for connections coming in via TNS.

Thanks,
Andrew
Previous Topic: Restrict DML on table
Next Topic: user creation
Goto Forum:
  


Current Time: Thu Mar 28 16:45:47 CDT 2024