Oracle | DBMS

Oracle DBMS_EPG 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 EPG is an acronym for Embedded PL/SQL Gateway. DAD is an acronym for Data Access Descriptor.

The XDBADMIN role is required to modify the embedded gateway configuration through the configuration API. Modification of the configuration by a user without the role will result in an "access denied" exception.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
LOG_EMERG	PLS_INTEGER	0
LOG_ALERT	PLS_INTEGER	1
LOG_CRIT	PLS_INTEGER	2
LOG_ERR	PLS_INTEGER	3
LOG_WARNING	PLS_INTEGER	4
LOG_NOTICE	PLS_INTEGER	5
LOG_INFO	PLS_INTEGER	6
LOG_DEBUG	PLS_INTEGER	7

DAD Attributes

database-username	document-path-docs	nls-language
default-page	document-procedure	request-validation
document-table-name

Data Types TYPE varchar2_table IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

Dependencies

DBMS_STANDARD	DBMS_XMLDOM	PLITBLM
DBMS_SYS_ERROR	DUAL	XMLTYPE
DBMS_XDB

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-24231	dad_not_found
ORA-24232	unknown_attribute
ORA-24240	invalid_dad_name

First Available 2004

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsepg.sql

Subprograms

AUTHORIZE_DAD	GET_ALL_DAD_ATTRIBUTES	GET_GLOBAL_ATTRIBUTE
CREATE_DAD	GET_ALL_DAD_MAPPINGS	MAP_DAD
DEAUTHORIZE_DAD	GET_ALL_GLOBAL_ATTRIBUTES	SET_DAD_ATTRIBUTE
DELETE_DAD_ATTRIBUTE	GET_DAD_ATTRIBUTE	SET_GLOBAL_ATTRIBUTE
DELETE_GLOBAL_ATTRIBUTE	GET_DAD_LIST	UNMAP_DAD
DROP_DAD

AUTHORIZE_DAD

Authorizes a DAD dbms_epg.authorize_dad( dad_name IN VARCHAR2, user IN VARCHAR2 DEFAULT NULL);

SELECT * FROM dba_epg_dad_authorization;

      

      set serveroutput on

      

      DECLARE

       path_list dbms_epg.varchar2_table;

       dad_list  dbms_epg.varchar2_table;

      BEGIN

        dbms_epg.create_dad('UWDAD', '/uwweb/*');

        dbms_epg.authorize_dad('UWDAD', 'UWCLASS');

      

        dbms_epg.get_all_dad_mappings('UWDAD', path_list);

        dbms_output.put_line('-' || path_list(1) || '-');

      

        dbms_epg.get_dad_list(dad_list);

        FOR i IN 1..dad_list.COUNT LOOP

          dbms_output.put_line('-' || dad_list(i) || '-');

        END LOOP;

      END;

      /

      

      SELECT * FROM dba_epg_dad_authorization;

CREATE_DAD

Creates a DAD dbms_epg.create_dad( dad_name IN VARCHAR2, path IN VARCHAR2 DEFAULT NULL);

See AUTHORIZE_DAD Demo Above

DEAUTHORIZE_DAD

Revoke authorization of a DAD dbms_epg.deauthorize_dad( dad_name IN VARCHAR2, user IN VARCHAR2 DEFAULT NULL);

exec dbms_epg.authorize_dad('UWDAD', 'UWCLASS');

DELETE_DAD_ATTRIBUTE

Deletes a DAD attribute dbms_epg.delete_dad_attribute( dad_name IN VARCHAR2, attr_name IN VARCHAR2);

exec dbms_epg.deauthorize_dad('UWDAD', 'nls-language');

DELETE_GLOBAL_ATTRIBUTE

Deletes a global attribute dbms_epg.delete_global_attribute(attr_name IN VARCHAR2);

BEGIN

        dbms_epg.delete_global_attribute('nls-language');

      END;

      /

DROP_DAD

Drops a DAD dbms_epg.drop_dad(dad_name IN VARCHAR2);

