Chapter 4. Distributed Database Security

The manager of a distributed database environment has security considerations over and above the typical user authentication and access level concerns of the single database environment. The DBA is responsible for ensuring the privacy and integrity of the data that travels the network and for implementing an appropriately secure user authentication policy. At the same time, any single database in a distributed environment must maintain a high degree of autonomy from the databases and machines with which it interacts. Oracle provides security mechanisms at several layers, including the levels of the database, operating system, and network. This chapter discusses how to implement a secure environment with these various levels and points out some situations that you should avoid.

Privilege Management

You have a variety o f choices for managing access to objects in remote databases; these choices fall into one of the following categories:

The simplistic approach: Remote objects are accessed over a public database link, with a local public synonym for each remote object.
The mirrored account approach: Remote objects are accessed over private database links for all user accounts, with a local public synonym for each remote object.
The local view approach: A local view is created for remote tables. Access to remote objects is via these local objects.
The local wrapper approach: Remote PL/SQL objects (procedures and packages) are called from local procedures; the remote procedures themselves are not available to local users.

The guidelines provided here will help you decide what is best for your applications.

The Simplistic Approach

The easiest way to provide users access to remote objects is to create a public database link to the remote database and create public synonyms for the objects there. For example, the DBA at the site D7NY.BIGWHEEL.COM could provide access to the SPROCKET.PRODUCTS table in D7CA.BIGWHEEL.COM by following these steps:

Create apublic database link to D7CA.BIGWHEEL.COM:
```
CREATE PUBLIC DATABASE LINK D7CA.BIGWHEEL.COM
CONNECT TO d7nydba IDENTIFIED BY masquerade
USING 'prodcal';
```
We assume that the account “d7nydba” exists in D7CA.BIGWHEEL.COM, and that it has sufficient privileges to SELECT from the SPROCKET.PRODUCTS table.
Create a public synonym for the remote SPROCKET.PRODUCTS table:
```
CREATE PUBLIC SYNONYM products
FOR sprocket.products@D7CA.BIGWHEEL.COM;
```
Actually, the public synonym is not required for users to access the remote object; they could also reference it by specifying the database link; for example:
```
SELECT *
FROM sprocket.products@D7CA.BIGWHEEL.COM;
```

Now any user in the local database D7NY.BIGWHEEL.COM can access the remote table SPROCKETS.PRODUCTS as though it were local and enjoy the privileges that have been granted to d7nydba.

Advantages of the simplistic approach

The primary advantage of this means of remote access is that it is extremely easy to implement, and it requires a minimal amount of coordination with the DBA at the remote site. In other words, this is the quick and dirty method, but be advised that it is dirty !

Disadvantages of the simplistic approach

What does it mean to say this method is dirty? Consider the following:

The simplistic approach opens the door to the remote database

Since we created a public database link to the remote database, which connects to a specific user ID, we have potentially (and probably) built a security hole. Any account in the local database can reference objects in the remote database with the privilege level of the account to which the public database link connects. Access is not restricted to the SPROCKET.PRODUCTS table for which we created the public synonym; any table, view, procedure, or package that d7nydba can access is available to all users in the local database. For example, curious users might help themselves to sensitive data as follows:

SELECT last_name, first_name, salary
FROM payroll@D7CA.BIGWHEEL.COM;

Of course, the d7nydba account in D7CA.BIGWHEEL.COM could be created with limited privileges, but it will always be able to see more than just the SPROCKET.PRODUCTS table since any objects accessible to PUBLIC are also accessible to d7nydba.

The simplistic approach provides no local control over access to remote objects

This issue is similar to that described in the previous item. Not only have we provided more access than is necessary to the remote database, but also we have no control over which local users can see the SPROCKET.PRODUCTS table; they all can. In addition, all users enjoy the same level of privileges on the table, as determined by d7nydba’s privilege level in D7CA.BIGWHEEL.COM. In other words, we cannot use database roles in the local database to define access levels. One size fits all, whether you like it or not.

The Mirrored Account Approach

