/****************************************************************************
*																			*
*						  cryptlib DBMS Misc Interface						*
*						Copyright Peter Gutmann 1996-2007					*
*																			*
****************************************************************************/

#if defined( INC_ALL )
  #include "crypt.h"
  #include "keyset.h"
  #include "dbms.h"
#else
  #include "crypt.h"
  #include "keyset/keyset.h"
  #include "keyset/dbms.h"
#endif /* Compiler-specific includes */

#ifdef USE_DBMS

/* The table structure for the various DBMS tables is (# = indexed, 
   * = unique, + = certificate store only):

	certificates:
		C, SP, L, O, OU, CN, email#, validTo, nameID#, issuerID#*, keyID#*, certID#*, certData
	CRLs:
		expiryDate+, nameID+, issuerID#*, certID#+, certData
	pkiUsers+:
		C, SP, L, O, OU, CN, nameID#*, keyID#*, certID, certData
	certRequests+:
		type, C, SP, L, O, OU, CN, email, certID, certData
	certLog+:
		action, date, certID#*, reqCertID, subjCertID, certData

   Note that in the CRL table the certID is the ID of the certificate being 
   revoked and not of the per-entry CRL data, and in the  PKIUsers table the 
   keyID isn't for a public key but a nonce used to identify the PKI user 
   and the nameID is used purely to ensure uniqueness of users.

   The certificate store contains a table for logging certificate management 
   operations (e.g. when issued, when revoked, etc etc).  The operations are 
   tied together by the certID of each object, associated with this in the 
   log are optional certIDs of the request that caused the action to be 
   taken and the subject that was affected by the request.  This allows a 
   complete history of each item to be built via the log.  The certLog has a 
   UNIQUE INDEX on the certID that detects attempts to add duplicates, 
   although this unfortunately requires the addition of dummy nonce certIDs 
   to handle certain types of actions that don't produce objects with 
   certIDs.

   The handling for each type of CA management operation is:

	CERTACTION_REQUEST_CERT/CERTACTION_REQUEST_RENEWAL/
	CERTACTION_REQUEST_REVOCATION: Stores the incoming requests and 
	generates a log entry.  Duplicate issue requests are detected by the 
	certLog.certID uniqueness constraint.  Available: request with certID: 

	  INSERT INTO certRequests VALUES ( <type>, <DN components>, <certID>, <request> );
	  INSERT INTO certLog VALUES
		(ACTION_REQUEST_CERT/RENEWAL/REVOCATION, $date, <certID>, NULL, NULL,
		  <request>);

	CERTACTION_ISSUE_CERT/CERTACTION_CERT_CREATION: Add the certificate and  
	remove the issue request.  Duplicate certificate issuance is detected by  
	the certLog.certID uniqueness constraint.  Available: request with 
	req.certID, certificate with certID

	  INSERT INTO certificates VALUES (<DN components>, <IDs>, <cert>);
	  INSERT INTO certLog VALUES
		(ACTION_ISSUE_CERT/CERT_CREATION, $date, <certID>, <req.certID>, NULL,
		  <cert>);
	  DELETE FROM certRequests WHERE certID = <req.certID>;

	CERTACTION_ISSUE_CRL: Read each CRL entry with caCert.nameID and 
	assemble the full CRL.  Requires an ongoing query:

	  SELECT FROM CRLs WHERE nameID = <caCert.nameID>

	CERTACTION_REVOKE_CERT: Add the CRL entry that causes the revocation, 
	delete the certificate and the request that caused the action.  
	Available: request with req.certID, certificate with cert.certID, CRL 
	entry with certID

	  INSERT INTO CRLs VALUES (<IDs>, <crlData>);
	  INSERT INTO certLog VALUES
		(ACTION_REVOKE_CERT, $date, <nonce>, <req.certID>, <cert.certID>, <crlData>);
	  DELETE FROM certRequests WHERE certID = <req.certID>;
	  DELETE FROM certificates WHERE certID = <cert.certID>;

	CERTACTION_EXPIRE_CERT/CERTACTION_RESTART_CLEANUP: Delete each expired 
	entry or clean up leftover certificate requests after a restart.  The 
	logging for these is a bit tricky, ideally we'd want to "INSERT INTO 
	certLog VALUES (ACTION_CERT_EXPIRE, $date, SELECT certID FROM 
	certificates WHERE validTo <= $date)" or the cleanup equivalent, however 
	this isn't possible both because it's not possible to mix static values 
	and a select result in an INSERT and because the certID is already 
	present from when the certificate/request was originally added.  We can 
	fix the former by making the static values part of the select result, 
	i.e. "INSERT INTO certLog VALUES SELECT ACTION_CERT_EXPIRE, $date, 
	certID FROM certificates WHERE validTo <= $date" but this still doesn't 
	fix the problem with the duplicate IDs.  In fact there isn't really a 
	certID present since it's an implicit action, but we can't make the 
	certID column null since it's not possible to index nullable columns.  
	As a result the only way that we can do it is to repetitively perform 
	"SELECT certID FROM certificates WHERE validTo <= $date" (or the 
	equivalent cleanup select) and for each time it succeeds follow it with:

	  INSERT INTO certLog VALUES
		(ACTION_EXPIRE_CERT, $date, <nonce>, NULL, <certID>);
	  DELETE FROM certificates WHERE certID = <certID>

	or

	  INSERT INTO certLog VALUES
		(ACTION_RESTART_CLEANUP, $date, <nonce>, NULL, <certID>);
	  DELETE FROM certRequests WHERE certID = <certID>

	This has the unfortunate side-effect that the update isn't atomic, we 
	could enforce this with "LOCK TABLE <name> IN EXCLUSIVE MODE" but the MS 
	databases don't support this and either require the use of baroque 
	mechanisms such as a "(TABLOCKX HOLDLOCK)" as a locking hint after the 
	table name in the first statement after the transaction is begun or 
	don't support this type of locking at all.  Because of this it isn't 
	really possible to make the update atomic, in particular for the cleanup 
	operation we rely on the caller to perform it at startup before anyone 
	else accesses the certificate store.  The fact that the update isn't 
	quite atomic isn't really a major problem, at worst it'll result in 
	either an expired certificate being visible or a leftover request 
	blocking a new request for a split second longer than they should.
	
	An additional feature that we could make use of for CA operations is the 
	use of foreign keys to ensure referential integrity, usually via entries 
	in the certificate log.  For example we could require that all 
	certificate requests be authorised by adding an authCertID column to the 
	certReq table and constraining it with:

		FOREIGN KEY (authCertID) REFERENCES certLog.reqCertID

	however (apart from the overhead of adding extra indexed columns just to 
	ensure referential integrity) the syntax for this varies somewhat 
	between vendors so that it'd require assorted rewriting by the back-end 
	glue code to handle the different requirements for each database type.  
	In addition since the foreign key constraint is specified at table 
	create time we could experience strange failures on table creation 
	requiring special-purpose workarounds where we remove the foreign-key 
	constraint in the hope that the table create then succeeds.

	An easier way to handle this is via manual references to entries in the 
	certificate log.  Since this is append-only, a manual presence check can 
	never return an incorrect result (an entry can't be removed between time 
	of  check and time of use) so this provides the same result as using 
	referential integrity mechanisms.

	Another database feature that we could use is database triggers as a 
	backup for the access control settings.  For example (using one 
	particular SQL dialect) we could say:

		CREATE TRIGGER checkLog ON certLog FOR UPDATE, DELETE AS
			BEGIN
				ROLLBACK
			END

	However as the "dialect" reference in the above comment implies this 
	process is *extremely* back-end specific (far more so than access 
	controls and the use of foreign keys) so we can't really do much here 
	without ending up having to set different triggers for each back-end 
	type and even back-end version */

