Maintaining HIPAA Compliance Using Views in MySQL

The Problem

We have a large web application that stores and displays sensitive HIPAA-related data. We're currently researching ways to improve HIPAA compliance and reduce the risk of having a violation.

Currently, there are several features and reports that do not correctly restrict client information based on the permissions of the person who's logged in (e.g. the client search capability and certain legacy reports).

Possible Solutions

Take care of the problem from a programatic perspective

We could always just rewrite the sections of the code that are causing the non-compliance. The trouble is, this approach is highly error prone given the scale of the application - stuff could get missed.

Altering the Database to Restrict the data that gets returned

We could alter the MySQL database structure to reflect the necessary permission restrictions needed in the application. That way, no one can see data they shouldn't because the database won't return data they shouldn't see.

My Question

The application itself has nearly 300 tables, most of which store some sort of sensitiv开发者_Python百科e data. Is it possible (and feasible) to use MySQL views to restrict data access?

If so, what's the best approach?

You can utilize a view to restrict or present any data you wish simply by tweaking the query utilized in the view.

You will need to rename the original table, to something like "original table", then name your view the name of the original table.

Your program will not know or care that it is now accessing a view rather than a table. It will continue to pull data as before. You can also include blank or default values for fields you do not wish to return actual values for. As an example (if you have a field "DOB" and you no longer wish to return the birth date you can simply write '01-01-2001' as DOB).

You can use the examples from MYSQL's website on how to create a view.

CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;

http://dev.mysql.com/doc/refman/5.0/en/create-view.html