ISACA Now Blog

Knowledge & Insights > ISACA Now > Posts > SQL Databases and Data Privacy

SQL Databases and Data Privacy

Robin Lyons, Technical Research Manager, ISACA
| Posted at 3:20 PM by ISACA News | Category: Privacy | Permalink | Email this Post | Comments (0)

Robin LyonsIf anyone had any doubts, data privacy is still kind of a big deal. Beyond being at the core of regulations ranging from the Health Insurance Portability and Accountability Act of 1996 (HIPAA) in the United States to the global, far-reaching General Data Protection Regulation (GDPR), data privacy has its own annual day of recognition – 28 January. As organizations design operational strategies and tactics around data privacy, opportunities to leverage applications with built-in functionality to safeguard sensitive and confidential data are valued. For those using Microsoft SQL Server 2016, there are a couple of areas where built-in functionality can assist with data privacy initiatives.

Where is the data?
Safeguarding of sensitive or confidential data generally begins with data classification. Once data has been identified and appropriately classified, the next effort is establishing internal controls commensurate with the sensitivity/confidentiality level of the data. Depending on the organization, designing and implementing internal controls may be a bit of a hurdle. In its 2017 State of Cybersecurity Metrics Annual Report, IT consulting firm Thycotic reported that 4 in 5 companies don’t know where their sensitive data is. Understandably, unknown data locations make it difficult to identify safeguards to protect the data. As in prior versions of SQL, using SQL Server Management Studio (SSMS) in SQL Server 2016 can provide a list of databases. Also, in addition to a variety of other data querying options, Transact-SQL (T-SQL) queries can be used to locate data and related tables.

Who has the data?
Having identified where the data resides, entities are faced with ensuring that access to the data is limited to those with the appropriate roles in their organizations. Once those access determinations are made (following the Principle of Least Privilege), organizations can then use Microsoft SQL Server 2016’s Dynamic Data Masking (DDM) feature to support its access strategy. With Dynamic Data Masking, sensitive/confidential data remains unchanged in the database while this data is hidden in designated database fields. Organizations can fully or partially mask the sensitive/confidential data depending on how they configure DDM.

Another option for limiting access to data is to use Always Encrypted. This feature allows encryption of sensitive data (at rest and in transit) within client applications. Since encryption and decryption happen outside of the SQL environment, it facilitates least privilege by limiting data access to those who own the data and need to view it.

As data privacy expectations become more permanent fixtures of entities’ operational landscapes, built-in features such as Dynamic Data Masking will become more commonplace. The newer DDM functionality, coupled with existing functionality through SQL Server Management Studio, can help entities achieve and maintain data privacy goals. Coupled with best practices in data management, this built-in functionality should provide an easier path to meeting the data privacy expectations of customers and compliance regulations.

Copy Item to All Language Codes
Lists/SqtResources/AllItems.aspx
0x0
0x0
ContentType
0x01009AF1BC4E56474a80B49512D1B30D6EEC
225
Manage Subscriptions
/_layouts/images/ReportServer/Manage_Subscription.gif
/Knowledge-Center/Blog/_layouts/ReportServer/ManageSubscriptions.aspx?list={ListId}&ID={ItemId}
0x80
0x0
FileType
rdl
350
Manage Data Sources
/Knowledge-Center/Blog/_layouts/ReportServer/DataSourceList.aspx?list={ListId}&ID={ItemId}
0x0
0x20
FileType
rdl
351
Manage Parameters
/Knowledge-Center/Blog/_layouts/ReportServer/ParameterList.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
rdl
352
Manage Processing Options
/Knowledge-Center/Blog/_layouts/ReportServer/ReportExecution.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
rdl
353
View Report History
/Knowledge-Center/Blog/_layouts/ReportServer/ReportHistory.aspx?list={ListId}&ID={ItemId}
0x0
0x40
FileType
rdl
354
View Dependent Items
/Knowledge-Center/Blog/_layouts/ReportServer/DependentItems.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
rsds
350
Edit Data Source Definition
/Knowledge-Center/Blog/_layouts/ReportServer/SharedDataSource.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
rsds
351
View Dependent Items
/Knowledge-Center/Blog/_layouts/ReportServer/DependentItems.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
smdl
350
Manage Tapthrough Reports
/Knowledge-Center/Blog/_layouts/ReportServer/ModelTapThrough.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
smdl
352
Manage Model Item Security
/Knowledge-Center/Blog/_layouts/ReportServer/ModelItemSecurity.aspx?list={ListId}&ID={ItemId}
0x0
0x2000000
FileType
smdl
353
Regenerate Model
/Knowledge-Center/Blog/_layouts/ReportServer/GenerateModel.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
smdl
354
Manage Data Sources
/Knowledge-Center/Blog/_layouts/ReportServer/DataSourceList.aspx?list={ListId}&ID={ItemId}
0x0
0x20
FileType
smdl
351
Load in Report Builder
/Knowledge-Center/Blog/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderModelContext&list={ListId}&ID={ItemId}
0x0
0x2
FileType
smdl
250
Edit in Report Builder
/_layouts/images/ReportServer/EditReport.gif
/Knowledge-Center/Blog/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderReportContext&list={ListId}&ID={ItemId}
0x0
0x4
FileType
rdl
250
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XsnLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
FileType
xsn
255
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
ProgId
InfoPath.Document
255
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
ProgId
InfoPath.Document.2
255
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
ProgId
InfoPath.Document.3
255
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
ProgId
InfoPath.Document.4
255
View in Web Browser
/_layouts/images/ichtmxls.gif
/Knowledge-Center/Blog/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&DefaultItemOpen=1
0x0
0x1
FileType
xlsx
255
View in Web Browser
/_layouts/images/ichtmxls.gif
/Knowledge-Center/Blog/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&DefaultItemOpen=1
0x0
0x1
FileType
xlsb
255
Snapshot in Excel
/_layouts/images/ewr134.gif
/Knowledge-Center/Blog/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&Snapshot=1
0x0
0x1
FileType
xlsx
256
Snapshot in Excel
/_layouts/images/ewr134.gif
/Knowledge-Center/Blog/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&Snapshot=1
0x0
0x1
FileType
xlsb
256

