SQL Anywhere 12 & 16

I start the database server and databases like this (for SA12):

dbsrv12 -n SERV part1.db part2.db part3.db

I want part2 and part3 to have access to some of the tables in part1

If I start sybase central and connect to part1 and try to grant the tables to part2 and part3

Grant select on part1.testtable to part2

Then I get an error messages stating

"Could not execute statement. User Id part1 'does not exist' SQL code -140 ODBC 3 state = "280000""

IF I remove part1 then it complaints about part2

If I do the same in SA16 then I get the error message

"Could not execute statement. Authentication violation SQLCODE=-98 ODBC 3 State "08001""

So I am obviously doing something wrong, but I do not know what...

asked 25 Nov '13, 09:22

M%20G's gravatar image

M G
629253044
accept rate: 42%

edited 28 Nov '13, 08:41

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654


SQL Anywhere does not allow direct access to other databases from the database that you are connected to. So in your case if you are connected to part1 then you do not have direct access to part2 and part3 databases.

To access the part2 and part3 databases when connected to part1 database you can use Remote Data Services (aka OMNI aka CIS aka Proxy Tables). Basically you need to create a 'proxy' table within Part1 that points to the tables in Part2 and Part3. Refer to the documentation for more information or post another question on this forum if you have specific questions.

permanent link

answered 25 Nov '13, 09:30

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266
accept rate: 40%

... or post another question on this forum if you have specific questions.

Or simply look for questions with the tags proxy-table or remote-data-access - there are several questions here dealing with accessing data from another SQL Anywhere database...

(26 Nov '13, 04:30) Volker Barth

ok, I will look in to this, the databases are started up on the same server so I do not see anything "remote" here, but perhaps that is how you view things in SQL Anywhere..but If I want to access tables in part1 from part2 and part3, then shouldn't the proxy table be located in part2 and part3?

(26 Nov '13, 10:06) M G
Replies hidden
1

...the databases are started up on the same server so I do not see anything "remote" here, but perhaps that is how you view things in SQL Anywhere

Yes, that's certainly a surprise if one is used to a master database concept, i.e. one database server hosting several databases, where users connect to the server and can access several databases at the same time if their permissions are sufficient. That's different with SQL Anywhere - here you connect to a database, not a database engine, so other database (even on the same server) a by design "remote ones".

If I want to access tables in part1 from part2 and part3, then shouldn't the proxy table be located in part2 and part3?

Yes, you are right.

(26 Nov '13, 10:14) Volker Barth

thanks for the clarification, does this means that a user only can be created within a database?

So there is no possibility to create one user which can access several different databases (at the same time)?

(28 Nov '13, 08:21) M G
Replies hidden
1

Correct. There is no "Master" database so users are created within a database.

If you want to create one user that has access to multiple databases then the user must login to one of the databases and create proxy tables to each of the other databases/tables that the user wants to access. Please read the documentation on proxy tables to get a better understanding of how to use them. E.g. read this page to get the steps to create a proxy table: http://dcx.sybase.com/index.html#sa160/en/dbusage/ug-accessrd-sectb-5161644.html

(28 Nov '13, 08:37) Mark Culp
1

Each SQL Anywhere database is a separate entity, there is no master database or the like. Therefore, each database has its own user management. (And therefore you can easily copy a SQLA database to a different machine and start it there - no need to "fix up" orphaned login/user relationships as in MS SQL/ASE...)

However, when using proxy tables, users who exist with the same credentials in the local and the remote database can access remote tables without the need to specify separate "extern logins" for them.

(On a different level, you can use integrated logins to map OS users to the same database user group, and you could do so for each database, and that would allow the same OS user to access several databases, too.)

(28 Nov '13, 08:38) Volker Barth
2

thank you both Mark and Volker, this is a lot different from what I am used to in other databases and I probably will miss it a little, but I have to agree, it is much easier to move/copy a SQL Anywhere database (I was surprised when I first noticed how easy it was, I was prepared for something more complicated)

(28 Nov '13, 08:56) M G
showing 4 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×26
×26
×22
×17
×5

question asked: 25 Nov '13, 09:22

question was seen: 1,868 times

last updated: 28 Nov '13, 08:56