/****************************************************************************
*																			*
*								Utility Routines							*
*																			*
****************************************************************************/

/* Set up key ID information for a query.  There are two variations of
   this, makeKeyID() encodes an existing keyID value and getKeyID() reads an
   attribute from an object and encodes it using makeKeyID() */

CHECK_RETVAL STDC_NONNULL_ARG( ( 1, 3, 5 ) ) \
int makeKeyID( OUT_BUFFER( keyIdMaxLen, *keyIdLen ) char *keyID, 
			   IN_LENGTH_SHORT_MIN( 16 ) const int keyIdMaxLen, 
			   OUT_LENGTH_SHORT_Z int *keyIdLen,
			   IN_KEYID const CRYPT_KEYID_TYPE iDtype, 
			   IN_BUFFER( idValueLength ) const void *idValue, 
			   IN_LENGTH_SHORT const int idValueLength )
	{
	BYTE hashBuffer[ CRYPT_MAX_HASHSIZE + 8 ];
	int idLength = idValueLength, status;

	assert( isWritePtr( keyID, keyIdMaxLen ) );
	assert( isWritePtr( keyIdLen, sizeof( int ) ) );
	assert( isReadPtr( keyID, idValueLength ) );

	REQUIRES( keyIdMaxLen >= 16 && keyIdMaxLen < MAX_INTLENGTH_SHORT );
	REQUIRES( iDtype == CRYPT_KEYID_NAME || \
			  iDtype == CRYPT_KEYID_URI || \
			  iDtype == CRYPT_IKEYID_KEYID || \
			  iDtype == CRYPT_IKEYID_ISSUERID || \
			  iDtype == CRYPT_IKEYID_CERTID );
	REQUIRES( idValueLength > 0 && idValueLength < MAX_INTLENGTH_SHORT );

	/* Clear return values */
	memset( keyID, 0, min( 16, keyIdMaxLen ) );
	*keyIdLen = 0;

	/* Name and email address are used as is */
	if( iDtype == CRYPT_KEYID_NAME || iDtype == CRYPT_KEYID_URI )
		{
		if( idLength > CRYPT_MAX_TEXTSIZE * 2 )
			{
			/* Truncate to the database column size */
			idLength = CRYPT_MAX_TEXTSIZE * 2;
			}
		if( idLength > keyIdMaxLen )
			{
			/* Truncate to the output buffer size */
			idLength = keyIdMaxLen;
			}
		memcpy( keyID, idValue, idLength );
		if( iDtype == CRYPT_KEYID_URI )
			{
			int i;

			/* Force the search URI to lowercase to make case-insensitive 
			   matching easier.  In most cases we could ask the back-end to 
			   do this but this complicates indexing and there's no reason 
			   why we can't do it here */
			for( i = 0; i < idLength; i++ )
				keyID[ i ] = toLower( keyID[ i ] );
			}
		*keyIdLen = idLength;

		return( CRYPT_OK );
		}

	/* A keyID is just a subjectKeyIdentifier, which is already supposed to 
	   be an SHA-1 hash but which in practice can be almost anything so we
	   always hash it to a fixed-length value */
	if( iDtype == CRYPT_IKEYID_KEYID )
		{
		HASHFUNCTION_ATOMIC hashFunctionAtomic;

		/* Get the hash algorithm information and hash the keyID to get
		   the fixed-length keyID */
		getHashAtomicParameters( CRYPT_ALGO_SHA1, &hashFunctionAtomic, NULL );
		hashFunctionAtomic( hashBuffer, CRYPT_MAX_HASHSIZE,
							idValue, idValueLength );
		idValue = hashBuffer;
		idLength = DBXKEYID_SIZE;
		}

	ENSURES( idLength >= DBXKEYID_SIZE && idLength <= keyIdMaxLen );

	/* base64-encode the key ID so that we can use it with database queries.
	   Since we only store 128 bits of a (usually 160 bit) ID to save space
	   (particularly where it's used in indices) and to speed lookups, this
	   encoding step has the side-effect of truncating the ID down to the
	   correct size */
	status = base64encode( keyID, keyIdMaxLen, keyIdLen, idValue, 
						   DBXKEYID_SIZE, CRYPT_CERTTYPE_NONE );
	if( cryptStatusError( status ) )
		retIntError();
	ENSURES( *keyIdLen == ENCODED_DBXKEYID_SIZE );

	return( CRYPT_OK );
	}

