Happy Holidays, Tessitura community!
I wonder if there are any of you who have written anything that strips customer data (perhaps except for a specified list of customer data) when restoring to a non-production or development environment in order to save space and eliminate the risk of secure data being on a laptop or other environment that might leave your premises. We’re still on version 11 & are looking at being able to quickly automate a new development environment and it would be faster and easier to install a smaller segment of customer data with all of the Ticketing setup and campaign setup left intact in order to develop against up-to-date setup.
Thank you in advance to anyone who might have built something that would help me accelerate our progress toward quickly replicating our live environment with most customer information stripped out.
Sincerely,
Monica
Monica Martin
Database Administrator
Oregon Shakespeare Festival
541-482-2111 ext. 221
www.osfashland.org
I think a lot of folk would be interested in this as well, including us. This issue does not occur frequently, but it does happen from time to time, and a routine that provides the ability to strip selected data would be a good add to our toolbox. Is this something you can forward to me as well, or perhaps post on Tessitura’s website?
Randall A. Mitchell Director of Information Systems | The Phoenix Symphony | Tel. (602) 452-0440 | Mobile. (602) 796-9323 | Fax. (602) 253-1772 RMitchell@PHOENIXSYMPHONY.ORG | www.phoenixsymphony.org
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tanya Hoffmann Sent: Tuesday, January 06, 2015 8:54 AM To: Randall A. Mitchell Subject: RE: [Tessitura Technical Forum] Stripping non-essential customer data from non-production database
I would be interested in that as well.
Thanks,
Tanya
The Kennedy Center
Tanya A. Hoffmann | Information Technology
Tel (202) 416-8684 | Fax (202) 416-4800| 2700 F Street N.W. | Washington, D.C. 20566
Email: tahoffmann@kennedy-center.org
http://Kennedy-Center.org
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Monica Martin Sent: Tuesday, January 06, 2015 10:43 AM To: Hoffmann, Tanya Subject: Re: [Tessitura Technical Forum] Stripping non-essential customer data from non-production database
Yes, I'd love that, Gawain!
On Dec 31, 2014, at 11:16 AM, Gawain Lavers <bounce-gawainlavers9512@tessituranetwork.com> wrote:
Hi Monica, I'm actually currently in the process of reviewing an old routine that we used for this in v10, which I need to upgrade for our current system (v12.1). If such a thing would be helpful at all (currently just have the v10 version) I can pass it along. --Gawain From: Monica Martin <bounce-monicamartin8067@tessituranetwork.com> Sent: 12/30/2014 11:43:37 PM Happy Holidays, Tessitura community! I wonder if there are any of you who have written anything that strips customer data (perhaps except for a specified list of customer data) when restoring to a non-production or development environment in order to save space and eliminate the risk of secure data being on a laptop or other environment that might leave your premises. We’re still on version 11 & are looking at being able to quickly automate a new development environment and it would be faster and easier to install a smaller segment of customer data with all of the Ticketing setup and campaign setup left intact in order to develop against up-to-date setup. Thank you in advance to anyone who might have built something that would help me accelerate our progress toward quickly replicating our live environment with most customer information stripped out. Sincerely, Monica Monica Martin Database Administrator Oregon Shakespeare Festival 541-482-2111 ext. 221 www.osfashland.org This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Hi Monica,
I'm actually currently in the process of reviewing an old routine that we used for this in v10, which I need to upgrade for our current system (v12.1). If such a thing would be helpful at all (currently just have the v10 version) I can pass it along.
--Gawain
From: Monica Martin <bounce-monicamartin8067@tessituranetwork.com> Sent: 12/30/2014 11:43:37 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
This e-mail message is intended only for the recipient(s) named above. This message may contain trade secrets, attorney-client communication, or other privileged and confidential information. Any review, re-transmission, dissemination, reproduction or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the Sender and delete the material from any computer.
Okay, I've got a couple of files, but I don't know how best to share them. First, though, a brief discussion of our utility:
This is not a script for PCI: PCI data (card numbers) are expunged using the Tessitura utility from our Test database already. Instead, the script is a good faith attempt at mangling "Personally identifiable information". Our goal is to have a fully functional Tessitura database that perfectly captures the structure and layout of our current database (so, for instance, anonymous metrics will be comparable), without any identifiable information about our customers. To this end we "mangle" the relevant text data. Although we do not use customer number for the most part in most interactions with our patrons, our "mangle" text is a not based directly on the customer number, but is instead a number incremented while updating the names in T_CUSTOMER (i.e. the first row returned to the UPDATE statement will be assigned 1, the second 2, and so on).
While the obvious (phone, name, email) are all mangled, we only mangle street name in the address table, giving us broad location metrics. I feel comfortable with this as we do not, as a rule store birthdate information.
http://arstechnica.com/tech-policy/2009/09/your-secrets-live-online-in-databases-of-ruin/
...in 2000, she showed that 87 percent of all Americans could be uniquely identified using only three bits of information: ZIP code, birthdate, and sex.
If you do, I think you would be obligated to either change that, or mangle your city/zip information.
There are also likely a lot of tables floating around in your environment that you have long forgotten about and no longer use, but may have lots of information in them. Conversion tables (C*_*) are one set, import tables (TW_*) another. These I just truncate where I identify them. Some can be easily TRUNCATEed, others have keys or triggers, and instead must be DELETEd.
Other easy to overlook areas are free text fields ("notes"), custom columns, and attached files. Part of my script involves knowing which custom fields we might use. It also has a lot of our own custom tables in it. Another thing that I am grappling with for the update is all of the new tables added by various Tessitura services (TN Social Ticketing, for instance).
Looking through my code I notice that doing all this will spam your audit tables dramatically. I included a few lines to deal with that.
Not included here, but a good idea (I should just wrap it into the script) would be clearing out all of your web session tables.
So, right now I have the old script, and I have a recent query that I've dug through for any new tables/columns that looked suspicious.
My TODO items are:
I welcome any information on tables/areas that I might have missed!
Oh, one more thing I want to add (and maybe add to my Live->Test script as well) is a way to not mangle certain customers, so that we don't have to fix up our personal accounts for testing. I'm thinking it would be based on a list, but the list would basically be anyone with a specific worker type established.
Okay, just figured out attachments for the forums! I think.