November 27, 2017 at 12:00 am
Comments posted to this topic are about the item Track Sensitive Data with SQL Server Extended Properties
November 27, 2017 at 1:22 am
Technically using extended properties suffers the same issue you level at documents - " the minute you hit save on the document, it is already outdated".
Obviously the benefit is that the information travels with the database but it is no less immune to being outdated. Nor does it improve the use of sensitive data. After all someone using the data still has to think to check the extended properties read what they say and then manually take action over what it says, this is no different to a document.
Given that you can query it you could of course simply attribute a "Sensitive" ex prop and then when you export it for some reason you could query to see if that prop exists and then obfuscate the data but still return it. All of which still requires the person writing the query to think to check if this exists.
November 27, 2017 at 3:08 am
Hmm, a little harsh.
" the minute you hit save on the document, it is already outdated" really? So if I had a customer table with columns first name, last name, marking them as PII would be outdated almost immediately? Once there is one row of data in that table, I'm struggling to imagine an example where the metadata will ever be out of date.
If you want to be GDPR compliant then knowing which data is in or out of scope is critical. Especially when considering pseudo sensitive data which isn't as obvious to identify.
@Ronny Smith, if you had a more comprehensive strategy then I think the article would have had a warmer reception. Methods for identifying GDPR data, labelling it, maintaining that metadata. Lastly giving an example of how a customers request to remove their personal data might play out with your extended properties approach. Thanks for the writing the article and getting the community thinking about GDPR concerns.
November 27, 2017 at 7:26 am
Seems like a value here is from the record keeping/auditing perspective.
To lessen the 'out of date' problem, why not add a date(s) as an ext prop(s)?
Something like this in keeping with the privacy thread here:
Name Value
Sensitive 'Y' or 'N'
Type 'N/A', 'PCI','PII'
VerifiedBy 'Name of data custodian making the Sensitivity designation'
DateVerified 'YYYYMMDD that designation was made/updated'
AuditedBy 'Name of supervisor auditing the Sensitivity designation'
DateAudited 'YYYYMMDD that designation was audited'
If you are going to start annotating tables as containing sensitive information, then I believe you would want to have these properties on ALL the tables to show that each table had been evaluated, when evaluated and by whom.
November 27, 2017 at 12:01 pm
Stephen Merkel - Monday, November 27, 2017 7:25 AMIf you are going to start annotating tables as containing sensitive information, then I believe you would want to have these properties on ALL the tables to show that each table had been evaluated, when evaluated and by whom.
Yep, always more than just a single property. In this case, need a datestamp and a person.
November 27, 2017 at 4:06 pm
Stephen Merkel - Monday, November 27, 2017 7:25 AMSeems like a value here is from the record keeping/auditing perspective.
To lessen the 'out of date' problem, why not add a date(s) as an ext prop(s)?Something like this in keeping with the privacy thread here:
Name Value
Sensitive 'Y' or 'N'
Type 'N/A', 'PCI','PII'
VerifiedBy 'Name of data custodian making the Sensitivity designation'
DateVerified 'YYYYMMDD that designation was made/updated'
AuditedBy 'Name of supervisor auditing the Sensitivity designation'
DateAudited 'YYYYMMDD that designation was audited'If you are going to start annotating tables as containing sensitive information, then I believe you would want to have these properties on ALL the tables to show that each table had been evaluated, when evaluated and by whom.
Seems to me a move to auditable tables would be another alternative, and with temporal tables starting in SQL Server 2016, keeping the history of such changes could be easier.
November 28, 2017 at 7:50 am
gary.strange-sqlconsumer - Monday, November 27, 2017 3:08 AMHmm, a little harsh." the minute you hit save on the document, it is already outdated" really? So if I had a customer table with columns first name, last name, marking them as PII would be outdated almost immediately? Once there is one row of data in that table, I'm struggling to imagine an example where the metadata will ever be out of date.
If you want to be GDPR compliant then knowing which data is in or out of scope is critical. Especially when considering pseudo sensitive data which isn't as obvious to identify.
@Ronny Smith, if you had a more comprehensive strategy then I think the article would have had a warmer reception. Methods for identifying GDPR data, labelling it, maintaining that metadata. Lastly giving an example of how a customers request to remove their personal data might play out with your extended properties approach. Thanks for the writing the article and getting the community thinking about GDPR concerns.
how about this: You (shortsightedly) mark your customer table as North American Customers. Then you hear that your company acquired (or was acquired by) a European company. Your customer table must be modified to accommodate the change and -- voila! -- the metadata is out of date. Maybe not the moment you hit save, but soon afterwards!
November 28, 2017 at 9:59 am
gbritton1 - Tuesday, November 28, 2017 7:50 AMgary.strange-sqlconsumer - Monday, November 27, 2017 3:08 AMHmm, a little harsh." the minute you hit save on the document, it is already outdated" really? So if I had a customer table with columns first name, last name, marking them as PII would be outdated almost immediately? Once there is one row of data in that table, I'm struggling to imagine an example where the metadata will ever be out of date.
If you want to be GDPR compliant then knowing which data is in or out of scope is critical. Especially when considering pseudo sensitive data which isn't as obvious to identify.
@Ronny Smith, if you had a more comprehensive strategy then I think the article would have had a warmer reception. Methods for identifying GDPR data, labelling it, maintaining that metadata. Lastly giving an example of how a customers request to remove their personal data might play out with your extended properties approach. Thanks for the writing the article and getting the community thinking about GDPR concerns.
how about this: You (shortsightedly) mark your customer table as North American Customers. Then you hear that your company acquired (or was acquired by) a European company. Your customer table must be modified to accommodate the change and -- voila! -- the metadata is out of date. Maybe not the moment you hit save, but soon afterwards!
What rationale would you have for marking your table as "North American Customers" ??? That's makes no sense in terms of logic, business data or technical design. If you're bought out by a European company your customers didn't just get up an swim across the Atlantic.
From a business point of view if you're considering geographic dissemination then having a single label for one region provides zero value.
And from a technical point of view a customer is an entity which can have 0 or many locations at different points in time. So you certainly wouldn't arrive at this design.
Meta data can certainly become stale. There is no denying that. However the context of the discussion is PII data, PCI data and GDPR compliance. And in the circumstances I outlined I still believe this is one of the few cases were the metadata can never become stale.
November 28, 2017 at 12:14 pm
gary.strange-sqlconsumer - Tuesday, November 28, 2017 9:59 AMgbritton1 - Tuesday, November 28, 2017 7:50 AMgary.strange-sqlconsumer - Monday, November 27, 2017 3:08 AMHmm, a little harsh." the minute you hit save on the document, it is already outdated" really? So if I had a customer table with columns first name, last name, marking them as PII would be outdated almost immediately? Once there is one row of data in that table, I'm struggling to imagine an example where the metadata will ever be out of date.
If you want to be GDPR compliant then knowing which data is in or out of scope is critical. Especially when considering pseudo sensitive data which isn't as obvious to identify.
@Ronny Smith, if you had a more comprehensive strategy then I think the article would have had a warmer reception. Methods for identifying GDPR data, labelling it, maintaining that metadata. Lastly giving an example of how a customers request to remove their personal data might play out with your extended properties approach. Thanks for the writing the article and getting the community thinking about GDPR concerns.
how about this: You (shortsightedly) mark your customer table as North American Customers. Then you hear that your company acquired (or was acquired by) a European company. Your customer table must be modified to accommodate the change and -- voila! -- the metadata is out of date. Maybe not the moment you hit save, but soon afterwards!
What rationale would you have for marking your table as "North American Customers" ??? That's makes no sense in terms of logic, business data or technical design. If you're bought out by a European company your customers didn't just get up an swim across the Atlantic.
From a business point of view if you're considering geographic dissemination then having a single label for one region provides zero value.
And from a technical point of view a customer is an entity which can have 0 or many locations at different points in time. So you certainly wouldn't arrive at this design.Meta data can certainly become stale. There is no denying that. However the context of the discussion is PII data, PCI data and GDPR compliance. And in the circumstances I outlined I still believe this is one of the few cases were the metadata can never become stale.
Well, you only gave those as examples, but not the only use cases. I'm only responding to the comment that "as soon as you write the doc, it's out of date." Granted, my example is absurd.
August 9, 2019 at 1:36 pm
Typing in all that data individually could be cumbersome. There are system stored procedures to help with maintenance, such as sp_AddExtendedProperty. Using these procedures, we can integrate the maintenance of the extended property values with the maintenance of the database.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply