Oracle Private vs Public Synonyms – Advantages Difference and Features

  • A normal synonym is called private synonym whereas a public synonym is created by a keyword public.
  • A private synonym is accessible within your schema and a public synonym is accessible to any schema in the database.
  • CREATE SYNONYM system privilege is required to create a private synonym and CREATE PUBLIC SYNONYM system privilege is required to create a public synonym
  • When you create a synonym for a remote object (function/procedure), you must qualify the remote object with its schema name. Alternatively you can create a local public synonym on the database where the remote object resides, in which case the database link must be included in all subsequent calls to the procedure or function
  • Synonym is an alias for a schema object
  • Synonym can create a level of security by hiding the owner name and exact object name
  • Synonyms are convenient to use and reduces the complexity of the SQL statements for the database users.
  • A public synonym is owned by a special user group named PUBLIC and is accessible to every user in the database.
  • When you grant object privilege on a synonym, you are really granting privileges on the underlying object, and the synonym is acting only as an alis of the object in the GRANT statement.
  • A private synonym can be dropped by a DROP SYNONYM <synonym name> whereas a public synonym can be dropped by DROP PUBLIC SYNONYM <synonym name> command
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google