previous  next  Title  Contents  Index

14. Databases & Transaction Monitors

Transaction monitors

X/Open DTP

The X/Open DTP (Distributed Transaction Processing) standard is based on USLs TUXEDO and is an open standard for OLTP (Online Transaction Processing). Transaction processing is an application programming system which defines and co-ordinates interactions between multiple users and multiple databases or other shared resources. DTP extends transaction processing to include multiple resources and networked machines. On-line transaction processing is that part of DTP which manages the logging and execution of individual transactions.

DTP can be described as middleware which allows a (possibly transaction oriented) application to be distributed across numerous machines in a heterogeneous environment.
DTP is comprised of 3 modules:

1. AP = Application
2. TM = Transaction Manager
3. RM = Resource Manager (often a front end to a database). The RM must be capable of doing two phase commit (2PC) and support the XA protocol.

The following protocols are defined in the DTP standard:

XATMI is a subset of Tuxedo's ATMI - there are no transaction, authorisation, queueing or forwarding functions. XATMI defines buffertypes X_OCTET (equivalent to Tuxedo CARRAY), X_C_TYPE (equivalent to Tuxedo VIEW) and X_COMMON (similar to X_C_TYPE but used for both COBOL and C). The Tuxedo FML buffer type is not part of the standard.

TxRPC is a modified RPC: to support transactions, both restrictions and new features (transactional RPC) have been added. There are two types: one with full DCE implementation and IDL only (no DCE runtime). Transarc's Encina uses TxRPC.

Other notes:

Stored messages: a facility whereby request messages are written to stable storage for later processing. Provides greater recoverability and reliability (each request is guaranteed to be executed exactly once) and is more "mainframe like". Four queue types exist: request, reply, failure and error queues. TMS_QM is the TM server for stored messages, TMQUEUE is the server which manages the queue and TMQFORWARD allows forwarding of requests to another service.

CPI-C is the X/Open interface to LU6.2.

Peer-to-peer: allows half duplex communication between APs.

OSI TP: Provides transaction semantics to the OSI protocol and services.

XAP: is an API for connection to services in the presentation layer of the OSI protocol stack. It provides for portability of OSI applications such as X.400, FTAM, Directory Services, Network Management, VPT.

XAP-TP is an interface to the OSI TP service element and to the presentation layer. It is an extension of the XAP protocol.


Unisys Open/OLTP 4.2.2 = IMC TUXEDO 4.2.2 (for UNIXNT/PC)

Open/OLTP is Unisys's implementation of the X/Open DTP standard. IML are the actual developers of UNIX and PC versions of Open/OLTP, hence Open/OLTP = IML Tuxedo.

Apparently global transactions with IMS Hosts are not yet possible as no interface to LU6.2 (syncpoint level 2) exists.



Refer to the original USL Tuxedo documentation. The author used "U6000 Series TransIT Open/OLTP Transaction Manager - Administration Guide" from Unisys (7844 9709-000), November 1994 (this is version 4.2.1).

Transaction Integrity

Transaction integrity is guaranteed if the RM implements the two phase commit (2PC) XA protocol, the client correctly uses the ATMI 2PC function calls (e.g. tpcommit()) and the services controlling the RM correctly use the XA 2PC function calls (e.g. xa_precom(), xa_commit() ). The client and the service can abort their respective transactions and be sure that rollback occurs.

The 2PC protocol used by XA requires the use of a transaction log (TLOG) for the second phase of the 2PC. TM uses TLOG for recovery of current distributed transactions. There is one TLOG per OLTP server. The TLOG can either be a raw device or a file.


User identification / authorisation

See also the section Secure data exchange: peer entity authentication.

Audit Trail

Access Control


PERM 0600 [bulletin board & request queues: IPC permissions]
SYSTEM_ACCESS FASTPATH [Unisys say PROTECTED is not usable in production]





Object Reuse

Reuse of objects for covert data transfer should be prevented by the measures above for UNIX login, filesystem and shared memory.

