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. |
I still can't give good advice without knowing more details. Just two more suggestions:
That would help to build unambiguous table name sets for all groups, and accessing tables without spcifying the owner name should work.
Some hints can be found here:
Comment Text Removed
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.
(05 Jun '12, 03:31)
sam
|
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:
The docs handle this well IMHO, see Managing user IDs, authorities, and permissions and in particular Database object names and prefixes. Just to add: The according problem description for error message -852 is as helpful as possible:
(25 May '12, 06:39)
Volker Barth
|
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
Then userB might not need to be a member of user1 at all. That may or may not be useful/performant.
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?
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