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 E mbedded P L/SQL G ateway. DAD is an acronym for D ata A ccess D escriptor.
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
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