Themirrored account approach entails creating user accounts in all databases in which they require access to data and private database links for these accounts from each database to all other databases they must reference. The private database links need not be created with a CONNECT or USING clause if a public database link exists to resolve link names. This is one of Oracle’s recommended configurations for the advanced replication facilities with Oracle7.

Suppose we wish to create accounts for users “cdye” and “jblow” in database D7NY.BIGWHEEL.COM so that these accounts can reference remote objects in database D7CA.BIGWHEEL.COM. Here are the steps we would take:

Create the user accounts in D7NY.BIGWHEEL.COM and in D7CA.BIGWHEEL.COM. If the accounts have the same passwords in both databases, we can create private database links without the CONNECT clause:
```
CREATE USER cdye IDENTIFIED BY yankeeclip
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO cdye;

GRANT app_admin TO cdye;

CREATE USER jblow IDENTIFIED BY aoldotcom
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO jblow;

GRANT app_user to jblow;
```
Note that in this example we have granted different roles to the different users (app_admin for cdye, and app_user for jblow ); the mirrored account method allows you to tailor privileges to specific users.
Create a public database link from D7NY.BIGWHEEL.COM to D7CA.BIGWHEEL.COM so that the private database links can be created without the USING clause:
```
CREATE PUBLIC DATABASE LINK D7CA.BIGWHEEL.COM
USING 'prodcal'
```
You can create this link from any account that has sufficient privileges to create public database links.
Create private database links from the cdye and jblow accounts.
Connected as cdye:
```
CREATE DATABASE LINK D7CA.BIGWHEEL.COM
```
Connected as jblow:
```
CREATE DATABASE LINK D7CA.BIGWHEEL.COM
```
Create synonyms for the remote objects in D7CA.BIGWHEEL.COM:
```
CREATE PUBLIC SYNONYM products FOR
sprocket.products@D7CA.BIGWHEEL.COM
```
The synonyms can be either public or private. From a practical standpoint, however, public synonyms make more sense since you only have to create one per object. If you do not create the synonyms, users can still reference the remote objects by specifying the database link, for example:
```
SELECT product_id, product_name
FROM products@D7CA.BIGWHEEL.COM;
```

Once you have created these accounts and links, the specified users can access remote objects, each with the access level you have granted.

Advantages of the mirrored account approach

Mirrored accounts allow you to grant access to remote objects only for those users who require it, and you can grant different privileges to different users according to their job functions and responsibilities. In addition, since the public database link we created does not include a CONNECT clause, there is no “open door” to the remote database.

Disadvantages of the mirrored account approach

Although mirrored accounts offer significant advantages over the simplistic approach, some troublesome issues persist:

Users with remote accounts can see any objects that are available to PUBLIC, in addition to the objects to which they have been granted access either explicitly or through roles.
Since users have accounts in the remote databases, they can log in to these databases directly, which may or may not be an issue depending on the site’s security policy.
The maintenance of user accounts, passwords, and roles in multiple databases can quickly become an administrative nightmare if there are large numbers of users or database instances. The administrators of remote database instances must sacrifice a degree of autonomy to support these users.
There is no local control over access to remote objects. This is still an issue, as with the simplistic approach.

The Local View Approach

The local view approach entails creating a single privileged account in the remote database which has sufficient privileges on all application tables there. A local account creates a private database link that connects to the privileged account and then builds views that reference the remote objects. Since these views are local, we can use roles to define access levels.

Consider the SPROCKET.PRODUCTS table described in the previous examples. This table resides in the database instance D7CA.BIGWHEEL.COM, but users in D7NY.BIGWHEEL.COM must access it. What if some users need read-only access while other users must update it? Here is the solution:

