Release 3 (8.1.7)
Part Number A85455-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Documentation Addendum Release 3 (8.1.7) Part Number A85455-01 |
|
This chapter contains a description of the new DBMS_LDAP package and the DBMS_OBFUSCATION_TOOLKIT which had significant changes for the 8.1.7 release.
This addendum contains these topics:
For detailed information on how to create your own packages, see Oracle8i Application Developer's Guide - Fundamentals.
See Also:
A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions.
Packages have many advantages over stand-alone procedures and functions. For example, they:
Most Oracle supplied packages are automatically installed when the database is created and the CATPROC.SQL script is run. For example, to create the DBMS_ALERT package, the DBMSALRT.SQL and PRVTALRT.PLB scripts must be run when connected as the user SYS. These scripts, however, are run automatically by the CATPROC.SQL script.
Certain packages are not installed automatically. Special installation instructions for these packages are documented in the individual chapters.
To call a PL/SQL function from SQL, you must either own the function or have EXECUTE privileges on the function. To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view. Instructions on special requirements for packages are documented in the individual chapters.
There are two distinct steps to creating a new package:
CREATE PACKAGE statement.
You can declare program objects in the package specification. Such objects are called public objects. Public objects can be referenced outside the package, as well as by other objects in the package.
CREATE PACKAGE BODY statement.
You can declare and define program objects in the package body:
For more information on creating new packages, see PL/SQL User's Guide and Reference and Oracle8i Application Developer's Guide - Fundamentals. For more information on storing and executing packages, see Oracle8i Concepts.
See Also:
The specification of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.
Oracle stores the specification and body of a package separately in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body. This distinction allows you to change the definition of a program object in the package body without causing Oracle to invalidate other schema objects that call or reference the program object. Oracle invalidates dependent schema objects only if you change the declaration of the program object in the package specification.
The following example shows a package specification for a package named EMPLOYEE_MANAGEMENT. The package contains one stored function and two stored procedures.
CREATE PACKAGE employee_management AS FUNCTION hire_emp (name VARCHAR2, job VARCHAR2, mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER, deptno NUMBER) RETURN NUMBER; PROCEDURE fire_emp (emp_id NUMBER); PROCEDURE sal_raise (emp_id NUMBER, sal_incr NUMBER); END employee_management;
The body for this package defines the function and the procedures:
CREATE PACKAGE BODY employee_management AS FUNCTION hire_emp (name VARCHAR2, job VARCHAR2, mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER, deptno NUMBER) RETURN NUMBER IS
The function accepts all arguments for the fields in the employee table except for the employee number. A value for this field is supplied by a sequence. The function returns the sequence number generated by the call to this function.
new_empno NUMBER(10); BEGIN SELECT emp_sequence.NEXTVAL INTO new_empno FROM dual; INSERT INTO emp VALUES (new_empno, name, job, mgr, hiredate, sal, comm, deptno); RETURN (new_empno); END hire_emp; PROCEDURE fire_emp(emp_id IN NUMBER) AS
The procedure deletes the employee with an employee number that corresponds to the argument emp_id. If no employee is found, then an exception is raised.
BEGIN DELETE FROM emp WHERE empno = emp_id; IF SQL%NOTFOUND THEN raise_application_error(-20011, 'Invalid Employee Number: ' || TO_CHAR(emp_id)); END IF; END fire_emp; PROCEDURE sal_raise (emp_id IN NUMBER, sal_incr IN NUMBER) AS
The procedure accepts two arguments. Emp_id is a number that corresponds to an employee number. Sal_incr is the amount by which to increase the employee's salary.
BEGIN -- If employee exists, then update salary with increase. UPDATE emp SET sal = sal + sal_incr WHERE empno = emp_id; IF SQL%NOTFOUND THEN raise_application_error(-20011, 'Invalid Employee Number: ' || TO_CHAR(emp_id)); END IF; END sal_raise; END employee_management;
To reference the types, items, and subprograms declared in a package specification, use the dot notation (you might need to specify the schema also). For example:
package_name.type_name package_name.item_name package_name.subprogram_name
The PL/SQL package DBMS_LDAP contains the functions and procedures which can be used by PL/SQL programmers to access data from LDAP servers. This section explains all of the API functions in detail. End users (client programmers) are expected to have read through the users guide and the examples (use cases). The information presented in this section should be used as a programming reference.
The DBMS_LDAP package shipped with RDBMS 8.1.7 can generate the following exceptions
The DBMS_LDAP package uses the following data-types.
init() initializes a session with an LDAP server. This actually establishes a connection with the LDAP server.
FUNCTION init (hostname IN VARCHAR2, portnum IN PLS_INTEGER ) RETURN SESSION;
| Value | Description |
|---|---|
|
SESSION (function return) |
A handle to an LDAP session which can be used for further calls into the API. |
DBMS_LDAP.init() is the first function that should be called in order to establish a session to the LDAP server. Function DBMS_LDAP.init() returns a "session handle," a pointer to an opaque structure that MUST be passed to subsequent calls pertaining to the session. This routine will return NULL and raise the "INIT_FAILED" exception if the session cannot be initialized.Subsequent to the call to init(), the connection has to be authenticated using DBMS_LDAP.bind_s or DBMS_LDAP.simple_bind_s().
DBMS_LDAP.simple_bind_s(), DBMS_LDAP.bind_s().
The function simple_bind_s can be used to perform simple username/password based authentication to the directory server.
FUNCTION simple_bind_s ( ld IN SESSION, dn IN VARCHAR2, passwd IN VARCHAR2) RETURN PLS_INTEGER;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle. |
|
dn |
The distinguished name of the user who is attempting to login |
|
passwd |
A text string containing the password. |
| Value | Description |
|---|---|
|
PLS_INTEGER (function return) |
DBMS_LDAP SUCCESS on a successful completion. If there was a problem, one of the following exceptions will be raised. |
DBMS_LDAP.simple_bind_s() can be used to authenticate a user whose directory distinguished name and directory password are known. It can be called only after a valid LDAP session handle is obtained from a call to DBMS_LDAP.init().
The function bind_s can be used to perform complex authentication to the directory server.
FUNCTION bind_s ( ld IN SESSION, dn IN VARCHAR2, passwd IN VARCHAR2, meth IN PLS_INTEGER ) RETURN PLS_INTEGER;
| Value | Description |
|---|---|
|
PLS_INTEGER (function return) |
DBMS_LDAP.SUCCESS on a successful completion. One of the following exceptions is raised if there was a problem. |
DBMS_LDAP.bind_s() can be used to authenticate a user. It can be called only after a valid LDAP session handle is obtained from a call to DBMS_LDAP.init().
DBMS_LDAP.init(), DBMS_LDAP.simple_bind_s().
The function unbind_s is used for closing an active LDAP session.
FUNCTION unbind_s (ld IN SESSION ) RETURN PLS_INTEGER;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle |
| Value | Description |
|---|---|
|
PLS_INTEGER (function return) |
SUCCESS on proper completion. One of the following exceptions is raised otherwise. |
The unbind_s() function, will send an unbind request to the server, close all open connections associated with the LDAP session and dispose of all resources associated with the session handle before returning. After a call to this function, the session handle ld is invalid and it is illegal to make any further LDAP API calls using ld.
DBMS_LDAP.bind_s(), DBMS_LDAP.simple_bind_s().
The function compare_s can be used to test if a particular attribute in a particular entry has a particular value.
FUNCTION compare_s ( ld IN SESSION, dn IN VARCHAR2, attr IN VARCHAR2, value IN VARCHAR2) RETURN PLS_INTEGER;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle |
|
dn |
The name of the entry to compare against |
|
attr |
The attribute to compare against. |
|
value |
A string attribute value to compare against |
| Value | Description |
|---|---|
|
PLS_INTEGER (function return) |
COMPARE_TRUE is the given attribute has a matching value. COMPARE_FALSE if the value of the attribute does not match the value given. |
| Exception | Description |
|---|---|
|
invalid_session |
Raised if hte session handle ld is invalid. |
|
general_error |
For all other errors. The error string associated with this exception will explain the error in detail. |
The function compare_s can be used to determine if the value of a given attribute stored in the directory server matches a certain value.This operation can only be performed on attributes whose syntax definition allows them to be compared. The compare_s function can only be called after a valid LDAP session handle has been obtained from the init() function and authenticated using the bind_s() or simple_bind_s() functions.
DBMS_LDAP.bind_s()
The function search_s performs a synchronous search in the LDAP server. It returns control to the PL/SQL environment only after all of the search results have been sent by the server or if the search request is timed-out by the server.
FUNCTION search_s ( ld IN SESSION, base IN VARCHAR2, scope IN PLS_INTEGER, filter IN VARCHAR2, attrs IN STRING_COLLECTION, attronly IN PLS_INTEGER, res OUT MESSAGE) RETURN PLS_INTEGER;
The function search_s() issues a search operation and does not return control to the user environment until all of the results have been returned from the server. Entries returned from the search (if any) are contained in the res parameter. This parameter is opaque to the caller. Entries, attributes, values, etc., can be extracted by calling the parsing routines described below.
DBMS_LDAP.search_st(), DBMS_LDAP.first_entry(), DBMS_LDAP.next_entry.
The function search_st performs a synchronous search in the LDAP server with a client-side timeout. It returns control to the PL/SQL environment only after all of the search results have been sent by the server or if the search request is timed-out by the client or the server.
FUNCTION search_st (ld IN SESSION, base IN VARCHAR2, scope IN PLS_INTEGER, filter IN VARCHAR2, attrs IN STRING_COLLECTION, attronly IN PLS_INTEGER, tv IN TIMEVAL, res OUT MESSAGE) RETURN PLS_INTEGER;
This function is very similar to DBMS_LDAP.search_s() except that it requires a timeout value to be given.
DBMS_LDAP.search_s(), DBML_LDAP.first_entry(), DBMS_LDAP.next_entry.
The function first_entry is used to retrieve the first entry in the result set returned by either search_s() or search_st()
FUNCTION first_entry (ld IN SESSION, msg IN MESSAGE ) RETURN MESSAGE;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle. |
|
msg |
The search result, as obtained by a call to one of the synchronous search routines. |
| Value | Description |
|---|---|
|
MESSAGE (function return) |
A handle to the first entry in the list of entries returned from the LDAP server. It is set to NULL if there was an error and an exception is raised. |
| Exception | Description |
|---|---|
|
invalid_session |
Raised if the session handle ld is invalid. |
|
invalid_message |
Raised if the incoming msg handle is invalid. |
The function first_entry() should always be the first function used to retrieve the results from a search operation.
DBMS_LDAP.next_entry(), DBMS_LDAP.search_s(), DBMS_LDAP.search_st()
The function next_entry() is used to iterate to the next entry in the result set of a search operation.
FUNCTION next_entry (ld IN SESSION, msg IN MESSAGE ) RETURN MESSAGE;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle. |
|
msg |
The search result, as obtained by a call to one of the synchronous search routines. |
| Value | Description |
|---|---|
|
MESSAGE |
A handle to the next entry in the list of entries returned from the LDAP server. It is set to null if there was an error and an exception is raised. |
| Exception | Description |
|---|---|
|
invalid_session |
Raised if the session handle, ld is invalid. |
|
invalid_message |
Raised if the incoming msg handle is invalid. |
The function next_entry() should always be called after a call to the function first_entry(). Also, the return value of a successful call to next_entry() should be used as `msg' argument used in a subsequent call to the function next_entry() to fetch the next entry in the list.
DBMS_LDAP.first_entry(), DBMS_LDAP.search_s(), DBMS_LDAP.search_st()
This function is used to count the number of entries in the result set. It can also be used to count the number of entries remaining during a traversal of the result set using a combination of the functions first_entry() and next_entry().
FUNCTION count_entries (ld IN SESSION, msg IN MESSAGE ) RETURN PLS_INTEGER;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle |
|
msg |
The search result, as obtained by a call to one of the synchronous search routines |
| Value | Description |
|---|---|
|
PLS INTEGER (function return) |
Non-zero if there are entries in the result set -1 if there was a problem. |
count_entries() returns the number of entries contained in a chain of entries; if an error occurs such as the res parameter being invalid, -1 is returned. The count_entries() call can also be used to count the number of entries that remain in a chain if called with a message, entry or reference returned by first_message(), next_message(), first_entry(), next_entry(), first_reference(), next_reference().
DBMS_LDAP.first_entry(), DBMS_LDAP.next_entry().
The function first_attribute() fetches the first attribute of a given entry in the result set.
FUNCTION first_attribute (ld IN SESSION, msg IN MESSAGE, ber_elem OUT BER_ELEMENT) RETURN VARCHAR2;
| Exception | Description |
|---|---|
|
invalid_session |
Raised if the session handle ld is invalid. |
|
invalid_message |
Raised if the incoming msg handle is invalid |
The handle to the BER_ELEMENT returned as a function parameter to first_attribute() should be used in the next call to next_attribute() to iterate through the various attributes of an entry. The name of the attribute returned from a call to first_attribute() can in turn be used in calls to the functions get_values() or get_values_len() to get the values of that particular attribute.
DBMS_LDAP.next_attribute(), DBMS_LDAP.get_values(), DBMS_LDAP.get_values_len(), DBMS_LDAP.first_entry(), DBMS_LDAP.next_entry().
The function next_attribute() fetches the next attribute of a given entry in the result set.
FUNCTION next_attribute ( ld IN SESSION, msg IN MESSAGE, ber_elem IN BER_ELEMENT) RETURN VARCHAR2;
| Value | Description |
|---|---|
|
VARCHAR2 (function return) |
The name of the attribute if it exists. |
| Exception | Description |
|---|---|
|
invalid_session |
Raised if the session handle ld is invalid. |
|
invalid_message |
Raised if the incoming msg handle is invalid. |
The handle to the BER_ELEMENT returned as a function parameter to first_attribute() should be used in the next call to next_attribute() to iterate through the various attributes of an entry. The name of the attribute returned from a call to next_attribute() can in turn be used in calls to the functions get_values() or get_values_len() to get the values of that particular attribute.
DBMS_LDAP.first_attribute(), DBMS_LDAP.get_values(), DBMS_LDAP.get_values_len(), DBMS_LDAP.first_entry(), DBMS_LDAP.next_entry().
The function get_dn() retrieves the X.500 distinguished name of given entry in the result set.
FUNCTION get_dn( ld IN SESSION, msg IN MESSAGE) RETURN VARCHAR2;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle. |
|
msg |
The entry whose dn is to be returned. |
| Value | Description |
|---|---|
|
VARCHAR2 (function return) |
The X.500 distinguished name of the entry as a PL/SQL string. NULL if there was a problem. |
The function get_dn() can be used to retrieve the dn of an entry as the program logic is iterating through the result set. This can in turn be used as an input to explode_dn() to retrieve the individual components of the dn.
DBMS_LDAP.explode_dn().
The function get_values() can be used to retrieve all of the values associated for a given attribute in a given entry.
FUNCTION get_values( ld IN SESSION, ldapentry IN MESSAGE, attr IN VARCHAR2) RETURN STRING_COLLECTION;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle |
|
ldapentry |
A valid handle to an entry returned from a search result |
|
attr |
The name of the attribute for which values are being sought |
| Value | Description |
|---|---|
|
STRING_COLLECTION (function return) |
A PL/SQL string collection containing all of the values of the given attribute NULL if there are no values associated with the given attribute |
| Exception | Description |
|---|---|
|
invalid session |
Raised if the session handle ld is invalid. |
|
invalid message |
Raised if the incoming "entry handle' is invalid. |
The function get_values() can only be called after the handle to entry has been first retrieved by call to either first_entry() or next_entry(). The name of the attribute may be known beforehand or can also be determined by a call to first_attribute() or next_attribute().The function get_values() always assumes that the data-type of the attribute it is retrieving is String. For retrieving binary data-types, get_values_len() should be used.
DBMS_LDAP.first_entry(), DBMS_LDAP.next_entry(), DBMS_LDAP.count_values(), DBMS_LDAP.get_values_len().
The function get_values_len() can be used to retrieve values of attributes that have a binary syntax.
FUNCTION get_values_len( ld IN SESSION, ldapentry IN MESSAGE, attr IN VARCHAR2) RETURN BINVAL_COLLECTION;
| Parameter | Description |
|---|---|
|
ld |
A valid LDAP session handle. |
|
ldapentrymsg |
A valid handle to an entry returned from a search result. |
|
attr |
The string name of the attribute for which values are being sought. |
| Value | Description |
|---|---|
|
BINVAL_COLLECTION (function return) |
A PL/SQL raw collection containing all the values of the given attribute. NULL if there are no values associated with the given attribute. |
| Exception | Description |
|---|---|
|
invalid_session |
Raised if the session handle ld is invalid. |
|
invalid_message |
Raised if the incoming entry handle is invalid |
The function get_values_len() can only be called after the handle to entry has been first retrieved by call to either first_entry() or next_entry().The name of the attribute may be known beforehand or can also be determined by a call to first_attribute() or next_attribute().This function can be used to retrieve both binary and non-binary attribute values.
DBMS_LDAP.first_entry(), DBMS_LDAP.next_entry(), DBMS_LDAP.count_values_len(), DBMS_LDAP.get_values().
The function delete_s() can be used to remove a leaf entry in the LDAP Directory Information Tree.
FUNCTION delete_s(ld IN SESSION, entrydn IN VARCHAR2) RETURN PLS_INTEGER;
| Parameter Name | Description |
|---|---|
|
ld |
A valid LDAP session |
|
entrydn |
The X.500 distinguished name of the entry to delete |
| Value | Description |
|---|---|
|
PLS_INTEGER (function return) |
DBMS_LDAP.SUCCESS if the delete operation was successful. And exception is raised otherwise. |
The function delete_s() can be used to remove only leaf level entries in the LDAP DIT. A leaf level entry is an entry that does not have any children or LDAP entries under it. It cannot be used to delete non-leaf entries.
DBMS_LDAP.modrdn2_s()
The function modrdn2_s() can be used to rename the relative distinguished name of an entry.
FUNCTION modrdn2_s (ld IN SESSION,entrydn in VARCHAR2 newrdn in VARCHAR2 deleteoldrdn IN PLS_INTEGER)RETURN PLS_INTEGER;
| Value | Description |
|---|---|
|
PLS_INTEGER (function return) |
DBMS_LDAP.SUCCESS if the operation was successful. An exception is raised otherwise. |
The function nodrdn2_s() can be used to rename the leaf nodes of a DIT. It simply changes the relative distinguished name by which they are known. The use of this function is being deprecated in the LDAP v3 standard. Please use rename_s() which can achieve the same foundation.
DBMS_LDAP.rename_s().
The function err2string() can be used to convert an LDAP error code to string in the local language in which the API is operating.
FUNCTION err2string( ldap_err IN PLS_INTEGER )RETURN VARCHAR2;
| Parameter | Description |
|---|---|
|
ldap_err |
An error number returned from one the API calls. |
| Value | Description |
|---|---|
|
VARCHAR@ (function return) |
A character string appropriately translated to the local language which describes the error in detail. |
| Exception | Description |
|---|---|
|
N/A |
None |
In this release, the exception handling mechanism automatically invokes this if any of the API calls encounter an error.
N/A
The function create_mod_array() allocates memory for array modification entries that will be applied to an entry using the modify_s() or add_s() functions.
FUNCTION create_mod_array (num IN PLS_INTEGER)RETURN MOD_ARRAY;
| Parameter | Description |
|---|---|
|
num |
The number of the attributes that you want to add or modify. |
| Value | Description |
|---|---|
|
MOD_ARRAY (function return) |
The data structure holds a pointer to an LDAP mod array. NULL if there was a problem. |
| Exception | Description |
|---|---|
|
N/A |
No LDAP specific exception will be raised |
This function is one of the preparation steps for DBMS_LDAP.add_s and DBMS_LDAP.modify_s. It is required to call DBMS_LDAP.free_mod_array to free memory after the calls to add_s or modify_s have completed.
DBMS_LDAP.populate_mod_array(), DBMS_LDAP.modify_s(), DBMS_LDAP.add_s(), and DBMS_LDAP.free_mod_array().
Populates one set of attribute information for add or modify operations.
PROCEDURE populate_mod_array (modptr IN DBMS_LDAP.MOD_ARRAY, mod_op IN PLS_INTEGER, mod_type IN VARCHAR2, modval IN DBMS_LDAP.STRING_COLLECTION);
| Value | Description |
|---|---|
|
N/A |
|
| Exception | Description |
|---|---|
|
invalid_mod_array |
Invalid LDAP mod array |
|
invalid_mod_option |
Invalid LDAP mod option |
|
invalid_mod_type |
Invalid LDAP mod type |
|
invalid_mod_value |
Invalid LDAP mod value |
This function is one of the preparation steps for DBMS_LDAP.add_s and DBMS_LDAP.modify_s. It has to be called after DBMS_LDAP.create_mod_array is called.
DBMS_LDAP.create_mod_array(), DBMS_LDAP.modify_s(), DBMS_LDAP.add_s(), and DBMS_LDAP.free_mod_array().
Populates one set of attribute information for add or modify operations. This procedure call has to happen after DBMS_LDAP.create_mod_array() is called.
PROCEDURE populate_mod_array (modptr IN DBMS_LDAP.MOD_ARRAY, mod_op IN PLS_INTEGER, mod_type IN VARCHAR2, modval IN DBMS_LDAP.BERVAL_COLLECTION);
| Value | Description |
|---|---|
|
N/A |
|
| Exception | Description |
|---|---|
|
invalid_mod_array |
Invalid LDAP mod array |
|
invalid_mod_option |
Invalid LDAP mod option |
|
invalid_mod_type |
Invalid LDAP mod type |
|
invalid_mod_value |
Invalid LDAP mod value |
This function is one of the preparation steps for DBMS_LDAP.add_s and DBMS_LDAP.modify_s. It has to happen after DBMS_LDAP.create_mod_array is called.
DBMS_LDAP.create_mod_array(), DBMS_LDAP.modify_s(), DBMS_LDAP.add_s(), and DBMS_LDAP.free_mod_array().
Performs a synchronous modification of an existing LDAP directory entry.
FUNCTION modify_s(ld IN DBMS_LDAP.SESSION, entrydn IN VARCHAR2, modptr IN DBMS_LDAP.MOD_ARRAY)RETURN PLS_INTEGER;
| Value | Description |
|---|---|
|
PLS_INTEGER |
The indication of the success or failure of the modification operation |
| Exception | Description |
|---|---|
|
invalid_session |
Invalid LDAP session |
|
invalid_entry_dn |
Invalid LDAP entry dn |
|
invalid_mod_array |
Invalid LDAP mod array |
This function call has to follow successful calls of DBMS_LDAP.create_mod_array() and DBMS_LDAP.populate_mod_array() .
DBMS_LDAP.create_mod_array(),DBMS_LDAP.populate_mod_array(), DBMS_LDAP.add_s(), and DBMS_LDAP.free_mod_array().
Adds a new entry to the LDAP directory synchronously. Before calling add_s, you have to call DBMS_LDAP.create_mod_array() and DBMS_LDAP.populate_mod_array().
FUNCTION add_s(ld IN DBMS_LDAP.SESSION, entrydn IN VARCHAR2, modptr IN DBMS_LDAP.MOD_ARRAY) RETURN PLS_INTEGER;
| Value | Description |
|---|---|
|
PLS_INTEGER |
The indication of the success or failure of the modification operation. |
| Exception | Description |
|---|---|
|
invalid_session |
Invalid LDAP session. |
|
invalid_entry_dn |
Invalid LDAP entry dn. |
|
invalid_mod_array |
Invalid LDAP mod array. |
The parent entry of the entry to be added must already exist in the directory. This function call has to follow successful calls of DBMS_LDAP.create_mod_array() and DBMS_LDAP.populate_mod_array() .
DBMS_LDAP.create_mod_array(),DBMS_LDAP.populate_mod_array(), DBMS_LDAP.modify_s(), and DBMS_LDAP.free_mod_array().
Frees the memory allocated by DBMS_LDAP.create_mod_array().
PROCEDURE free_mod_array(modptr IN DBMS_LDAP.MOD_ARRAY);
| Parameter | Description |
|---|---|
|
modptr |
This parameter is the handle to an LDAP mod structure, as returned by successful call to DBMS_LDAP.create_mod_array(). |
| Value | Description |
|---|---|
|
N/A |
|
| Exception | Description |
|---|---|
|
N/A |
No LDAP specific exception will be raised. |
N/A
DBMS_LDAP.populate_mod_array(), DBMS_LDAP.modify_s(), DBMS_LDAP.add_s(), and DBMS_LDAP.create_mod_array().
Counts the number of values returned by DBMS_LDAP.get_values().
FUNCTION count_values(values IN DBMS_LDAP.STRING_COLLECTION)RETURN PLS_INTEGER;
| Parameter | Description |
|---|---|
|
values |
The collection of string values. |
| Value | Description |
|---|---|
|
PLS_INTEGER |
The indication of the success or failure of the operation. |
| Exception | Description |
|---|---|
|
N/A |
No LDAP specific exception will be raised. |
N/A
DBMS_LDAP.count_values_len(), DBMS_LDAP.get_values().
Counts the number of values returned by DBMS_LDAP.get_values_len().
FUNCTION count_values_len (values IN DBMS_LDAP.BINVAL_COLLECTION)RETURN PLS_INTEGER;
| Parameter | Description |
|---|---|
|
values |
The collection of binary values. |
| Value | Description |
|---|---|
|
PLS_INTEGER |
The indication of the success or failure of the operation. |
| Exception | Description |
|---|---|
|
N/A |
No LDAP specific exception will be raised. |
N/A
DBMS_LDAP.count_values(), DBMS_LDAP.get_values_len().
Renames an LDAP entry synchronously.
FUNCTION rename_s(ld IN SESSION, dn IN VARCHAR2, newrdn IN VARCHAR2, newparent IN VARCHAR2, deleteoldrdn IN PLS_INTEGER, serverctrls IN LDAPCONTROL, clientctrls IN LDAPCONTROL)RETURN PLS_INTEGER;
| Value | Description |
|---|---|
|
PLS_INTEGER |
The indication of the success or failure of the operation. |
N/A
DBMS_LDAP.modrdn2_s().
Breaks a DN up into its components.
FUNCTION explode_dn (dn IN VARCHAR2, notypes IN PLS_INTEGER)RETURN STRING_COLLECTION;
| Value | Description |
|---|---|
|
STRING_COLLECTION |
An array of strings. If the dn can not be broken up, NULL will be returned. |
| Exception | Description |
|---|---|
|
invalid_entry_dn |
Invalid LDAP dn. |
|
invalid_notypes |
Invalid LDAP notypes value. |
N/A
DBMS_LDAP.get_dn().
Establishes an SSL(Secure Sockets Layer) connection over an existing LDAP connection.
FUNCTION open_ssl(ld IN SESSION,sslwrl IN VARCHAR2, sslwalletpasswd IN VARCHAR2, sslauth IN PLS_INTEGER)RETURN PLS_INTEGER;
| Value | Description |
|---|---|
|
PLS_INTEGER |
The indication of the success or failure of the operation. |
Need to call DBMS_LDAP.init() first to acquire a valid LDAP session.
DBMS_LDAP.init().
The DBMS_OBFUSCATION_TOOLKIT package allows an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms.
The Data Encryption Standard (DES), also known as the Data Encryption Algorithm (DEA) by the American National Standards Institute (ANSI) and DEA-1 by the International Standards Organization (ISO), has been a worldwide encryption standard for over twenty years. The banking industry has also adopted DES-based standards for transactions between private financial institutions, and between financial institutions and private individuals.
DES is a symmetric key cipher; that is, the same key is used to encrypt data as well as decrypt data. DES encrypts data in 64-bit blocks using a 56-bit key. The DES algorithm ignores 8 bits of the 64-bit key that is supplied; however, developers must supply a 64-bit key to the algorithm.
Triple DES (3DES) is a far stronger cipher than DES; the resulting ciphertext (encrypted data) is much harder to break using an exhaustive search: 2**112 or 2**168 attempts instead of 2**56 attempts. Triple DES is also not as vulnerable to certain types of cryptanalysis as is DES.
The DES procedures are the following:
Oracle installs this package in the SYS schema. You can then grant access the package to existing users and roles as needed. The package also grants access to the PUBLIC role so no explicit grant needs to be done.
Key management, including both generation and secure storage of cryptographic keys, is one of the most important aspects of encryption. If keys are poorly chosen or stored improperly, then it is far easier for a malefactor to break the encryption. Rather than using an exhaustive key search attack (that is, cycling through all the possible keys in hopes of finding the correct decryption key), cryptanalysts typically seek weaknesses in the choice of keys, or the way in which keys are stored.
Key generation is an important aspect of encryption. Typically, keys are generated automatically through a random-number generator. Provided that the random number generation is cryptographically secure, this can be an acceptable form of key generation. However, if random numbers are not cryptographically secure, but have elements of predictability, the security of the encryption may be easily compromised.
The DBMS_OBFUSCATION_TOOLKIT package does not generate encryption keys nor does it maintain them. Care must be taken by the application developer to ensure the secure generation and storage of encryption keys used with this package. Furthermore, the encryption and decryption done by the DBMS_OBFUSCATION_TOOLKIT takes place on the server, not the client. If the key is passed over the connection between the client and the server, the connection must be protected using Oracle Advanced Security, otherwise the key is vulnerable to capture over the wire.
Key storage is one of the most important, yet difficult aspects of encryption and one of the hardest to manage properly. To recover data encrypted with a symmetric key, the key must be accessible to the application or user seeking to decrypt data. The key needs to be easy enough to retrieve that users can access encrypted data when they need to without significant performance degradation. The key also needs to be secure enough that it is not easily recoverable by unauthorized users trying to access encrypted data they are not supposed to see.
The three options available to a developer are:
Storing the keys in the database cannot always provide "bullet-proof" security if you are trying to protect data against the DBA accessing encrypted data (since an all-privileged DBA could access tables containing encryption keys), but it can provide security against the casual snooper, or against someone compromising the database files on the operating system. Furthermore, the security you can obtain by storing keys in the database does not have to be bullet-proof in order to be extremely useful.
For example, suppose you want to encrypt an employee's security number, one of the columns in table EMP. You could encrypt each employee's security number using a key which is stored in a separate column in EMP. However, anyone with SELECT access on the EMP table could retrieve the encryption key and decrypt the matching social security number. Alternatively, you could store the encryption keys in another table, and use a package to retrieve the correct key for the encrypted data item, based on a primary key-foreign key relationship between the tables.
A developer could envelope both the DBMS_OBFUSCATION_TOOLKIT package and the procedure to retrieve the encryption keys supplied to the package. Furthermore, the encryption key itself could be transformed in some way (for example, XORed with the foreign key to the EMP table) so that the key itself is not stored in easily recoverable form.
Oracle recommends using the wrap utility of PL/SQL to obfuscate the code within a PL SQL package itself that does the encryption. That prevents people from breaking the encryption by looking at the PL/SQL code that handles keys, calls encrypting routines, etc.. In other words, use the wrap utility to obfuscate the PL/SQL packages themselves.
This scheme is secure enough to prevent users with SELECT access to EMP from reading unencrypted sensitive data, and a DBA from easily retrieving encryption keys and using them to decrypt data in the EMP table. It can be made more secure by changing encryption keys regularly, or having a better key storage algorithm (so the keys themselves are encrypted, for example).
Storing keys in the operating system (e.g. in a flat file) is another option. Oracle8i allows you to make callouts from PL/SQL, which you could use to retrieve encryption keys. If you store keys in the O/S and make callouts to retrieve the keys, then the security of your encrypted data is only as secure as the protection of the key file on the O/S. Of course, a user retrieving keys from the operating system would have to be able to either access the Oracle database files (to decrypt encrypted data), or be able to gain access to the table in which the encrypted data is stored as a legitimate user.
If you ask user to supply the key, it is crucial that you use network encryption, such as that provided by Oracle Advanced Security, so the key is not passed from client to server in the clear. Furthermore, you have to rely on the user to remember the key, or your data is nonrecoverable.
The DESEncrypt procedure generates the encrypted form of the input data. An example of the DESEncrypt procedure appears at the end of this chapter.
The DES algorithm encrypts data in 64-bit blocks using a 56-bit key. The DES algorithm throws away 8 bits of the supplied key (the particular bits which are thrown away is beyond the scope of this documentation). However, developers using the algorithm must supply a 64-bit key or the package will raise an error.
Table 6-91 and Table 6-92 list the parameters for the DESEncrypt syntax as well as their modes, types, and descriptions.
| Parameter Name | Mode | Type | Description |
|---|---|---|---|
|
input |
IN |
RAW |
data to be encrypted |
|
key |
IN |
RAW |
encryption key |
|
encrypted_data |
OUT |
RAW |
encrypted data |
| Parameter Name | Mode | Type | Description |
|---|---|---|---|
|
input_string |
IN |
VARCHAR2 |
string to be encrypted |
|
key_string |
IN |
VARCHAR2 |
encryption key string |
|
encrypted_string |
OUT |
VARCHAR2 |
encrypted string |
If the input data or key given to the PL/SQL DESEncrypt procedure is empty, then the procedure raises the error ORA-28231 "Invalid input to Obfuscation toolkit".
If the input data given to the DESEncrypt procedure is not a multiple of 8 bytes, the procedure raises the error ORA-28232 "Invalid input size for Obfuscation toolkit".
If the user tries to double encrypt data using the DESEncrypt procedure, then the procedure raises the error ORA-28233 "Double encryption not supported".
The DESEncryption procedure has two restrictions. The first is that the DES key length for encryption is fixed at 56 bits; you cannot alter this key length.
The second is that you cannot execute multiple passes of encryption. That is, you cannot re-encrypt previously encrypted data by calling the function twice.
The purpose of the DESDecrypt procedure is to generate the decrypted form of the input data. An example of the DESDecrypt procedure appears at the end of this chapter.
Table 6-93 and Table 6-94 list the parameters for the DESDecrypt syntax, their modes, types, and descriptions.
| Parameter Name | Mode | Type | Description |
|---|---|---|---|
|
input |
IN |
RAW |
Data to be decrypted |
|
key |
IN |
RAW |
Decryption key |
|
decrypted_data |
OUT |
RAW |
Decrypted data |
| Parameter Name | Mode | Type | Description |
|---|---|---|---|
|
input_string |
IN |
VARCHAR2 |
String to be decrypted |
|
key_string |
IN |
VARCHAR2 |
Decryption key string |
|
decrypted_string |
OUT |
VARCHAR2 |
Decrypted string |
If the input data or key given to the PL/SQL DESDecrypt function is empty, then Oracle raises ORA error 28231 "Invalid input to Obfuscation toolkit".
If the input data given to the DESDecrypt function is not a multiple of 8 bytes, Oracle raises ORA error 28232 "Invalid input size for Obfuscation toolkit".
The DES key length for encryption is fixed at 64 bits (of which 56 bits are used); you cannot alter this key length.
Following is a sample PL/SQL program for your reference. Segments of the code are numbered and contain narrative text explaining portions of the code.
DECLARE input_string VARCHAR2(16) := 'tigertigertigert'; raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string); key_string VARCHAR2(8) := 'scottsco'; raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string); encrypted_raw RAW(2048); encrypted_string VARCHAR2(2048); decrypted_raw RAW(2048); decrypted_string VARCHAR2(2048); error_in_input_buffer_length EXCEPTION; PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232); INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) := '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***'; double_encrypt_not_permitted EXCEPTION; PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233); DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) := '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
-- 1. Begin testing raw data encryption and decryption BEGIN dbms_output.put_line('> ========= BEGIN TEST RAW DATA ========='); dbms_output.put_line('> Raw input : ' || UTL_RAW.CAST_TO_VARCHAR2(raw_input)); BEGIN dbms_obfuscation_toolkit.DESEncrypt(input => raw_input, key => raw_key, encrypted_data => encrypted_raw ); dbms_output.put_line('> encrypted hex value : ' || rawtohex(encrypted_raw)); dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw, key => raw_key, decrypted_data => decrypted_raw); dbms_output.put_line('> Decrypted raw output : ' || UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw)); dbms_output.put_line('> '); if UTL_RAW.CAST_TO_VARCHAR2(raw_input) = UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN dbms_output.put_line('> Raw DES Encyption and Decryption successful'); END if; EXCEPTION WHEN error_in_input_buffer_length THEN dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG); END; dbms_output.put_line('> ');
-- 2. Begin testing string data encryption and decryption dbms_output.put_line('> ========= BEGIN TEST STRING DATA =========');
BEGIN dbms_output.put_line('> input string : ' || input_string); dbms_obfuscation_toolkit.DESEncrypt( input_string => input_string, key_string => key_string, encrypted_string => encrypted_string ); dbms_output.put_line('> encrypted hex value : ' || rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string))); dbms_obfuscation_toolkit.DESDecrypt( input_string => encrypted_string, key_string => key_string, decrypted_string => decrypted_string ); dbms_output.put_line('> decrypted string output : ' || decrypted_string); if input_string = decrypted_string THEN dbms_output.put_line('> String DES Encyption and Decryption successful'); END if; EXCEPTION WHEN error_in_input_buffer_length THEN dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG); END; dbms_output.put_line('> ');
The DES3Encrypt procedure generates the encrypted form of the input data by passing it through the Triple DES encryption algorithm. An example of the DESEncrypt procedure appears at the end of this chapter.
Oracle's implementation of 3DES supports either a 2-key or 3-key implementation, in outer cipher-block-chaining (CBC) mode.
A developer choosing to use Oracle's 3DES interface with a 2-key implementation must supply a single key of 128 bits as an argument to the DES3Encrypt procedure. Oracle then breaks the supplied key into two 64-bit keys. As with DES, the 3DES algorithm throws away 8 bits of each derived key (the particular bits which are thrown away is beyond the scope of this documentation). However, developers using the algorithm must supply a single 128-bit key for the 2-key 3DES implementation or the package will raise an error. The DES3Encrypt procedure uses the 2-key implementation by default.
A developer using Oracle's 3DES interface with a 3-key implementation must supply a single key of 192 bits as an argument to the DES3Encrypt procedure. Oracle then breaks the supplied key into three 64-bit keys. As with DES, the 3DES algorithm throws away 8 bits of each derived key (the particular bits which are thrown away is beyond the scope of this documentation). However, developers using the algorithm must supply a single 192-bit key for the 3-key 3DES implementation or the package will raise an error.
Table 6-95 and Table 6-96 list the parameters for the DES3Encrypt syntax, their modes, types, and descriptions.
| Parameter Name | Mode | Type | Description |
|---|---|---|---|
|
input |
IN |
RAW |
data to be encrypted |
|
key |
IN |
RAW |
encryption key |
|
encrypted_data |
OUT |
RAW |
encrypted data |
| Parameter Name | Mode | Type | Description |
|---|---|---|---|
|
input_string |
IN |
VARCHAR2 |
string to be encrypted |
|
key_string |
IN |
VARCHAR2 |
encryption key string |
|
encrypted_string |
OUT |
VARCHAR2 |
encrypted string |
If the input data or key given to the PL/SQL DES3Encrypt procedure is empty, then the procedure raises the error ORA-28231 "Invalid input to Obfuscation toolkit".
If the input data given to the DES3Encrypt procedure is not a multiple of 8 bytes, the procedure raises the error ORA-28232 "Invalid input size for Obfuscation toolkit".
If the user tries to double encrypt data using the DES3Encrypt procedure, then the procedure raises the error ORA-28233 "Double encryption not supported".
The DES3Encrypt procedure has two restrictions. The first is that the DES key length for encryption is fixed at 128 bits (for 2-key DES) or 192 bits (for 3-key DES); you cannot alter these key lengths.
The second is that you cannot execute multiple passes of encryption using 3DES. The 3DES algorithm itself encrypts data multiple times; however, you cannot call the 3DESencrypt function itself more than once to encrypt the same data using 3DES.
The purpose of the DES3Decrypt procedure is to generate the decrypted form of the input data. An example of the DES3Decrypt procedure appears at the end of this chapter.
Table 6-97 and Table 6-98 list the parameters for the DES3Decrypt syntax, their modes, types, and descriptions.
| Parameter Name | Mode | Type | Description |
|---|---|---|---|
|
input |
IN |
RAW |
Data to be decrypted |
|
key |
IN |
RAW |
Decryption key |
|
decrypted_data |
OUT |
RAW |
Decrypted data |
| Parameter Name | Mode | Type | Description |
|---|---|---|---|
|
input_string |
IN |
VARCHAR2 |
String to be decrypted |
|
key_string |
IN |
VARCHAR2 |
Decryption key string |
|
decrypted_string |
OUT |
VARCHAR2 |
Decrypted string |
If the input data or key given to the DES3Decrypt procedure is empty, then the procedure raises the error ORA-28231 "Invalid input to Obfuscation toolkit".
If the input data given to the DES3Decrypt procedure is not a multiple of 8 bytes, the procedure raises the error ORA-28232 "Invalid input size for Obfuscation toolkit".
As stated above, a developer must supply a single key of either 128 bits for a 2-key implementation (of which only 112 are used), or a single key of 192 bits for a 3-key implementation (of which 168 bits are used). Oracle automatically truncates the supplied key into 56-bit lengths for decryption. This keylength is fixed and cannot be altered.
Following is a sample PL/SQL program for your reference. Segments of the code are numbered and contain narrative text explaining portions of the code.
DECLARE input_string VARCHAR2(16) := 'tigertigertigert'; raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string); key_string VARCHAR2(8) := 'scottsco'; raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string); encrypted_raw RAW(2048); encrypted_string VARCHAR2(2048); decrypted_raw RAW(2048); decrypted_string VARCHAR2(2048); error_in_input_buffer_length EXCEPTION; PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232); INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) := '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***'; double_encrypt_not_permitted EXCEPTION; PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233); DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) := '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***'; -- 1. Begin testing raw data encryption and decryption BEGIN dbms_output.put_line('> ========= BEGIN TEST RAW DATA ========='); dbms_output.put_line('> Raw input : ' || UTL_RAW.CAST_TO_VARCHAR2(raw_input)); BEGIN dbms_obfuscation_toolkit.DES3Encrypt(input => raw_input, key => raw_key, encrypted_data => encrypted_raw ); dbms_output.put_line('> encrypted hex value : ' || rawtohex(encrypted_raw)); dbms_obfuscation_toolkit.DES3Decrypt(input => encrypted_raw, key => raw_key, decrypted_data => decrypted_raw); dbms_output.put_line('> Decrypted raw output : ' || UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw)); dbms_output.put_line('> '); if UTL_RAW.CAST_TO_VARCHAR2(raw_input) = UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN dbms_output.put_line('> Raw DES3 Encyption and Decryption successful'); END if; EXCEPTION WHEN error_in_input_buffer_length THEN dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG); END; dbms_output.put_line('> '); -- 2. Begin testing string data encryption and decryption dbms_output.put_line('> ========= BEGIN TEST STRING DATA ========='); BEGIN dbms_output.put_line('> input string : ' || input_string); dbms_obfuscation_toolkit.DES3Encrypt( input_string => input_string, key_string => key_string, encrypted_string => encrypted_string ); dbms_output.put_line('> encrypted hex value : ' || rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string))); dbms_obfuscation_toolkit.DES3Decrypt( input_string => encrypted_string, key_string => key_string, decrypted_string => decrypted_string ); dbms_output.put_line('> decrypted string output : ' || decrypted_string); if input_string = decrypted_string THEN dbms_output.put_line('> String DES3 Encyption and Decryption successful'); END if; EXCEPTION WHEN error_in_input_buffer_length THEN dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG); END; dbms_output.put_line('> '); /
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|