After you have configured the necessary database links, you can use various tools to hide the distributed nature of the database system from users. In other words, users can access remote objects as if they were local objects. The following sections explain how to hide distributed functionality from users:
Using Views to Create Location Transparency
Local views can provide location transparency for local and remote tables in a distributed database system.
For example, assume that table emp is stored in a local database and table dept is stored in a remote database. To make these tables transparent to users of the system, you can create a view in the local database that joins local and remote data:
Views and Location Transparency
When users access this view, they do not need to know where the data is physically stored, or if data from more than one table is being accessed. Thus, it is easier for them to get required information. For example, the following query provides data from both the local and remote database table:SELECT * FROM company;
The owner of the local view can grant only those object privileges on the local view that have been granted by the remote user. (The remote user is implied by the type of database link). This is similar to privilege management for views that reference local data.
Using Synonyms to Create Location Transparency
Synonyms are useful in both distributed and nondistributed environments because they hide the identity of the underlying object, including its location in a distributed database system. If you must rename or move the underlying object, you only need to redefine the synonym; applications based on the synonym continue to function normally. Synonyms also simplify SQL statements for users in a distributed database system.
You can create synonyms for the following:
All synonyms are schema objects that are stored in the data dictionary of the database in which they are created. To simplify remote table access through database links, a synonym can allow single- word access to remote data, hiding the specific object name and the location from users of the synonym.
The syntax to create a synonym is:
Omitting this parameter makes a synonym private, and usable only by the creator. Public synonyms can be created only by a user with CREATE PUBLIC SYNONYM system privilege.
A synonym must be a uniquely named object for its schema. If a schema contains a schema object and a public synonym exists with the same name, then the database always finds the schema object when the user that owns the schema references that name.
Example: Creating a Public Synonym
Assume that in every database in a distributed database system, a public synonym is defined for the scott.emp table stored in the hq database:CREATE PUBLIC SYNONYM emp FOR email@example.com;
You can design an employee management application without regard to where the application is used because the location of the table firstname.lastname@example.org is hidden by the public synonyms. SQL statements in the application access the table by referencing the public synonym emp.
Furthermore, if you move the emp table from the hq database to the hr database, then you only need to change the public synonyms on the nodes of the system. The employee management application continues to function properly on all nodes.
Managing Privileges and Synonyms
A synonym is a reference to an actual object. A user who has access to a synonym for a particular schema object must also have privileges on the underlying schema object itself. For example, if the user attempts to access a synonym but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.
Assume scott creates local synonym emp as an alias for remote object scott cannot grant object privileges on the synonym to another local user. scott cannot grant local privileges for the synonym because this operation amounts to granting privileges for the remote emp table on the sales database, which is not allowed. This behavior is different from privilege management for synonyms that are aliases for local tables or views. Therefore, you cannot manage local privileges when synonyms are used for location transparency. Security for the base object is controlled entirely at the remote node. For example, user admin cannot grant object privileges for the emp _syn synonym.
Unlike a database link referenced in a view or procedure definition, a database link referenced in a synonym is resolved by first looking for a private link owned by the schema in effect at the time the reference to the synonym is parsed. Therefore, to ensure the desired object resolution, it is especially important to specify the schema of the underlying object in the definition of a synonym.
Using Procedures to Create Location Transparency
PL/SQL program units called procedures can provide location transparency. You have these options:
Using Local Procedures to Reference Remote Data
Procedures or functions (either standalone or in packages) can contain SQL statements that reference remote data. For example, consider the procedure created by the following statement:
When a user or application calls the fire _emp procedure, it is not apparent that a remote table is being modified. A second layer of location transparency is possible when the statements in a procedure indirectly reference remote data using local procedures, views, or synonyms. For example, the following statement defines a local synonym:CREATE SYNONYM emp FOR email@example.com;
Given this synonym, you can create the fire_emp procedure using the following statement:
If you rename or move the table emp@hq, then you only need to modify the local synonym that references the table. None of the procedures and applications that call the procedure require modification.
Using Local Procedures to Call Remote Procedures
You can use a local procedure to call a remote procedure. The remote procedure can then execute the required DML. For example, assume that scott connects to local _db and creates the following procedure:
CONNECT scott/tiger@local _db
CREATE PROCEDURE fire _emp (enum NUMBER)
Now, assume that scott connects to the remote database and creates the remote procedure:
When a user or application connected to local_db calls the fire_emp procedure, this procedure in turn calls the remote term_emp procedure on hq.acme.com.
Using Local Synonyms to Reference Remote Procedures
For example, scott connects to the local sales.acme.com database and creates the following procedure:
User peggy then connects to the supply.acme.com database and creates the following synonym for the procedure that scott created on the remote sales database:
A local user on supply can use this synonym to execute the procedure on sales.
Managing Procedures and Privileges
Assume a local procedure includes a statement that references a remote table or view. The owner of the local procedure can grant the execute privilege to any user, thereby giving that user the ability to execute the procedure and, indirectly, access remote data.
In general, procedures aid in security. Privileges for objects referenced within a procedure do not need to be explicitly granted to the calling users.
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.