Create a privileged account in D7CA.BIGWHEEL.COM that has SELECT, INSERT, UPDATE, and DELETE privileges on SPROCKET.PRODUCTS. (Assume these privileges are granted to the role product_admin.)
```
CREATE USER d7nydba IDENTIFIED BY masquerade
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO d7nydba;

GRANT product_admin TO d7nydba;
```
In D7NY.BIGWHEEL.COM, create a private database link to D7CA.BIGWHEEL.COM that connects to the account created in Step 1. For the sake of clean design, this link should be created under the account that owns the application schema, but it could be made under any account that has sufficient privileges to create views. In our case, we create the link under the SPROCKET account:
```
CREATE DATABASE LINK D7CA.BIGWHEEL.COM
CONNECT TO d7nydba IDENTIFIED BY masquerade
USING 'prodcal';
```
From the account that created the database link in Step 2, create a private synonym for SPROCKET.PRODUCTS@D7CA.BIGWHEEL.COM:
```
CREATE SYNONYM hq_products FOR SPROCKET.PRODUCTS@D7CA.BIGWHEEL.COM;
```
Strictly speaking, this step is optional. However, it is advisable because it eliminates the necessity to create a view that contains a database link name in the query text. This makes administrative tasks simpler, as we shall see in Chapter 6.

Create a view that selects from the remote table:

CREATE VIEW products AS
    SELECT  product_id,
            product_type,
            catalog_id,
            description,
            rev_level,
            production_date,
            production_status
    FROM    hq_products;

Create roles and grant privileges on the view, as appropriate:

CREATE ROLE product_viewer;

GRANT SELECT ON products TO product_viewer;

CREATE ROLE product_admin;

GRANT SELECT, INSERT, UPDATE, DELETE ON products TO product_admin

Optionally create a public synonym for the view:

CREATE PUBLIC SYNONYM products FOR sprocket.products;

Advantages of the local view approach

The local view approach solves the problem of controlling access to remote objects with local roles. You can create as many roles as you need to provide appropriate levels of access. In addition, there is no public database link involved and therefore no open door to the remote database. In fact, in the implementation outlined here, the local account that owns the database link and the view does not even need to have CREATE SESSION privileges once the view is in place! Then nobody can use the account to exploit the private link to the remote database. (I always revoke CREATE SESSION from schema owner accounts once the schema objects are created.)

Note

The local view approach would also work with private database links from each of the user accounts that need to reference the remote object. You could, therefore, require that local users have accounts in the remote database. This adds an additional level of security.

Disadvantages of the local view approach

Truly there are no disadvantages to this approach other than the fact that the initial setup is slightly more involved than the other techniques because you must create the local view and roles. However, this is a small price to pay for the flexibility and security that you realize.

The Local Wrapper Approach

Just as you can create local views on remote tables to control privileges, you can also write local PL/SQL procedures which execute remote procedures. By writing a local procedure or package that calls a remote procedure or package, you can use local roles to administer privileges to the remote objects. The alternative is to create database links that provide access to the remote procedures and packages and sacrifice all local control over who can execute them.

Consider the package ProductMaint, which allows users to add new products to the product table:

CREATE OR REPLACE PACKAGE ProductMaint IS
    PROCEDURE AddProduct  (product_type_IN    IN NUMBER,
                           catalog_id_IN      IN VARCHAR2,
                           description_IN     IN VARCHAR2,
                           rev_level_IN       IN VARCHAR2,
                           production_date_IN IN DATE,
                           product_status_IN  IN VARCHAR);
END ProductMaint;  
/ 
CREATE OR REPLACE PACKAGE BODY ProductMaint IS

PROCEDURE AddProduct(product_type_IN   IN NUMBER,
                catalog_id_IN          IN VARCHAR2,
                description_IN         IN VARCHAR2,
                rev_level_IN           IN VARCHAR2,
                production_date_IN     IN DATE,
                product_status_IN      IN VARCHAR) IS
BEGIN
    INSERT INTO products (product_id,
                product_type,
                catalog_id,
                description,
                rev_level,
                production_date,
                production_status,
                audit_date,
                audit_user,
                global_name )
    VALUES (seq_products.nextval,
            product_type_IN,
            catalog_id_IN,
            description_IN,
            rev_level_IN,
            production_date_IN,
            product_status_IN,
            SYSDATE,
            USER,
            DBMS_REPUTIL.GLOBAL_NAME);
END AddProduct;  

END ProductMaint;  
/

If this package exists in the database D7CA.BIGWHEEL.COM, how can we give some (but not all !) users in D7NY.BIGWHEEL.COM access to it? Specifically, we wish to allow those users in D7NY.BIGWHEEL.COM with the product_admin role the ability to execute ProductMaint.AddProduct.

