Oracle | DBMS_ROUTER

Oracle DBMS_ROUTER_ADMIN Version 23c

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 The Oracle in-database router is used in a Router to redirect queries and conventional DML statements to target PDBs.

AUTHID DEFINER

Dependencies

DBA_OBJECTS	DBMS_PDB_LIB	ROUTING_TABLE$
DBA_USERS	DBMS_STANDARD	USER$
DBMS_ASSERT	OBJ$

Documented No

Exceptions

Error Code	Reason
ORA-20010	Common or Oracle Maintained schema objects cannot be routed
ORA-20012	Target Database OCID <ocid_number> does not exist

First Available 23.2

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role.

Source {ORACLE_HOME}/rdbms/admin/dbmsrouter.sql {ORACLE_HOME}/rdbms/admin/prvtrouter.plb

Subprograms

ADD_ROUTER_ENTRY	ENABLE_ROUTER	REMOVE_ROUTER_ENTRY
DISABLE_ROUTER	GRANT_OR_REVOKE

ADD_ROUTER_ENTRY (new 23c)

Add an entry to the routing table

Compare this proc to the one in DBMS_APPLICATION. The value required for database_ocid is not known at this time dbms_router_admin.add_router_entry( object_owner IN VARCHAR2, object_name IN VARCHAR2, database_ocid IN VARCHAR2);

exec dbms_router_admin.add_router_entry('UWCLASS', 'TEST', 3);

           *

      ORA-20010 Common or Oracle Maintained schema objects cannot be routed

DISABLE_ROUTER (new 23c)

Disable the router dbms_router_admin.disable_router;

exec dbms_router_admin.disable_router;

      

      PL/SQL procedure successfully completed.

ENABLE_ROUTER (new 23c)

Enable the router dbms_router_admin.enable_router;

exec dbms_router_admin.enable_router;

      

      PL/SQL procedure successfully completed.

GRANT_OR_REVOKE (new 23c)

Grants or revokes privileges from an object that's enabled for routing

The demo at right is based on the use of DBMS_APPLICATION to create the routing table entry

VULNERABILITY ALERT dbms_router_admin.grant_or_revoke( action IN VARCHAR2, -- 'GRANT' or 'REVOKE' object_owner IN VARCHAR2, object_name IN VARCHAR2);

exec dbms_router_admin.grant_or_revoke('GRANT', 'SH', 'SALES');

      

      PL/SQL procedure successfully completed.

REMOVE_ROUTER_ENTRY (new 23c)

Remove an entry from the routing table dbms_router_admin.remove_router_entry( object_owner IN VARCHAR2, object_name IN VARCHAR2,

TBD