CHECK_RETVAL STDC_NONNULL_ARG( ( 1, 3 ) ) \
int getKeyID( OUT_BUFFER( keyIdMaxLen, *keyIdLen ) char *keyID, 
			  IN_LENGTH_SHORT_MIN( 16 ) const int keyIdMaxLen, 
			  OUT_LENGTH_SHORT_Z int *keyIdLen,
			  IN_HANDLE const CRYPT_HANDLE iCryptHandle,
			  IN_ATTRIBUTE const CRYPT_ATTRIBUTE_TYPE keyIDtype )
	{
	BYTE hashBuffer[ CRYPT_MAX_HASHSIZE + 8 ];
	int status;

	assert( isWritePtr( keyID, keyIdMaxLen ) );
	assert( isWritePtr( keyIdLen, sizeof( int ) ) );

	REQUIRES( keyIdMaxLen >= 16 && keyIdMaxLen < MAX_INTLENGTH_SHORT );
	REQUIRES( isHandleRangeValid( iCryptHandle ) );
	REQUIRES( ( keyIDtype == CRYPT_CERTINFO_FINGERPRINT_SHA || \
				keyIDtype == CRYPT_IATTRIBUTE_AUTHCERTID ) || \
			  ( keyIDtype == CRYPT_CERTINFO_SUBJECTKEYIDENTIFIER || \
				keyIDtype == CRYPT_IATTRIBUTE_ISSUER || \
				keyIDtype == CRYPT_IATTRIBUTE_SUBJECT || \
				keyIDtype == CRYPT_IATTRIBUTE_ISSUERANDSERIALNUMBER || \
				keyIDtype == CRYPT_IATTRIBUTE_SPKI ) );

	/* Clear return values */
	memset( keyID, 0, min( 16, keyIdMaxLen ) );
	*keyIdLen = 0;

	/* Get the attribute from the certificate and hash it, unless it's 
	   already a hash */
	if( keyIDtype == CRYPT_CERTINFO_FINGERPRINT_SHA || \
		keyIDtype == CRYPT_IATTRIBUTE_AUTHCERTID )
		{
		MESSAGE_DATA msgData;

		setMessageData( &msgData, hashBuffer, CRYPT_MAX_HASHSIZE );
		status = krnlSendMessage( iCryptHandle, IMESSAGE_GETATTRIBUTE_S,
								  &msgData, keyIDtype );
		if( cryptStatusError( status ) )
			return( status );
		ENSURES( msgData.length == KEYID_SIZE );
		}
	else
		{
		DYNBUF idDB;
		HASHFUNCTION_ATOMIC hashFunctionAtomic;
		int hashSize;

		/* Get the attribute data and hash it to get the ID */
		status = dynCreate( &idDB, iCryptHandle, keyIDtype );
		if( cryptStatusError( status ) )
			return( status );
		getHashAtomicParameters( CRYPT_ALGO_SHA1, &hashFunctionAtomic, 
								 &hashSize );
		hashFunctionAtomic( hashBuffer, CRYPT_MAX_HASHSIZE,
							dynData( idDB ), dynLength( idDB ) );
		ENSURES( hashSize == KEYID_SIZE );
		dynDestroy( &idDB );
		}

	return( makeKeyID( keyID, keyIdMaxLen, keyIdLen, CRYPT_IKEYID_CERTID, 
					   hashBuffer, KEYID_SIZE ) );
	}

/* Get a certID for a certificate */

