| A synonym is merely another name for a table or a view. Synonyms are usually created so that a user can avoid having to qualify another user's table or view to access the table or view. Synonyms can be created as PUBLIC or PRIVATE. A PUBLIC synonym can be used by any user of the database; a PRIVATE synonym can be used only by the owner and any users that have been granted privileges. | | Synonyms are used by several major implementations. Synonyms are not ANSI SQL standard; however, because synonyms are used by major implementations , it is best to discuss them briefly . You must check your particular implementation for the exact use of synonyms, if available. Also note, however, that synonyms are not supported in MySQL. | Managing Synonyms Synonyms are either managed by the database administrator (or another designated individual) or by individual users. Because there are two types of synonyms, PUBLIC and PRIVATE, different system-level privileges may be required to create one or the other. All users can generally create a PRIVATE synonym. Typically, only a DBA or privileged database user can create a PUBLIC synonym. Refer to your specific implementation for required privileges when creating synonyms. Creating Synonyms The general syntax to create a synonym is as follows : CREATE [PUBLICPRIVATE] SYNONYM SYNONYM_NAME FOR TABLEVIEW You create a synonym called CUST, short for CUSTOMER_TBL, in the following example. This frees you from having to spell out the full table name. CREATE SYNONYM CUST FOR CUSTOMER_TBL; Synonym created. SELECT CUST_NAME FROM CUST; CUST_NAME ---------------------------- LESLIE GLEASON NANCY BUNKER ANGELA DOBKO WENDY WOLF MARYS GIFT SHOP SCOTTYS MARKET JASONS AND DALLAS GOODIES MORGANS CANDIES AND TREATS SCHYLERS NOVELTIES GAVINS PLACE HOLLYS GAMEARAMA HEATHERS FEATHERS AND THINGS RAGANS HOBBIES INC ANDYS CANDIES RYANS STUFF 15 rows selected. It is also a common practice for a table owner to create a synonym for the table to which you have been granted access so that you do not have to qualify the table name by the name of the owner: CREATE SYNONYM PRODUCTS_TBL FOR USER1.PRODUCTS_TBL; Synonym created. Dropping Synonyms Dropping synonyms is like dropping most any other database object. The general syntax to drop a synonym is as follows: DROP [PUBLICPRIVATE] SYNONYM SYNONYM_NAME The following is an example: DROP SYNONYM CUST; Synonym dropped. |