Comments

There are no comments yet for this post.
Copy Item to All Language Codes
Lists/SqtResources/AllItems.aspx
0x0
0x0
ContentType
0x01009AF1BC4E56474a80B49512D1B30D6EEC
225
Manage Subscriptions
/_layouts/images/ReportServer/Manage_Subscription.gif
/Knowledge-Center/Blog/_layouts/ReportServer/ManageSubscriptions.aspx?list={ListId}&ID={ItemId}
0x80
0x0
FileType
rdl
350
Manage Data Sources
/Knowledge-Center/Blog/_layouts/ReportServer/DataSourceList.aspx?list={ListId}&ID={ItemId}
0x0
0x20
FileType
rdl
351
Manage Parameters
/Knowledge-Center/Blog/_layouts/ReportServer/ParameterList.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
rdl
352
Manage Processing Options
/Knowledge-Center/Blog/_layouts/ReportServer/ReportExecution.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
rdl
353
View Report History
/Knowledge-Center/Blog/_layouts/ReportServer/ReportHistory.aspx?list={ListId}&ID={ItemId}
0x0
0x40
FileType
rdl
354
View Dependent Items
/Knowledge-Center/Blog/_layouts/ReportServer/DependentItems.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
rsds
350
Edit Data Source Definition
/Knowledge-Center/Blog/_layouts/ReportServer/SharedDataSource.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
rsds
351
View Dependent Items
/Knowledge-Center/Blog/_layouts/ReportServer/DependentItems.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
smdl
350
Manage Tapthrough Reports
/Knowledge-Center/Blog/_layouts/ReportServer/ModelTapThrough.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
smdl
352
Manage Model Item Security
/Knowledge-Center/Blog/_layouts/ReportServer/ModelItemSecurity.aspx?list={ListId}&ID={ItemId}
0x0
0x2000000
FileType
smdl
353
Regenerate Model
/Knowledge-Center/Blog/_layouts/ReportServer/GenerateModel.aspx?list={ListId}&ID={ItemId}
0x0
0x4
FileType
smdl
354
Manage Data Sources
/Knowledge-Center/Blog/_layouts/ReportServer/DataSourceList.aspx?list={ListId}&ID={ItemId}
0x0
0x20
FileType
smdl
351
Load in Report Builder
/Knowledge-Center/Blog/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderModelContext&list={ListId}&ID={ItemId}
0x0
0x2
FileType
smdl
250
Edit in Report Builder
/_layouts/images/ReportServer/EditReport.gif
/Knowledge-Center/Blog/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderReportContext&list={ListId}&ID={ItemId}
0x0
0x4
FileType
rdl
250
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XsnLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
FileType
xsn
255
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
ProgId
InfoPath.Document
255
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
ProgId
InfoPath.Document.2
255
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
ProgId
InfoPath.Document.3
255
Edit in Browser
/_layouts/images/icxddoc.gif
/Knowledge-Center/Blog/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser
0x0
0x1
ProgId
InfoPath.Document.4
255
View in Web Browser
/_layouts/images/ichtmxls.gif
/Knowledge-Center/Blog/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&DefaultItemOpen=1
0x0
0x1
FileType
xlsx
255
View in Web Browser
/_layouts/images/ichtmxls.gif
/Knowledge-Center/Blog/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&DefaultItemOpen=1
0x0
0x1
FileType
xlsb
255
Snapshot in Excel
/_layouts/images/ewr134.gif
/Knowledge-Center/Blog/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&Snapshot=1
0x0
0x1
FileType
xlsx
256
Snapshot in Excel
/_layouts/images/ewr134.gif
/Knowledge-Center/Blog/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&Snapshot=1
0x0
0x1
FileType
xlsb
256
You must be logged in and a member to post a comment to this blog.