ORACLE Synonyms (SYNONYM)

ORACLE Synonyms (SYNONYM)

The following content is compiled from the official Oracle documents.

Concept 1

A synonym is an alias for any table, view, materialized view, sequence, procedure, function, package, type, Java classschema object, user-defined object type, or another synonym. because a synonymis simply an alias, it requires no storage other than its definition in thedata dictionary.

Synonyms in Oracle are aliases of any table, view, materialized view, sequence, stored procedure, function, package, type, JAVA class object, user-defined type, or other synonyms. Because it is only an alias

The definition in the data dictionary does not occupy any space.

 

Synonyms are often used for security andconvenience. For example, they can do the following:

Synonyms are commonly used for security and convenience. For example, they can do:

1. Mask the name and owner of anobject disguise object name and its owner.

2. Provide location transparency for remoteobjects of a distributed database provides location transparency for distributed database Remote objects

3. Simplify SQL statements for databaseusers

4. Enable restricted access similar tospecialized views when exercising fine-grained access control

 

 

You can create both public and privatesynonyms. A public synonym is owned by the special user group named PUBLIC andevery user in a database can access it. A private synonym is in the schema of aspecific user who has control over its availability to others.

You can create public and private synonyms. Public synonyms belong to the PUBLIC group and can be accessed by every user. Private synonyms belong to the object owner. They can only be accessed by other users after explicit authorization.

 

Synonyms are very useful in bothdistributed and nondistributed database environments because they hide theidentity of the underlying object, including its location in a distributedsystem. this is advantageous because if the underlying object must be renamedor moved, then only the synonym needs to be redefined. applications based onthe synonym continue to function without modification.

The advantage of a synonym is that if the underlying object is renamed or transferred, you only need to redefine the synonym. Applications Based on this synonym do not need to be modified.

 

Synonyms can also simplify SQL statementsfor users in a distributed database system. the following example shows how andwhy public synonyms are often created by a database administrator to hide theidentity of a base table and reduce the complexity of SQL statements. assume thefollowing:

The following example illustrates how synonyms simplify user access:

 

A table called SALES_DATA is in the schemaowned by the user JWARD.

The JWARD user has a table SALES_DATA.

 

The SELECT privilege for the SALES_DATAtable is granted to PUBLIC.

The PUBLIC group has the SALES_DATA query permission.

 

At this point, you have to query the tableSALES_DATA with a SQL statement similar to the following:

In this case, you need the following statement to query the SALES_DATA table:

SELECT * FROM jward. sales_data;

 

Notice how you must include both the schemathat contains the table along with the table name to perform the query.

 

Assume that the database administratorcreates a public synonym with the following SQL statement:

Assume that the database administrator has created a public synonym:

 

Create public synonym sales FORjward. sales_data;

 

After the public synonym is created, youcan query the table SALES_DATA with a simple SQL statement:

Your statement will be simplified:

 

SELECT * FROM sales;

 

Notice that the public synonym SALES hidesthe name of the table SALES_DATA and the name of the schema that contains thetable.

 

 

2. create synonym create Synonyms

1. syntax structure:

 

2. Prerequisites:

To create a private synonym in your own schema, you must haveCREATE SYNONYMSystem privilege.

To create a private synonym in your own modeCREATE SYNONYMPermission.

To create a private synonym in another user's schema, you must haveCREATE ANY SYNONYMSystem privilege.

To create a private synonym in other user modesCREATE ANY SYNONYMPermission.

To createPUBLICSynonym, you must haveCREATE PUBLIC SYNONYMSystem privilege.

To create a public synonym, you must haveCREATE PUBLIC SYNONYMPermission.

 

3. Example

Sqlplus/as sysdba

 

Create synonym offices

FOR hr. locations;

Grant select on hr. locations to SCOTT;

 

Create public database link l_hr

Connect to hr identified by hr

USING 'orcl ';

Create public synonym emp_table

FORHR. employees @ l_hr;

Grant select on hr. employees to SCOTT;

Conn scott/tiger @ orcl

SELECT count (*) from sys. offices;

Select count (*) from emp_table;

3. drop synonym to delete Synonyms

1. syntax structure:

 

2. Prerequisites:

To drop a private synonym, either the synonym must be in your own schema or you must haveDROP ANY SYNONYMSystem privilege.

To delete a private synonym, you must haveDROP ANY SYNONYMPermission.

To dropPUBLICSynonym, you must haveDROP PUBLIC SYNONYMSystem privilege.

To delete a public synonym, you must haveDROP PUBLIC SYNONYMPermission.

3. Example

3.1 to delete a public synonym, the public keyword must be added:

SYS @ orcl> drop synonym emp_table;

DROPSYNONYM emp_table

*

Row 3 has an error:

ORA-01434: The dedicated synonym to delete does not exist

 

 

SYS @ orcl> drop public synonym emp_table;

 

The synonym has been deleted.

 

3.2 delete a private synonym:

Drop synonym offices;

 

Iv. Q &

 

Q: Can I INSERT, UPDATE, or DELETE synonyms?

A:

SCOTT @ orcl> UPDATE sys. offices t SETt. city = 'shanghai' WHERE location_id = 1000;

UPDATE sys. offices t SET t. city = 'shanghai' WHERE location_id = 1000

*

Row 3 has an error:

ORA-01031: insufficient Permissions

SYS @ orcl> grant update on hr. locationsto scott;

 

Authorization successful.

SCOTT @ orcl>/

 

1 row updated.

 

SO: the user's operation permissions on synonyms are based on the Operation permissions on the underlying objects.

 

 

 

 

-------------------------------

Dylan Presents.