By default, a database link allows access to all objects on the remote database, according to the user name which is used in the database link definition. The access rules which apply are the same as if the user logs in to the remote database through SQL*Plus or an SQL worksheet. Often it is sufficient (and this is generally a good idea) to restrict the access to the objects in the remote database only to these objects which are absolutely necessary.
An additional fact to consider is that authorization and authentication which is implemented in the legacy system’s application logic is completely bypassed by the database link approach. If some application logic exists which prevent access on specific rows depending on some foreign key, for example, this is not considered in this approach. We need therefore make sure to restrict access to the data in the legacy system as much as possible.
To implement access restriction, we create a separate user (let’s call it “RemoteAccess” )(make sure that the user does not have any grants by default!) for the remote access and use this user in the database link definition’s CONNECT TO clause:
CREATE DATABASE LINK LegacyLink CONNECT TO RemoteAccesss IDENTIFIED BY Secret USING 'Legacy';
Then, on the LEGACY database, grant SELECT access to this user to access the relevant objects in the LEGACY schema. As the legacy schema user, execute the statement
GRANT SELECT ON t_legacy TO RemoteAccess;
The RemoteAccess user now has only SELECT access to the t_legacy table, but no other access to the t_legacy table and no access at all to any other tables. On the local database, execute a query against the database link like
SELECT * FROM t_legacy@LegacyLink;
1 Test data 2 Test data 2 3 More testdata
SELECT * FROM T_LEGACY2@LegacyLink;
ORA-01031: insufficient privileges
UPDATE t_legacy @LegacyLink SET aValue='Modified data' WHERE aValue='Test data';
SQL Error: ORA-01031: insufficient privileges
To restrict access to specific columns, we create a view in the LEGACY schema which selects the relevant columns:
CREATE VIEW v_legacy_restricted AS SELECT aValue FROM t_legacy;
We then grant access for the remote user on the view only (if you tried the steps before, do not forget to revoke the earlier grant on the table!)
GRANT SELECT ON v_legacy_restricted TO RemoteAccess;
Then, ony the relevant columns from the t_legacy table can be accessed through the database link:
SELECT * FROM v_legacy_restricted@LegacyLink;
Even more data Test data Test data 2
Note that you MUST NOT create these views in the RemoteAccess user’s schema you would need to grant access to the corresponding table in the Legacy schema so that the view can be created, but this would bypass the column access restriction since you would be able to access the table in the Legacy schema (with the dot notation, even through the database link), and not the view only!