Safeguarding Corporate Data
TECHNICAL ARTICLES -> DB SECURITY ARTICLES
[ Printer Friendly Version ]

Is your corporate data really safe? Before you answer this question, you must first ask "Safe from what?"

Database security is often confused with data secrecy. To fully evaluate whether your database is secure, the definition of database security must include secrecy, integrity and availability.

There are three essential aspects to data security; secrecy, integrity and availability. Therefore, to answer the simple question, "Is you data safe?" you need to look at each aspect separately. Different sites have different requirements for secrecy, integrity and availability. Therefore, it is impossible to solve all "security" issues with a single solution. The security solution must match the unique privacy, integrity and availability requirements of each site.

SECURITY FROM THE DATABASE PERSPECTIVE

Computer security is a highly complex topic because there are so many different aspects to it. The purpose of this article is to help you understand basic database security issues. This article is not intended to be an exhaustive reference of securing computer systems; such an article requires significantly more space than is available here. Therefore, I will assume a generally secure computing environment, and will focus on security from the database perspective.

Oracle Rdb and Oracle CODASYL DBMS have many features that ensure a high level of database security, in each of the aspects mentioned above.

SECRECY

Database secrecy refers to the protection of data against accidental or intentional disclosure to unauthorized persons, unauthorized modifications or destruction. Rdb and DBMS provide many tools to prevent unauthorized persons from accessing your data.

VMS PROTECTION

The security features of VMS influence the security mechanisms of Rdb and DBMS. The file protection of database files for Rdb (.RDB, .RDA, .SNP) and DBMS (.ROO, .DBS, .SNP) should be set to prevent access by VMS utilities, such as delete, rename, copy, backup, etc. Proper VMS protection of your database files guards against corruption or illicit access to data through non-Rdb and DBMS utilities. AIJ files should be placed in a directory accessible only to the DBA.

The database monitor log files, RDMMON.LOG and DBMMON.LOG, provides information about all database attaches. An unscrupulous user could use the information contained in this file to determine the location of other databases on your system. Armed with this information, they may try to exploit any holes in your system security to gain unauthorized access to your database files.

Environment with very high security requirements should restrict access to the RMU and DBO images. RMU[DBO]/SHOW USERS shows information about who is running what images, and to which database they are attached.

Rdb DATABASE PROTECTION

Rdb protection allows security to be set at the database level, table level or individual columns. The database administrator has a great amount of control and flexibility in designing the security that is appropriate for your environment.

DBMS DATABASE PROTECTION

Security Schemas provide a way to control DML access to sensitive data. A user can access a data entity only if it is included in their subschema view and only to the extent allowed by the security schema mapping.

In versions of DBMS before v4.3, every database was created with an empty user execution list (UEL) in the root file. As long as the UEL is empty, the database is unsecured against DML access. Starting in v4.3, all newly created databases will have only the creator's UIC placed in the UEL, thus allowing only the creator DML access to the database. Likewise, all securable DBO commands will only be granted to the creator of the database.

COMMAND AUTHORIZATION LISTS (CALs)

While security schemas provide access control over DML access to the database, CALs provide access control over RMU and DBO access to the database.

If used carelessly or maliciously, some of the RMU [DBO] commands can destroy data or corrupt the integrity of the database, while others may have a negative impact on database performance or could be used to illicitly gain access to the information contained in the database. CALs protect the database against unauthorized RMU [DBO] access. Secure those RMU [DBO] commands that access only the metadata in the CDD through the CDD security mechanisms.

If a CAL contains one or more UIC entries, then only those users with matching entries in the CAL will have access to the RMU [DBO] command. If the CAL is empty, then the RMU [DBO] command for that CAL is unsecured. DBMS Version 4.3 changes the way CALs are placed on newly created databases. By default, only the creator will have access to all DBO commands.

AUDITABILITY