CHECK_RETVAL STDC_NONNULL_ARG( ( 1, 3 ) ) \
int getCertKeyID( OUT_BUFFER( keyIdMaxLen, *keyIdLen ) char *keyID, 
				  IN_LENGTH_SHORT_MIN( 16 ) const int keyIdMaxLen, 
				  OUT_LENGTH_SHORT_Z int *keyIdLen,
				  IN_HANDLE const CRYPT_CERTIFICATE iCryptCert )
	{
	int status;

	assert( isWritePtr( keyID, keyIdMaxLen ) );
	assert( isWritePtr( keyIdLen, sizeof( int ) ) );

	REQUIRES( keyIdMaxLen >= 16 && keyIdMaxLen < MAX_INTLENGTH_SHORT );
	REQUIRES( isHandleRangeValid( iCryptCert ) );

	/* Clear return values */
	memset( keyID, 0, min( 16, keyIdMaxLen ) );
	*keyIdLen = 0;

	/* Certificate keyID handling isn't quite as simple as just reading an
	   attribute from the certificate since the subjectKeyIdentifier (if
	   present) may not be the same as the keyID if the certificate has come 
	   from a CA that does strange things with the sKID.  To resolve this we 
	   try and build the key ID from the sKID, if this isn't present we use 
	   the keyID (the sKID may have a nonstandard length since it's possible 
	   to stuff anything in there, getKeyID() will hash it to the standard 
	   size if the length is wrong) */
	status = getKeyID( keyID, keyIdMaxLen, keyIdLen, iCryptCert,
					   CRYPT_CERTINFO_SUBJECTKEYIDENTIFIER );
	if( cryptStatusOK( status ) )
		return( status );

	/* There's no subjectKeyIdentifier, use the keyID.  Note that we can't
	   just read the CRYPT_IATTRIBUTE_KEYID attribute directly since this
	   may be a data-only certificate (either a standalone certificate or 
	   one from the middle of a chain) so we have to generate it indirectly 
	   by hashing the SubjectPublicKeyInfo, which is equivalent to the keyID 
	   and is always present in a certificate */
	return( getKeyID( keyID, keyIdMaxLen, keyIdLen, iCryptCert, 
					  CRYPT_IATTRIBUTE_SPKI ) );
	}

/* Extract certificate data from a certificate object.  Note that the 
   formatType is given as an int rather than an enumerated type because
   it can be either a CRYPT_CERTFORMAT_TYPE or a CRYPT_ATTRIBUTE_TYPE */

CHECK_RETVAL STDC_NONNULL_ARG( ( 3, 5 ) ) \
int extractCertData( IN_HANDLE const CRYPT_CERTIFICATE iCryptCert, 
					 IN_INT const int formatType,
					 OUT_BUFFER( certDataMaxLength, *certDataLength ) \
						void *certDataBuffer, 
					 IN_LENGTH_SHORT_MIN( MIN_CRYPT_OBJECTSIZE ) \
						const int certDataMaxLength, 
					 OUT_LENGTH_SHORT_Z int *certDataLength )
	{
	MESSAGE_DATA msgData;
	int status;

	assert( isWritePtr( certDataBuffer, certDataMaxLength ) );
	assert( isWritePtr( certDataLength, sizeof( int ) ) );

	REQUIRES( isHandleRangeValid( iCryptCert ) );
	REQUIRES( formatType == CRYPT_CERTFORMAT_CERTIFICATE || \
			  formatType == CRYPT_ICERTFORMAT_DATA || \
			  formatType == CRYPT_IATTRIBUTE_CRLENTRY );
	REQUIRES( certDataMaxLength >= MIN_CRYPT_OBJECTSIZE && \
			  certDataMaxLength < MAX_INTLENGTH_SHORT );

	/* Make sure that there's no collision in format type values (although 
	   the switch() statement will also catch this by producing a compile 
	   error */
	assert( CRYPT_CERTFORMAT_CERTIFICATE != CRYPT_ICERTFORMAT_DATA && \
			CRYPT_CERTFORMAT_CERTIFICATE != CRYPT_IATTRIBUTE_CRLENTRY );

	/* Clear return values */
	memset( certDataBuffer, 0, min( 16, certDataMaxLength ) );
	*certDataLength = 0;

	/* Extract the certificate object data */
	setMessageData( &msgData, certDataBuffer, certDataMaxLength );
	switch( formatType )
		{
		case CRYPT_CERTFORMAT_CERTIFICATE:
		case CRYPT_ICERTFORMAT_DATA:
			status = krnlSendMessage( iCryptCert, IMESSAGE_CRT_EXPORT,
									  &msgData, formatType );
			break;

		case CRYPT_IATTRIBUTE_CRLENTRY:
			status = krnlSendMessage( iCryptCert, IMESSAGE_GETATTRIBUTE_S, 
									  &msgData, formatType );
			break;

		default:
			retIntError();
		}
	if( cryptStatusError( status ) )
		return( status );
	*certDataLength = msgData.length;

	return( CRYPT_OK );
	}

/* Some internal actions set extended error codes as a result of their 
   operation that the user shouldn't really see.  For example performing a 
   certificate cleanup will return a no-data-found error once the last 
   certificate is reached, which will be read by the user the next time that 
   they read the CRYPT_ATTRIBUTE_INT_ERRORCODE/CRYPT_ATTRIBUTE_INT_ERRORMESSAGE 
   even though the error came from a previous internal operation.  To avoid
   this problem we clean up the error status information when it's been set 
   by an internal operation */

RETVAL STDC_NONNULL_ARG( ( 1 ) ) \
int resetErrorInfo( INOUT DBMS_INFO *dbmsInfo )
	{
	DBMS_STATE_INFO *dbmsStateInfo = dbmsInfo->stateInfo;
	ERROR_INFO *errorInfo = &dbmsStateInfo->errorInfo;

	assert( isWritePtr( dbmsInfo, sizeof( DBMS_INFO ) ) );

	memset( errorInfo, 0, sizeof( ERROR_INFO ) );
	return( CRYPT_OK );
	}

/* Get names for various items */

CHECK_RETVAL_PTR \
char *getKeyName( IN_KEYID const CRYPT_KEYID_TYPE keyIDtype )
	{
	REQUIRES_N( keyIDtype > CRYPT_KEYID_NONE && \
				keyIDtype <= CRYPT_KEYID_LAST );
				/* CRYPT_KEYID_LAST is mapped to the database-use-only 
				   lookup value "nameID" */

	switch( keyIDtype )
		{
		case CRYPT_KEYID_NAME:
			return( "CN" );

		case CRYPT_KEYID_URI:
			return( "email" );

		case CRYPT_IKEYID_KEYID:
			return( "keyID" );

		case CRYPT_IKEYID_ISSUERID:
			return( "issuerID" );

		case CRYPT_IKEYID_CERTID:
			return( "certID" );

		case CRYPT_KEYID_LAST:
			return( "nameID" );
		}

	retIntError_Null();
	}