Secure Data Exchange

The client can use the ATMI function tpchkauth() to check the level of security required for an application. Then the client fills the TPINIT buffer with the required security information and sends it to the server via tpinit().

Peer entity authentication

OLTP offers very little security as standard, however it provides an open framework in which an application can implement strict security functionality. Three authentication methods are offered: service based, client based and customised.

=> Server based access (OLTP "Level 2" security)

=> Client based authentication (OLTP "Level 3")

=> Customised authentication/authorisation

=> Secure naming services such as NIS+ or Kerberos (and hence DCE) can be used for authentication, if a front end is written to OLTP and installed as an AUTHSVC service. Special authentication services can also be used (Unisys have already implemented an authentication server ZKM for the Schweizerische Aussenministerium).


The integrity of data transferred between OLTP client and server is guaranteed by TCP sockets. No additional measures (such as checksums) are implemented.


The password information in the TPINIT buffer is bit mask encrypted before being sent over the network. This is not enough for sensitive applications, therefore application password and authentication code should be encrypted before being written into the TPINIT buffer. The encryption mechanism should be such that playback is not possible and should not be easily decrypted by brute force. Public key algorithms for generating an unique session key depending on time / user names / IP address / host names are recommended.

System and application messages can be compressed (Tuxedo 4.2.1 or later) with the environment variable TMCMPLIMIT. Especially useful for low speed networks and reducing application boot time. Compression strength can be set for local and remote messages separately. By setting remote compression, but no local compression, data does not appear in clear text during network communications. This offers very simple confidentiality against casual network sniffers. To implement this functionality, set TMCMPLIMIT=0,MAXLONG.

Recommendation: use both local and remote compression.

Data origin authentication

Data origin is known, if "OLTP level 3" security is used. It should be noted that TCP/IP has very definite weaknesses in the area of authentication (IP spoofing).

Non repudiation of origin/receipt

Digital signatures are not defined in OLTP, however they may be implemented on the application level.


Load Balancing
Service Redundancy
Master Redundancy
WSH (Workstation Handler) Redundancy

DOS/Windows PCs do not have a full OLTP implementation. TDWIN (the OLTP client) requires access to a UNIX machine with a WSH process. There is no way of specifying a backup WSH server in the OLTP protocol. To provide redundancy, the following are possible:

From V4.2.2. there is a time-out on the server side, so if a PC client hangs up, the server will close open connections to this PC after a certain time-out.

OLTP Interface to Oracle 7

Oracle 7.0.12 is XA compliant. The XA interface allow OLTP client to access an OLTP server (the Oracle RM) which can pass on requests (SQL) to an Oracle server. The Oracle RM runs under one user (specified in UBBCONFIG), so multiple OLTP servers must be running under separate users, if db access under different usernames is required.

HIT (Host Integration Toolkit) 1.0

Unisys's HIT tool is not a TM, but is mentioned here because it uses Open/OLTP. HIT interfaces to classical mainframe applications by telnet or 3270. It translates terminal oriented information into transactions. This conversion takes place on an Open/OLTP server. Clients normally access HIT server services via OLTP, but a direct connection via the SThandler protocol is also possible (though it is not discussed here).


Transaction Integrity

Depends on how scripts & client software are written. No implicit transaction integrity is offered by HIT.


User identification / authorisation

See the section Secure data exchange: peer entity authentication.

Audit Trail

Access control

UNIX Login security

Different UNIX users are required when running HIT:

  1. "Installation user": The HIT application should be installed as a dedicated user(s) e.g. hit1-0. If possible this account should be locked. It's home directory should be the directory where all HIT binaries (e.g. /opt/hit-1.0) are found.
  2. "Application manager": This user controls the OLTP system application and must belong to the ST group. It's home directory is $PROJDIR.
  3. "Domain manager": HIT servers run under a special user, e.g. hit_domain whose home directory is $DOMAINHOME, where server configuration files (e.g. UBBCONFIG) are found. This user must be a member of the ST group and is primarily for administration.
  4. "HIT user": Client requests run under this user, e.g. hit_client, whose home directory is $DOMAINHOME. If possible this account should be locked. This user must be a member of the ST group.
    - It is possible that the above users 2,3 and 4 be one user with home directory $DOMAINHOME for simple installations.