SELECT * FROM dba_epg_dad_authorization;

      

      exec dbms_epg.drop_dad('UWDAD');

      

      SELECT * FROM dba_epg_dad_authorization;

GET_ALL_DAD_ATTRIBUTES

Retrieves all the attributes of a DAD dbms_epg.get_all_dad_attributes( dad_name IN VARCHAR2, attr_names OUT NOCOPY VARCHAR2_TABLE, attr_values OUT NOCOPY VARCHAR2_TABLE);

set serveroutput on

      

      DECLARE

       name_list dbms_epg.varchar2_table;

       vals_list dbms_epg.varchar2_table;

      BEGIN

        dbms_epg.get_all_dad_attributes('APEX', name_list, vals_list);

      

        FOR i IN 1..name_list.COUNT LOOP

          dbms_output.put_line(name_list(i) || '-' || vals_list(i));

        END LOOP;

      END;

      /

GET_ALL_DAD_MAPPINGS

Gets all virtual paths a DAD is mapped to dbms_epg.get_all_dad_mappings( dad_name IN VARCHAR2, paths OUT NOCOPY VARCHAR2_TABLE);

See AUTHORIZE_DAD Demo Above

GET_ALL_GLOBAL_ATTRIBUTES

Returns all global attributes and values dbms_epg.get_all_global_attributes( attr_names OUT NOCOPY VARCHAR2_TABLE, attr_values OUT NOCOPY VARCHAR2_TABLE);

set serveroutput on

      

      DECLARE

       name_list dbms_epg.varchar2_table;

       vals_list dbms_epg.varchar2_table;

      BEGIN

        dbms_epg.get_all_global_attributes(name_list, vals_list);

      

        FOR i IN 1..name_list.COUNT LOOP

          dbms_output.put_line('-' || name_list(1) || '-');

          dbms_output.put_line('-' || vals_list(1) || '-');

        END LOOP;

      END;

      /

GET_DAD_ATTRIBUTE

Retrieves the value of a DAD attribute dbms_epg.get_dad_attribute( dad_name IN VARCHAR2, attr_name IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_epg.get_dad_attribute('UWDAD', 'database-username')

      FROM dual;

GET_DAD_LIST

Returns a list of all DADs dbms_epg.get_dad_list(dad_names OUT NOCOPY VARCHAR2_TABLE);

See AUTHORIZE_DAD Demo Above

GET_GLOBAL_ATTRIBUTE

Gets a global attribute dbms_epg.get_global_attribute(attr_name IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_epg.get_global_attribute('default-page')

      FROM dual;

MAP_DAD

Maps a DAD to a virtual path. If the virtual path exists already, the virtual path will be mapped to the new DAD dbms_epg.map_dad( dad_name IN VARCHAR2, path IN VARCHAR2);

BEGIN

        dbms_epg.unmap_dad('UWDAD', '/uwweb/*');

      

        dbms_epg.map_dad('UWDAD', '/uwweb/*');

      END;

      /

SET_DAD_ATTRIBUTE

Sets a DAD attribute dbms_epg.set_global_attribute( attr_name IN VARCHAR2, attr_value IN VARCHAR2);

BEGIN

        dbms_epg.set_dad_attribute('UWDAD', 'database-username', 'SCOTT');

        execute immediate 'GRANT execute ON dbms_epg TO SCOTT';

      END;

      /

SET_GLOBAL_ATTRIBUTE

Sets a global attribute dbms_epg.set_global_attribute( attr_name IN VARCHAR2, attr_value IN VARCHAR2);

BEGIN

        dbms_epg.set_global_attribute('database-username', 'SYSTEM');

      END;

      /

UNMAP_DAD

Unmaps a DAD from a virtual path. If the virtual path is NULL, unmap the DAD from all virtual paths. dbms_epg.unmap_dad( dad_name IN VARCHAR2, path IN VARCHAR2 DEFAULT NULL);

See MAP_DAD Demo Above