ORACLE synonyms (synonym) detailed

The following are organized from Oracle's official documentation

A concept

A synonym is an alias for any table, view,materialized view, sequence, procedure, function, package, type, Java Classschem A 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 for any table, view, materialized view, sequence, stored procedure, function, package, type, Java class object, user-defined type, or other synonym. Because it's just an alias, so in addition to the

The definitions in the data dictionary do not occupy any space.

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

Synonyms are often used for safety and convenience. For example, they can do:

1. Mask the name and owner of the AnObject masquerading object name and its owners.

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

3. Simplify SQL statements for Databaseusers simplifying database user access to object SQL statements

4. Enable restricted access similar tospecialized views when exercising fine-grained access control provides access similar to the specified view when exercising fine-grained access controls Limit

You can create both public and privatesynonyms. A public synonym are 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 have control over their availability to others.

You can create public and private synonyms. The public synonym belongs to the public group, which is accessible to each user. Private synonyms belong to the owner of the object and are accessible only to other users after they have been explicitly authorized.

Synonyms is 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 being Renamedor moved, then only the synonym needs to be redefined . Applications based onthe synonym continue to function without modification.

The advantage of synonyms is that if the underlying object is renamed or transferred, then only the synonym needs to be redefined. Applications that are based on this synonym do not need any modifications.

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

Here's an example of how a synonym simplifies user access:

A table called Sales_data is in the schemaowned by the user Jward.

Jward the user has a table sales_data

The SELECT privilege for the sales_datatable are granted to public.

The public group has sales_data query permissions

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

At this point, you need the following statement if you query the Sales_data table:

SELECT * from Jward.sales_data;

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

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

If the database administrator creates a public synonym:

CREATE public synonym sales forjward.sales_data;

After the public synonym are created, youcan query the table sales_data with a simple SQL statement:

Your statement will be simplified to:

SELECT * from sales;

Notice that the public synonym SALES hidesthe name of the table Sales_data and the name of the schema that contains Thetab Le.

II. create synonym creating synonyms

1. Grammatical structure:

2. Prerequisites:

To create a private synonym in your own schema, you must CREATE has the SYNONYM system privilege.

Creating a private synonym in your own mode requires the Create synonym permission.

To create a private synonym in another user's schema, you must CREATE ANY has SYNONYM the /c9>system privilege.

Creating a private synonym in other user mode requires the Create any synonym permission.

To create a PUBLIC synonym, you must CREATE PUBLIC has the SYNONYM sys TEM privilege.

To create a public synonym, you need to have the create publicly synonym permission.

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

[Email protected]_hr;

GRANT SELECT on hr.employees to SCOTT;

Conn Scott/[email protected]

SELECT Count (*) from sys.offices;

Select COUNT (*) from emp_table;

Third, drop synonym delete synonyms

1. Grammatical structure:

2. Prerequisites:

To drop a private synonym, either the synonym must is in your own schema or you must has the DROP SYNONYM > system privilege.

Deleting a private synonym requires a drop any synonym permission.

To drop a PUBLIC synonym, you must DROP PUBLIC has the SYNONYM syste M privilege.

Drop public synonym permission is required to delete a common synonym.

3. Example

3.1 Delete public synonyms, you must add the Public keyword:

[email protected]>drop synonym emp_table;

Dropsynonym emp_table

*

An error occurred on line 1th:

ORA-01434: The private synonym to be removed does not exist

[Email protected]>drop public synonym emp_table;

The synonym has been deleted.

3.2 Delete Private synonyms:

DROP synonym offices;

Iv. Q&a Questions and Answers

Q: Can I do a insert/update/delete operation on synonyms?

A:

[email protected]> UPDATE sys.offices T sett.city= ' Shanghai ' WHERE location_id=1000;

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

*

An error occurred on line 1th:

ORA-01031: Insufficient Permissions

[Email protected]> Grant Update on Hr.locationsto Scott;

Authorization is successful.

[Email protected]>/

1 rows have been updated.

So: The user's permissions on the synonym are based on what permissions they have on the underlying object.

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

Dylan presents.