The IT Security Cookbook - Databases & Transaction Monitors


Published: Oct 16, 2002 Updated: Oct 16, 2002 Section: Network Security Library :: Misc :: The IT Security Cookbook Author: Printable Version Adjust font size: Rating: 3.8/5 - 4 Votes 1 2 3 4 5

Transaction monitors
Databases

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:

XAPTMI = AP to TM protocol
XA = RM to TM protocol. The TM always initiates the connection, but an RM can register/ unregister itself with the TM. XA may be synchronous or (optionally) asynchronous. XA makes 2PC possible through such functions as xa_start(), xa_commit(), xa_complete(), xa_recover(), xa_rollback() .
The server part of the AP talks to RM via SQL or other protocols.
TMS = Transaction Management System is required for the implementation of 2PC over distributed databases.
TX = AP to TM protocol for defining and managing global transactions. It supports chained and unchained transactions. e.g. tx_begin(), tx_commit(), tx_rollback().
TxRPC = AP to CRM protocol, based on DCE RPC.
XA+ = TM to CRM protocol. Based on XA, defines the verbs necessary for the RM to initiate communication with the TM. May never become a standard, controversial, voted down in the June 1993 meeting of the TP working group. USL doesn't want it.

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.

Documentation

X/Open CAE Specification: "Distributed Transaction Processing: The XA Specification". (ISBN 1 872630 24 3, 1991)
TX Specification (preliminary): ISBN 1 872630 650
XATMI Snapshot specification: ISBN 1 872630 804

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.
Illustration:

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

General

Characteristics

Open/OLTP runs on UNIX, Unisys & MVS mainframes and Windows NT. DOS/Windows PCs can have a greatly reduced OLTP client called Tuxedo/WS (from IMC) or TDWIN (Transactional Desktop for Windows, from Unisys). Tuxedo/WS connects to a WSH on a UNIX machine to access OLTP services (V4.2 or later). Macintoshes can also access OLTP.
The Master BBS server cannot be a PC client.
In Tuxedo, the TM is not a process. TM functionality is achieved by the use of a distributed bulletin board which contains information on what services and servers are available, where. The master OLTP server is responsible for the distribution and maintenance of the bulletin board (via the BBL - Bulletin Board Liaison).
Three modes of communication are available: synchronous tpcall(),asynchronous tpacall() and conversation tpsend() mode.
Multi-node configuration is only required where service calls must be made to physically separate machines.
System and application messages can be compressed when the environment variable TMCMPLIMIT is set. Recommended. See confidentiality section below.

Compatibility

Open/OLTP should be 100% compatible to USL's Tuxedo.
The DTP X/OPEN standard does not contain some features in Tuxedo, such as the (very useful) FML fields.
Tuxedo defines buffer types CARRAY (equivalent to X_OCTET), VIEW (equivalent to X_C_TYPE). The X_COMMON buffer (similar to VIEW but used for both COBOL and C) is not available. The FML buffer type available in Tuxedo is not part of the DTP standard.
Databases supported: Oracle 7, Informix, Sybase 10 (apparently yes), Sybase 11 (planned?). Unknown: MS-SQL Server V6

Documentation

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.

Accountability

User identification / authorisation

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

UNIX Login security: The OLTP server should be started under a dedicated user e.g. tuxedo or tm4-2-2. If possible this account should be locked. It's home directory should be the directory (e.g. $APPDIR ) where all OLTP server and service configuration files (e.g. UBBCONFIG) are found.
The umask should be set for this user to 077, to ensure that the ULOG and TLOG file have permissions rw---------- .

Audit Trail

All log files should have permission 0600 and be regularly monitored, archived and pruned.
It is suggested that all log files be kept in a separate partition, such as /var.
The UNIX system logs should ensure a record of attempts to access the UNIX accountsOLTP operation and administration (e.g. via syslogd).
The OLTP server process writes server errors to the (ASCII) ULOG file (one exists on each server machine). Applications can write to this log via the userlog() function. A ULOG is created for each day. The ULOG path is specified in SERVERS/ULOGPFX in the UBBCONFIG file.
The server can also log services called to ULOG (UBBCONFIG option CLOPT -r, see man txrpt). This could help audit trail for understanding synchronisation of different service calls.
Services write to stdout and stderr files by default. If these are used, they should be renamed to indicate service names (can be specified with the CLOPT variable in the SERVERS UBBCONFIG file).
It is suggested that there be a special audit log for each application: Each application server would write two entries in the log for each call from a client. E.g. when a client calls a service, the service writes: User, date, time, client IP address, function, function parameters to the log. When the service has done the work requested by the client, it writes another entry to the log detailing the information returned to the client. (Is it possible for a service to get the client IP address?).

