Healthcare databases require data integrity and security. In fact, in healthcare data describe patients and needs to be protected more rigorously than in other diagnoses. Incorrect information presented to clinicians may lead to misdiagnosis, incorrect treatment, and eventually negative outcomes, including patient's death. Security of health data is also required by law, including HIPPA and other regulations.
This lecture describes two interrelated issues in databases: integrity of data and security. It also presents mechanisms built into many SQL-based database management systems that help maintain integrity and security of data.
Both integrity of data and security of data can (and should) be approached in different ways. For example, an EMR system built on top of a database should check for correctness of data, users' authorization, and other issues. This can be done independently from the mechanisms built into database systems.
When talking about database integrity, one of the most important concept is one of a constraint. A constraint ensures that a database has specific properties. For, example, a constraint may not allow to enter date of visit in the past. Another constraint may prevent creating an order for non-existing patient. In general, constraints check if data physically makes sense, and are not intended to act as clinical decision support systems that provide alerts, warnings, etc.
A database is said to be in a consistent state if all constraints are satisfied (no constraints are violated). Obviously, a consistent database is one in consistent state. The following examples illustrate typical problems that violate database consistency. In many cases these can be detected by common sense when looking at the data, but can also be automatically prevented.
Some of the problems with database integrity can be avoided using normalization of databases as described in an earlier lecture.
Consider database with two tables: patient and visit depicted below.
One can immediately detect several problems with the two tables:
As the above example shows, some problems can be easily detected and others require more checks.
Here, let's assume that the database is viewed in 2010. Consider the following patient table.
In the above table date of birth of patient 3 is missing. Also, the age of patient 2 is inconsistent with his/her date of birth.
In this last example, the database consists of two tables -- order and order item. It can be easily checked that for some items, total values of order are inconsistent with individual values of items.
Reasons for the Lost of Integrity
Database integrity can be lost for multiple reasons. Most often, the integrity of database is lost when data are entered or imported incorrectly. These types of problems are usually easy to prevent by adding additional checks to the database system. Other reasons for the loss if data integrity are:
Recovery from Failure
Recovery form failure may be long, labor intensive,a nd complicated process. Particularly when data are lost due to a catastrophic failure. When data are physically lost, they need to be recovered from backup. Very often during the recovery process, when the loss is extensive the database system is not operational.
For non-catastrophic failure, it may be possible to:
These sometimes require to:
Most database systems, including PostgreSQL, include mechanisms for preventing inconsistency. Two main mechanisms are:
These mechanisms will be discussed in the following sections.
Structured query language provides mechanisms for enforcinf integrity of data.
Constraints provide the simplest method for checking integrity of data. Here are the most common types of constraints:
Constraints are created in SQL when creating pr modifying tables using CREATE and ALTER commands. For example, the following command creates table patient with several constraints.
SOme additional infomration on how to use constraints in SQL are available in the W3Schools.com. Unfortunately, in many complicated conditions, it is not possible to create constraints.
Database management systems often provide graphical interfaces to allow for specifying constraints. For example, MS Access includes "Validation Rule," "Default Value," "Allow Zero Length" and other options in definition of columns. Check Access help for more details.
Transactions provide mechanism to execute a set of operation in a block and store results only if all of these operations were successful. This idea is simewhat similar to "undo" operation available in many programs. Each transaction needs to be started with BEGIN command, then a set of operations is performed, and the transaction is ended with COMMIT, ROLLBACK or END commands. The END command automatically
The following example illustrates the use of transactions in the manual process of updating tables in PostgreSQL. The left side shows SQL commands and the right side shows comments.
Here is another example in which the transaction is aborted manually, bacause of incorrect condition in query.
Similarly, the transaction with correct answer can be commited by the user. Here is the example script.
Transactions are particularly useful when running long scripts with multiple operations, all of which need to be completed to guarantee consistency of the database. Transactions can also be used to prevent some concurrency related problems by checking if data were updated correctly.
When considering security of databases (and in general all information systems), one needs to keep in mind two important things:
The privacy and security issues are very extensive and go well beyond the scope of this course. In fact, specialized courses are offered that deal with these issues. This lecture is limited to basic commands that show how basic security can be managed in SQL-based database systems.
It is important to distinguish security policy from security mechanism. Security policy simply defines who has access to what data, based on regulations, performed functions, etc. Security policies are not always enforced. Often, systems do not prevent users from accessing data, but rather detect "suspicious" uses and report them to appropriate authorities. For example, most EMR systems allow all clinicians to access all patients' data. However, most systems have implemented mechanisms that check for suspicious uses, like access to data of family members, access to data of patients of other physicians, etc.
On the other hand, security mechanisms are used to enforce security policies. Security mechanisms can be:
The following section shows basic commands for the discretionary access control in PostgreSQL.
Security mechanisms in SQL-based are based on users and their privileges. Each object in the database (i.e. table, view) has its owner that can grant or revoke permissions to other users. Database administrators are also allowed to create users and groups of users in the database using ADD USER and ADD GROUP comamnds.
Grant and Revoke Permmissions
The main commad in SQL that lets other users access resources is GRANT. The syntax of teh colums is as follows:
GRANT <privileges> ON <object> TO <users> [WITH <options>]
For example, the command
GRANT select ON patient to jwojt;
allows user jwojt to execute select command on the table patient. Similarly, teh command
GRANT all ON patient to smith;
allows user smith to execute all SQLcommands on the table patient.
To remove permissions, the REVOKE command is used:
REVOKE <permission> ON <object> FROM <users>
Views in Databses
Permissions to access data in SQL are given based on entire tables. It is not possible to grant access to specific fields or records in a table. In order to further restrict access to data, views can ve used. A view is a "virtual table" resulting from executing a query. Access can then be granted to a view that shows only data to which an user should have access. In order to create views, the CREATE VIEW command is used:
CREATE VIEW <name> AS <query>
CREATE VIEW patients AS SELECT * PERSON WHERE p_type = ‘Patient’;
creates a view "patients" which includes only patient records from the person table (and does not include employees etc.). Then, access can be granted to the view:
GRANT select on patients to smith;
Advanced learners like you, often need different ways of understanding a topic. Reading is just one way of understanding. Another way is through writing. When you write you not only recall what you have written but also may need to make inferences about what you have read. Please complete the following assessment:
Submit answers by email to your instructor.
Following resources are available to assist you: