The concept of Oracle synonym

Oracle synonym

The synonym for Oracle (synonyms) is similar to View, but more powerful. Most database objects in the Oracle database, such as tables, views, materialized views, sequences, functions, stored procedures, packages, synonyms, and so on, can be used as synonyms. It mainly has two purposes: to simplify object access and improve the security of object access.

Here's a simple demonstration:

SQL > conn scott/tiger

Connected.

SQL > create table test1 (id number)

Table created.

SQL > insert into test1 values (1)

1 row created.

SQL > commit

1. Create a connection user:

SQL > conn / as sysdba

Connected.

SQL > create user zsl identified by zsl

User created.

2. Create a profile:

SQL > create profile zsl_test1 limitsessions_per_user 5

Profile created.

3. Set profile to bind users:

SQL > alter user zsl profile zsl_test1

User altered.

4. Grant authority

SQL > grant create session to zsl

Grant succeeded.

SQL > grant select on scott.test1 to zsl

Grant succeeded.

5. Create synonyms

SQL > create synonym zsl.test1 forscott.test1

Synonym created.

6. Switch to zsl user for testing

SQL > conn zsl/zsl

Connected.

SQL > select * from test1

ID

-

one

7. Find the owner

SQL > conn / as sysdba

Connected.

SQL > set line 150

SQL > select owner,SYNONYM_NAME,TABLE_OWNER, TABLE_NAME from dba_synonyms where SYNONYM_NAME=upper ('test1')

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME

ZSL TEST1 SCOTT TEST1