The umask must be set for these users to 077, to ensure that files created by HIT have permissions rwx------ . (TBD, perhaps 027 is necessary rwxr-x--- ?)

File System

File and directory permissions must be set restrictively for the application and server directories.

chmod 750 $PROJDIR
chmod -R w-rwx,g-w $PROJDIR/* /etc/ /etc/stconfig
chown -R hit.ST $PROJDIR/* /etc/ /etc/stconfig
chmod 750 $DOMAINHOME
chmod -R w-rwx,g-w $DOMAINHOME/*
chown -R hit_domain.ST $DOMAINHOME/*

If $SPOOLDIR is set to a directory only used by HIT, then logs should be protected by use of umask (see above) and the directory should also be protected:

chmod 770 $SPOOLDIR
chown hit.ST $SPOOLDIR

Object Reuse

Reuse of objects should be protected by the measures above for UNIX login and filesystem. Shared memory must also be protected (see OLTP chapter).

Secure Data Exchange

Peer Entity authentication

Since the HIT servers are started from inetd, it should be possible to restrict client access by IP address if the tcp wrappers are used and DHCP is not used.

HIT has it's own authentication server (AUTHserver), which uses two (ASCII) password files in $DOMAINHOME, one for host accounts (serv_passwd, managed by stpasswd -s) and one for client access (trans_passwd, managed by stpasswd -t). The host password file lists accounts and passwords on the host access via telnet/3270.

AUTHserver offers three services:

  1. The AUTH service is called by SThandler on transaction #1 and verifies username/password against the trans_passwd file. If all is OK contact is made with the OLTP TM and subsequent transactions are allowed.
  2. The transPSW service is called after the AUTH service above is called. It checks password aging (PASSREQ, MAXDAYS) and passes additional parameters ${PSW[0-0]} are passed back to the transaction script.
  3. The servPSW reads $USER and $PASSWD and $PSW[0-9] from serv_passwd on the basis of server name and id and returns these values.


Non repudiation of origin / receipt

Digital signatures are not defined in HIT or OLTP, however they may be implemented at the application level.


Service Redundancy

HIT offers no additional redundancy to that offered by OLTP.


General guidelines for (relational) Databases


TCSEC Evaluated Databases

Consider using a TCSEC evaluated database. The following table lists the databases evaluated by the NSA in Spring 1996 [nsa1]. See Appendix C for a more detailed discussion of TCSEC. C2 is the TCSEC level aimed for by most commercial systems.

Even if a system is evaluated to a certain level (e.g. TCSEC C2), it still requires careful configuration, monitoring and organisation processes for it to be considered "secure" in a real production environment. Don't attach too much importance to the "label" C2 for it's own sake. It is often used as a sales pitch without real substance. E.g. a system may offer "C2 auditing", but that doesn't mean that the audit logs are useful, or that tools for high level analysis of these logs are included in the system, or that anyone actually reads the logs!

Database Level Cert. date Notes
Informix Online/Secure 5.0 B1 15.11.94  
Trusted Oracle 7 B1 5.4.94  
Secure SQL Server, V11.0 B1 18.5.95 Sybase
SQL Server, V11.0.6 C2 13.10.95 Sybase
Informix Online/Secure 5.0 C2 15.11.94  
Oracle 7 C2 5.4.94  

Transaction Integrity


User Identification / authorisation

See also the "Policy" chapter for general rules.

Audit trail

Access Control

UNIX Login security
File System
Views and stored procedures

Views and SPs can serve as security mechanisms. A user can be granted permissions on a view or stored procedure, even if he/she has no permissions on objects that the view or procedure accesses. Through a view, users can query and modify only data they can see. The rest of the database is neither visible, nor accessible.

Object Reuse

Objects used by a subject must be reinitialised before being used by another subject.

Communication / Secure Data exchange

Peer Entity authentication

A user enters a password to access an application database via an application. The application encrypts this password to form a second password. This second password is the actual password used by the database access routines. The database knows only the second password, while the user knows only the first password - therefore the user cannot access the database directly (even if he has the tools available) since he has no valid password. It is important that the encryption algorithm used by the application not become known. This method can be applied to any database.


Guaranteed by the transport protocol used (e.g. TCP sockets, Named pipes...).


Are passwords and usernames passed in clear text over the network between the SQL client and database?

Data origin authentication

Guaranteed by the transport protocol used (e.g. TCP sockets, Named pipes...), plus the challenge response method used on initial connection.

Non repudiation of origin / receipt

Digital signatures are not normally offered by databases, they can be implemented on the application level.



Basically a full backup of all databases and transaction logs would be nice each day. However it is rarely possible due to performance (dumping a 50GB database can take a while...), costs (disk space, jukeboxes) or time (the night is not long enough for updating, checking and backing up) reasons.

Prevention of resource abuse

Quotas, CPU, memory limits etc. per user are available with some databases.


Certain databases offer replication of data between servers. This feature can be used to improve availability.



See also general database recommendations.


4.9.x is very similar to Microsoft's SQL V4 (because MS bought 4.9 for OS2 & NT from Sybase!). Refer to the MS-SQL section until this section is complete, for recommendations.

Known security problems

dd if=/dev/rdsk/c?d?t?s? | strings | egrep "mastersa|masterMYUSERNAME"

System 10, System 11

Microsoft SQL server

See also general database recommendations.



Identification / authorisation

The hierarchy of users is sa (system administrator), dbo (database administrator), doo (database object owner) and users. The sa is a superuser who works outside the permissions system, so it is very important to protect this account from unauthorised access.

SQL logon can be configured for standard, integrated or mixed modes.

  1. Integrated: The NT login validation system is used by SQL server. User accounts defined in NT which are assigned user level privileges in SQL server can directly access the database without entering any additional username or password. Only trusted connections are allowed into SQL server. NT users who have Administrator privilege are logged into SQL server as sa.
  2. Standard: SQL server manages it's own login validation (i.e. usernames and passwords) independently from the operating system. This is the default.
  3. Mixed: Logins are first treated as in integrated mode the as in standard mode. This is useful where not all users connect via named pipes or are not logged onto an NT domain.

Even if a user has an SQL login, he does not have automatic access to databases. The database owner must add the user to each database (sp_adduser).

Tools: xp_loginconfig displays the current login setup. xp_logininfo shows accounts and their login configuration.


sp_password null,NEW_PASSWORD,sa

Audit trail

When installing SQL server, the following options are recommended:

The NT event log can be sorted by application, date and priority. It should be monitored regularly for unusual activity. NT alerts should be used to notify the administrator of critical conditions.

Access Control

Views and stored procedures

Views and SPs can serve as security mechanisms. A user can be granted permissions on a view or stored procedure, even if he/she has no permissions on objects that the view or procedure accesses. Through a view, users can query and modify only data they can see. The rest of the database is neither visible, nor accessible.

Object Permissions

sp_helpprotect can be used to display an object's permissions. Permissions may be set on objects and statements.

Object Reuse

no relevant features.

Communication / Secure data exchange

Peer Entity authentication

SQL server can communicate with clients via sockets and named pipes. It is preferable to use named pipes, as SQL server can directly use the NT user account database (integrated logon), so user accounts on the SQL server do not need to be managed separately from NT.

Remote server access: A local server may directly access a remote server without having to logon (sp_addserver,sp_configure 'remote access' 1). The remote server is effectively controlled by the local server. The mapping of local users to remote usernames may be achieved by:

The commands sp_addremotelogin, sp_helpremotelogin can be used to configure/examine remote users.

Trusts: The remote server can trust the local server (no password exchange is necessary) or he can consider the connection as not to be trusted. Trust can be used between servers of equal security classification and administrated by the same persons.

To ensure that user authorisation takes place, the option trusted should be set to FALSE in SQLadmin -> remotes -> manage -> remote logins -> set login ID -> manage -> remote login options (or via sp_remoteoption).


Communication via named pipes guarantees (weak) encryption of username/password, whereas sockets do not, so named pipes are preferable.


See also General database availability guidelines.

Backup / Recovery

See also General database availability guidelines.


Set the recovery interval to control maximum time to recover databases after a crash. This has the effect of setting the time between checkpoints.

Consistency Checking

net send /users "SQL is going down in 30 minutes, please disconnect"


Only available in V6. TBD.


Mirroring, can prevent continuous operation in the event of disk failure. In addition to SQL Server mirroring, NT server offers filesystem level mirroring and RAID 5. Mirroring/RAID may also be implemented at the hardware level. Mirroring affects performance as well as availability.

SQL Server V6.0

A new version is available since summer 1995: SQL Server 6.0. This version offers enhanced security features over the previous version (V4.21):

TBD: specific recommendations for V6

Oracle 7.1 or later



See "Oracle7 Server Documentation: Addendum Release 7.1", "Oracle7 Server Concepts Manual" delivered with the Oracle product.


Identification / authorisation

Oracle allows user authentication to be carried out by either:

  1. the OS (usernames must still exist in the database).
  2. or by Oracle itself. In this case a password is stored for each user (in encrypted form) in the database.

Both methods may be used within the same database.

Privileged user, prior to V7.1:

Privileged users, V7.1 and later:

A privileged user can connect via CONNECT user_name/server@my.domain AS SYSDBA. To check which users have these privileges, use the view V$PWFILE_USERS .

Audit Trail

Access Control

Discretionary Access Control

Oracle provides fine-grained access control through the use of schemas, privileges, roles, views and table security.

A user's access rights are controlled by the settings in the user's security domain. The security domain consists of:

The privileges and roles which provide the user with access to objects.

Each Oracle database has a list of schemas. Each schema is a collection of schema objects, such as tables, views, clusters, procedures and packages. Each database also has a list of valid users and to access a database, the user must identify himself and be authorised (via a personal password). When the database user is created, a corresponding schema is also created which govern access to objects in that database. A user can only connect with a schema of the same name.

A privilege is a right to execute a particular SQL statement (system privileges) or access a particular object (object privileges). Privileges can be directly granted to a user or a role (see below). System privileges are attributed via the SQL commands GRANT/REVOKE or SQL*DBA (Grant system privileges/Roles dialog box). Only users with the system privilege ADMIN OPTION or GRANT ANY PRIVILEGE can grant/revoke system privileges to/from users/roles of the database.
Object privileges are also attributed via the SQL commands GRANT/REVOKE. Object privileges can be granted/revoked by the owner of the schema, or by a user who has been granted the GRANT OPTION on that schema.

A role is a named group of privileges which can be attributed to users or other roles. For example, an application can be split up into the following roles: db Administrator (full privileges), db Operator (backup privileges), Application Owner (for each db application) and Application User.
Roles offer the advantages of:

Roles can be subdivided into application and user roles.

Within a database each role name must be unique and cannot be the same as a username. Each role has it's own security domain. Each user has the privileges associated with his security domain, plus the privileges of roles granted to the user (that are currently enabled).
Recommendation: attribute privileges to specific roles, not users.

Predefined roles in V7: CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE and IMP_FULL_DATABASE. These roles may be modified.

Each database contains a user group called PUBLIC (to which all users belong). Member of PUBLIC may see all data dictionary tables prefixed with USER and ALL. Privileges (system, object privilege or role) can be granted to PUBLIC. Restrictions: tablespace quotas cannot be assigned PUBLIC. The only objects which can be created as PUBLIC are links and synonyms (CREATE PUBLIC DATABASE LINK/SYNONYM).
Recommendation: only grant privileges to PUBLIC which are really necessary for ALL users.

Table security is provided for in two ways:

Views: To use a view, a user requires only the privilege for the view (not for the underlying tables/data). This improves security by providing access to only certain rows/columns in a table. It may be easier (and less error prone) to manage view access than privileges to the underlying data/columns.
A view can be created if a user has the privilege CREATE (ANY) VIEW and SELECT, INSERT, UPDATE/DELETE on the underlying base objects. To grant access to this view to other users, the GRANT OPTION or ADMIN OPTION is needed.
Recommendation: use views for access control.

Packages: can be used to group together procedures. A role/user can be granted EXECUTE privilege on a package, effectively allowing use of all procedures and public variables in that package (assuming also that the user has sufficient privileges to access the data manipulated by the package). Specific EXECUTE privileges cannot be attributed for a package's constructs.
Note that the system privilege EXECUTE ANY PROCEDURE allows a user to execute any procedure in the database.

Secure system startup

See general database recommendations.

Secure data exchange / communications

Remote Links:
Remote databases communicate with each other via links. A link is a path to a remote database and has two components: a database string and a remote account (username & password). Two types of links exist: Private and (created for the group PUBLIC. Any user can use this link, there is no way to restrict access). When a user accesses a remote database via a link, he/she may do so either using the same username/password as locally, or by using a "central" account for access to the remote database e.g.

CONNECT TO remote_user_name IDENTIFIED BY remote_user_password
USING `some_db_string';

If the CONNECT TO phase is ommitted, individual accounts are used.

Recommendation: Use individual links for class databases.

Peer entity authentication
Data integrity

Depends on the communications protocol used by SQL*net, e.g. TCP/IP, DECnet, SNA (LU6.2), Appletalk, OSI4, IPX, Named Pipes....

Data confidentiality
Non repudiation of origin/receipt

Not supported by SQLnet or the protocols it uses.

Access control

Standard SQL access control.


Prevention of Resource Abuse

On large multiuser systems, it is important to be able to set restrictions on the system resources used by a user. However monitoring of resources normally results in a slight degradation in performance. It also requires extra sysadmin's time.

A profile is a set of resource limits which can be assigned to a user. Each of these resources can be managed per session (a session is created each time a user connects to a database) or per SQL call (each time an SQL statement is executed). When the limits are reached, the current statement is stopped and the user can either roll back, commit or disconnect. Resource limits:

Define a minimum number of different profiles and attribute them to users. The more profiles, the more time it takes to manage them. The best way to estimate limits is to look at statistics on a live system.

Quotas: Tablespace quotas per user can be use for disk space management. If the quota for a tablespace is set to zero, a user cannot use any new space, but the existing space occupied by him remains.

Backup and restore


  1. Operate the database in ARCHIVELOG mode, it provides:
  2. If possible, shutdown Oracle before doing backups.

See also General database recommendations.
Oracle does not offer Mirroring, it must be achieved on the OS, disk or filesystem level.


Replication can increase performance (by reducing remote queries) and availability (replicated copies are still available if the master dies). The source server contains the master data and the target server contains a read-only copy of the master data (called a snapshot).Oracle provides two methods or replicating data from one server to another:

The snapshot can be refreshed via a complete refresh (i.e. all data in the snapshot is transferred from the master), or a fast refresh (only changed rows are transmitted). Fast refreshes are only possible on simple snapshots (i.e. each row in the snapshot corresponds exactly to a row in a single remote table, no subqueries, joins etc. are allowed) used with a snapshot log (i.e. a table in the master database which tracks rows changed in the master table).

[1] See "Application Development & Administration, Tuxedo Release 4.2 ETP" from USL, page TA2-7.
[2] Note that orapwd expects the password for INTERNAL or SYS on the command line. The command line is visible to other users on a UNIX system (via ps) when orapwd is running.

previous  next  Title  Contents  Index  IT Security Cookbook, 21 July, 2000