Oracle | DBMS

Oracle DBMS_EPG Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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

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