/****************************************************************************
*																			*
*							Database Access Functions						*
*																			*
****************************************************************************/

/* Create a new key database */

CHECK_RETVAL STDC_NONNULL_ARG( ( 1, 3 ) ) \
static int createDatabase( INOUT DBMS_INFO *dbmsInfo, 
						   const BOOLEAN hasPermissions, 
						   INOUT ERROR_INFO *errorInfo )
	{
	int updateProgress = 0, status;

	assert( isWritePtr( dbmsInfo, sizeof( DBMS_INFO ) ) );

	REQUIRES( errorInfo != NULL );

	/* Create tables for certificates, CRLs, certificate requests, PKI 
	   users, and CA logs.  We use CHAR rather than VARCHAR for the ID 
	   fields since these always have a fixed length and CHAR is faster than 
	   VARCHAR.  In addition we make as many columns as possible NOT NULL 
	   since these fields should always be present and because this is  
	   faster for most databases.  The BLOB type is nonstandard, this is 
	   rewritten by the database interface layer to the type which is 
	   appropriate for the database */
	status = dbmsStaticUpdate(
			"CREATE TABLE certificates ("
				"C CHAR(2), "
				"SP VARCHAR(64), "
				"L VARCHAR(64), "
				"O VARCHAR(64), "
				"OU VARCHAR(64), "
				"CN VARCHAR(64), "
				"email VARCHAR(64), "
				"validTo DATETIME NOT NULL, "
				"nameID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"issuerID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"keyID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"certID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"certData BLOB NOT NULL)" );
	if( cryptStatusError( status ) )
		{
		retExtErr( status, 
				   ( status, errorInfo, getDbmsErrorInfo( dbmsInfo ),
					 "Couldn't create certificate database: " ) );
		}
	if( isCertStore( dbmsInfo ) )
		{
		/* The certificate store contains in addition to the other CRL 
		   fields the certificate expiry time which is used to remove the 
		   entry from the CRL table once the certificate has expired anyway, 
		   the nameID which is used to force clustering of entries for each 
		   CA, and the ID of the certificate being revoked, which isn't 
		   available if we're creating it from a raw CRL */
		status = dbmsStaticUpdate(
			"CREATE TABLE CRLs ("
				"expiryDate DATETIME NOT NULL, "
				"nameID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"issuerID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL,"
				"certID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"certData BLOB NOT NULL)" );
		if( cryptStatusOK( status ) )
			{
			updateProgress++;
			status = dbmsStaticUpdate(
			"CREATE TABLE pkiUsers ("
				"C CHAR(2), "
				"SP VARCHAR(64), "
				"L VARCHAR(64), "
				"O VARCHAR(64), "
				"OU VARCHAR(64), "
				"CN VARCHAR(64), "
				"nameID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"keyID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"certID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"certData BLOB NOT NULL)" );
			}
		if( cryptStatusOK( status ) )
			{
			updateProgress++;
			status = dbmsStaticUpdate(
			"CREATE TABLE certRequests ("
				"type SMALLINT NOT NULL, "
				"C CHAR(2), "
				"SP VARCHAR(64), "
				"L VARCHAR(64), "
				"O VARCHAR(64), "
				"OU VARCHAR(64), "
				"CN VARCHAR(64), "
				"email VARCHAR(64), "
				"certID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"certData BLOB NOT NULL)" );
			}
		if( cryptStatusOK( status ) )
			{
			updateProgress++;
			status = dbmsStaticUpdate(
			"CREATE TABLE certLog ("
				"action SMALLINT NOT NULL, "
				"actionTime DATETIME NOT NULL, "
				"certID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL, "
				"reqCertID CHAR(" TEXT_DBXKEYID_SIZE "), "
				"subjCertID CHAR(" TEXT_DBXKEYID_SIZE "), "
				"certData BLOB)" );
			}
		}
	else
		{
		status = dbmsStaticUpdate(
			"CREATE TABLE CRLs ("
				"issuerID CHAR(" TEXT_DBXKEYID_SIZE ") NOT NULL,"
				"certData BLOB NOT NULL)" );
		}
	if( cryptStatusError( status ) )
		{
		/* Undo the previous table creations */
		dbmsStaticUpdate( "DROP TABLE certificates" );
		if( updateProgress > 0 )
			dbmsStaticUpdate( "DROP TABLE CRLs" );
		if( updateProgress > 1 )
			dbmsStaticUpdate( "DROP TABLE pkiUsers" );
		if( updateProgress > 2 )
			dbmsStaticUpdate( "DROP TABLE certRequests" );
		retExtErr( status, 
				   ( status, errorInfo, getDbmsErrorInfo( dbmsInfo ),
					 "Couldn't create certificate%s database: ",
					 isCertStore( dbmsInfo ) ? " store" : "" ) );
		}

	/* Create an index for the email address, nameID, issuerID, keyID, and
	   certID in the certificates table, the issuerID and certID in the CRLs
	   table (the CRL nameID isn't indexed since we only use it for linear
	   scans), the nameID and keyID in the PKI users table (the former isn't 
	   used but is made a UNIQUE INDEX to ensure that the same entry can't 
	   be added more than once) and the certID in the certificate log (this 
	   also isn't used but is made a UNIQUE INDEX to ensure that the same 
	   entry can't be added more than once).  We have to give these unique 
	   names since some databases don't allow two indexes to have the same 
	   name even if they're in a different table.  Since most of the fields 
	   in the tables are supposed to be unique we can specify this for the 
	   indexes that we're creating, however we can't do it for the email 
	   address or the nameID in the certificates table since there could be 
	   multiple certificates present that differ only in key usage.  We 
	   don't index the other tables since indexes consume space and we don't 
	   expect to access any of these much */
	status = dbmsStaticUpdate(
			"CREATE INDEX emailIdx ON certificates(email)" );
	if( cryptStatusOK( status ) )
		status = dbmsStaticUpdate(
			"CREATE INDEX nameIDIdx ON certificates(nameID)" );
	if( cryptStatusOK( status ) )
		status = dbmsStaticUpdate(
			"CREATE UNIQUE INDEX issuerIDIdx ON certificates(issuerID)" );
	if( cryptStatusOK( status ) )
		status = dbmsStaticUpdate(
			"CREATE UNIQUE INDEX keyIDIdx ON certificates(keyID)" );
	if( cryptStatusOK( status ) )
		status = dbmsStaticUpdate(
			"CREATE UNIQUE INDEX certIDIdx ON certificates(certID)" );
	if( cryptStatusOK( status ) )
		status = dbmsStaticUpdate(
			"CREATE UNIQUE INDEX crlIssuerIDIdx ON CRLs (issuerID)" );
	if( cryptStatusOK( status ) && isCertStore( dbmsInfo ) )
		status = dbmsStaticUpdate(
			"CREATE UNIQUE INDEX crlCertIDIdx ON CRLs (certID)" );
	if( cryptStatusOK( status ) && isCertStore( dbmsInfo ) )
		status = dbmsStaticUpdate(
			"CREATE UNIQUE INDEX userKeyIDIdx ON pkiUsers (keyID)" );
	if( cryptStatusOK( status ) && isCertStore( dbmsInfo ) )
		status = dbmsStaticUpdate(
			"CREATE UNIQUE INDEX userNameIDIdx ON pkiUsers (nameID)" );
	if( cryptStatusOK( status ) && isCertStore( dbmsInfo ) )
		status = dbmsStaticUpdate(
			"CREATE UNIQUE INDEX logCertIDIdx ON certLog (certID)" );
	if( cryptStatusOK( status ) && isCertStore( dbmsInfo ) )
		{
		char dummyCertID[ ENCODED_DBXKEYID_SIZE + 8 ];
		const int dummyCertIDlength = ENCODED_DBXKEYID_SIZE;

		/* Create a special dummy certID with an out-of-band value to mark
		   the first entry in the log */
		memset( dummyCertID, '-', ENCODED_DBXKEYID_SIZE );

		/* Add the initial log entry recording the creation of the log */
		status = updateCertLog( dbmsInfo, CRYPT_CERTACTION_CREATE,
								dummyCertID, dummyCertIDlength, 
								NULL, 0, NULL, 0, NULL, 0,
								DBMS_UPDATE_NORMAL );
		}
	if( cryptStatusError( status ) )
		{
		/* Undo the creation of the various tables */
		dbmsStaticUpdate( "DROP TABLE certificates" );
		dbmsStaticUpdate( "DROP TABLE CRLs" );
		if( isCertStore( dbmsInfo ) )
			{
			dbmsStaticUpdate( "DROP TABLE pkiUsers" );
			dbmsStaticUpdate( "DROP TABLE certRequests" );
			dbmsStaticUpdate( "DROP TABLE certLog" );
			}
		retExtErr( CRYPT_ERROR_WRITE, 
				   ( CRYPT_ERROR_WRITE, errorInfo, getDbmsErrorInfo( dbmsInfo ),
					 "Couldn't create indexes for certificate%s database: ",
					 isCertStore( dbmsInfo ) ? " store" : "" ) );
		}

	/* If the back-end doesn't support access permissions (generally only 
	   toy ones like Access and Paradox) or it's not a CA certificate 
	   store, we're done */
	if( !hasPermissions || !isCertStore( dbmsInfo ) )
		return( CRYPT_OK );

	/* Set access controls for the certificate store tables:

						Users				CAs
		certRequests:	-					INS,SEL,DEL
		certificates:	SEL					INS,SEL,DEL
		CRLs:			-					INS,SEL,DEL
		pkiUsers:		-					INS,SEL,DEL
		certLog:		-					INS,SEL
	
	   Once role-based access controls are enabled we can allow only the CA 
	   user to update the certstore tables and allow others only read access 
	   to the certificates table.  In addition the revocation should be 
	   phrased as REVOKE ALL, GRANT <permitted> rather than revoking specific 
	   privileges since each database vendor has their own nonstandard 
	   additional privileges that a specific revoke won't cover.  
	   Unfortunately configuring this will be somewhat difficult since it
	   requires that cryptlib users create database user roles, which in turn
	   requires that they read the manual */
#if 1
	dbmsStaticUpdate( "REVOKE UPDATE ON certificates FROM PUBLIC" );
	dbmsStaticUpdate( "REVOKE UPDATE ON CRLs FROM PUBLIC" );
	dbmsStaticUpdate( "REVOKE UPDATE ON pkiUsers FROM PUBLIC" );
	dbmsStaticUpdate( "REVOKE UPDATE ON certRequests FROM PUBLIC" );
	dbmsStaticUpdate( "REVOKE DELETE,UPDATE ON certLog FROM PUBLIC" );
#else
	dbmsStaticUpdate( "REVOKE ALL ON certificates FROM PUBLIC" );
	dbmsStaticUpdate( "GRANT INSERT,SELECT,DELETE ON certificates TO ca" );
	dbmsStaticUpdate( "GRANT SELECT ON certificates TO PUBLIC" );
	dbmsStaticUpdate( "REVOKE ALL ON CRLs FROM PUBLIC" );
	dbmsStaticUpdate( "GRANT INSERT,SELECT,DELETE ON CRLs TO ca" );
	dbmsStaticUpdate( "REVOKE ALL ON pkiUsers FROM PUBLIC" );
	dbmsStaticUpdate( "GRANT INSERT,SELECT,DELETE ON pkiUsers TO ca" );
	dbmsStaticUpdate( "REVOKE ALL ON certRequests FROM PUBLIC" );
	dbmsStaticUpdate( "GRANT INSERT,SELECT,DELETE ON certRequests TO ca" );
	dbmsStaticUpdate( "REVOKE ALL ON certLog FROM PUBLIC" );
	dbmsStaticUpdate( "GRANT INSERT,SELECT ON certLog TO ca" );
#endif /* 1 */

	return( CRYPT_OK );
	}

