The owners of a synonym and a target database object are usually different, but they can also be the same in some cases. When their owners are different, ensure that the synonym owner has the permission to access the table that the actual object owner owns.
You can use the CREATE SYNONYM statement to create a synonym. Syntax:
CREATE SYNONYM synonym_name FOR [owner.]object_name;
Example: Creating a synonym for an owner across databases
Create a separate tpcc_ro user and grant the tpcc user the permission to use the GRANT command.
obclient> create user tpcc_ro identified by **1***;
Query OK, 0 rows affected (0.04 sec)
obclient> grant all privileges on tpcc_ro.* to tpcc_ro;
Query OK, 0 rows affected (0.02 sec)
obclient> grant create synonym on *.* to tpcc_ro ;
Query OK, 0 rows affected (0.02 sec)
obclient> grant select on sys.* to tpcc_ro;
Query OK, 0 rows affected (0.01 sec)
obclient> grant all privileges on tpcc.* to tpcc with grant option;
Query OK, 0 rows affected (0.02 sec)
Log on as the tpcc user and grant the tpcc_ro user the permission to read the ordr and ordl tables.
$obclient -h10.0.0.0 -utpcc@oracle0_85#obv22_stable -P2883 -p**1*** tpcc
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient> grant select on ordr to tpcc_ro;
Query OK, 0 rows affected (0.02 sec)
obclient> grant select on ordl to tpcc_ro;
Query OK, 0 rows affected (0.01 sec)
Log on as the tpcc_ro user and create a synonym.
obclient> create synonym ordr for tpcc.ordr;
Query OK, 0 rows affected (0.02 sec)
obclient> create synonym ordl for tpcc.ordl;
Query OK, 0 rows affected (0.01 sec)
obclient> select count(*) from ordr t1 join ordl t2 on (t1.o_w_id=t2.ol_w_id and t1.o_d_id=t2.ol_d_id and t1.o_id=t2.ol_o_id);
+----------+
| COUNT(*) |
+----------+
| 626 |
+----------+
1 row in set (0.02 sec)
obclient> select * from user_synonyms;
+--------------+-------------+------------+---------+
| SYNONYM_NAME | TABLE_OWNER | TABLE_NAME | DB_LINK |
+--------------+-------------+------------+---------+
| ORDR | TPCC | ORDR | NULL |
| ORDL | TPCC | ORDL | NULL |
| WARE | TPCC | WARE | NULL |
+--------------+-------------+------------+---------+
3 rows in set (0.02 sec)