Tracking data access

Backstory

I work for a company that has an online site that allows user to text personal information for collection. We collect the data, and make it available online. Users can choose to share the data with other users.

Going Forward

At some point, this may become classified an FDA-governed medical tool. In antici开发者_JAVA技巧pation, we'd like to have in place a logging system that shows each time someone accesses our users' data, whether it be the user themselves, another authorized user, or a support person.

Current Architecture

We are currently running Ruby/Rails, and using a MySQL database. The personal information is encrypted in the database.

Data Access for Support

Today, support personnel can access data one of three ways:

1. admin site The admin site is limited to whatever screens we develop. While we don't currently, we could easily add logging to keep an audit trail of who accessed which data using the admin tool.
2. sql client I use MySQLWorkbench to access production. However, when connected this way, all personal information (user name, cell number, etc), is encrypted.
3. Ruby Rails console - Finally, support can log into one of the production boxes and use the Ruby/Rails console from command line. Ruby will decrypt the data, so we can do some simple things such as u=User.find_all_by_state('active') and it will return the recordset of all users with state='active', and decrypt their personal information in the resultset.

Holy Grail

1. logging
2. easy access for support

I'd love to be have a way to allow easy support access (once authenticated) to the data, but would log everything that is accessed (read or updated). That way, if I'm checking out my buddy's ex-wife's data for example, it gets logged to a place where I can't get in and clean it the audit trail. (See Google firing Gmail employee for an example of employees breaching the data policies).

Anyone have ideas, thoughts, experiences, suggestions with this issue?

hey devguy. This was a issue for me a couple months back. We ended up centralizing our mysql queires so that we could start to track all information coming in and out. Unfortunately the class I wrote is in PHP but the idea behind it could make it very easy to start logging.

https://code.google.com/p/php-centralized-mysql-controller/

Try stored procedures. Make all code use the stored procedures for CRUD activities. This defines an API that your developers can use while business rules are global enforced (don't return entire SSN values, but only last 4 digits, etc).

This serves as the basis for an external API as well.

If you want logging/auditing, you put it in the procedure.

This protects you from everyone except the DBAs.