It’s not uncommon to set up a special Oracle account to access data in another schema. This is common practice and a good practice, especially when it comes to reporting users.

You want to give a reporting user a limited view of the system; only SELECT access on known interface tables or only SELECT on tables in exactly one other schema. This helps protect the usually powerful schema owner (ACTUAL_SCHEMA) from being widely disseminated. This won’t be new to any bayon blog readers.

The question is:

How does one set up an OWB Oracle Data Source Module (ACTUAL_SCHEMA) that uses one username and password (LOW_PRIV_USER) for authentication but access the objects needed (ACTUAL_SCHEMA)?

The answer is:

You can’t in OWB 10gR1, but you can in OWB 10gR2 (in beta). Note: OWB Product Management emailed a solution in 10gR1 (see below)

I’d love to be wrong, so if anyone would like to send along a solution I’d be happy to post it here.

I reviewed the link area (for importing metdata), nothing! Ok, so perhaps it’s just for metadata. It’s not the end of the world as long as we can register when a different username/schema pair.

However, you can’t register using a different schema than the user either.

Last thought was to try and “Configure” a location, but you can’t do that.

I’m starting to sound like an Oracle employee, but the next version is great. This whole process was a snap with Paris.

If anyone has figured out how to do it in 10gR1 please email me and let me know.

UPDATE: The way to accomplish this in OWB 10gR1 is to configure the mapping to not use the LOCATION but to use a custom DBLink or schema prefix instead. I’ll try it and add a screenshot of this process, but basically this allows you to override the location for every mapping. Thanks Nikolai!