/* Return status information for the keyset */

CHECK_RETVAL_BOOL STDC_NONNULL_ARG( ( 1 ) ) \
static BOOLEAN isBusyFunction( KEYSET_INFO *keysetInfoPtr )
	{
	assert( isWritePtr( keysetInfoPtr, sizeof( KEYSET_INFO ) ) );

	return( ( keysetInfoPtr->keysetDBMS->flags & \
			  ( DBMS_FLAG_UPDATEACTIVE | DBMS_FLAG_QUERYACTIVE ) ) ? \
			  TRUE : FALSE );
	}

/* Open a connection to a database */

CHECK_RETVAL STDC_NONNULL_ARG( ( 1, 2 ) ) \
static int initFunction( INOUT KEYSET_INFO *keysetInfoPtr, 
						 IN_BUFFER( nameLen ) const char *name,
						 IN_LENGTH_NAME const int nameLen,
						 IN_ENUM( CRYPT_KEYOPT ) const CRYPT_KEYOPT_TYPE options )
	{
	DBMS_INFO *dbmsInfo = keysetInfoPtr->keysetDBMS;
	DBMS_NAME_INFO nameInfo;
	int featureFlags, status;

	assert( isWritePtr( keysetInfoPtr, sizeof( KEYSET_INFO ) ) );
	assert( isReadPtr( name, nameLen ) );

	REQUIRES( nameLen >= MIN_NAME_LENGTH && nameLen < MAX_ATTRIBUTE_SIZE );
	REQUIRES( options >= CRYPT_KEYOPT_NONE && options < CRYPT_KEYOPT_LAST );

	/* Parse the data source name.  The parsed form isn't needed at this 
	   point but it allows us to return a more meaningful error response */
	status = dbmsParseName( &nameInfo, name, nameLen );
	if( cryptStatusError( status ) )
		retExt( status, 
				( status, KEYSET_ERRINFO, "Invalid database name" ) );

	/* Perform a database back-end specific open */
	status = dbmsOpen( name, nameLen,
					   ( options == CRYPT_KEYOPT_READONLY ) ? \
							options : CRYPT_KEYOPT_NONE, &featureFlags );
	if( cryptStatusError( status ) )
		{
		endDbxSession( keysetInfoPtr );
		return( status );
		}

	/* If the back-end is read-only (which would be extremely unusual, 
	   usually related to misconfigured DBMS access permissions) and we're 
	   not opening it in read-only mode, signal an error */
	if( ( featureFlags & DBMS_FEATURE_FLAG_READONLY ) && \
		options != CRYPT_KEYOPT_READONLY )
		{
		endDbxSession( keysetInfoPtr );
		retExt( CRYPT_ERROR_PERMISSION, 
				( CRYPT_ERROR_PERMISSION, KEYSET_ERRINFO, 
				  "Certificate database can only be accessed in read-only "
				  "mode" ) );
		}

	/* If we're being asked to create a new database, create it and exit */
	if( options == CRYPT_KEYOPT_CREATE )
		{
		status = createDatabase( dbmsInfo, 
								 ( featureFlags & DBMS_FEATURE_FLAG_PRIVILEGES ) ? \
								   TRUE : FALSE, KEYSET_ERRINFO );
		if( cryptStatusOK( status ) && isCertStore( dbmsInfo ) )
			status = updateCertLog( dbmsInfo, CRYPT_CERTACTION_CONNECT,
									NULL, 0, NULL, 0, NULL, 0, NULL, 0,
									DBMS_UPDATE_NORMAL );
		if( cryptStatusError( status ) )
			{
			dbmsClose();
			endDbxSession( keysetInfoPtr );
			}
		return( status );
		}

	/* Check to see whether it's a certificate store.  We do this by 
	   checking for the presence of the certificate store creation entry in 
	   the log, this is always present with an action value of 
	   CRYPT_CERTACTION_CREATE */
	status = dbmsStaticQuery(
			"SELECT certData FROM certLog WHERE action = "
				TEXT_CERTACTION_CREATE,
							  DBMS_CACHEDQUERY_NONE, DBMS_QUERY_CHECK );
	if( cryptStatusOK( status ) )
		{
		/* It's a certificate store, if we're opening it as a non-certificate 
		   store it has to be in read-only mode.  We return an error rather 
		   than quietly changing the access mode to read-only both to make it 
		   explicit to the user at open time that they can't make changes 
		   and because we need to have the read-only flag set when we open 
		   the database to optimise the buffering and locking strategy, 
		   setting it at this point is too late */
		if( !isCertStore( dbmsInfo ) )
			{
			if( options != CRYPT_KEYOPT_READONLY )
				{
				dbmsClose();
				endDbxSession( keysetInfoPtr );
				retExt( CRYPT_ERROR_PERMISSION, 
						( CRYPT_ERROR_PERMISSION, KEYSET_ERRINFO, 
						  "Certificate store can't be accessed as a normal "
						  "database except in read-only mode" ) );
				}

			/* Remember that even though it's not functioning as a 
			   certificate store we can still perform some extended queries 
			   on it based on fields that are only present in certificate 
			   stores */
			dbmsInfo->flags |= DBMS_FLAG_CERTSTORE_FIELDS;

			return( CRYPT_OK );
			}

		/* If this isn't a read-only open, record a connection to the
		   store */
		if( options != CRYPT_KEYOPT_READONLY )
			{
			status = updateCertLog( dbmsInfo, CRYPT_CERTACTION_CONNECT,
									NULL, 0, NULL, 0, NULL, 0, NULL, 0,
									DBMS_UPDATE_NORMAL );
			if( cryptStatusError( status ) )
				{
				/* This is a critical error, if we can't update the access 
				   log at this point then it's not safe to use the 
				   certificate store (if we fail during a general update 
				   operation we give the caller the option of continuing 
				   since the transaction itself will have been rolled back 
				   so there's no permanent harm done) */
				dbmsClose();
				endDbxSession( keysetInfoPtr );
				retExtArg( CRYPT_ARGERROR_NUM1, 
						   ( CRYPT_ARGERROR_NUM1, KEYSET_ERRINFO, 
							 "Couldn't update certificate store log" ) );
				}
			}

		return( CRYPT_OK );
		}

	/* It's not a certificate store, if the DBMS information indicates that 
	   we're expecting to open it as one tell the caller */
	if( isCertStore( dbmsInfo ) )
		{
		dbmsClose();
		endDbxSession( keysetInfoPtr );
		retExtArg( CRYPT_ARGERROR_NUM1, 
				   ( CRYPT_ARGERROR_NUM1, KEYSET_ERRINFO, 
					 "Keyset isn't a certificate store" ) );
		}

	/* Since the failure of the query above will set the extended error
	   information we have to explicitly clear it here to avoid making the
	   (invisible) query side-effects visible to the user */
	resetErrorInfo( dbmsInfo );

	return( CRYPT_OK );
	}

