|
Home > Archive > SQL Anywhere database > August 2005 > Any pros & cons of using dbo as application table owner
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Any pros & cons of using dbo as application table owner
|
|
|
| I am trying to use the same schema setup as ASE that I have.
What is the disadvantage of having the application tables in dbo group in
ASA?
thanks
vsv
| |
| Glenn Paulley 2005-08-15, 8:23 pm |
| "vsv" <nospam@nospam.com> wrote in news:43010368@forums
-2-dub:
> I am trying to use the same schema setup as ASE that I have.
> What is the disadvantage of having the application tables in dbo group
in
> ASA?
> thanks
> vsv
>
>
>
SQL Anywhere uses "dbo" as the creator for a variety of "system" objects
that are not formally part of the catalog (which are owned by "sys"). For
example, several tables required by the index consultant are owned by
dbo.
It is probably not a good idea to pollute the set of objects owned by
"dbo" with user tables - but on the other hand I can't, off the top of my
head, give you a stronger reason to not use "dbo".
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
| |
| Breck Carter [TeamSybase] 2005-08-15, 8:23 pm |
| IMO folks who are familiar with ASE should *not* use the dbo user id
in ASA because it does not work the same way... it will just confuse,
and it is better to become familiar with the way ASA works.
In particular, dbo is not a real login id in ASE, but it is in ASA.
What most folks do is use DBA as the owner of all application tables,
then GRANT GROUP TO DBA and GRANT MEMBERSHIP IN GROUP DBA TO
other_users so that everyone can write table_name instead of
DBA.table__name. Members do not inherit DBA privileges so other GRANTs
are also required, for *permissions*, just like in ASE.
Breck
On 15 Aug 2005 14:04:44 -0700, "vsv" <nospam@nospam.com> wrote:
>I am trying to use the same schema setup as ASE that I have.
>What is the disadvantage of having the application tables in dbo group in
>ASA?
>thanks
>vsv
>
--
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
| |
|
| One possible downside to having DBA own the tables is that occasionally some
tools (MS Query used to be one, not sure lately) insist on generating SQL
that automatically references all tables in the format "owner.tablename" and
do not quote the owner name - for example, dba.my_table_name. Since DBA is
a reserved word in ASA, it must be quoted and since it was not, this caused
SQL errors.
I recall years ago having to send out scripts to our customers to recreate
and rebuild all of our tables so they wouldn't be owned by DBA so MS Query
would work (since there was no way to change the owner of a table it had to
be rebuilt).
(IMHO, it was shortsighted to make the default owner name be the same as a
reserved word, but that goes back to the Watcom days...)
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:3d22g1l8tlgi92f
adp5mumel9aevfldo37@
4ax.com...
> IMO folks who are familiar with ASE should *not* use the dbo user id
> in ASA because it does not work the same way... it will just confuse,
> and it is better to become familiar with the way ASA works.
>
> In particular, dbo is not a real login id in ASE, but it is in ASA.
>
> What most folks do is use DBA as the owner of all application tables,
> then GRANT GROUP TO DBA and GRANT MEMBERSHIP IN GROUP DBA TO
> other_users so that everyone can write table_name instead of
> DBA.table__name. Members do not inherit DBA privileges so other GRANTs
> are also required, for *permissions*, just like in ASE.
>
> Breck
>
> On 15 Aug 2005 14:04:44 -0700, "vsv" <nospam@nospam.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
| |
| Breck Carter [TeamSybase] 2005-08-17, 9:27 am |
| DBA is *not* a reserved word.
On 16 Aug 2005 18:08:32 -0700, "JJ" <jj@nospam.com> wrote:
>One possible downside to having DBA own the tables is that occasionally some
>tools (MS Query used to be one, not sure lately) insist on generating SQL
>that automatically references all tables in the format "owner.tablename" and
>do not quote the owner name - for example, dba.my_table_name. Since DBA is
>a reserved word in ASA, it must be quoted and since it was not, this caused
>SQL errors.
>
>I recall years ago having to send out scripts to our customers to recreate
>and rebuild all of our tables so they wouldn't be owned by DBA so MS Query
>would work (since there was no way to change the owner of a table it had to
>be rebuilt).
>
>(IMHO, it was shortsighted to make the default owner name be the same as a
>reserved word, but that goes back to the Watcom days...)
>
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:3d22g1l8tlgi92f
adp5mumel9aevfldo37@
4ax.com...
>
--
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
| |
| Stephen Rice 2005-08-17, 9:27 am |
| Breck Carter [TeamSybase] wrote:
> DBA is *not* a reserved word.
>
> On 16 Aug 2005 18:08:32 -0700, "JJ" <jj@nospam.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
it was way back in v5.5. I don't recall when it was changed
/steve - ever notice the cops are getting younger
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com
| |
| Breck Carter [TeamSybase] 2005-08-17, 1:23 pm |
| Say what? This works fine on 5.5.05.2787
create table dba.dba ( dba integer );
select * from dba;
On 17 Aug 2005 05:18:03 -0700, Stephen Rice <NSsrice@ianywhere.com>
wrote:
>Breck Carter [TeamSybase] wrote:
>it was way back in v5.5. I don't recall when it was changed
>
>/steve - ever notice the cops are getting younger
--
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
| |
| Breck Carter [TeamSybase] 2005-08-17, 1:23 pm |
| DBA *is* listed in the 5.5.0.4 Help...
SQL Anywhere User's Guide
PART 6. SQL Anywhere Reference
CHAPTER 48. SQL Anywhere Keywords
Alphabetical list of keywords
but if memory serves that list was not reliable as a list of
*reserved* words.
Certainly there has never been a requirement to doublequote "DBA" as
an owner name, not in my (failing) memory :)
Breck
On 17 Aug 2005 05:18:03 -0700, Stephen Rice <NSsrice@ianywhere.com>
wrote:
>Breck Carter [TeamSybase] wrote:
>it was way back in v5.5. I don't recall when it was changed
>
>/steve - ever notice the cops are getting younger
--
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
| |
| Stephen Rice 2005-08-18, 9:40 am |
| I went back and tried it too and you're right (should have known better :)
/steve
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com
Breck Carter [TeamSybase] wrote:
> DBA *is* listed in the 5.5.0.4 Help...
>
> SQL Anywhere User's Guide
> PART 6. SQL Anywhere Reference
> CHAPTER 48. SQL Anywhere Keywords
> Alphabetical list of keywords
>
> but if memory serves that list was not reliable as a list of
> *reserved* words.
>
> Certainly there has never been a requirement to doublequote "DBA" as
> an owner name, not in my (failing) memory :)
>
> Breck
>
> On 17 Aug 2005 05:18:03 -0700, Stephen Rice <NSsrice@ianywhere.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
OK so I went back and tried it too and you're right (should have known
better :)
/steve
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com
| |
| Breck Carter [TeamSybase] 2005-08-18, 11:23 am |
| While we're on the subject of remembering days of yore, PL/I was
renowned for being free of reserved words. The following repetitive
loop
DO I = -10 TO 5 BY 5;
could be written thusly, assuming the corresponding variables existed:
DO DO = TO TO BY BY BY;
AFAIK there's never been a parser quite like PL/I... reserved words
are a compiler writer's *crutch* <grad>
Breck Also Remembers Hendrix, Joplin, Morrison ...
--
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
|
|
|
|
|