multiple tables with same name but different owners

[naushi]

We have 3 tables all called 'customer' with owners A B & C. The problem is creating synonyms for all the tables. Each has a different set of users but users may be working with all the tables. We want to make the names as transparent as possible to the user. Can someone suggest how we handle the naming of synonyms for these tables. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[stefes007]

you could give access to your users to a view of the tables they need ( if these tables are joinable ) and then make an alias on the view. Dekeyzer Stephane.
Belgium

 

[Turkbear]

Hi,
Use public synonyms like
A_Customer, B_Customer, C_Customer
so the user will know which customer table is 'in play'.

On a curiosity note: Why on earth would you want to have such a situation? The possiblilty of confusion is immense..

 

[naushi]

You are right but three different applications merged into one database. Problem is user wants to use one name and is leaving the burden on us to make it transparent to them. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[Turkbear]

Well, one name cannot refer to multiple things for the same user - how would the parser know what to use?..
The only other option is to merge the 3 customer tables into one named new_customer, for instance, and use that one from now on...

 

[naushi]

. One set of users say user1 thru 10 uses A_customer 11-20 uses B_customer and 21 -30 uses C-customer. If we create private synonym for each with the same name 'customer' under a role (if possible) and then grant access to the appropriate set of users. So when they use their own table they use customer and when they go to other shema's table they use the actual qualifier like B.customer or C.customer. Now the question is how private synonyms work. Who should create the private synonym and how we give access to the approriate set of users. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[rcurva]

One solution there is to merge the 3 tables into one view, and then group these users into a new table, say USER_GROUPS.

Your view should have some thing like;

CREATE VIEW samp_vi
AS
SELECT *
FROM CUSTOMERS_A
WHERE USER IN (SELECT USERNAME
FROM USER_GROUPS
WHERE GROUP_CD = 'A')
UNION ALL
SELECT *
FROM CUSTOMERS_B
WHERE USER IN (SELECT USERNAME
FROM USER_GROUPS
WHERE GROUP_CD = 'B')
UNION ALL
SELECT *
FROM CUSTOMERS_B
WHERE USER IN (SELECT USERNAME
FROM USER_GROUPS
WHERE GROUP_CD = 'B');


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

Try to use

ALTER SESSION SET CURRENT_SCHEMA=<application owner>

at application startup. In this case the name CUSTOMER will be resolved as <application owner>.CUSTOMER. I suppose user do not access all the tables within one application.

 

[jee]

The use of private synonyms could work, if each user only uses one specific customer table. Unfortunately each user would need his/her own private synonym (can't be set up under roles). Either each user would have to create their own synonym OR someone with the CREATE ANY SYNONYM system priv. could create them all.

However you indicated that some users have a need to access more than one customer table. In this case they could use the synonym for the one they access the most and then if they have to access one of the others, fully qualify the table with the schema name.

Note that the Union solution given previously will only work if all of the customer tables have the same layout.

 

[naushi]

Jee - I buy your suggestion about private synonyms since tables cant be merged. I could create a synonym for each rable and make the apropriate table a default for each user. But how would some one with 'create any synonym' privilege create a private synonym and allow other users to use it. Coudl you expalin Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[rcurva]

Have in mind that if you're going to use PRIVATE synonyms, you may need to create N nos. of private synonyms for N nos. of users.

This is not a problem if you are talking about 2 to 10 users. However, if we are talking about 100+ users here, maintenance will be a problem.
Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[jee]

If you decide to use the private synonyms, the command is as follows:

CREATE SYNONYM user1.customer FOR schema1.customer;

Where user1 is the user ID and schema1 is the schema of the customer table that user1 accesses the most.

Now when user1 does something like this:

select * from customer;

He/she will be accessing the schema1.customer table.