Oracle DBMS_DISTRIBUTED_TRUST_ADMIN
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 / 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$;

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$;

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$;

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
Database Link
Packages
Security

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-2014 Daniel A. Morgan All Rights Reserved