An Oracle database link is one of the most straight forward and useful methods for transferring data from one system to another. Data in remote systems can be searched or copied from table to table over the network, just like it was in the local database. If not handled correctly however, a database link can become a major security flaw for both systems involved in the connection.
For instance, I have seen the following configuration on multiple occasions: two systems, an OLTP financial application and a data warehouse, are Oracle databases. Each is maintained and operated by completely separate departments with different administrators, developers, and design standards. The warehouse system needs to get data from the OLTP system and provide it to management users in corporate financial dashboards and reports. The well meaning administrators of the OLTP system create an account for the warehouse database link, and (thinking to make it secure) grant it connect privileges along with select privileges on the tables and views required by the warehouse system. The developers of the warehouse (thinking to keep the design simple) create a public database link that connects to the OLTP system, then write code for their reports that references the link. Data from the OLTP system is then presented on-demand to the warehouse users.
Unwittingly, the administrators of both systems have collaborated to expose a good deal more information than they may have meant to. For instance, even though the account in the OLTP database has only been granted explicit privileges on a limited set of tables or views, every account in an Oracle database also has access to additional information contained in the “ALL_%” views of the data dictionary – like ALL_USERS, ALL_TABLES, ALL_VIEWS, ALL_LINKS, ALL_OBJECTS, etc. – by default, because access to these resources is granted to the PUBLIC user. Add to this the fact that the developers of the warehouse system created a public database link for use by multiple end-users in their code, and significant portions of the OLTP data dictionary will have inadvertently been exposed to every warehouse database user.
The OLTP administrators probably didn’t realize that they were handing out a complete list of their user accounts, database tables and other objects to every user in the remote system, nor did it occur to them that a number of sensitive system packages and stored procedures are also executable by PUBLIC by default. Given that most Oracle database applications have proprietary designs, that user account lists are generally considered sensitive and worth protecting, and that exposing system packages could lead to possible privilege escalation or other vulnerabilities and we have the potential for a big problem.
Also consider that by allowing users in the warehouse to execute SQL directly against the tables in the OLTP system that the OLTP administrators have introduced a different sort of threat: that of denial-of-service. The warehouse users represent an entirely unknown and unpredictable workload that the OLTP database may not have been designed to handle, and that they will have no way to control. A couple of impatient warehouse users – submitting multiple requests for data and combined with a badly written SQL report – could lead to a performance bottleneck in the OLTP database that would bring both systems to a standstill.
How can I create a secure database link that allows access to the information we need in the warehouse without unintentionally compromising the OLTP system or leaving ourselves open to unplanned performance bottlenecks in both systems? I start out very similarly to the original design: I still need an account for the database link in the OLTP system, configured with connect privileges along with select privileges on the required tables and views. The next steps take us in a completely different direction, however.
In the data warehouse, I create a dedicated account to “own” the database link. In that account’s schema I create a private database link. Because it is private, only the schema owner can access it directly. Views or synonyms for the tables in the OLTP system are also created in the link schema. The link schema is then locked to prevent anyone using the warehouse from connecting directly to it, and select access to the synonyms and views is granted through roles to the warehouse users. In this manner, warehouse users can be granted access to local objects in the link schema that allow them access to the data in the OLTP database, without exposing the database link directly or allowing users in the warehouse to create ad-hoc SQL against the OLTP data dictionary.
This deals with the straight-up security portion of my problem, but what about the potential performance impact of the link? By using materialized views in my link schema to cache OLTP data, I can limit the added workload on the OLTP database to just that required to refresh the warehouse data. Ideally the warehouse will only copy updates to the OLTP tables, rather than perform complete refreshes. This fast refresh process is aided by the creation of materialized view logs in the OLTP database. These logs track changes to the source data tables and allow the warehouse materialized views to quickly identify which rows need to be updated and which do not. As each refresh takes place, the logs are truncated to make room for new updates, so they remain small and consume minimal resources.
This has several benefits. By using materialized views to maintain a local copy of data for warehouse processing, absolutely no adhoc processing is offloaded to the OLTP system. If the OLTP system is down for maintenance or is inaccessible due to a network outage, the warehouse database is able to continue processing normally until the OLTP system is back online. When refresh rates are kept frequent, fewer rows at a time must be processed by either system. The result is a minimal background presence of the database link processing that does not interfere with user-driven processing in either system. Data transfer occurs in near real-time and need not be relegated to the middle of the night or to weekends (conversely, less frequent refresh rates generally result in many more rows being processed in a single refresh cycle; this often results in performance “spikes” that risk interference with normal operations). The warehouse’s ability to generate reports or feed dashboards is no longer constrained by the processing capability of the OLTP system.
This architecture is both secure and efficient. It allows both systems to do what they do best, providing services to their users without letting the design or overhead processing of the data sharing compromise their availability or security.