The RDMMON and DBMMON log files provide limited audit history of database attaches. Version 4.2 of Rdb and 4.3 of DBMS greatly extends the audit capabilities through the RMU [DBO]/AUDIT command. Event audits for ACL checks, ACL changes, RMU [DBO] usage and DML access, can be enabled on a per-database basis. Alarms and/or journal records can be produced for each type of event.

INTEGRITY

Database integrity refers to the logical and physical soundness of the data stored in a database. If the data is physically corrupt, Rdb and DBMS will not be able to access the data. RMU/VERIFY and DBO/VERIFY utility checks the database for physical corruption and reports any inconsistencies. Verification of the logical integrity of data is a more difficult problem. In Rdb, referential integrity constraints and triggers can provide some level of logical integrity. While in DBMS, this can be implemented through schema check clauses. However, neither of these solutions can realistically provide a foolproof level of logical validation.

Rdb and DBMS maintains database integrity by automatic detection, rollback and recovery of incomplete transaction through user based recovery-unit journaling, enabled after-image journaling, automatic use of the VMS distributed lock manager and automatic database recovery following a complete system failure.

DISTRIBUTION LOCK MANAGER

Rdb and DBMS use the distributed lock manager extensively to maintain database integrity. The lock manager controls virtually all aspects of database integrity from managing process failure, cluster fail over, concurrent access, snapshot sequencing, buffer management and much more.

RECOVERY UNIT JOURNALS

By default, all database transactions record a "before image" of all database updates to the Recovery Unit Journal (RUJ) file. The RUJ recovery mechanisms provide the most crucial functions for maintaining database integrity. If the transaction is aborted (due to a system failure, software failure, or user-initiated termination [Ctrl-Y]), Rdb and DBMS automatically rolls back (undoes) the partially completed transaction. This helps to ensure both the logical integrity and physical integrity of your database.

I noted above, that Rdb and DBMS log all updates to the database to the RUJ file by default. I implied in this statement, that it is possible to "disable" RUJs. It is possible to "disable" writing to the RUJ using the “SET TRANSACTION BATCH UPDATE” for Rdb or by readying the database for BATCH UPDATE for DBMS. Updating your database in BATCH UPDATE mode (disabling RUJs) is like engaging in unsafe...computing! If there is any failure during a BATCH UPDATE transaction, your database will be marked CORRUPT!

To guard against unintentional use of BATCH UPDATE, I strongly recommend that all sites deny BATCH UPDATE to all users through database protection. For those rare times when you require batch update mode, grant an alternative security schema to the user's UIC for the duration of the BATCH UPDATE transaction.

CONSISTENCY CHECKS

RMU and DBO/VERIFY are your best means of early detection of physical corruption in your database. Verify performs physical integrity checks of the internal page format, record structures, index structures and most set pointers in your database.

Frequent use of VERIFY will help to ensure that you can recover your database from your most recent backup(s) and After Image Journals.

If you can't recover your database from a backup, VERIFY provides a skilled technician with valuable information about the location and type of corruption in your database. They can use this information to perform low-level patches to the database using the RMU/ALTER or DBO/ALTER utility.

DBO/BACKUP & DBO /RESTORE

If updating your database without using RUJs is like practicing unsafe computing, then failure to adequately BACKUP your database is like playing Russian-Roulette. The database backup and restore utilities, RMU/BACKUP and DBO/BACKUP/MULTITHREAD, provide the single most important mechanism for recovering your database in the event of database corruption.

While I include media failure (where the disk that contains your database has been completely obliterated by a head crash) as an extreme form of database corruption, the corruption could be more subtle such as an "isolated" pointer problem. In either case, being able to successfully use RMU/RESTORE or DBO/RESTORE to restore your database to the last known corruption-free point is critical to being able to successfully restore your database with confidence.

AVAILABILITY

Database availability refers to the accessibility of the data within the database. No matter how accurate, free of corruption, and logically organized your data is, it is useless if it is not available when needed.  Rdb and DBMS provide many mechanisms to ensure high availability to the data in your database.

CLUSTER SUPPORT

