Re: Table Names Query
>From: barry@telerama.lm.com
>Subject: Table Names Query
>Date: 20 Sep 1994 16:47:49 -0400
>X-Informix-List-Id: <news.8812>
> ...
>Logged in as "barry", I cold start Informix Version 5 and run a simple
>database populate program that creates a database, grants DBA access
>to PUBLIC, and then create tables alpha, beta, and gamma. When I use
>dbaccess to inspect the database, I see tables alpha, beta, and gamma.
>When a colleague logged in as "oz" uses dbaccess to inspect the
>database, he sees tables barry.alpha, barry.beta, barry.gamma.
>
>What must I do to ensure that _everyone_ sees tables alpha, beta, and
>gamma? ...
>
>The ESQL/C statements
>
> $CREATE DATABASE foo IN :space WITH LOG MODE ANSI;
> $GRANT DBA TO PUBLIC;
>
>were used to create the database.
>
>A sample table create statement in the database load program is
>
> $CREATE TABLE alpha ( .... ) LOCK MODE ROW;
>From: Toby Denbow <tdenbow@mathworks.com>
>Subject: Re: Table Names Query
>Date: 20 Sep 1994 22:39:46 GMT
>X-Informix-List-Id: <news.8815>
>You must specify table ownership when using CREATE TABLE.
>Example CREATE TABLE "Informix".alpha (...) LOCK MODE ROW;
>Should give you what you want. By not specifying ownership, it defaults to
>the current user.
The only answer to the direct question is "do not use a MODE ANSI database".
Since there are undoubtedly other reasons for using a MODE ANSI database,
the second choice is to revise the requirements:
What must I do to ensure that _everyone_ sees the tables alpha,
beta, and gamma by the same name?
Toby supplies you with the basic answer -- you should ensure that all the
tables, indexes, constraints, synonyms, views, ... are created with a defined
owner, and then everyone should access the objects using the 'owner'.object
notation.
Where I differ from Toby is in the choice of owner, on two counts.
First of all, "Informix".alpha is owned by user "Informix", who is quite
different from user "informix", and also quite different from the owner of
informix.alpha! The quotes make the owner case sensitive; the absence of
quotes in a MODE ANSI database means that the owner is converted to UPPER
CASE.
Secondly, I don't think you should try using "informix" as the surrogate owner.
You would be better off using another login. User "informix" has other jobs to
do than be DBA for a database, and you should distinguish between the informix
role and the DBA role. I have a database called WTS which is owned by and run by
wtsdba -- I create the user wtsdba in /etc/passwd and use that user name when I
need to create a table or whatever. The simplest scheme for naming things uses
a single owner for all the objects in the database -- that will almost certainly
be adequate for a benchmark database. For a major production database, you might
have different owners for different segments of the database; it is imperative that
such a scheme be tightly controlled and carefully documented.
Finally, there is one other possibility, but it is likely to be completely unwieldy.
You could create a (private) synonym for each table for each user of the database.
CREATE SYNONYM 'oz'.alpha FOR 'barry'.alpha;
...
In a MODE ANSI database, all synonyms are private.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>