The solution, of course, is to create a local procedure (or “wrapper”) in D7NY.BIGWHEEL.COM which calls the remote ProductMaint.AddProduct. Then we can grant EXECUTE on the wrapper to the product_admin role. Here’s how:

Create a privileged account in D7CA.BIGWHEEL.COM that has EXECUTE privileges on ProductMaint. (Assume these privileges are granted to the role product_admin.)
```
CREATE USER d7nydba IDENTIFIED BY masquerade
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO d7nydba;

GRANT product_admin TO d7nydba;
```
In D7NY.BIGWHEEL.COM, create a private database link to D7CA.BIGWHEEL.COM which connects to the account created in Step 1. For the sake of clean design, this link should be created under the account that owns the application schema, but it could be made under any account that has sufficient privileges to create views. In our case, we create the link under the SPROCKET account:
```
CREATE DATABASE LINK D7CA.BIGWHEEL.COM
CONNECT TO d7nydba IDENTIFIED BY masquerade
USING 'prodcal';
```
From the account that created the database link in Step 2, create a private synonym for SPROCKET.PRODUCTMAINT@D7CA.BIGWHEEL.COM:
```
CREATE SYNONYM hq_productmaint
FOR SPROCKET.PRODUCTMAINT@D7CA.BIGWHEEL.COM;
```
Strictly speaking, this step is optional. However, it is advisable because it eliminates the necessity to create a procedure that contains a database link name. This makes administrative tasks simpler, as we shall see in Chapter 6.

Create the “wrapper” procedure:

CREATE OR REPLACE PACKAGE ProductMaint IS
    PROCEDURE AddProduct(product_type_IN    IN NUMBER,
                         catalog_id_IN      IN VARCHAR2,
                         description_IN     IN VARCHAR2,
                         rev_level_IN       IN VARCHAR2,
                         production_date_IN IN DATE,
                         product_status_IN  IN VARCHAR);
END ProductMaint;  
/ 
CREATE OR REPLACE PACKAGE BODY ProductMaint IS

PROCEDURE AddProduct (product_type_IN    IN NUMBER,
                      catalog_id_IN      IN VARCHAR2,
                      description_IN     IN VARCHAR2,
                      rev_level_IN       IN VARCHAR2,
                      production_date_IN IN DATE,
                      product_status_IN  IN VARCHAR) IS
BEGIN
    hq_ProductMaint.AddProduct(product_type_IN,
                    catalog_id_IN,
                    description_IN,
                    rev_level_IN,
                    production_date_IN,
                    product_status_IN);
END AddProduct;

END ProductMaint;  
/

Grant EXECUTE privileges on the local package as appropriate:
```
GRANT EXECUTE ON ProductMaint TO product_admin;
```

Advantages of the local wrapper approach

Just as a local view of a remote object facilitates local privilege administration over remote tables, so a local wrapper facilitates local privilege administration over remote procedures and packages. In addition, the wrapper can help to ensure data consistency by performing edit checks, setting parameter values, and so on.

Disadvantages of the local wrapper approach

As with local views, the local wrapper requires a bit of extra work initially; you have to write the local procedure or package and manage the role grants.

Conclusions on Privilege Management

It is a very simple matter to offer access to objects in a remote database: just create a public database link. The challenge is to develop an access model that allows the local administrator the ability to control privilege levels on the remote objects with the same granularity that is possible with local objects. But database roles cannot manage privileges on remote objects:

SQL> GRANT SELECT ON sprocket.products@D7CA.BIGWHEEL.COM TO product_viewer;
GRANT SELECT ON sprocket.products@D7CA.BIGWHEEL.COM TO product_viewer
                                                          *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

The recommended solution is to create local views for remote tables and local wrapper functions for remote procedures and packages. You can grant privileges on these local objects to local roles.

There are, however, occasions when the local objects may not be appropriate. For example, if you are using the advanced replication facilities, the access model is quite different (as we’ll see in see Chapter 10).

Get Oracle Distributed Systems now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Oracle Distributed Systems by Charles Dye