Securing your SIRA_PRISE databases

The concepts

SIRA_PRISE includes support for a concept similar to what is more commonly known as "transition constraints", called "Assignment constraints".

How assignment constraints help enforce security

Assignment constraints allow you to constrain which database updates are valid and which aren't. They differ from database constraints in that database constraints depend on nothing else than the database value after the update to determine their outcome. Database constraints can therefore not include context factors such as the current calendar date, or the userID owning the transaction that issues the update. Assignment constraints, on the contrary, can include such context factors.

This makes assignment constraints obviously the suitable solution for (at least) two common classes of constraint enforcement problem that "classic" database constraints fail to address : one class being constraints that involve the evaluation of some "current calendar date" field (e.g. : "It should not be possible to introduce a new customer whose birth date is later than the current calendar date."), and another class being constraints that involve "knowing who is issuing this requested update" (e.g. : "The employee relvar may only be updated by employees of the HR department, and no HR employee may update his own data.").

Assignment Constraints

Assignment constraints are defined by adding an AssignmentConstraint tuple to the catalog. This tuple defines the nature of the constraint (that is, the expression that must be evaluated), and the number identifying the constraint.

Defining when to check an assignment constraint is done by adding one or more AssignmentConstraintCheck tuple(s) to the catalog. Such a tuple identifies the assignment constraint for which a check is defined, and the combination of relvarname/update operation type for which a check of the constraint is to be carried out. UPDATE commands are thereby treated as if the corresponding delete and insert operations were executed consecutively (meaning that for an UPDATE command applied to some relvar, both delete-related and insert-related assignment constraints for that relvar will be checked).

It is worth noting that multiple assignments in which the same update operation type appears multiple times for the same relvar (CMD(ADD A,A(...))CMD(ADD A,A(...))) will be "canonicalized" into one single operation. So in the example just given, the insert-related assignment constraints defined for relvar A will be checked only once. This might affect the results of assignment constraints that try to enforce rules such as, e.g., "No more than one tuple may be added to relvar X in a single update".

When an assignment constraint must be checked, the corresponding relational expression is evaluated. The assignment constraint is satisfied if the result of that evaluation is the empty relation. Any tuple present in the result of the evaluation constitutes a constraint violation that will be reported to the user.

The constraint expressions can contain three distinct "special-purpose" constructs : the CURRENTDATE relation, the CURRENTUSER relation and invocations of any one of the transaction operators.

Using The CURRENTDATE relation

An insert-related assignment constraint on a relvar CUSTOMER might, e.g., be defined by the following expression (see here for details on the INSERTS() operator) :


The INSERTS() invocation returns the tuples to be inserted, the JOIN invocation joins in a DATE attribute that has the current date as value, and the RESTRICT retains exactly those tuples where the inserted-customer's BIRTHDATE is greater than the current date. That relation is required to be empty for the transaction constraint to be satisfied.

Using The CURRENTUSER relation

Prohibiting updates on relvars by anonymous transactions can be achieved by defining an assignment constraint such as :


If the transaction is anonymous, then CURRENTUSER is the empty relation, and the MINUS is equal to TABLE_DEE, which is non-empty. If the transaction is non-anonymous, then CURRENTUSER is non-empty, its projection will be equal to TABLE_DEE, and the MINUS is equal to TABLE_DUM, which is empty.

An assignment constraint on a relvar EMPLOYEE might further be defined by the following expression :


The tuple from the EMPLOYEE relvar that corresponds the current user is retrieved from the EMPLOYEE relvar, and if the department in that tuple is not equal to "HR", then the RESTRICT expression is non-empty, meaning a violation is detected.

The transaction relational operators

The transaction relational operators are a special-purpose family of operators aimed precisely for being invoked in the context of assignment constraints. It evaluates to the relation that is being added resp. deleted from the relvar named in the argument (by the update statement that triggered the verification of the assignment constraint). The sole argument to a transaction operator is the name of a relvar being updated.  Two such operators are the most suitable for being used in assignment constraints : INSERTS(...) and DELETES(...).

Predictably, DELETES(<relvarname>) returns a relation holding all the tuples that are being deleted from the named relvar by the current update statement, and INSERTS(<relvarname>) returns a relation holding all the tuples that are being inserted to the named relvar by the current update statement.  Note that if the update statement is of the ASSERT/UNASSERT kind, then it is possible that a tuple that is indeed present in the (value that the) corresponding <relational expression> (evaluates to), is nonetheless absent in the value of the INSERTS() or DELETES() invocation.  I.e. INSERTS() and DELETES() represent the _actual_ updates (insertions/deletions) that will be done to the database, not the _attempted_ ones.


There is very little point in using security enforcement mechanisms that depend on userID's provided to the system, if the authenticity of those userID's isn't properly verified. SIRA_PRISE therefore supports authentication mechanisms too. These are briefly explained in this section.

