SQL Anywhere database - migrating ase-> asa

Author

migrating ase-> asa

P Merritt

2005-12-27, 8:23 pm

I'm trying to migrate a 60 table database from ase to asa 9.0.2. The
database is owned by dbo on ase. I create a database owned by dba in asa.
The migration moves all the tables go across but if I close the Central
connection to the new database I cannot re-open it; I get an error about not
supporting integrated logins. If I give my w2003 userid ownership as part of
the migration, I have no problems re-opening the database in Central but
none of my application's SQL works; I have to revise it by appending that
userid to all the table names (which is not nice.. a lot of code to change).
Any suggestions?

Breck Carter [TeamSybase]

2005-12-28, 9:23 am

Ownership and authorization works differently in ASA; no harder, just
different. So far, you've done the right thing (dba owns everything),
you just need to do a couple more steps:

GRANT GROUP TO DBA; -- members do not inherit "DBA" privilege
GRANT SELECT ON t1 TO DBA; -- members do inherit "SELECT" privilege
GRANT SELECT ON t2 TO DBA;
....

GRANT MEMBERSHIP IN GROUP DBA TO u1; -- inherits "SELECT" privilege
GRANT MEMBERSHIP IN GROUP DBA TO u2;
....

The users u1, u2, ... inherit two things: the ability to refer to
table names with out the "DBA." prefix, and the privilege to actually
SELECT rows.

Breck

On 27 Dec 2005 15:14:05 -0800, "P Merritt"
< pmerritt@transcendon
line.com> wrote:

>I'm trying to migrate a 60 table database from ase to asa 9.0.2. The
>database is owned by dbo on ase. I create a database owned by dba in asa.
>The migration moves all the tables go across but if I close the Central
>connection to the new database I cannot re-open it; I get an error about not
>supporting integrated logins. If I give my w2003 userid ownership as part of
>the migration, I have no problems re-opening the database in Central but
>none of my application's SQL works; I have to revise it by appending that
>userid to all the table names (which is not nice.. a lot of code to change).
>Any suggestions?
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

P Merritt

2005-12-28, 8:23 pm

no such luck; using the version of the database where u1 has ownership of
the tables, I granted select on all tables to both DBA and the web user but
I'm getting "Table or view not found". (I granted GROUP to DBA but not to
the web user..)

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:fla5r1l0rg95k84
5tjr6fq2amdn14j40pu@
4ax.com...
> Ownership and authorization works differently in ASA; no harder, just
> different. So far, you've done the right thing (dba owns everything),
> you just need to do a couple more steps:
>
> GRANT GROUP TO DBA; -- members do not inherit "DBA" privilege
> GRANT SELECT ON t1 TO DBA; -- members do inherit "SELECT" privilege
> GRANT SELECT ON t2 TO DBA;
> ...
>
> GRANT MEMBERSHIP IN GROUP DBA TO u1; -- inherits "SELECT" privilege
> GRANT MEMBERSHIP IN GROUP DBA TO u2;
> ...
>
> The users u1, u2, ... inherit two things: the ability to refer to
> table names with out the "DBA." prefix, and the privilege to actually
> SELECT rows.
>
> Breck
>
> On 27 Dec 2005 15:14:05 -0800, "P Merritt"
> < pmerritt@transcendon
line.com> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Greg Fenton

2005-12-29, 3:23 am

P Merritt wrote:
> no such luck; using the version of the database where u1 has ownership of
> the tables, I granted select on all tables to both DBA and the web user but
> I'm getting "Table or view not found". (I granted GROUP to DBA but not to
> the web user..)
>

Breck has the right idea, but based on the info you just provided try:

GRANT GROUP TO u1; -- make "u1" into a group
GRANT MEMBERSHIP IN GROUP u1 TO DBA;
GRANT MEMBERSHIP IN GROUP DBA TO web_user;

The thing is that both DBA and web_user cannot "see" the tables owned by
u1 unless they belong to the group "u1". Alternatively, DBA and
web_user could do:

SELECT * FROM "u1"."t1"

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links