Rem
Rem $Header: dbmsepg.sql 05-jan-2006.17:56:32 rpang Exp $
Rem
Rem dbmsepg.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem dbmsepg.sql - DBMS Embedded PL/SQL Gateway package
Rem
Rem DESCRIPTION
Rem This package provides the PL/SQL interface to administer the
Rem embedded PL/SQL gateway.
Rem
Rem NOTES
Rem This package must be created under SYS.
Rem
Rem MODIFIED (MM/DD/YY)
Rem rpang 01/05/06 - Add constants for log-levels
Rem rpang 09/22/05 - Add anonymous authentication
Rem rpang 04/21/05 - Add new DAD attributes
Rem rpang 03/11/05 - Add invalid_dad_name exception
Rem rpang 10/08/04 - Move table creation to catepg.sql
Rem rpang 08/31/04 - Add authorization API
Rem rpang 06/22/04 - Created for XML DB integration
Rem
CREATE OR REPLACE PACKAGE dbms_epg AUTHID CURRENT_USER IS
--
-- The PL/SQL gateway enables a Web browser to invoke a PL/SQL stored
-- procedure through an HTTP listener. It is a platform on which PL/SQL
-- users develop and deploy PL/SQL Web applications. The embedded PL/SQL
-- gateway is an embedded version of the PL/SQL gateway that runs in the
-- XML DB HTTP Server in the Oracle database. It provides the core
-- features of mod_plsql in the database but does not require the
-- Oracle HTTP Server powered by Apache.
--
----------------
---- Types -----
----------------
type VARCHAR2_TABLE is table of varchar2(4000) INDEX BY BINARY_INTEGER;
----------------
-- Exceptions --
----------------
invalid_dad_name EXCEPTION;
dad_not_found EXCEPTION;
unknown_attribute EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_dad_name, -24240);
PRAGMA EXCEPTION_INIT(dad_not_found, -24231);
PRAGMA EXCEPTION_INIT(unknown_attribute, -24232);
invalid_dad_name_num constant PLS_INTEGER := -24240;
dad_not_found_num constant PLS_INTEGER := -24231;
unknown_attribute_num constant PLS_INTEGER := -24232;
---------------
-- Constants --
---------------
-- Log levels for the global attribute "log-level"
LOG_EMERG CONSTANT PLS_INTEGER := 0;
LOG_ALERT CONSTANT PLS_INTEGER := 1;
LOG_CRIT CONSTANT PLS_INTEGER := 2;
LOG_ERR CONSTANT PLS_INTEGER := 3;
LOG_WARNING CONSTANT PLS_INTEGER := 4;
LOG_NOTICE CONSTANT PLS_INTEGER := 5;
LOG_INFO CONSTANT PLS_INTEGER := 6;
LOG_DEBUG CONSTANT PLS_INTEGER := 7;
----------------------------- Configuration API ----------------------------
-- 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.
--------------------------------------------
------ Global Attribute Configuration ------
--------------------------------------------
--
-- Sets a global attribute.
--
-- If the attribute has been set before, the old value will be overwritten
-- with the new value. The attribute name is case sensitive. The value
-- may or may not be case-sensitive depending on the attribute.
--
-- PARAMETERS
-- attr_name The global attribute to set
-- attr_value The attribute value to set
-- RETURN
-- None
-- EXCEPTIONS
-- - if the attribute is unknown
-- - if the invoker does not have the XDBADMIN role
-- EXAMPLES
-- dbms_epg.set_global_attribute('max-parameters', '100');
--
procedure set_global_attribute(attr_name IN varchar2,
attr_value IN varchar2);
--
-- Gets the value of a global attribute.
--
-- PARAMETERS
-- attr_name The global attribute to retrieve
-- RETURN
-- The global attribute value. Returns NULL if the attribute is unknown or
-- has not been set.
-- EXCEPTIONS
-- None
--
function get_global_attribute(attr_name IN varchar2) return varchar2;
--
-- Deletes a global attribute.
--
-- PARAMETERS
-- attr_name The global attribute to delete
-- RETURN
-- None
-- EXCEPTIONS
-- None
--
procedure delete_global_attribute(attr_name IN varchar2);
--
-- Gets all global attributes/values.
--
-- The outputs are 2 correlated index-by tables of the name/value pairs.
--
-- PARAMETERS
-- attr_names The global attribute names
-- attr_values The values of the global attributes
-- RETURN
-- None
-- EXCEPTIONS
-- None
-- NOTES
-- If no global attribute has been set, "attr_names" and "attr_values"
-- will be set to empty arrays.
--
procedure get_all_global_attributes(attr_names OUT NOCOPY VARCHAR2_TABLE,
attr_values OUT NOCOPY VARCHAR2_TABLE);
----------------------------------------------------------
----- Database Access Descriptor (DAD) Configuration -----
----------------------------------------------------------
--
-- In order to make a PL/SQL application accessible from the browser via
-- HTTP, a Database Access Descriptor (DAD) must be created and mapped to
-- a virtual path. A DAD is a set of configuration values used for database
-- access and the virtual-path mapping makes the application accessible
-- under a virtual path of the XML DB HTTP Server. A DAD is represented
-- as a servlet in XML DB HTTP Server.
--
--
-- Creates a new DAD. None of its attributes will be set. If a virtual path
-- is given, the DAD will be mapped to the virtual path. Otherwise, the DAD
-- will not be mapped. If the virtual path exists already, the virtual path
-- will be mapped to the new DAD.
--
-- DAD name is case-sensitive. If a DAD with this name already exists,
-- the old DAD's information will be deleted.
--
-- The embedded gateway handles database authentication differently from
-- mod_plsql. In particular, it does not store any database password in a
-- DAD. The following explains the database authentication schemes.
--
-- 1. Static Authentication
--
-- For mod_plsql users who store database usernames/passwords in the DADs
-- so that the browser user will not be required to enter the database
-- authentication information, they can utilize the embedded gateway's static
-- authentication scheme. To use this scheme, the administrator with the
-- XDBADMIN role creates the DAD with the DAD attribute "database-username"
-- set, for example,
--
-- > sqlplus xdb/...
-- SQL> begin
-- dbms_epg.create_dad('HR', '/hrweb/*');
-- dbms_epg.set_dad_attribute('HR', 'database-username', 'SCOTT');
-- end;/
--
-- and the database user authorizes the embedded gateway to use his
-- privileges to invoke procedures and access document tables through the
-- DAD, for example,
--
-- > sqlplus scott/...
-- SQL> begin
-- dbms_epg.authorize_dad('HR');
-- end;
-- /
--
-- In order to use this scheme, both the DAD attribute "database-username"
-- must be set and the DAD must be authorized to use the user's privileges.
-- The DAD attribute "database-username" is case-sensitive. See the
-- description of the "set_dad_attribute" procedure for details.
--
-- Note that in this scheme, the embedded gateway, unlike mod_plsql, logs on
-- to the database as the special user "ANONYMOUS" but accesses database
-- objects using the user's privileges and default roles. Access will be
-- rejected if the browser user attempts to log on explicitly with the HTTP
-- "Authorization" header.
--
-- 2. Dynamic Authentication
--
-- For mod_plsql users who do not store database usernames/passwords in
-- the DADs, they can utilize the embedded gateway's dynamic authentication
-- scheme. To use this scheme, the administrator with the XDBADMIN role
-- simply creates the DAD. For example,
--
-- > sqlplus xdb/...
-- SQL> begin
-- dbms_epg.create_dad('HR', '/hrweb/*');
-- end;
-- /
--
-- To access the procedures or document tables through the DAD, browser users
-- will be required to supply the database authentication information via the
-- HTTP Basic Authentication scheme to log on to the database. If the DAD
-- attribute "database-username" is set, logon will be restricted to the
-- specified user. Caution: since the passwords sent through the HTTP Basic
-- Authentication scheme are not encrypted, the administrator should set up
-- the embedded gateway to use the HTTPS protocol to protect the passwords
-- sent by the browser users.
--
-- Note that in this scheme, the embedded gateway logs on to the database as
-- the user supplied by the browser user. The database user does not have to
-- authorize the embedded gateway to use his privileges to access database
-- objects since the browser user provides the database authentication
-- information to log on explicitly.
--
-- 3. Anonymous Authentication
--
-- For mod_plsql users who create a special DAD database user for database
-- logon purpose but store the application procedures and document tables
-- in a different schema and grant access to the procedures and document
-- tables to PUBLIC, they can utilize the embedded gateway's anonymous
-- authentication scheme. To use this scheme, the administrator with the
-- XDBADMIN role simply creates the DAD with the DAD attribute
-- "database-username" set to "ANONYMOUS" (case-sensitive). For example,
--
-- > sqlplus xdb/...
-- SQL> begin
-- dbms_epg.create_dad('HR', '/hrweb/*');
-- dbms_epg.set_dad_attribute('HR', 'database-username', 'ANONYMOUS');
-- end;
-- /
--
-- In order to use this scheme, the DAD attribute "database-username" must be
-- set to "ANONYMOUS" (case-sensitive). There is no need to authorize the
-- embedded gateway to use ANONYMOUS' privileges to access database objects
-- since ANONYMOUS has no system privileges and owns no database objects.
--
-- PARAMETERS
-- dad_name The name of the DAD to create
-- path The virtual path to map the DAD to
-- RETURN
-- None
-- EXCEPTIONS
-- - if the invoker does not have the XDBADMIN role
--
procedure create_dad(dad_name IN varchar2, path IN varchar2 DEFAULT NULL);
--
-- Drops a DAD. All virtual-path mappings of the DAD will be dropped also
-- but the authorizations of the DAD will not be dropped.
--
-- PARAMETERS
-- dad_name The DAD to drop
-- RETURN
-- None
-- EXCEPTIONS
-- - if the DAD does not exist
-- - if the invoker does not have the XDBADMIN role
--
procedure drop_dad(dad_name IN varchar2);
--
-- Sets an attribute for a DAD.
--
-- If the attribute has been set before, the old value will be overwritten
-- with the new value for single-occurrence attributes. For multi-occurrence
-- attributes, the value will be appended instead.
--
-- The DAD attribute name is case-sensitive. The attribute value may or may
-- not be case-sensitive depending on the attribute.
--
-- DAD attributes are named differently from the DAD attributes of mod_plsql.
-- The name mapping is as follows:
--
-- mod_plsql attributes embedded PL/SQL gateway attributes
-- ----------------------------- ----------------------------------
-- PlsqlDatabaseUsername database-username
-- PlsqlAuthenticationMode authentication-mode
-- PlsqlSessionCookieName session-cookie-name
-- PlsqlSessionStateManagement session-state-management
-- PlsqlMaxRequestsPerSession max-requests-per-session
-- PlsqlDefaultPage default-page
-- PlsqlDocumentTablename document-table-name
-- PlsqlDocumentPath document-path
-- PlsqlDocumentProcedure document-procedure
-- PlsqlUploadAsLongRaw upload-as-long-raw
-- PlsqlPathAlias path-alias
-- PlsqlPathAliasProcedure path-alias-procedure
-- PlsqlExclusionList exclusion-list
-- PlsqlCGIEnvironmentList cgi-environment-list
-- PlsqlCompatibilityMode compatibility-mode
-- PlsqlNLSLanguage nls-language
-- PlsqlFetchBufferSize fetch-buffer-size
-- PlsqlErrorStyle error-style
-- PlsqlTransferMode transfer-mode
-- PlsqlBeforeProcedure before-procedure
-- PlsqlAfterProcedure after-procedure
-- PlsqlBindBucketLengths bind-bucket-lengths
-- PlsqlBindBucketWidths bind-bucket-widths
-- PlsqlAlwaysDescribeProcedure always-describe-procedure
-- PlsqlInfoLogging info-logging
-- PlsqlOWADebugEnable owa-debug-enable
-- PlsqlRequestValidationFunction request-validation-function
-- PlsqlInputFilterEnable input-filter-enable
--
-- Note that the embedded gateway DAD attribute "database-username", unlike
-- its matching mod_plsql DAD attribute "PlsqlDatabaseUsername", is
-- case-sensitive as in the USERNAME column of the ALL_USERS view. The DAD
-- attribute "PlsqlDatabasePassword" is not needed. See the explanation of
-- the database authentication schemes in the "create_dad" procedure. Also,
-- the DAD attribute "PlsqlDatabaseConnectString" is not needed since the
-- embedded gateway does not support logon to external databases.
--
-- PARAMETERS
-- dad_name The DAD to set attribute
-- attr_name The DAD attribute to set
-- attr_value The attribute value to set
-- RETURN
-- None
-- EXCEPTIONS
-- - if the DAD does not exist
-- - if the attribute is unknown
-- - if the invoker does not have the XDBADMIN role
-- EXAMPLES
-- dbms_epg.set_dad_attribute('HR', 'default-page', 'HRApp.home');
--
procedure set_dad_attribute(dad_name IN varchar2,
attr_name IN varchar2,
attr_value IN varchar2);
--
-- Gets the value of a DAD attribute.
--
-- PARAMETERS
-- dad_name The DAD to get attribute
-- attr_name The DAD attribute to get
-- RETURN
-- The DAD attribute value. Returns NULL if the attribute is unknown or
-- has not been set.
-- EXCEPTIONS
-- - if the DAD does not exist
--
function get_dad_attribute(dad_name IN varchar2,
attr_name IN varchar2) return varchar2;
--
-- Gets all attributes of a DAD.
--
-- The outputs are 2 correlated index-by tables of the name/value pairs.
--
-- PARAMETERS
-- dad_name The DAD to get attributes
-- attr_names The DAD attribute names
-- attr_values The values of the DAD attributes
-- RETURN
-- None
-- EXCEPTIONS
-- - if the DAD does not exist
-- NOTE
-- If the DAD has no attributes set, "attr_names" and "attr_values"
-- will be set to empty arrays.
--
procedure get_all_dad_attributes(dad_name IN varchar2,
attr_names OUT NOCOPY VARCHAR2_TABLE,
attr_values OUT NOCOPY VARCHAR2_TABLE);
--
-- Deletes a DAD attribute.
--
-- PARAMETERS
-- dad_name The DAD to delete attribute
-- attr_name The DAD attribute to delete
-- RETURN
-- None
-- EXCEPTIONS
-- - if the DAD does not exist
--
procedure delete_dad_attribute(dad_name IN varchar2,
attr_name IN varchar2);
--
-- Maps a DAD to a virtual path. If the virtual path exists already, the
-- virtual path will be mapped to the new DAD.
--
-- PARAMETERS
-- dad_name The DAD to map
-- path The virtual path to map
-- RETURN
-- None
-- EXCEPTIONS
-- - if the DAD does not exist
--
procedure map_dad(dad_name IN varchar2, path IN varchar2);
--
-- Unmaps a DAD from a virtual path. If the virtual path is NULL, unmap the
-- DAD from all virtual paths.
--
-- PARAMETERS
-- dad_name The DAD to unmap
-- path The virtual path to unmap
-- RETURN
-- None
-- EXCEPTIONS
-- - if the DAD does not exist
--
procedure unmap_dad(dad_name IN varchar2, path IN varchar2 DEFAULT NULL);
--
-- Gets all virtual paths a DAD is mapped to.
--
-- PARAMETERS
-- dad_name The DAD to retrieve virtual-path mappings
-- paths The virtual paths mapped to the DAD
-- RETURN
-- None
-- EXCEPTIONS
-- - if the DAD does not exist
-- NOTE
-- If the DAD is not mapped to any virtual path, "paths" will be set
-- to an empty array.
--
procedure get_all_dad_mappings(dad_name IN varchar2,
paths OUT NOCOPY VARCHAR2_TABLE);
--
-- Gets the list of all DADs.
--
-- PARAMETERS
-- dad_names The list of all DADs
-- RETURN
-- None
-- EXCEPTIONS
-- None
-- NOTE
-- If no DADs exist, "dad_names" will be set to an empty array.
--
procedure get_dad_list(dad_names OUT NOCOPY VARCHAR2_TABLE);
---------------------------- Authorization API -----------------------------
-- Any user can authorize the use of his privileges to the embedded gateway
-- through the authorization API in his schema. The XDBADMIN role is not
-- required to perform such authorization.
--
-- Authorizes a DAD to use a user's privileges to invoke procedures and
-- access document tables. The invoker can always authorize the use of
-- his own privileges. To authorize the use of another user's privileges,
-- the invoker must have the the ALTER USER system privilege.
--
-- The DAD authorization may be performed before the DAD is created. The
-- DAD attribute "database-username" does not have to be set to user to
-- authorize. Multiple users can authorize the same DAD and it is up to
-- DAD's "database-username" attribute setting to decide which user's
-- privileges to use. To view the DAD authorizations, see the database
-- dictionary views USER_EPG_DAD_AUTHORIZATION and DBA_EPG_DAD_AUTHORIZATION.
--
-- PARAMETERS
-- dad_name The DAD to authorize use
-- user The user whose privileges to authorize. If the user is NULL,
-- the invoker is assumed. The username is case-sensitive as in
-- the USERNAME column of the ALL_USERS view.
-- RETURN
-- None
-- EXCEPTIONS
-- - if the user does not exist
-- - if the invoker authorizes for another user but he does not have the
-- ALTER USER system privilege
-- EXAMPLE
-- dbms_epg.authorize_dad('HR');
--
procedure authorize_dad(dad_name IN varchar2,
user IN varchar2 DEFAULT NULL);
--
-- Deauthorizes a DAD's use of a user's privileges to invoke procedures and
-- access document tables. The invoker can always deauthorize the use of
-- his own privileges. To deauthorize the use of another user's privileges,
-- the invoker must have the the ALTER USER system privilege.
--
-- PARAMETERS
-- dad_name The DAD to deauthorize use
-- user The user whose privileges to deauthorize. If the user is NULL,
-- the invoker is assumed. The username is case-sensitive as in
-- the USERNAME column of the ALL_USERS view.
-- RETURN
-- None
-- EXCEPTIONS
-- - if the user does not exist
-- - if the invoker deauthorizes for another user but he does not have the
-- ALTER USER system privilege
-- EXAMPLE
-- dbms_epg.deauthorize_dad('HR');
--
procedure deauthorize_dad(dad_name IN varchar2,
user IN varchar2 DEFAULT NULL);
END dbms_epg;
/
show errors;
CREATE OR REPLACE PUBLIC SYNONYM dbms_epg FOR sys.dbms_epg
/
GRANT EXECUTE ON dbms_epg TO PUBLIC
/