Table name is ambiguous

[Former Member]

I have migrated an old database to SA 12. The database contains tables with the same names but different owners. Because of that I get the error 'Table name xx is ambiguuous'.

Is it possible to change permissions of users in the Sybase Central so that the user can access only the table whose owner it is and the above error does not occur?

I tried to create two groups in Sybase Central and restricted the tables from the 'Table Permissions', but it does not seem to help. Am I doing something wrong?

Edit: After reading Volker Barth's answer I wanted to explain bit more about the problem:

Yes, the problem is causing because the owner of the table is not specified in the application. Please consider following structure:

Tables:

table1  owner user1
table1  owner user2
table2  owner user1

As you can see there are some tables with unique names, and some have the same name but different owner.

Now there are two groups. both of them have membership to the user1 because both need to use tables owned by both users. Here's example of my groups:

Groups:
group1
group2

Group Memberships:
group1 membership: user1, PUBLIC
group2 membership: user1, PUBLIC

Users:
userA 
userB

User Memberships:
userA  (group1, PUBLIC)
userB (group2, PUBLIC)

Now if I create the users in the group1, it will NOT have any problem, and it will always use the table1 owned by the user1. However, I need to create a user in group2, so that it can use table 1 owned by the user2 as well as table2 owned by user1.

So, Is it possible to give permission to the group2 of the tables owned by user2 and ONLY those tables of user1 which do not have same name as tables owned by user1? I hope my question makes some sense.

I'll highly appriciate any help regarding this problem.

[VolkerBarth]

In your sample, group1 and group2 seem to have same membership - I guess group2 (or userB) should have access to user2's tables as well? And are user1 and user2 groups as well?

That being said, are you really asking "Is it possible to give permission...?" or "Is it possible to use table names without qualification..."? - IMHO, for the latter question, the answer is "no" for your particular situation.

A workaround would be to "duplicate" those tables from user1 that don't exist for user2, say simply by adding same-name views, such as

create view user2.table2 as select * from user1.table2

Then userB might not need to be a member of user1 at all. That may or may not be useful/performant.

[Former Member]

Thanks again. Yes, user1 and user2 are groups as well. Yes, duplicating the tables solve the problem. (I duplicated the tables by copying and pasting with different owner), but I think your suggestion of creating views is better. About the views, suppose i have 100 tables which are owned by user1 and are needed to duplicate for the 10 users. Does it mean I will need to make 1000 views? In that situation, would it be better to to phyically duplicate the tables in the database?

[VolkerBarth]

I can't tell that - I guess you will have to tell more why you are using same-name tables for different users at all...is this same kind of multi-tenant situation?

Or in other words: Is there a need to omit the qualification - i.e. do you really need that userA does access a different table then userB?

Copying all these tables or adding "dummy" views seems somewhat overkill from my remote perspective...

Just my 2 cents

[VolkerBarth · Accepted solution]

I still can't give good advice without knowing more details. Just two more suggestions:

1. In case you have lots of tables that can be shared between all users, and only some need to be "different" for all users, you might use one owner for the common tables and separate owners for the "different" table sets. Assumed all owners are groups, you could then organize your user groups as following:
2. groupA belongs to ownerShared, ownerA
3. groupB belongs to ownerShared, ownerB
4. and so on.

That would help to build unambiguous table name sets for all groups, and accessing tables without spcifying the owner name should work.

1. A different (and much more common) approach to organize "different table contents" for different users (say, let particular user only access rows/columns with contents in the fitting language) would be to use views to filter based on rows/columns.

Some hints can be found here:

• This FAQ: Limit table content by user group/profiles/rules?
• From Breck's blog: SQL Anywhere: Example: Using Views For Row-Level Security

[Former Member · Accepted solution]

I maneged to make it work using your first suggestion. I devided tables into 3 owners. 1 owner for common, and some tables for group1 and others for group2. Some of the datawindows were using the procedures, so I had to change their ownership (and copy for same procedure for group1 and group2), bit complicated but at the end i made it work. Thank you for the great help.

[VolkerBarth]

I assume the error message results from accessing the table without specifying the owner name, as the combination of owner and object name MUST be non-ambiguous.

There are the different concepts of visibility/name scope vs. permissions (note: there may be better terms for these notions) that do matter here:

1. Visibility ("scope"): You can "see" any database object without specifying the owner name (i.e. without a "qualified" name like "GROUPO.Products") if you are the owner of the object or are member of a group that owns the object. For that reason, ambiguities may result if a user U belongs to two different groups and each group owns an object with the same name, say, "GROUPO.Products" and "GROUPR.Products". Now, if U tries to access "select * from Products" this will fail. - There's nothing you can do about that except modifying the group membership or using a qualified name, i.e. adding the owner name.

2. Permission: The above is totally independent of the question whether a user is allowed to access the database object in the desired way (SELECT, INSERT, EXECUTE, whatever). This is managed by database permissions, in particular by GRANT and REVOKE. And there are particular permissions, such as the owner's imoplicit permission to do anything with its objects.

The docs handle this well IMHO, see Managing user IDs, authorities, and permissions and in particular Database object names and prefixes.

[VolkerBarth]

Just to add: The according problem description for error message -852 is as helpful as possible:

A statement has referred to a table name that is not unique. Preface the table name by an owner name.

[Former Member]

@Volker Barth Thanks for the detailed answer. I added some details in my question, please have a look.