Tutorials Oracle 10gIn this tutorial you will learn about Synonyms, Creating Synonyms, Using Synonyms in DML Statements, Dropping Synonyms and viewing Information About Views, Synonyms, and Sequences.
This section describes aspects of managing synonyms, and contains the following topics:
A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Also, they are convenient to use and reduce the complexity of SQL statements for database users.
Synonyms allow underlying objects to be renamed or moved, where only the synonym needs to be redefined and applications based on the synonym continue to function without modification.
You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and the user's grantees.
To create a private synonym in your own schema, you must have the CREATE SYNONYM privilege. To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM privilege. To create a public synonym, you must have the CREATE PUBLIC SYNONYM system privilege.
Create a synonym using the CREATE SYNONYM statement. The underlying schema object need not exist, nor do you need privileges to access the object. The following statement creates a public synonym named public_emp on the emp table contained in the schema of jward:
CREATE PUBLIC SYNONYM public_emp FOR jward.emp
Figure 29. Creating a synonym.
When you create a synonym for a remote procedure or function, 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.
You can successfully use any private synonym contained in your schema or any public synonym, assuming that you have the necessary privileges to access the underlying object, either explicitly, from an enabled role, or from PUBLIC. You can also reference any private synonym contained in another schema if you have been granted the necessary object privileges for the private synonym.
You can only reference another user's synonym using the object privileges that you have been granted. For example, if you have the SELECT privilege for the jward.emp_tab synonym, then you can query the jward.emp_tab synonym, but you cannot insert rows using the synonym for jward.emp_tab.
A synonym can be referenced in a DML statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named emp_tab refers to a table or view, then the following statement is valid:
Next Page: Oracle 10g Free Training - Synonyms - Page 2
|
I have a userID/Password in oracle. This user has a number of privileges in other schema objects also. Now, if I have sufficient privileges, I will be able to view other schema objects also from my schema. In that case, I have to write schema_name.object_name in my query. But from my schema I am being able to view a table which is not in my schema, but in some other schema. I am not even giving the schema_name before the table name. How is this possible? An option is that this is a synonym. But I’ve checked out the all_synonyms view and its name is not there in the list. Please explain. I’m totally confused. Also, I deleted one row from the table/synonym without giving its schema name. The deletion is reflected in the table present in the other schema also. Are these two tables same? Then why I am not to give the schema name? If this is a synonym, then why it is not present in all_synonyms view? |
|
I think, you have given the 'sys' level privileges to user.please check your user privileges Thanks and Regards Vikram S. |
