CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_DISTRIBUTED_TRUST_ADMIN

Version 11.1
 
General Information
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.
Source {ORACLE_HOME}/rdbms/admin/dbmstrst.sql
First Available 2001
Dependencies
DUAL TRUSTED_LIST$
Security Model Execute is granted to the execute_catalog_role role
 
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.PSOUG.ORG');

SQL> SELECT * FROM trusted_list$;

DBNAME                         USERNAME
------------------------------ ------------------------------
-*                             *
BIGDOG.PSOUG.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;
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.PSOUG.ORG');

SQL> SELECT * FROM trusted_list$;

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

SQL> SELECT * FROM ku$_trlink_view;
 
Demo Code

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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [204 users online]    © 2010 psoug.org