Rdb and DBMS allow concurrent, multi-processor access to your database when running in a cluster environment. If one of the nodes on your cluster fails, the database monitor will automatically recover your database on one of the surviving nodes.

In a properly configured cluster environment, it is possible to achieve virtually uninterrupted access to your database. Proper file placement is one key to high availability in a cluster environment.

In a cluster environment, you want to make certain that all database files (RDB, .RDA, .ROO, .DBS, .SNP, .RUJ, .AIJ) are available to all nodes sharing the database. The RUJ files are the most critical, since they must be accessible in order for automatic recoveries to occur if a node fails.

By default, your RUJ files are placed in your sys$login device (except prior to v4.3, where the default is [dbm$ruj] on the sys$login directory). To override the default location of the RUJ files, define the system logical name, DBM$RUJ, to point to the directory to contain the RUJs. The directory that contains the RUJ files should NOT have version limits enabled.

Place your AIJ files on a separate device from all other database files.

Multi-pathed controller access to your disks provide you with the highest level of availability. If one controller fails, the other can take over. Dual-pathed MSCP-served disks also provide high availability, but do not provide for high performance, except for the node to which the disks are connected.

DATABASE BACKUPS

There are four primary ways in which RMU and DBO BACKUP improves database availability: Use of the /ONLINE features for ONLINE backups; Multi-threaded features for maximizing throughput; /INCREMENTAL backups to minimize the amount of data to be backed up and by-area features to further minimize the amount of data being backed up.

Performing ONLINE backups can significantly improve availability, since the ONLINE backups allow full concurrent access to the database during the backup. Thus, it is possible to obtain a consistent backup of your database, even while other processes update the database. You must have snapshots allowed and enabled to perform online backups.

DBMS’ multi-threaded backups (standard in Rdb) optimize database backups by concurrently backing up multiple areas to multiple tape devices. Thus, in an ideal environment, the time required to backup your database could be reduced to the time required to backup up your largest area file.

INCREMENTAL backups save time by backing up only those database pages that have changed since the last full backup.

DATABASE /RESTORE

If a database or media failure occurs, you will need to quickly restore your database. The multi-threaded features of the Rdb and DBMS RESTORE utility allow you to maximize the throughput of your database restores by concurrently restoring multiple database files from multiple tape devices. In addition, it is also possible to use by-area restores to restore only those areas that were on the failed device(s).

AFTER IMAGE JOURNALS

After image journaling (AIJ) is your database's primary protection against media failures (disk crashes). If you have AIJs enabled and you experience a media failure, you can fully recover your database back to the last completed (committed) transaction – Without loosing a single update! To achieve this level of protection, place your AIJ file on a separate device from any of your database files.

In general, you can expect about a 4% performance penalty for using AIJs. However, by enabling AIJs with "FAST COMMITS," you may actually improve database performance with AIJs enabled.

STRIPED DISKS/BOUND SHADOW SETS

Disk striping spreads the contents of a “logical” disk across multiple physical disks. Chunks of the each disk are interleafed together to provide a larger, faster disk to the system. Bound shadow sets can also improve performance by allowing multiple disks to be bound together into a single larger disk. The performance benefit of striped or bound volume sets comes from placing the data across more disks – essentially spreading the I/O across more devices.

SUMMARY

Rdb and DBMS provide secure environments with all the tools necessary to achieve level C2 security. With both databases, security is UIC based. Therefore, a user with BYPASS privilege is not subject to UIC checking and can therefore access any file, including those of the database. If you need a very secure environment, you will want to strictly control privileges such as BYPASS, SETPRV, SECURITY and READALL.

Rdb and DBMS provide many facilities to tailor your database security requirements to meet the needs of your organization. The most stringent database security levels may be too costly for some organizations. Likewise, the cost of recovering from system or media failures, or a breach of privacy may be too great to justify applying only minimal security features.

Determining the proper level of security features to implement in your environment requires a cost/benefit analysis to determine the cost of implementing security options versus the cost (risk) of breaching database secrecy, integrity and availability.