Oracle7 Server Distributed Systems Volume I: Distributed Data

Balancing Location Transparency and Security

The choice of using a view, synonym, or procedure for location transparency determines the degree to which the local and remote administrators are responsible for object security. The following example statements and sections outline the issues to consider when choosing among the options for location transparency.

Statement issued at remote database:

GRANT SELECT, DELETE ON scott.emp TO user1;

Statements issued at local database:

CREATE DATABASE LINK hr.acme.com

   CONNECT TO user1 IDENTIFIED BY password

   USING 'db_string';

CREATE VIEW admin.emp_view AS

   SELECT * FROM scott.emp@hr.acme.com;

CREATE PROCEDURE admin.fire_emp (enum NUMBER) AS

BEGIN

   DELETE FROM scott.emp@hr.acme.com

      WHERE empno = enum;

END;

CREATE SYNONYM admin.emp_syn FOR scott.emp@hr.acme.com;

Privilege Management With Views

Assume a local view (ADMIN.EMP_VIEW) references a remote table or view. The owner of the local view can grant only the object privileges on his view that have been granted the remote user referenced in the database link. This is similar to privilege management for views that reference local data. Therefore, local privilege management is possible when views are used for location transparency. For example, the user ADMIN can successfully grant the SELECT and DELETE privileges, but not the INSERT and UPDATE privileges for EMP_VIEW.

Views are a good choice for location transparency if unlimited local object privilege management is a requirement. For example, assume the local security administrator needs to selectively grant object privileges for several remote tables. The remote administrator can create a powerful user account that is granted many privileges for many remote tables. Then, the local administrator can create a private database link that connects to the powerful remote account and, in the same schema, create views to "mirror" the remote tables. The local administrator controls local privilege management for the remote tables by granting privileges on the local views. Also note that in this example, many users can use a private database link.

Privilege Management With Procedures

Assume a local procedure includes a statement that references a remote table or view (see previous example). 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 access remote data.

In general, procedures aid in security. Users who call a procedure can only perform the controlled operations of the procedure. Privileges for objects referenced within a procedure do not need to be explicitly granted to the calling users. Much like views, procedures are a good choice for location transparency if unlimited local privilege management is a requirement.

For example, assume the local security administrator needs to selectively allow users to query and update several remote tables. The remote administrator can create a powerful user account that grants many object privileges. Then, the local administrator can create a private database link that connects to the powerful remote account and, in the same schema, create procedures to query and modify the remote tables, as desired. The local administrator can control how local users can access the remote tables by selectively granting the EXECUTE privilege for the local procedures, thus controlling local privilege management for remote objects.

Privilege Management With Synonyms

Assume a local synonym is an alias for a remote object. The owner of the local synonym cannot grant any object privileges on the synonym to any other local user. This behavior is different from privilege management for synonyms that are aliases for local tables or views; in the case where a synonym is an alias for a remote object, local privileges for the synonym cannot be granted because this would amount to granting privileges for the remote object, which is not allowed. Therefore, no local privilege management can be performed when synonyms are used for location transparency; security for the base object is controlled entirely at the remote node. For example, the user ADMIN cannot grant any 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 underlying object's schema in the definition of a synonym.