/* Close the connection to a database */

RETVAL STDC_NONNULL_ARG( ( 1 ) ) \
static int shutdownFunction( INOUT KEYSET_INFO *keysetInfoPtr )
	{
	DBMS_INFO *dbmsInfo = keysetInfoPtr->keysetDBMS;

	assert( isWritePtr( keysetInfoPtr, sizeof( KEYSET_INFO ) ) );

	/* If it's a certificate store opened in read/write mode, record a 
	   closed connection to the store */
	if( isCertStore( dbmsInfo ) && \
		keysetInfoPtr->options != CRYPT_KEYOPT_READONLY )
		{
		updateCertLog( dbmsInfo, CRYPT_CERTACTION_DISCONNECT, 
					   NULL, 0, NULL, 0, NULL, 0, NULL, 0, 
					   DBMS_UPDATE_NORMAL );
		}

	/* If we're in the middle of a query, cancel it.  We always use 
	   DBMS_CACHEDQUERY_NONE because this is the only query type that can
	   remain active outside the keyset object */
	if( dbmsInfo->flags & DBMS_FLAG_QUERYACTIVE )
		dbmsStaticQuery( NULL, DBMS_CACHEDQUERY_NONE, DBMS_QUERY_CANCEL );

	dbmsClose();
	return( endDbxSession( keysetInfoPtr ) );
	}

