PUBLIC and PRIVATE Synonyms

If you use the PRIVATE keyword to declare a synonym in a database that is not ANSI-compliant, the unqualified synonym can be used by its owner. Other users must qualify the synonym with the name of the owner.

If you use the PUBLIC keyword (or no keyword at all), anyone who has access to the database can use your synonym. If the database is not ANSI-compliant, a user does not need to know the name of the owner of a public synonym.

In an ANSI-compliant database, all synonyms are private. If you use the PUBLIC or PRIVATE keywords, the database server issues a syntax error.

Examples of PRIVATE and PUBLIC synonyms

Suppose that in a database that is not ANSI-compliant, user primus issues the following SQL statements:

CREATE SEQUENCE IF NOT EXISTS MySequence 
   INCREMENT BY 1 START WITH 1 
   MAXVALUE 8000 MINVALUE 0 
   NOCYCLE CACHE 20 ORDER; 

CREATE PRIVATE SYNONYM IF NOT EXISTS anaphora FOR MySequence;

Now user primus is the owner of two new objects in the current database:

a sequence object called MySequence,
and a private synonym called anaphora for the MySequence sequence.

Suppose that in the same database, user primus also issues the following SQL statements

to create a permanent table called twoSmall,
and to declare litotes as a private synonym for the twoSmall table:

CREATE TABLE IF NOT EXISTS twoSmall (c1 SMALLINT, c2 SMALLINT); 
INSERT INTO twoSmall VALUES (0, 0); 

CREATE PRIVATE SYNONYM litotes FOR twoSmall;

In the same database that is not ANSI-compliant, another user could reference both the litotes and anaphora synonyms in the following INSERT statement, where twoSmall is the target table:

INSERT INTO primus.litotes (col1, col2) 
   VALUES (primus.anaphora.NEXTVAL, primus.anaphora.NEXTVAL);

Although both synonyms in the INSERT statement were created as PRIVATE, the user can access the objects that those synonyms reference by qualifying the synonym names with the authorization identifier of primus, who is the owner of those synonyms.

If instead of creating the private synonyms in the previous examples, user primus had implicitly or explicitly created both anaphora and litotes as PUBLIC synonyms, any other user who held sufficient access privileges could omit the authorization identifier of primus in the previous INSERT statement:

INSERT INTO litotes (col1, col2) 
   VALUES (anaphora.NEXTVAL, anaphora.NEXTVAL);

In a database that was not created as MODE ANSI, the following example shows the syntax for user primus to create synecdoche as a PUBLIC synonym for the MySequence object:

CREATE SYNONYM IF NOT EXISTS synecdoche FOR MySequence;

In the example above, synecdoche is a public synonym by default, because neither the PUBLIC nor PRIVATE keyword was specified.

In the same database, the next statement creates zeugma as an explicit PUBLIC synonym:

CREATE PUBLIC SYNONYM IF NOT EXISTS zeugma FOR MySequence;

In the same database, the next statement creates pleonasm as a PRIVATE synonym for the same sequence object:

CREATE PRIVATE SYNONYM IF NOT EXISTS pleonasm FOR MySequence;

In a database created as MODE ANSI, the next statement creates asyndeton as a PRIVATE synonym, because ANSI-compliant databases do not support PUBLIC synonyms:

CREATE SYNONYM asyndeton FOR MySequence;

In a database created as MODE ANSI, the next statement fails with a syntax error, because the CREATE SYNONYM statement does not support the PRIVATE or PUBLIC keywords in ANSI-compliant databases:

CREATE PRIVATE SYNONYM litotes FOR MySequence;

For information about the scope of reference of the identifiers of public and private synonyms in databases that are not ANSI-compliant, see the topic Synonyms with the Same Name.