| Team Fly | |
Remember in the ''Work with Tables" section where we discussed creating tables that the key was passing Oracle Database 10g the create table keywords? In a nutshell, table creation is undertaken after establishing a successful connection to the database, and then, with appropriate privileges in place, defining a table. One of the key concepts with all database management systems is sharing data. Since it is key to only have one copy of a table and have its contents shared amongst applications, synonyms are a way to reference other people's data.
Suppose we wanted to use the PART_MASTER table in an application owned by a user other than the owner. That owner would permit us to work with the table's data, and then we would create a synonym to reference its contents. The code would resemble the following:
SQL*Plus: Release 10.1.0.1.0 - Production on Sun Mar 11 10:29:42 2007
Copyright (c) 1982, 2003, Oracle. All rights reserved.
Connected to:
Oracle10g Enterprise Edition Release 10.1.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create synonym part_master for inv.part_master;
Synonym created.
SQL> select count(*)
2 from part_master
3 where in_stock is not null;
COUNT(*)
-------------
13442
The preceding SQL statement references an object called part_master. The owner of the table references it using its name, and others using their synonym. There are actually two kinds of synonyms:
Private synonyms are created in one account and are only usable by the creator.
Public synonyms are created by a central privileged user and are available to anyone able to connect to the Oracle Database 10g.
NOTE
One needs the appropriate object privileges to be able to work with someone else's data using a private or public synonym. The synonym itself does not imply that the appropriate privileges can be circumvented.
| Team Fly | |