Re: Non-ANSI Informix databases and Intergraph

Alan Popiel asked: >->Date: Thu, 2 Jun 94 08:19:20 PDT >->From: johnl@informix.com (Jonathan Leffler) >->To: alan@po.den.mmc.com, hugh@nezsdc.icl.co.nz, informix-list@rmy.emory.edu >->Subject: Re: Non-ANSI Informix databases and Intergraph >-> >->>Date: Wed, 1 Jun 94 08:35:58 MDT >->>From: alan@po.den.mmc.com (Alan Popiel) >->>Subject: Re: Non-ANSI Informix databases and Intergraph >-> >->>Question to Informix, Inc.: Do/will newer versions of Informix support the >->>concept of *public* synonym? In Oracle, a user with DBA privilege can say: >->> CREATE PUBLIC SYNONYM tablename FOR owner.tablename; >->>This eliminates the need for each user to do the same, and would be a big >->>help in situations like Hugh's. >-> >->All synonyms are public unless you say CREATE PRIVATE SYNONYM... >-> >->This has been true ever since 4.00. >Good morning, Jonathan, > >Are you sure about the version? I have checked my 4.00 SQL Ref. Manual and >User Guide. I find no reference in either to the keyword PRIVATE, nor to >PUBLIC SYNONYMs. Other than the 4.10 4GL supplement, these are my most recent >docs; hence my question about newer versions. > >I have not used MODE ANSI databases so I can't be certain, but it would seem >to me that synonyms would default to "private" in such a DB. Like tables, >they would need to be refered to as owner.synonym by non-owners. This would >still require the "owner...." syntax that Hugh was hoping to avoid. To clarify. Before version 4.00, synonyms were handled via the syssynonyms system table. With 4.00 and above, synonyms are handled via the syssyntable table (and syssynonyms is redundant!). With versions 4.00 and 4.10, there are no private synonyms. See the Informix Guide to SQL Reference Version 4.10, p6-43, where it says: "A synonym is valid for all users, not just the creator." With version 5.00 and above, you can specify private synonyms or public synonyms, and the SQL reference p7-63 modifies the sentence to read: "A public synonym is ..." The systables table distinguishes between public and private synonyms using the tabtype column: S denotes a public synonym, and P a private one. I don't have a copy of the 4.00 documentation at hand, but it should say basically the same as the 4.10 documentation. The 5.00 Guide to SQL has the additional comment: "[In a MODE ANSI database,] synonyms are always private. If you use the PUBLIC or PRIVATE keywords, you receive a syntax error." The 4.10 Guide has no comments about synonyms being private in a MODE ANSI database. Alan is right -- I hadn't made myself clear. For the most part, I'm right too -- if you can't use the notation CREATE PRIVATE SYNONYM, you can't create a private synonym and the synonym is public. I had forgotten that the public/private keywords in the CREATE SYNONYM syntax were new with 5.00, and I hadn't realised that you couldn't create a public synonym in a 5.00 or later MODE ANSI database. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>