오라클 시노님(Synonym) 사용법 총정리 (생성, 조회, 권한, 삭제)

I. Introduction

In Oracle, synonyms are used to provide an alternative name for database objects such as tables, views, procedures, or sequences. Synonyms are useful when you want to simplify the process of accessing these objects or when you want to provide a more meaningful or memorable name.

Creating, querying, granting privileges on, and deleting synonyms are common tasks performed by database administrators and developers. This guide will provide a comprehensive overview of how to perform these tasks effectively.

II. Creating Synonyms

To create a synonym in Oracle, you can use the CREATE SYNONYM statement. This statement allows you to define a new name for an existing database object. The syntax for creating a synonym is as follows:

CREATE SYNONYM synonym_name FOR object_name;

Here, synonym_name is the new name you want to give to the object, and object_name is the name of the existing database object.

III. Querying Synonyms

Once you have created synonyms, you can query them to retrieve information about the objects they reference. You can use the data dictionary views to find details such as the object name, owner, type, and other relevant information.

One commonly used view for querying synonyms is ALL_SYNONYMS. This view provides information about all synonyms accessible to the current user. You can also use DBA_SYNONYMS or USER_SYNONYMS views based on your user privileges.

IV. Granting Privileges on Synonyms

Oracle allows you to grant privileges on synonyms to other database users or roles. By granting privileges on a synonym, you provide access to the underlying object without disclosing its actual name. The GRANT statement is used to assign privileges to synonyms.

The syntax for granting privileges on a synonym is as follows:

GRANT privilege ON synonym_name TO user_or_role;

Here, privilege refers to the specific permissions you want to grant, such as SELECT, INSERT, UPDATE, or DELETE. synonym_name is the name of the synonym, and user_or_role is the user or role to which you want to grant the privilege.

V. Deleting Synonyms

If you no longer require a synonym, you can delete it using the DROP SYNONYM statement. This statement removes the association between the synonym and the underlying object.

The syntax for deleting a synonym is as follows:

DROP SYNONYM synonym_name;

Here, synonym_name is the name of the synonym you want to remove from the database.

In conclusion, Oracle synonyms provide a flexible and efficient way to manage database object names. By understanding how to create, query, grant privileges on, and delete synonyms, you can streamline your database operations and enhance data accessibility for users.

II. Creating Synonyms

To create a synonym in Oracle, you can use the CREATE SYNONYM statement. This statement allows you to define a new name for an existing database object. Synonyms are particularly useful when you want to simplify the process of accessing database objects or provide a more meaningful or memorable name.

The syntax for creating a synonym is as follows:

CREATE SYNONYM synonym_name FOR object_name;

Here, synonym_name is the new name you want to give to the object, and object_name is the name of the existing database object. For example, if you want to create a synonym for a table called "employees" and name it "emp_data", you would use the following statement:

CREATE SYNONYM emp_data FOR employees;

When creating a synonym, it's essential to ensure that the object you are referencing exists and is accessible to the user creating the synonym. If the specified object doesn't exist or the user doesn't have the necessary privileges to access it, the synonym creation will fail.

Synonyms can be created for various types of objects, including tables, views, procedures, functions, sequences, and more. Additionally, synonyms can be created in different schemas, allowing for cross-schema access to database objects.

By creating synonyms, you can simplify queries by using a shorter and more intuitive name for a database object. This can improve productivity and code readability.

In summary, the CREATE SYNONYM statement in Oracle allows you to create an alternative name for an existing database object. Synonyms provide a convenient way to simplify object access and improve code readability in your Oracle databases.

III. Querying Synonyms

When you have created synonyms in Oracle, you can query them to retrieve information about the objects they reference. This is especially useful when you want to find details such as the object name, owner, type, and other relevant information.

One commonly used view for querying synonyms is ALL_SYNONYMS. This view provides information about all synonyms accessible to the current user. You can also use DBA_SYNONYMS or USER_SYNONYMS views based on your user privileges.

To query synonyms in Oracle, you can use a SELECT statement on the appropriate data dictionary view. Here is an example query using the ALL_SYNONYMS view:

SELECT *
FROM ALL_SYNONYMS
WHERE synonym_name = 'synonym_name';

In the above query, replace 'synonym_name' with the actual name of the synonym you want to query.

The result of the query will provide information about the synonym, such as the owner, object name it references, object type, and other relevant details. You can use the retrieved information to gain insights into the synonyms present in your Oracle database.

Using the appropriate data dictionary views allows you to gather valuable information about the synonyms in your database, facilitating tasks such as understanding the object's context and ownership, identifying dependencies, and ensuring the integrity of your database structure.

In summary, querying synonyms in Oracle involves using data dictionary views such as ALL_SYNONYMS, DBA_SYNONYMS, or USER_SYNONYMS. These views provide valuable information about the synonyms in your database, allowing you to gain insights into the objects they reference and their associated metadata.

IV. Granting Privileges on Synonyms

When you create a synonym in Oracle, you may need to grant privileges on the synonym to other users or roles. By doing so, you can control the access and operations that can be performed on the underlying object through the synonym.

To grant privileges on a synonym, you can use the GRANT statement in Oracle. The syntax for granting privileges on a synonym is as follows:

GRANT privileges ON synonym_name TO user_or_role;

Here, privileges represent the specific privileges you want to grant, such as SELECT, INSERT, UPDATE, DELETE, or a combination of these. synonym_name is the name of the synonym to which you want to grant the privileges. Lastly, user_or_role represents the user or role to whom you want to grant the privileges.

For example, to grant SELECT and UPDATE privileges on a synonym called emp_data to a user named John, you would use the following statement:

GRANT SELECT, UPDATE ON emp_data TO John;

Furthermore, you can include the WITH GRANT OPTION clause in the GRANT statement to allow the grantee to further grant the same privileges to other users or roles.

If you want to grant all privileges on a synonym in one statement, you can use the ALL PRIVILEGES keyword. For instance:

GRANT ALL PRIVILEGES ON emp_data TO John;

By granting privileges on synonyms, you can control the level of access and operations that can be performed on the objects they reference. This helps in maintaining security and ensuring that only authorized users can interact with the underlying database objects.

In summary, granting privileges on synonyms in Oracle involves using the GRANT statement with the appropriate syntax. By granting privileges, you can control access to the underlying objects through the synonym and ensure that users or roles have the necessary permissions to perform desired operations.

V. Deleting Synonyms

In Oracle, when you no longer need a synonym, you can delete it from the database. Deleting a synonym removes its definition and any associated privileges or dependencies. Deleting a synonym, however, does not affect the underlying object that the synonym references.

To delete a synonym in Oracle, you can use the DROP SYNONYM statement. The syntax for deleting a synonym is as follows:

DROP SYNONYM synonym_name;

Here, synonym_name represents the name of the synonym you want to delete.

For example, to delete a synonym called emp_data_syn from the database, you would use the following statement:

DROP SYNONYM emp_data_syn;

When you execute the DROP SYNONYM statement, Oracle removes the specified synonym from the database. If there are any privileges granted on the synonym, those privileges are also removed.

It's important to note that when you delete a synonym, none of the data or objects associated with the synonym are affected. Deleting a synonym only removes the reference to the underlying object.

Before deleting a synonym, ensure that you no longer need it and that there are no dependent objects relying on it. If dependent objects are still using the synonym, deleting it may result in errors or broken dependencies.

In summary, deleting a synonym in Oracle involves using the DROP SYNONYM statement followed by the name of the synonym you want to delete. This removes the synonym definition and any associated privileges, while leaving the underlying object unaffected. Remember to check for dependencies before deleting a synonym to avoid any unintended consequences.