Oracle DBMS_DISTRIBUTED_TRUST_ADMIN Built-In Package
Versions 9.0 - 21c

Security Advisory
According to Oracle's documentation this package maintains the Trusted Servers List. These procedures are used to define whether a server is trusted. If a database is not trusted, Oracle refuses current user database links from the database. That makes a lot of sense unless you realize than 99.95% of all Oracle DBAs, if asked what the Trusted Servers List is, wouldn't have even the remotest idea that you were asking about a data dictionary table inside of essentially every Oracle database they've ever worked on.

Security by Obscurity has never been viewed as a good strategy: This is worse. This is Lack-of-Security by Obscurity. So let's shine a bright light on this package and why it should be an essential component of every database installation.

Review the "How Oracle Works" section below to find out why this package is an essential component of securing your Oracle databases.
 
Recommended Security Rules

 NEVER
  • Deploy an Oracle Database without executing DBMS_DISTRIBUTED_TRUST_ADMIN.DENY_ALL;
 WITH GREAT CARE
  • Use DBMS_DISTRIBUTED_TRUST_ADMIN.ALLOW_SERVER to allow database links to specific physical and logical hosts.
 CAUTIONS
  • Just because it made sense to allow database links to a specific host today does not mean that it will be a good idea next month or next year. Be sure that part of your annual security review is to validate the need for every server to be "Trusted."
 
How Oracle Works
A basic component of Oracle Databases is the database link. Database Links grant one or more users the ability to transparently connect, no password required, from one database to another. From a security standpoint, consider the implications of an attacker compromising a database that contains nothing of value, perhaps the light bulb replacement schedule. Now consider the implications of a database link transparently connecting that database to your Finance or HR system. Or the risks associated with a database link connecting that database to databases with medical (HIPAA), credit card (PCI), or other sensitive data.

By default anyone with the correct privileges can easily accomplish that goal in a few seconds to a few minutes.

An object that requires granting either the CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK to create and for which there are also ALTER DATABASE LINK and ALTER PUBLIC DATABASE LINK system privileges. In addition to those schemas that have one of these privilege granted to them the privileges are also, by default, granted to a number of roles as demonstrated below in this section.

The value of a properly configured Trusted Servers List is that it allow for the prevention of database links that go to unapproved servers. For example, you could prevent access to the Credit Cards database from the Light Bulb Replacement database.

This all seems like a "no brainer" and it is. So why is it that even though this package has been around for decades, even though this package is fully documented and support by Oracle in the Types and Packages docs, almost no one configures a Trusted Servers List? Because it is free and when Oracle does charge for something ... Oracle's customers don't know it exists and don't appreciate its value.
Identify database link related privilege assignments -- users and roles with Database Link related privileges

SELECT UNIQUE grantee
FROM dba_sys_privs
WHERE privilege like '%LINK%'
ORDER BY 1;

GRANTEE
-------------------------
DBA
GGSYS
GSMADMIN_INTERNAL
IMP_FULL_DATABASE
RECOVERY_CATALOG_OWNER
SYS

-- and, over time individual applications, schemas, users, and additional roles may obtain one or more of these system
-- privileges either directly or through a grant to an application role.
 
DBMS_DISTRIBUTED_TRUST_ADMIN General Information
AUTHID DEFINER
Dependencies
DBMS_STANDARD DUAL TRUSTED_LIST$
Documented Yes
First Available 9.0.1
Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO_WITH_COMMIT);
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmstrst.sql
Subprograms
ALLOW_ALL DENY_ALL DENY_SERVER
ALLOW_SERVER    
 
ALLOW_ALL
Empties the list and then inserts a row indicating all servers should be trusted

This is the DBCA installation: It is inexcusable if left unmodified		 dbms_distributed_trust_admin.allow_all;
conn sys@pdbdev as sysdba

desc trusted_list$

col dbname format a30
col username format a30

SELECT * FROM trusted_list$;

exec dbms_distributed_trust_admin.allow_all;

SELECT * FROM trusted_list$;

DBNAME                         USERNAME
------------------------------ ------------------------------
+*                             *

SELECT * FROM ku$_trlink_view;
 
ALLOW_SERVER
Allows a named server to be accessed when DENY_ALL is the default dbms_distributed_trust_admin.allow_server(server IN VARCHAR2);
SELECT * FROM trusted_list$;

exec dbms_distributed_trust_admin.allow_server('BIGDOG.MLIB.ORG');

SELECT * FROM trusted_list$;

DBNAME                         USERNAME
------------------------------ ------------------------------
-*                             *
BIGDOG.MLIB.ORG                *

SELECT * FROM ku$_trlink_view;
 
DENY_ALL
Empties the list and then inserts a row indicating no servers should be trusted

This is the only responsible configuration. Use allow server to allow links only when required.		 dbms_distributed_trust_admin.deny_all;
col dbname format a9

SELECT * FROM trusted_list$;

exec dbms_distributed_trust_admin.deny_all;

SELECT * FROM trusted_list$;

DBNAME                         USERNAME
------------------------------ ------------------------------
-*                             *

SELECT * FROM ku$_trlink_view;
 
DENY_SERVER
Denies access to a named server when ALLOW_ALL is the default dbms_distributed_trust_admin.deny_server(server IN VARCHAR2);
SELECT * FROM trusted_list$;

exec dbms_distributed_trust_admin.deny_server('BIGDOG.MLIB.ORG');

SELECT * FROM trusted_list$;

DBNAME                         USERNAME
------------------------------ ------------------------------
+*                             *
BIGDOG.MLIB.ORG                *

SELECT * FROM ku$_trlink_view;
 
Related Queries
From catmeta.sql CREATE OR REPLACE FORCE VIEW ku$_trlink_view
OF ku$_trlink_t WITH OBJECT IDENTIFIER(name) AS
SELECT '1', '0', tl.dbname,
  DECODE(tl.dbname, '+*', 'DBMS_DISTRIBUTED_TRUST_ADMIN.ALLOW_ALL',
  '-*', 'DBMS_DISTRIBUTED_TRUST_ADMIN.DENY_ALL', fdef.function),
  DECODE(tl.dbname, '+*', 0, '-*', 0, 1)
FROM sys.trusted_list$ tl, (
  SELECT DECODE(dbname,
  '+*', 'DBMS_DISTRIBUTED_TRUST_ADMIN.DENY_SERVER',
  '-*', 'DBMS_DISTRIBUTED_TRUST_ADMIN.ALLOW_SERVER') FUNCTION
  FROM sys.trusted_list$
  WHERE dbname like '%*') FDEF
WHERE (SYS_CONTEXT('USERENV','CURRENT_USERID') = 0
OR EXISTS (SELECT * FROM session_roles WHERE role='SELECT_CATALOG_ROLE'));

Related Topics
Database Link