OraFAQ Forum: Security » Schema consolidation and user schema mapping based on service?

Home » RDBMS Server » Security » Schema consolidation and user schema mapping based on service? (Oracle 10.2.0.3 (potentially upgrading soon to 11gR2))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Schema consolidation and user schema mapping based on service? [message #517219]

Sun, 24 July 2011 20:52

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

Report message to a moderator

Re: Schema consolidation and user schema mapping based on service? [message #517243 is a reply to message #517219]

Mon, 25 July 2011 01:05

Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Have a look at VPD (Virtual Private Database).

Regards
Michel

Report message to a moderator

Re: Schema consolidation and user schema mapping based on service? [message #517248 is a reply to message #517219]

Mon, 25 July 2011 01:34

John Watson
Messages: 8738
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.

Report message to a moderator

Re: Schema consolidation and user schema mapping based on service? [message #517420 is a reply to message #517219]

Mon, 25 July 2011 23:11

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

Report message to a moderator

Re: Schema consolidation and user schema mapping based on service? [message #517422 is a reply to message #517420]

Tue, 26 July 2011 00:45

Michel Cadot
Messages: 68264
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

Report message to a moderator

Re: Schema consolidation and user schema mapping based on service? [message #517746 is a reply to message #517422]

Wed, 27 July 2011 21:19

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

Report message to a moderator

Previous Topic:	Restrict DML on table
Next Topic:	user creation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 17 23:17:38 CDT 2022