Access Control

Bulletin Board protection: The PERM variable specifies the permissions used when creating IPC shared segments. Read/write for owner only are recommended. The command ipcs lists all shared segments and their permissions. The UBBCONFIG file should be configured as follows:

*RESOURCES

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

*MACHINES

PERM as RESOURCES/PERM

*SERVICES

RQPERM as RESOURCES/PERM
RPPERM as RESOURCES/PERM
SYSTEM_ACCESS as RESOURCES/SYSTEM_ACCESS

The SYSTEM_ACCESS variable can have two values (V4.2 or later), PROTECTED (the shared memory for Tuxedo internal tables is not accessible from code outside the Tuxedo libraries) or FASTPATH (internal shared memory is not protected). Unisys have indicated that FASTPATH must be used, but this is possibly because they were still using 4.1. (Unisys: please check!). For maximum security, PROTECTED would be better.
Request queue and reply queue permissions are controlled by the RQPERM and RPPERM variables. These should be set to 0600 (as PERM above). If these parameters are not specified, the value in PERM is used.
File System: File and directory permissions must be set restrictively for $APPDIR directory (e.g. ~tuxedo). i.e.
chmod 700 ~$APPDIR
chmod -R w-rwx,g-rwx ~$APPDIR/*
chown -R tuxedo ~$APPDIR/*
The OLTP binaries directory (e.g. $ROOTDIR ) normally belongs to root and is only writeable by root.
chmod 755 ~$ROOTDIR
chmod -R w-rwx,g-rwx ~$ROOTDIR/*
chown -R tuxedo ~$ROOTDIR/*

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)

A password is required for access to a server. Set the following in UBBCONFIG:
*RESOURCES
SECURITY APP_PW
When UBBCONFIG is processed by tmloadcf, a password will be demanded. Any client connect to this server must provide this password in the TPINIT buffer. This password may be changed dynamically via tdadmin -> passwd or tmconfig.
OLTP sends the password (i.e. part of the TPINIT buffer entry) over the network bit-mask encrypted (i.e. very weak encryption).

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

Level 2 must be enabled.
Initial client authentication: The client must supply an authentication code before being allowed to access a server service. An authentication server must be defined (AUTHSVC). It is recommended that class systems implement this security function. The UBBCONFIG file should be configured as follows:
*RESOURCES
AUTHSVC auth_server [name of auth. service, max. 15 chars]
*SERVERS
,,auth_server" [authentication server program name]
Session authorisation: After the initial authentication, an authentication key is exchanged with each buffer transferred (appkey). This (4 byte) session key is checked during each communication (transparently to the application). Most modern session keys are at least 16 bytes in length, it is doubtful whether this 4 byte key is sufficient for protection of confidential transmission.
A default authentication service AUTHSVR is provided. This service checks users against a UNIX style password file. See the AUTHSVR(5) man page. TBD
For class systems, it is recommended not to use the default authentication server, but to use a well known system such as Kerberos or NIS+. However, the session key remains a weak 4 bytes, so it may be preferable to forget the OLTP authentication mechanisms and implement a customised solution.

=> Customised authentication/authorisation

A few special tpcall() after tpinit() can be used to implement customised authentication.
One (or all) of the parameters passed from client to server could be encrypted using a commonly known session key.
Another option would be to replace the standard field encoding/decoding routines with special routines which implement additional encryption or signatures. However, this may require source code knowledge of the original encoding/decoding routines.
An application can implement customised encoding/decoding of transmitted data by defining new communication buffer types (by adding an instance to the tm_typesw[] array).

=> 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).

Integrity

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

An application can implement additional integrity checking by defining new communication buffer types (by adding an instance to the tm_typesw[] array).
Multi-node: During the booting of a multi-node configuration (tmboot), the configuration file is copied from the master to all servers. This ensures that all servers have a integral view of system configuration. However, this feature slows bootup time.

Confidentiality

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.

Availability

Load Balancing

Static load balancing is possible if the OLTP administrator correctly defines the SERVERS/LOAD variable in UBBCONFIG and LDBAL=Y. Each service can be given a weighting factor and OLTP uses this weighting factor plus queue length to decide where to queue requests.
TMNETLOAD allow a bias to be set for local services over remote services.
Queue definition is also important. Queuing can be:
MSSQ: multiple services, single queue
MSMQ: multiple services multiple queues (each service has it's own queue).
In an MSSQ environment, real time load balancing (i.e. based on bulletin board statistics) is used if MODEL=SHM, for V4.2.1 or later.
If MODEL=MP, in an MSMQ environment, round-robin balancing is used.
A server can examine requests waiting in it's queue, and do it's own load balancing/prioritisation.

Service Redundancy

OLTP allows data dependant routing of services i.e. several servers may offer a particular service, when a client requests use of a particular service, the TM will see what servers offer this service and route the request according to data content. The rules for routing are very flexible.
An application can implement customised routing by defining new communication buffer types (by adding an instance to the tm_typesw[] array).
Data dependant routing is possible by defining the SERVICES/ROUTING variable in UBBCONFIG.
If an OLTP node (in a multi-node configuration) goes down, the master server would notice after a certain time (specified by the SANITYUNITS*SANITYSCAN parameters[1] in UBBCONFIG) and update the bulletin board. If duplicate services are offered on other servers, all new requests will be routed to these other servers.

Master Redundancy

A backup master OLTP can be used. i.e. the UBBCONFIG can have a entry of the form:
*RESOURCES
MASTER master_machine_name,backup_machine_name
For single node configuration this is not possible, so redundancy would have to be provided on the system level.

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:

The UNIX machine providing WSH service for PCs must have high availability.
The PCs must be configured to reboot with a backup configuration which points at another WSH server.
The client application SW implements logic such that it tests for WSH availability and automatically switches to another WSH server. This increases client SW complexity, however.

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.

The OPENINFO string (Acc=P/USER/PASSWORD ) defined in UBBCONFIG can contain a user account and password in clear text! e Protect this file (UBBCONFIG), if possible once it has been used to generate TUXCONFIG(it's binary equivalent), encrypt UBBCONFIG and remove any clear text copies. UBBCONFIG does not need to exist on production systems.
The OPENINFO string Logdir= is used to specify where XA error and tracing information is logged. The default log directory is $ORACLE_HOME/rdbms/log. The default file name is xa_DBNAME_MMDDYY.trc. The Logdir must exist and be writeable.
The OPENINFO string SQLnet = is used allow logins over SQLnet V1 or V2. e Use V2.

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).

General

Installation

HIT can be cleanly installed/deinstalled using the SVR4 package commands.
An account for the "Application manager" and a group ST is created during installation.

Transaction Integrity

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

Accountability

User identification / authorisation

See the section Secure data exchange: peer entity authentication.

Audit Trail

It is recommended that all log files be kept on a special partition (i.e $SPOOLDIR = /var/hit) and be regularly archived and pruned.
HIT offers the following utilities for logging, debugging and monitoring. The services ALARMserver and LOGserver are recommended.
I. LOGserver
This server is called if a LOG statement is included in a transaction or function script. If LOGserver is not active, all messages are lost. Messages are logged to $LOGPFX.mmddyy if they have priority $LOGLEVEL or higher. Multiple servers are advised to assure that messages are not queued too long.

II. STATserver
STATserver receives statistics information from the transaction-handler and server. If STATserver is not active, all statistics are lost. Messages are logged to $STATPFX.mmddyy if they have priority $STATLEVEL or higher. Multiple servers are advised to assure that messages are not queued too long. Note: Statistics files can grow quickly!!

III. ALARMserver
This server is called if a ALARM statement is included in a transaction or function script. If ALARMserver is not active, all messages are lost. Messages are logged to $ALARMPFX.mmddyy and displayed on the ALARM device (e.g. console) if they have priority $ALARMLEVEL or higher. Multiple servers are advised to assure that messages are not queued too long.

IV. stmon
The server and transaction processes can be checked while they are active with the stmon tool.

V. tracing
During the development and debugging stages, tracing can be useful. If TRACE is set to greater than 0, then tracing information is sent to TRLOGFILE.

VI. Recv & send debugging
In $PROJDIR/envfile.app, DEBUGFILE can be used to specify the file prefix for recv(), send() and ID statement logging.
Application audit trail logs: see guidelines in OLTP section.

Access control

UNIX Login security

Different UNIX users are required when running HIT:

"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.
"Application manager": This user controls the OLTP system application and must belong to the ST group. It's home directory is $PROJDIR.
"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.
"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/domainname.map /etc/stconfig
chown -R hit.ST $PROJDIR/* /etc/domainname.map /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:

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.
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.
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.

Weaknesses:

Only authenticates initial TPINIT connection, not each communication.
It uses flat files for passwords (not distributed databases) the same as the standard UNIX password file.
Does not use the standard 4 byte session key.

Integrity

Telnet/3270 (hence sockets) is the only guarantor of integrity of data transmitted to the Hosts.
OLTP guarantees data integrity of data transmissions to clients (via TCP sockets).

Confidentiality

Data transferred between HIT and the hosts (via telnet) is in clear text - even usernames and passwords. This is due to the telnet / 3270 protocol.
Data transferred between HIT and clients go over OLTP. Passwords are weakly encrypted, but usernames and data are not.

Non repudiation of origin / receipt

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

Availability

Service Redundancy

HIT offers no additional redundancy to that offered by OLTP.

Databases

General guidelines for (relational) Databases

General

Install known vendor security patches.
Documentation: specify where detailed information on Security topics for each database can be found.
use a database certified to military security or do not install the database on a networked machine.

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

Database engines protect data integrity with their rollback/rollforward recovery mechanisms. Data integrity manipulated by an application is guaranteed when the application correctly uses the begin transaction, rollback transaction and commit SQL commands.
The two phase commit commands can guarantee the integrity of distributed databases, if used correctly in applications.
Referential integrity in relational databases is enhanced by the use of triggers (Sybase, SQL Server, Oracle).

Accountability

User Identification / authorisation

See also the "Policy" chapter for general rules.

Use different passwords for database administrator (sa) and OS system administrator.

Audit trail

Document non standard installations.
Monitor logs regularly for security breaches or strange behaviour.
Applications should keep a centralised record of who did what, from which terminal, on what machine, when, with what object and whether successful or not.

Access Control

UNIX Login security

The database engine should be started under a dedicated user e.g. sybase. If possible this account should be locked. It's home directory should be the directory where all database configuration files (e.g. /opt/sybase) are found.
Unix Servers : The umask should be set for this user (e.g. sybase) to 077, to ensure that files (logs, dumps...) created by the database have permissions rwx------. Although if group access is required, then umask 027 may be necessary).

File System

Unix servers: If filesystems are used, scramble the inode numbers (via fsirand) to make it more difficult to read the raw device directly (Suns).
Set ownership/permissions of files/raw devices restrictively - only the database should be able to read or write these devices/files.
Database administration scripts should only be readable by the database administrator.
Database administration scripts containing clear text passwords should be encrypted, see [unix1].
Don't use passwords on the command line of utilities, they are visible in the process table list. This can be avoided by entering the password interactively or redirecting stdin from a file.
Create a (UNIX) group (e.g. sybase) for those users who need access to database tools directly.
File and directory permissions must be set restrictively for the database home directory (where the configuration files & binaries are kept). E.g. for user ~sybase on an Unix server:
chmod 750 ~sybase
chmod -R g-w,o-rwx ~sybase/*
chown -R sybase.sybase ~sybase

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

If SQL clients can access the database directly over sockets/TLI/RPC/named pipes, what measures are taken to restrict access? Can any machine with the corresponding SQL client software connect to the server and be presented with the database login prompt?
If possible, users should be preventing from directly accessing databases via tools such as ODBC, isql, dwb, Gupta SQL plus etc. One method of doing this is to use a two-pass password encryption scheme.

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.

Integrity

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

Confidentiality

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.

Availability

Backups

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.

A backup and restore policy must exist and be regularly tested. Restores, especially, need to be tested.
Specification of a recovery time is the first step in choosing a backup policy.
- It takes longer to restore a database and transaction logs, than just a database.
- Backups (database dumps) may have to be made to disk, rather than tape to ensure minimum downtime. Dumping 2GB of data may take only 30 minutes, but restoring a 6GB Sybase database from fast disks (barracudas) may take 8 hours (5 with a Clariion RAID), a tape would take 4 or 5 times longer.....
If backups are made to disk, store on a separate disk to either data or transaction logs .
Use a database which allows on-line backups, i.e. backups can be made when the db is active.
The transaction log is normally backed up more frequently backed up than the database for speed/space reasons. Backing up the transaction log captures changes made since the last database dump.
Databases should be backed up after creating a database or index, or after performing a nonlogged operation (dump transaction with no log or with truncate_only, fast bulk copy). Sybase for instance, will not allow transactions to be dumped after such an operation unless a database dump has been carried out.
The master database rarely changes (on a production db, perhaps only when users or disks are changed), so backups can be less frequent than live databases.

Prevention of resource abuse

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

Replication

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

Redundancy

Use separate disks for database data and OS.
Use separate devices for database data and transaction logs.
Some type of disk or database redundancy (RAID or replication) is required.
If disk mirroring is used, mirror to separate disks (and separate controllers if possible).
Performance as well as availability is affected by RAID configurations. While mirroring offers more redundancy than RAID 5, it takes more disk space and may be slower.

Sybase (Nov.'95)

See also general database recommendations.

4.9.x

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

No log is kept of successful or failed login attempts.
No socket protection (Could install socket monitor monitor_socket.pl), anyone with an SQL client (isql, dwb) can connect to the db and be presented with the Sybase login.
Easy to "sniff" client/server login process.
User passwords are stored in clear text in the master..syslogins table, visible to the sa. This means that the passwords are also stored in clear text on disk - meaning that if one has the right to read the Sybase raw devices, then the following command could possible list passwords:

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

Sybase allows on line backups, but not to pipes and compression is not supported. This issues (and other enhancements) are addresses in the 3rd party backup utilities "dttools" (from Datatrack??). Recommended for large (> 2GB) databases.

System 10, System 11

Logging is much more detailed, User actions can be logged down to the statement level.
Passwords are encrypted in the syslogins table and on disk.
TBD

Microsoft SQL server (Dec.'95)

General

Analysis is based on SQL server 4.12.
SQL server V6 is a feature rich DBMS based on Sybase 4.9 with many security options.
Tools: Many GUI & command line utilities are included which are useful for managing the database & system e.g. SQL Security Manager, SQLadmin, ISQL/w, isql, console, SQL monitor, SQL service manager, NETSQL, SQL object manager, SQL Tape Utility, NT Perfmeter, NT event log & alerts.
Known security problems:
- Easy to "sniff" client/server login process (when socket connections are used) ?
- User passwords are stored in clear text in the syslogins table, visible to the sa.
- The xp_cmdshell extended stored procedure allows a user to execute any file system command. Imagine nice things like formatting the disk! TBD: How can this functionality be restricted??
- No password aging mechanism.
- Documentation: SQL security issues are described in

SY52433-0893

4.2

System Administrator's Guide: SQL Serve

1993

Microsoft

Accountability

Identification / authorisation

Overview

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.

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.
Standard: SQL server manages it's own login validation (i.e. usernames and passwords) independently from the operating system. This is the default.
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.

Recommendations

Integrated or mixed modes are preferred, since they offer unified user administration and authentication and no passwords traverse the network during the SQL logon.
Default domain should be set in integrated and mixed modes (during installation).
Immediately after the SQL server is installed, the sa password must be changed (it is NULL by default), e.g. via the SQL command:

sp_password null,NEW_PASSWORD,sa

No default login should be used. If one is necessary it should have very restricted read only access to data.
No visitor or guest accounts should be used. By default the pubs and master database have guest accounts. They can be disabled by: sp_dropuser guest.
Login time-out: In SQLadmin -> configure, the time to elapse before login attempts are cancelled can be set. A setting of 00:01:00 (1 minute) is recommended in general.
For non integrated user logons (via SQLadmin -> logins, or sp_addlogin):
- Assign new users a default database other than master.
- Assign new users a password (never leave blank) and instruct the user to change this temporary password a.s.a.p.

Audit trail

When installing SQL server, the following options are recommended:

Error logging to: Windows NT event log = Yes (i.e. not to a text file)
Autostart Server at boot time = Yes
Autostart Monitor at boot time = Yes
SQL performance monitor integration = Yes, direct response mode.
Audit level = failed logins + successful logins (Setup -> security options).

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

Filesystem

Use only NTFS, never FAT.

Object Permissions

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

Permissions for the
SELECT
UPDATE
INSERT
DELETE
EXECUTE
statements are called object permissions (since they always apply to objects). Object permissions may be set using the SQL Object Manager -> Object Permissions, or with grant and revoke.
Statement permissions are database dependant and apply to the following statements:
CREATE DATABASE
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
DUMP DATABASE
DUMP TRANSACTION
The statement permissions may be set in the SQLadmin -> DB -> Manage -> Users -> permissions, or with grant and revoke.

Object Reuse

TBD.

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:

All users keep the same ID in the local & remote servers.
All remote users are mapped to one local ID.
Individual users may be mapped to different Ids on the remote server.

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).

Confidentiality

Communication via named pipes guarantees encryption of username/password, whereas sockets do not (100% sure of this?), so named pipes are preferable.

Availability

See also General database availability guidelines.

Backup / Recovery

See also General database availability guidelines.

Recovery

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

When databases are created/changed using the graphical utilities, it is recommended that a database script be generated which is capable of recreating the database. The SQL object manager -> scripts window allows generation of scripts for Tables, views, stored procs, triggers, rules, defaults, user datatypes and logins for selected objects. This offers a minimum in documentation and allows regeneration of the objects on another server.
dbcc (database consistency checker) is the principal tool used for checking table (checkdb) and database structures for inconsistencies.
dbcc newalloc checks data and index pages against corresponding extent structures. It replaces checkalloc (which also exists for backward compatibility) but doesn't stop if it encounters an error and produces a more detailed report. Recommendation: run as frequently as possible, but especially before database backups.
Locks prevent other users from interfering with data being used for an active transaction. SQL server has many types of locks: exclusive, shared or demand. The command sp_lock shows current locks. SQL server detects and resolves both deadlocks and livelocks. See [sql1], page 346.

Organisation

If the SQL server must be shutdown, all users should be warned to prevent unnecessary loss of user data. e.g. a message could be sent to all users connected via Lan Manager:

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

Redundancy

Monitoring

The SQL monitor should be configured to restart the SQL server if it crashes abnormally. An entry will be made in the NT event log.
Mirrors should be monitored, Sybase does not notify when it switches over from original to mirror. An SQL script using the WAITFOR MIRROREXIT can achieve this, or the event log can be monitored.
Monitor transaction log space: sp_apaceused, sp_helpdb or dbcc sqlperf(logspace).

Replication

Only available in V6. TBD.

Mirroring

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.

A minimum configuration would mirror the transaction log on a separate drive.
An advanced configuration would mirror all user databases, the master and the transaction log on several drives on several controllers.
If the master is mirrored, the mirror device should be given on the server startup line. e.g. In SQL Setup -> Options > Set server options -> continue -> parameter add -rd:\sql\mirror\mastmir.dat. The mirror device is used when the primary device fails.
Enable serial writes: write first to the original then to the mirror disk. (SQLadmin -> devices -> select device -> manage -> mirroring -> mirror). (Check in practice?)

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):

V6.0 Supports replication, both asynchronous log-based transaction propagation as well as "snapshots" of tables and objects. Replication uses a Publisher/Subscriber metaphor. Each publication can have a security status of unrestricted (default) or restricted. Restricted status should be used for class databases which are published.
Logging and alerts are fully integrated with those of NT. If no alerts are defined locally, the event can be forwarded to another server for processing. This allows groups of servers to be monitored centrally.
OLE is supported, allowing use of Visual basic for administration/security scripts.
Field level security is supported.
New encryption services provide secure data exchange between clients and servers.

TBD: specific recommendations for V6

Oracle 7.1 or later (Dec.'95)

General

Oracle is a complex DBMS with lots of in-built security (especially V7.1 or later). It's backup mechanisms are primitive, however.
Trusted Oracle 7.1 or later is recommended for class . It is not discussed here.
Use Oracle 7.1 or later (& SQL*net 2.1) for class :
- it has additional security features over 7.0.12: password encryption for remote connections and parallel recovery.
- If V7.1 security features are required, the following parameter must be set: COMPATIBLE = 7.1.0
Operating System authentication was first supported in V7.0.
To guarantee data & referential integrity, Oracle provide triggers and constraints.

Dangers

Prior to V7.1, SQL*net communication between Oracle servers or between server and client sent passwords and usernames in clear text over the network, meaning that an intruder could "sniff" the network and see these passwords.
Utilities such as orapwd and SQLplus accept passwords on the command line, which are visible in the process table of many systems (and hence to non authorised users). Administrators must be aware of this!

Documentation

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

Accountability

Identification / authorisation

Oracle allows user authentication to be carried out by either:

the OS (usernames must still exist in the database).
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:

INTERNAL: Only users connecting as INTERNAL can shutdown/start the database. INTERNAL normally has an addition password to that used by a user. It is not possible to know who is INTERNAL, if this privilege is shared by multiple administrators. In addition secure remote administration is not always possible.

Privileged users, V7.1 and later:

SYSOPER: permits STARTUP, SHUTDOWN, ALTER DATABASE, OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG AND RECOVER. It also includes the RESTRICTED SESSION privilege. This privilege is attributed to a user by: GRANT SYSOPER to user_name.
SYSDBA: contains all ADMIN OPTION privileges, SYSOPER, CREATE DATABASE and time-based recovery. This privilege is attributed to a user by: GRANT SYSDBA to user_name.

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 .

V7.0: OSOPER and OSDBA, associated with CONNECT INTERNAL, allow the same privileges as above. (TBD: sure?)

Audit Trail

Auditing can be used for statistics collection, monitoring of suspicious activity and providing a faithful log of what happened when.
Privileged connections to the database are always audited, whether auditing is enabled or not.
In V7.0, only users connected to Oracle as INTERNAL could start or stop the server. V7.1 may require users to connect with a unique name and password, allowing per user auditing (in the OS audit trail).
What can Oracle audit? For the following both successful/unsuccessful can be audited:
1. Statements (DML & DLL): Can be set for selected users or all users.
  e.g. AUDIT CREATE TABLE
2. Privileges: Useful for monitoring powerful system privileges.
  e.g. AUDIT SELECT ANY TABLE
3. Schema Objects: Applies to attempts to access a particular object (e.g. tables, views, functions, sequences and stand-alone stored procs) from all users via (DML).
  e.g. AUDIT SELECT ON my_table BY SESSION WHENEVER NOT SUCCESSFUL
One can audit once per user session (BY SESSION), or each time the statement is executed (BY ACCESS).
Where is the audit trail?: either in an OS audit trail (file), or in the AUD$ table in the SYS schema of each database. Several predefined views are available for examining AUD$. TBD. In distributed databases, auditing is local. There is no synchronisation between sites.

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:

Whether authentication information is maintained by Oracle or the OS.
The user's default and temporary tablespaces.
List of tablespaces accessible to the user and the associated quotas.
The user's limits on system resources.

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:

Reduced privilege administration: not user dependant. A role can be attributed to members of a group easily.
Dynamic privilege management: if privileges for a group of users must change, only the role need change.
Selective availability of privileges: privileges can be selectively made available to a user as needed.
Application awareness: applications can query the data dictionary to see what roles exist.
Application specific security: roles can be protected by a password and applications can enable these roles, since it- not users, know the role password.

Roles can be subdivided into application and user roles.

An Application role is a role that is granted the privileges necessary to run a particular application. Often, an application will have several roles each allowing different access to the application. Application roles may be granted to specific users or other roles.
A user role, on the other hand, is user for a group of users requiring the same privileges in the application.

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:

DML (Data Manipulation Language) Operations: such as DELETE, INSERT, SELECT and UPDATE should only be granted to users/roles that need to manipulate a table's data. The INSERT and UPDATE privileges can be granted for a specific column only. A selective INSERT , a row can be inserted, but only values of specific columns - all other columns receive NULL or the column's default value.
DDL (Data Definition Language) Operations: such as ALTER, INDEX and REFERENCES allow users to alter or create dependencies on a table, so they should be attributed conservatively. For certain operations (e.g. create a trigger on a table), a user requires an (ALTER TABLE) object privilege and a system privilege (CREATE TRIGGER). The REFERENCE privilege can be attributed per column. It is normally only needed on primary or unique keys.

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

SQL*net is the network interface that allows Oracle clients to access Oracle servers. SQL*net supports communication on all major communications protocols (SNA, TCP/IP, OSI....).
Oracle supports distributed databases (and hence 2 phase commit), however sites are administered (i.e. also security) separately. Each site is a distinct data repository. Schemas in remote databases are accessed by adding a domain address to objects (e.g. mytable@mydatabase.myserver.ourdomain).

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.

CREATE PUBLIC DATABASE LINK remote_db_name
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.

Central accounts -advantages: easier to administer
Central accounts -disadvantages: no accurate audit trail is possible on the remote database, a username & password is specified in the link, the central account might have more privileges than any one user requires and ANY user in the local database can access the remote database.
Individual accounts -advantages: Can be tailored to the users requirements, no username/password is specified in the link, audit trail is more accurate on the remote system, user has the same name on both systems.
Individual accounts -disadvantages: requires more administration time (for account creating/synchronisation)

Recommendation: Use individual links for class databases.

Peer entity authentication

The use of a secure password file permits remote database administration over non-secure network connections. TCP/IP and Decnet are not considered by Oracle to be secure connections.
It is possible to remotely login to an Oracle server without entering a password (allowed by default prior to V7.1) using the special OPS$user logon accounts. This feature should not be used for class systems. When used with class or , it should only be used between Oracle servers of the same security classification and managed by the same administrators.
In V7.1 and later, one can specify that users accessing the SYS, INTERNAL schema must connect using a unique name & password. Recommended for class .
1. If REMOTE_LOGIN_PASSWORD_FILE is NONE (the default), the user must be authenticated by the OS. If an Oracle password file exists, it is ignored.
2. If REMOTE_LOGIN_PASSWORD_FILE is EXCLUSIVE, Oracle use an externally stored password file (created using orapwd[2]) for identification/authentication of privileged users. This provides greater security since it allows all privileged connections to be audited by username. An EXCLUSIVE password file can only be used with one database. Recommended.
3. If REMOTE_LOGIN_PASSWORD_FILE is SHARED, privileged users must connect as user SYS with the appropriate password. Not as secure as EXCLUSIVE. Useful for an administrator who must remotely administer a number of databases.

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

With V7.1, specify encryption of passwords crossing the network, set ORA_ENCRYPT_LOGIN = TRUE on the client and DBLINK_ENCRYPT_LOGIN = TRUE on the server.
Use SQLnet 2.1 (the Oracle client) where possible. SQLnet 2.1 encrypts passwords before transmitting them to a V7.1 server.
The orapwd utility itself presents a security risk in that it receives a password on the command line, which is visible in the process table of many systems. Administrators must be aware of this!

Non repudiation of origin/receipt

Not supported by SQLnet or the protocols it uses.

Access control (TBD)

Availability

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:

CPU time may be limited per call (in 1/100 sec).
The logical data block reads (from both memory and disk) per call and per session.
The number of concurrent sessions per user.
The idle time (in minutes) per session (the current transaction is rolled back, session aborted and resources returned to the system).
The elapsed connect time per session (in minutes).
The amount of SGA space (used for private SQL areas, in the PGA) can be limited on multithreaded servers.

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

See also General database recommendations.
Parallel recovery in V7.1 can reduce downtime when backups must be reloaded.
Oracle is primitive in that online backups are not possible. Backups normally occur in two steps: 1). The database is shutdown. 2). An OS level backup of files used by Oracle is carried out.
New tools are coming (1Q96) which will allow online database backups. TBD.
A quasi on-line backup (Oracle call it a fuzzy backup!) is possible with the following procedure:
1. Oracle is online & being actively used.
2. Execute ALTER TABLESPACE with the BEGIN BACKUP option. Checkpoints are no longer noted in file headers. During recovery, this allows Oracle to know when the last checkpoint was carried out and to do a rollfoward based on the redo log.
3. Do a OS backup of the Oracle files (data and control).
4. Execute ALTER TABLESPACE with the END BACKUP option. File headers are updated to the current checkpoint.
5. Automatic recovery is possible, but special scripts may have to be written. TBD.
The Import/Export utilities may also be used to transfer data to/from Oracle. Can they do on-line backups??

Recommendations:

Operate the database in ARCHIVELOG mode, it provides:
- greater recovery after disk failures
- transaction integrity
- allows quasi "on-line backups" (see above).
If possible, shutdown Oracle before doing backups.

Redundancy

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

Replication

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:

Asynchronous: tables changes are updated to a read-only snapshot table at regular intervals.
Synchronous: triggers can be used to apply changes to replicated copies immediately.

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.