/****************************************************************************
*																			*
*							Database Access Routines						*
*																			*
****************************************************************************/

/* Set up the function pointers to the keyset methods */

CHECK_RETVAL STDC_NONNULL_ARG( ( 1 ) ) \
int setAccessMethodDBMS( INOUT KEYSET_INFO *keysetInfoPtr,
						 IN_ENUM( CRYPT_KEYSET ) const CRYPT_KEYSET_TYPE type )
	{
	int status;

	assert( isWritePtr( keysetInfoPtr, sizeof( KEYSET_INFO ) ) );
	assert( DBMS_CACHEDQUERY_LAST == NO_CACHED_QUERIES );

	REQUIRES( type > CRYPT_KEYSET_NONE && type < CRYPT_KEYSET_LAST );

	/* Set up the lower-level interface functions */
	status = initDbxSession( keysetInfoPtr, type );
	if( cryptStatusError( status ) )
		return( status );

	/* Set the access method pointers */
	keysetInfoPtr->initFunction = initFunction;
	keysetInfoPtr->shutdownFunction = shutdownFunction;
	status = initDBMSread( keysetInfoPtr );
	if( cryptStatusOK( status ) )
		status = initDBMSwrite( keysetInfoPtr );
	if( cryptStatusError( status ) )
		return( status );
	if( type == CRYPT_KEYSET_ODBC_STORE || \
		type == CRYPT_KEYSET_DATABASE_STORE || \
		type == CRYPT_KEYSET_PLUGIN_STORE )
		{
		status = initDBMSCA( keysetInfoPtr );
		if( cryptStatusError( status ) )
			return( status );
		}
	keysetInfoPtr->isBusyFunction = isBusyFunction;

	return( CRYPT_OK );
	}
#endif /* USE_DBMS */
