Grant select to another database/schema

[Former Member]

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...

[MarkCulp]

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.

[VolkerBarth]

... 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...

[Former Member]

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?

[VolkerBarth]

...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.

[Former Member]

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)?

[MarkCulp]

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

[VolkerBarth]

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.)

[Former Member]

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)