Quick Search:
 
 The Oracle PL/SQL SYNONYM Keyword      [Return To Index] Jump to:  

Term: SYNONYM

Definition:
In Oracle PL/SQL, the term SYNONYM refers to a schema object which is created by a user to access an object that is owned by another user. In the process of creating a synonym, the user seeking the object access must first create a synonym for the required object. The Owner User must then grant the access on the synonym to the seeking user.

Synonyms are of two types: Private and Public Synonyms. Private synonyms are the default synonyms created. Public synonyms are created by the Owner User with the PUBLIC keyword. Any user residing within the database can access objects created with the PUBLIC keyword.
Note that public synonyms are non-schema objects. This is because they are able to be accessed by all users within the database.

Example Syntax:

CREATE [ | OR REPLACE] [PUBLIC] SYNONYM [SYNONYM NAME] FOR OWNER.OBJECT_NAME


Example Usage:

In the example below, user Bob creates synonym for EMPLOYEE table which is owned by user Anne:

SQL> CREATE SYNONYM EMPLOYEE FOR A.EMPLOYEE;

Synonym created.


Now user Anne logs in and grants only SELECT privilege to Bob on the EMPLOYEE table. Note that all other operations on the EMPLOYEE table would be restricted for user Bob.

SQL> GRANT SELECT ON EMPLOYEE TO BOB;

Grant succeeded.


User Bob logs in and queries the EMPLOYEE table.

SQL> SELECT EMPNO, ENAME FROM EMPLOYEE;

EMPNO ENAME
---------- ----------
100 JOHN
110 KATE
120 MILLER
130 JOHNY
140 LARRY
150 EDWIN
160 KING
170 SUEZ
180 HICK

9 rows selected.


Now user Bob attempts to DELETE the EMPLOYEE table records. The operation fails because of insufficient privileges:

SQL> DELETE FROM EMPLOYEE;
DELETE FROM EMPLOYEE
*
ERROR at line 1:
ORA-01031: insufficient privileges



Related Links:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org