Oracle DBMS_DISTRIBUTED_TRUST_ADMIN
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose Maintain 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
AUTHID DEFINER
Dependencies
DBMS_STANDARD DUAL TRUSTED_LIST$
Documented Yes
First Available 2001
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmstrst.sql
Subprograms
Note ALLOW_ALL only applies to the servers listed as trusted at the Central Authority. DENY_SERVER provides a way to indicate that, even though allow all is indicated in the list, a specific server is to be denied. Similarly, ALLOW_SERVER provides a way to indicate that even though deny all is indicated in the list, some specific servers are to be allowed access.
 
ALLOW_ALL
Empties the list and then inserts a row indicating all servers should be trusted 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;

SQL> SELECT * FROM trusted_list$;

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

SQL> 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);
SQL> SELECT * FROM trusted_list$;

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

SQL> SELECT * FROM trusted_list$;

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

SQL> SELECT * FROM ku$_trlink_view;
 
DENY_ALL
Empties the list and then inserts a row indicating no servers should be trusted dbms_distributed_trust_admin.deny_all;
col dbname format a9

SQL> SELECT * FROM trusted_list$;

SQL> exec dbms_distributed_trust_admin.deny_all;

SQL> SELECT * FROM trusted_list$;

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

SQL> 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);
SQL> SELECT * FROM trusted_list$;

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

SQL> SELECT * FROM trusted_list$;

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

SQL> SELECT * FROM ku$_trlink_view;
 
Demo
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
Built-in Functions
Built-in Packages
Database Link
Security
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved