NAME

OpenCA::DBI - Perl Certificates DBI Extension.

SYNOPSIS

use OpenCA::DBI;

DESCRIPTION

This module makes available a lot of low level database functionality to the OpenCA-scripts. The interface is like the OpenCA::DB-module's interface.

Nevertheless the configuration is completely different because this module tries to support several databases and not only one. The documentation is devided into

dependencies
configuration - new ()
public functions
private functions

The modules name comes from the DBI module which is used for the databaseconnectivity.

DEPENDENCIES

the module use the following other modules

OpenCA::REQ
OpenCA::X509
OpenCA::CRL
OpenCA::CRR
OpenCA::OpenSSL
OpenCA::Tools
DBI
OpenCA::DBIS (actually not but in some weeks)

USAGE

$new_object = new OpenCA::DBI (option1 => $value1, ...);

All names are case sensitive !!!

CONFIGURATION - new ()

This is perhaps the most complicated part for the users of this module.

You configure and init a new object of the class OpenCA::DBI by calling the function new. The usage is:

The often used remote and local means remote database and local database.

Actually OpenCA does not have a sync-module so the use of a local database insteed of the central remote database makes absolut no sense. The code for the documented options is written but deactivated by enforcing special settings of the options.

SHELL => $object

object of class OpenCA::OpenSSL

mode => mode_name

the following modes are available

mode         |  db 
----------------------
secure       |  remote
standard     |  local

"secure" is enforced because the synchronization doing module of OpenCA is not implemented. Attention, the code is implemented for this feature so if you take the code and remove the line

$self->{mode}          = "secure";

then the described mechanism works.

failsafe => (on|off)

if failsafe is on and an action fails a failover is encouraged by using the other database.

Actually failsafe is always set to "off". (Code is written and works so you can activate this feature but this is senseless because the Sync-module is not implemented yet.)

second_chance => (yes|no)

If no backup is set due to the settings of failsafe a "yes" enforce a second try on the standarddatabase.

Actually "no" is enforced but manually you can made this option working. The enforced "no" is only there for easier testing.

logsecurity => integer_value

The default setting is 0. You can set integer values higher than -1 (so >=0).

logperformance => integer_value

The default setting is 8. You can set integer values higher than 7 (so >=8).

So this is the time to write something about the signing code.

The log signing algorithm creates a signature from the following logrecords:

-> actual (serial of the actual logrecord)
-> actual - 2**0
-> actual - 2**1
-> ...
-> actual - 2**k (last value with result >= 0)

1. Now the records 0..(logperformance-1) are ignored.
2. The records logperformance..t are removed until k-t equals logsecurity. If logsecurity equals 0 then the second operation is not performed and all records are signed.

logperformance is an option which protects us against the possibilities of systems which has a high system load factor. logsecurity defines the used references.

remoteType => Pg

DBI type of the database

remoteName => database_name

be warned this string is a must for every databasesystem! Several databases does not need the setting of host or port because the information is stored in an interfaces-file like on Sybase. So the "database_name" is the string needed by the databasedrivers of the different vendors. For more information please look at the documentation of the DBD::vendor_name drivers (e.g. Informix, Interbase, mSQL, MySQL, Oracle, Pg, Sybase)

remoteHost => hostname

host where the database is located - so remote has only a logical meaning. Actually until you use no VPN-software it is strongly recommended that the database is on your local machine. The use of DNS is not necessary and not recommended because the use of pure IP protects you against DNS spoofing. Alternatively you can insert the used hostname in your /etc/hosts

remotePort => port_number

remoteUser => user

remotePasswd => passwd

localType => Pg

localName => database_name

localHost => hostname

localPort => port_number

localUser => user

localPasswd => passwd

CERT_FILE => cert.pem

This is for logging only. The cert is used for signing.

KEY_FILE => priv.key

This is for logging only. The key is used for signing.

PASSWD => passwd

This is for logging only. The passphrase is used for signing.

DEBUG => true_value

If you enter nothing then there is nothing. If you enter a value which perl interprets as true then debugging is on.

(The values of mode, failsafe and second_chance are not case sensitive.)

PUBLIC FUNCTIONS

The supported public functions are:

new - see CONFIGURATION -new ()

Please see the description of the configuration of OpenCA::DBI which describe the "new" function.
initDB

This function initializes the databases. It knows the following options:
DB => @databases
You can pass an array which can include "remote", "local" or "remote" and "local". If nothing is included then the value is set to "remote". The databases will then initialized.

This means the function tries to do all the sql-create commands which are needed for operation of the OpenCA::DBI module.

These tables are:
```
	request
        ca_certificate
	certificate
	crr
	crl
	log
	signature
```
MODE => (NONE|FORCE|FORCE_LOCAL|FORCE_REMOTE|FORCE_ALL)
If successful then the function returns a 1. If not successfull then undef is returned. Please read this section carefully because I perhaps switch to returnvalue 1 for success. Comments are welcome.
storeItem
DATATYPE => (old_type|basic_type)
The old_types which are accepted are the same like in the OpenCA::DB module. These are strings like PENDING_REQUEST or REVOKED_CERTIFICATE.

The basic_type means you can enter normal basic types like:
```
REQUEST
CA_CERTIFICATE
CERTIFICATE
CRR
CRL
```
If you use basic types and you not set the option "status" status is setting to "VALID". If you use old_types then the status will be extracted from the string via the private function getStatus.
STATUS => (VALID|RENEWED|UPDATED|PENDING|APPROVED| SUSPENDED|REVOKED|DELETED|ARCHIVED|EXPIRED|)

The status can be any of the above terms. If status is not seeded I use first the DATATYPE if it is an old_type and if not not then the status is "VALID".

INFORM => (PEM|DER|SPKAC|)

This option is actually a little bit unclear because I get the data via objects so I don't need the format because I get the data directly from the object. If the format is not detectable I use PEM.

Résumé: this is waste!

OBJECT => $openca_object

This is an OpenCA object which has to be stored. This could be OpenCA::REQ OpenCA::X509 OpenCA::CRR OpenCA::CRL

MODULETYPE => (CA|PKIManager|RA|WebGateway|)

This for logging only. If you set it you can read the log in the database and can verify via OpenCA::DBI::MODULETYPE->{number_from_db} the moduletype which has done this action.

MODULE => module_name

This is for logging only. If you set it you can read the log in the database and can verify which module has done this action (it is stored as ascii so it is humanreadable - means you can read it as databaseadmin).
Last a small comment to the code which do the signing. Because this code is a highlevel feature I will move this in the future to the OpenCA::DBIS module so not be shocked if you are looking into the code and don't find the code. If the code moved you will find a notice about the version were the move starts here.

Version: not moved yet.
getItem
DATATYPE => (old_type|basic_type)
The old_types which are accepted are the same like in the OpenCA::DB module. These are strings like PENDING_REQUEST or REVOKED_CERTIFICATE.

The basic_type means you can enter normal basic types like:
```
REQUEST
CERTIFICATE
CRR
CRL
```
If you use basic types and you not set the option "status" status is setting to "VALID". If you use old_types then the status will be extracted from the string via the private function getStatus.
STATUS => (VALID|RENEWED|UPDATED|PENDING|APPROVED| SUSPENDED|REVOKED|DELETED|ARCHIVED|EXPIRED|)

The status can be any of the above terms. If status is not seeded I use first the DATATYPE if it is an old_type and if not then the status is ignored.

KEY => key

This is the key (the unique identifier) of this special requested object. So this can be a serial number or a md5 etc..

If KEY is not given then I return the last element. This feature is useful for CRLs and only actually allowed for CRLs!!! If you search the latest one you have only to call:

$openca_dbi->getItem (DATATYPE => "CRL");

I think this is a good feature.

If you need this feature for other objects you must uncomment the following line in getItem:

return if ((not $serial) && ($table ne "CRL"));

MODE => (RAW|)

RAW causes the return of the plain text of stored data. Nothing causes the return of an object.
getNextItem

The same options like getItem except MODE which is not supported. An object will be returned at every time. The function determines only the next key itself and then passes the request to the function getItem. The option KEY is required.
getPrevItem

The same options like getItem except MODE which is not supported. An object will be returned at every time. The function determines only the next key itself and then passes the request to the function getItem. The option KEY is required.
destroyItem

DATATYPE => (old_style|basic_type)

KEY => key
destroyItem really delete the request from the database. Attention this function is reserved for a fututre recovery algorithm! therefore the operation will not be logged!

So please "hands off" if you not very shure what you are doing!!!

Use deleteItem (which do nothing ;-)) or better (best)

storeItem (DATATYPE= xyz, MODE=>"UPDATE", STATUS=>"DELETED", OBJECT=>xyz);>
deleteItem

This is a dummy to be proof against old codeparts which think they must remove the object from VALID_CERTIFICATE after they store the certificate to REVOKED_CERTIFICATE.
elements
DATATYPE => (old_type|basic_type)
The old_types which are accepted are the same like in the OpenCA::DB module. These are strings like PENDING_REQUEST or REVOKED_CERTIFICATE.

The basic_type means you can enter normal basic types like:
```
REQUEST
CERTIFICATE
CRR
CRL
```
If you use basic types and you not set the option "status" the function returns the number of all elements of this table.
STATUS => (VALID|RENEWED|UPDATED|PENDING|APPROVED| SUSPENDED|REVOKED|DELETED|ARCHIVED|EXPIRED|)

If not used the scan performs on the hole table.
This function counts the elements which are in the same table and have the same status (if status is set via STATUS or DATATYPE).
searchItem

The options are the well known options DATATYPE, MODE and STATUS (please see above).

The new options are all possible searchattributes. To get them please use the new function getAttributes! The old functions support some types not. The function getAttribute don't return the unique identifiers, but you can get the unique identifiers of the tables via OpenCA::DBI::SQL->{VARIABLE}->{tablename."_SERIAL"}[0] (Attention - the tablename is stored in big letters!)
getTimeString

This function returns an ISO-timestring (2001-01-14 18:24:06).

Unchanged public functions (from OpenCA::DB v0.8.7a):

rows

Same options like searchItem. The function calls searchItem and count the returned objects. Simple but errorproof

Working but unclear status (private or public???) (directly taken from OpenCA::DB v0.8.7a)

listItem

This function is directly taken over from OpenCA::DB v0.8.7a. Because I don't know for what it is used I don't change and use it.

The following unsupported functions are not supported because they perform operations which are not necessary or possible for RDBMSs (Relational DataBase Management Systems). These systems take care by themselves on things like number of elements, locks, next and preview operators etc..

Unsupported functions (empty dummies):

getIndex
saveIndex
getReferences

I don't know what this function does.

If someone can emulate the unsupported functions or can determine the state of listItem please contact me (Michael Bell <michael.bell@web.de>.

PRIVATE FUNCTIONS

The new private functions are:

storeItem_getArguments

is called from storeItem and returns a hash with all needed variables
storeItem_checkData

checks the data which will be transmitted to storeItem
storeItem_update

performs the update-operations
storeItem_insert

performs the insert operations
storeItem_logging

build the data for the log and store the data into database
storeItem_signing

if logsignng is activated then this function performs the signing and store the signatur einto the database
getTable

It extract from a datatype (old or new) the tableand return it.
getStatus

It extracts from STATUS and DATATYPE the status. If STATUS is present DATATYPE will be ignored.
getSequence

This function has the job to return a new ACTION_NUMBER for the table log. This is done by a function to keep the vendordependent code away from the not vendordependent code. Sequences, sequence generators etc. are not standardized. The option is a db_hash_write called hash. Pleae see doConnect for a detailed description of this code.
doConnect, doQuery, doRollback, doCommit, doDisconnect

All of these function get an hash as option. The hash is structured like follows:
```
my %db_hash = (STATUS => 0,
               DBH    => 0,
               STH    => [],
               QUERY  => "",
               BIND_VALUES => [],
              );
```
STATUS is the errorstate of the connection. Please never touch this value it is absolut internal and highly critical.

ERRORS include all errors which are happened during the use of this hash. All errors are available via $openca_dbi_object::ERROR->{error_name}.

DBH is the actual used databasehandle from DBI->connect.

STH is an array with all statementhandles of the actual DBH. The handle for the last doQuery is available via $hash{STH}[scalar (@{$hash{STH}}) -1]. Actually I don't use other than the last result of a statement but somewhere in the future ...

QUERY this is the actual query which you have only to set for doQuery.

BIND_VALUES this is the actual array of binded values which you have only to set for doQuery.

getBaseType
listItems (not used but perhaps not private!!!)
byKey (not used)
getSearchAttributes

The only argument is the tablename via getiSearchAttributes ("REQUEST"); The returned value is an array with the available attributes.
hash2txt
txt2hash (not used)

SUPPORTED DATABASES

Every subscribed item has the same behaviour for remoteXYZ and localXYZ.

PostgreSQL

option           |  default  |  required
----------------------------------------
remoteType       |    Pg     |    yes
remoteName       |    -      |    yes
remoteHost       | localhost |    no
remotePort       |   5432    |    no
remoteUser       |    -      |    yes
remotePasswd     |    -      |    yes

If you would not set the remoteUser then DBD::Pg would use the username of the processowner. Because this is special for the Pg-driver this feature is not supported or used by the OpenCA::DBI-module and cause an undef return value for the new () call.

Be shure that YOU set a password!

You can test this with nessus (http://www.nessus.org).

This was and is the most common error of us (some anonymous people of the staff of the datacenter of the Humboldt-University of Berlin ;-D).

MySQL

Attention the name which you must enter is mysql!!!

option           |  default  |  required
----------------------------------------
remoteType       |   mysql   |    yes
remoteName       |    -      |    yes
remoteHost       | localhost |    no
remotePort       |    ?      |    no
remoteUser       |    -      |    yes
remotePasswd     |    -      |    yes

Because I have not the time to test MySQL please write any mistake in this documentation suddenly to me. I don't know the standard MySQL-Port so I hope the DBD::mysql module knows it ;-)

Please don't use MySQL in a realworld-PKI. This database is deprecated because it doesn't support the ACID-features actually and I have no chance to grant the integrity of your PKI-DB.

A tomicity
C onsistency
I solation
D urability

DB2

option           |  default  |  required
----------------------------------------
remoteType       |    DB2    |    yes
remoteName       |    -      |    yes
remoteHost       |    -      |    no
remotePort       |    -      |    no
remoteUser       |    -      |    yes
remotePasswd     |    -      |    yes

If you get the error "The total environment is not set ..." Please read the CAVEATS-file like recommended by IBM. If this not help then you must do the following:

$HOME is the home of the db2-instance

cp $HOME/sqllib/db2profile $HOME_OF_HTTPD_USER/

vi /etc/init.d/apache (or where ever your apache- startupscript is placed) > . $HOME_OF_HTTPD_USER/db2profile

vi /etc/httpd/httpd.conf go to the position of your virtual host or otherwise enter it in the right global context >PassEnv LD_LIBRARY_PATH >PassEnv PATH >PassEnv LIBPATH >PassEnv CLASSPATH >PassEnv DB2INSTANCE >PassEnv DB2DIR >PassEnv INSTHOME

Perhaps you don't need some of the environmentvariables but my installation works with this environment and this should only be a point where you can start.

Oracle

option           |  default  |  required
----------------------------------------
remoteType       |  Oracle   |    yes
remoteName       |    -      |    yes
remoteHost       |    -      |    no
remotePort       |    -      |    no
remoteUser       |    -      |    yes
remotePasswd     |    -      |    yes

Warning, this port is completely untested. It was only added because of a user request but I get never a feedback.

LICENSE

This library is free for commercial and non-commercial use as long as the following conditions are aheared to. The following conditions apply to all code found in this distribution, be it the RC4, RSA, lhash, DES, etc., code; not just the SSL code. The documentation included with this distribution is covered by the same copyright terms

Copyright remains Massimiliano Pala's and Michael Bell's, and as such any Copyright notices in the code are not to be removed. If this package is used in a product, Massimiliano Pala and Michael Bell should be given attribution as the author of the parts of the library used. This can be in the form of a textual message at program startup or in documentation (online or textual) provided with the package.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: 1. Redistributions of source code must retain the copyright notice, this list of conditions and the following disclaimer. 2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. 3. All advertising materials mentioning features or use of this software must display the following acknowledgement: "This product includes OpenCA software written by Massimiliano Pala (madwolf@openca.org) and the OpenCA Group (www.openca.org)" 4. If you include any Windows specific code (or a derivative thereof) from some directory (application code) you must include an acknowledgement: "This product includes OpenCA software (www.openca.org)"

THIS SOFTWARE IS PROVIDED BY OPENCA DEVELOPERS ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

The licence and distribution terms for any publically available version or derivative of this code cannot be changed. i.e. this code cannot simply be copied and put under another distribution licence [including the GNU Public Licence.]

AUTHORS

Massimiliano Pala <madwolf@openca.org> (OpenCA::DB)
Michael Bell <michael.bell@web.de> (OpenCA::DBI)

P.S. EXAMPLE

	Block: {
	doConnect
	  if doConnect returns negative then last BLOCK 
	  (final error, all options failsafe or 
	  second_chance did not help.)
	  best thing is now to say return -1; insteed of
      	  last BLOCK;
	doQuery until the first returncode is -1
        then doRollback
             doDisconnect
	if never doQery fails 
	then doCommit
	if returnvalue is -1
	then doRollback
     	     doDisconnect
	else doDisconnect

	if somethig fails except doConnect "next BLOCK"
	}

you can repeat this block so often as you want until the first time doConnect returns undef. So long this not happens you can try to get a successful transaction.

1 POD Error

The following errors were encountered while parsing the POD:

Around line 3949:: Expected text after =item, not a bullet

To install OpenCA::DBI, copy and paste the appropriate command in to your terminal.

cpanm

cpanm OpenCA::DBI

CPAN shell

perl -MCPAN -e shell
install OpenCA::DBI

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)