Oracle | DBMS_DISTRIBUTED_TRUST

Oracle DBMS_DISTRIBUTED_TRUST_ADMIN Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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: Packages and Types Reference

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	Demo
ALLOW_SERVER	DENY_SERVER

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;

      

      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                *

      

      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

      ------------------------------ ------------------------------

      -*                             *

      

      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                *

      

      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'));