Personally, I'd write or have someone else write, a .Net application (VB or C#) that can trawl through the tables you have. Within it you would be able to include enough logic to provide a suitable screening of each field.A) You can have it read the sys tables so the program will pick up the names of the tables and columns (attributes), across multiple databases if necessary, and then process them sequentially.B) Include a list held in another table containing information on the sensitive material (no, not actual SocSec numbers but the types of pattern needed to be looked for, and perhaps specific catch words).This would obviously be extendable as more records can be added as "new" things come to mind.C) The App/prog' can sit there and chew on the data for as long as it takesD) publish reports or dump appropriate information in tables for later reporting or export, the output from which can be analysed independantly of theprog's run; i.e. even tho the prog is still running, reports can be run against it listing the ever increasing information.E) The prog would include in its reportable data the database name, the table name, the column and the specific information it deems suspicious or sensitive.F) You get to call it something catchy like... Carnivore....Trying to do this with anything other than a specific application is going to be a nightmare!You have my sympathies!Jeremy GreavesFenris Software Consulting, Inc> To: [Email address protected] From: mssqldba-ezmlmshield-x23733968.[Email address protected] Date: Wed, 28 Nov 2007 13:31:28 -0500> Subject: Re: needle in a hay stack> > I used the method of looking for column names like'%ssn%' and addr and name. It is a manual, slow process. You can also look for data that matches, len(snum) = 9. Again manual, but you can use system tables to pull the table name and column name to automate it some. But there is not a sure fire way to find this type of data without eyes on, very time consuming search (or the wonderfully documentation we all have of all our tables and processes....HA HA :->). > > > > > > Stephen Dyckes> > > > > > > > -----Original Message-----> > From: Gary Nease <mssqldba-ezmlmshield-x20210501.[Email address protected]> > To: LazyDBA Discussion <[Email address protected]> > Sent: Wed Nov 28 12:48:23 2007> > Subject: RE: needle in a hay stack> > > > At Microsoft this is called the "PII Scrub", with PII standing for> > Personally Identifiable Information. It was supposedly a required> > deliverable in the process of designing and deploying any new data store> > (table), and was vaguely considered a requirement for compliance with> > "SOX" (Sarbanes-Oxley Act> > http://en.wikipedia.org/wiki/Sarbanes-Oxley_Act). All copies of> > production data being used in Dev, Test, and QA were supposed to go> > through the scrub. The required scrub process was usually neglected> > from planning for all new products, and only nominally addressed at the> > last minute after testers noticed PII in their data, too late for proper> > attention to design and far to late to develop effective test criteria.> > > > > > Professional database publications have written extensivley on the> > due-diligence implications of that legislation and the need to weigh> > cost/benefit for efforts to comply. You may need to do some negotiation> > after reading up on the subject. I have heard of no silver bullet.> > > > Don't forget to look for xml tags containing strings such as "%addr%",> > "%mail%", "%phone%", etc. I suggest you start managing expectations and> > seek some attainable and measurable success criteria. This might> > include a semi-finite list of strings found in column-names and xml> > tags, and/or manual examination of samples of an agreed size and> > randomization. > > > > -----Original Message-----> > From: Ligda John> > [mailto:mssqldba-ezmlmshield-x40535838.[Email address protected] > > Sent: Wednesday, November 28, 2007 9:31 AM> > To: LazyDBA Discussion> > Subject: needle in a hay stack> > > > I have been asked to identify all tables and columns with sensitive data> > such as social security numbers and the like. Our business analyst> > quickly provided a report but I fear it represents the expected use of> > the systems not actual use. For example, I already discovered some very> > sensitive data in a description field which the analyst didn't expect,> > although he wasn't surprised.> > > > > > > > I have about nine hundred tables from about nine gigabytes of data> > files.> > > > > > > > I thought about a bulk copy batch to character format where I could run> > some Perl regular expression searches against everything, but it would> > be a nightmare to map the findings back to the table's columns. I> > can't imagine I'm the first one to need to do this yet my internet> > search reveals only tools for documents and not SQL Server tables.> > Does anyone have experience or ideas?> > > > > > > > Thanks! > > > > John> > > > > > > > > > --> > DISCLAIMER> > > > Confidentiality Note: This e-mail is intended only for the person or> > entity to which it is addressed and may contain information that is> > privileged, confidential or otherwise protected from disclosure.> > Dissemination, distribution or copying of this e-mail or the information> > herein by anyone other than the intended recipient, or an employee or> > agent responsible for delivering the message to the intended recipient,> > is prohibited. If you have received this e-mail in error, please notify> > us immediately (telephone> > 415-288-0544 or e-mail [Email address protected] and destroy the> > original message and all copies.> > > > Hall Capital Partners LLC reserves the right to monitor and review the> > content of all e-mail communications sent and/or received by its> > employees.> > > > > > > > ---------------------------------------------------------------------> > TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a> > dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com> > To unsubscribe: http://www.lazydba.com/unsubscribe.html> > > > > > > > ---------------------------------------------------------------------> > TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY> > To post a dba job: http://jobs.lazydba.com> > To subscribe : http://www.LazyDBA.com> > To unsubscribe: http://www.lazydba.com/unsubscribe.html> > > > > > -----------------------------------------> > The information contained in this e-mail message is intended only> for the personal and confidential use of the recipient(s) named> above. This message may be an attorney-client communication and/or> work product and as such is privileged and confidential. If the> reader of this message is not the intended recipient or an agent> responsible for delivering it to the intended recipient, you are> hereby notified that you have received this document in error and> that any review, dissemination, distribution, or copying of this> message is strictly prohibited. If you have received this> communication in error, please notify us immediately by e-mail, and> delete the original message.> > > ---------------------------------------------------------------------> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY> To post a dba job: http://jobs.lazydba.com> To subscribe : http://www.LazyDBA.com> To unsubscribe: http://www.lazydba.com/unsubscribe.html>
MS Sql Server LazyDBA home page