Two types of counterparties can be authenticated by SIRA_PRISE : clients requesting to connect to the server, and users requesting to start a transaction on a connection which was obtained by a client. Clients are the software packages that actually request connections to SIRA_PRISE, users are the real persons (or possibly other software packages) using those clients to manage data recorded by SIRA_PRISE.

The authentication mechanism is based on PKI technology using X.509 certificates. The implementation builds on the JCE framework, but the "exposure" of those underlying components is kept to a minimum.

SIRA_PRISE also supports cryptographic services for purposes of data privacy. They can be used to prevent your data being logged "in plain text" on third-party-owned hardware if your data has to travel through networks owned by such third parties.

Supported algorithms

Which signing algorithms are supported by the server is controlled by the SIGNATUREALGORITHMS property in the server configuration file. This property's value is a comma-separated list of all the (names of) signing algorithms that the server must support. If the property is not present, then SIRA_PRISE uses the value "MD5withRSA, MD2withRSA, SHA1withRSA, SHA1withDSA". The latter four are algorithms that come standard with Java, the first is SIRA_PRISE's proprietary signature algorithm. All algorithms named in this list must be configured in the JCE framework (except for the algorithms that are packaged standard with Java, of course). This involves declaring the "security provider" of the algorithm in the Java runtime's security configuration file. We refer to the JCE documentation that came with Java for further details on how to do this. The name of the provider class for SIRA_PRISE's proprietary SPS algorithm is (pls note that SPS is there if you want it, but's it is not specially recommended to use it).

As for encryption algorithms used for purposes of data privacy, only one algorithm is currently supported, and that is SIRA_PRISE's proprietary SPE algorithm. No configuration work must be done in the Java runtime to make that algorithm available.

Client authentication

When a client requests a connection, that client has the option of authenticating himself. If the client chooses not to do so, the connection is accepted and runs in unauthenticated mode. If the client chooses to authenticate, but cannot come to an agreement with the server about the algorithm to use (e.g. because no signing algorithm exists that is supported by both sides), the connection is accepted, and runs in unauthenticated mode too.

Any failure in the actual authentication process will cause the server to not accept the connection. A connection will therefore run in authenticated mode only if the client has chosen to authenticate himself, and has succeeded in doing so. Successful authentication requires a.o. that the server "knows" the client. This is achieved by registering a tuple holding the client's identity in the CLIENT relvar, and a tuple holding the client's certificate for a particular signing algorithm (of the client's choice) in the CLIENTCERTIFICATE relvar.

Thus, the steps involved in using client authentication are generally as follows :

  • Choose a name for the client, and a signature algorithm that the client will use to authenticate.
  • On the platform where the client will run, generate a keypair for this client corresponding to the chosen signing algorithm, and store it appropriately such that the client has access to the private key.
  • Generate an X.509 certificate holding the public key. The CN= part of that certificate must be the client's chosen name.
  • On the server, add a tuple to the CLIENT relvar with the appropriate value for the TRUSTAUTHENTICATION attribute (set this attribute to true only if the client performs user authentication, and you choose to trust this client's user authentications).
  • On the server, run the REGISTERCLIENTX509CERTIFICATE procedure to register the client's certificate in the catalog.

User authentication

SIRA_PRISE supports user authentication using two distinct methods : by accepting the user authentications performed by an authenticated client whose user authentications are trusted, or by authenticating the user directly.

The first method requires that a client can successfully authenticate himself to a SIRA_PRISE server, and that the server is configured to trust this client's user authentications. In those circumstances, a user transaction started in SIRA_PRISE is considered to be in authenticated mode if the userID passed to SIRA_PRISE when the transaction was started, is known to the server (this requires that a tuple exists in the USER relvar holding that given userID as an attribute value). If a userID is passed to SIRA_PRISE that the server does not recognize, then the transaction is not started. The connection remains idle, waiting for a new transaction to be started.

The second method requires that the client allows the user to create a signature using a private key that pertains to the user. The public key corresponding to this user's private key, must have been registered in the USERCERTIFICATE relvar. How the client software obtains the private key from the user, is for the client to decide. The client may, e.g., have been programmed to retrieve the private key from the chip on the user's identity card, or the client may use its own local keystores, etc. etc.

Thus, the steps involved in user authentication with the second method are generally as follows :

  • Choose the user ID, and a signature algorithm that will be used to authenticate.
  • Generate a keypair for this user corresponding to the chosen signing algorithm, and store it appropriately such that nobody but the user himself has access to the private key.
  • Adapt/configure the client such that it can interact with the user to compute the needed signature.
  • Generate an X.509 certificate holding the public key. The CN= part of that certificate must be the chosen user ID.
  • On the server, add a tuple to the USER relvar.
  • On the server, run the REGISTERUSERX509CERTIFICATE procedure to register the user's certificate in the catalog.