PROBLEM
In my previous post I described how Data Discovery and Classification is a half done tool.
Personally I loved it the first time saw it. But the more you use it the more you see that is very basic. At the same time I understand that Microsoft cannot keep providing software for free. So if you really need to do a proper Data Discovery and Classification you have to knock at the door of big vendors like Red-Gate or ApexSQL.
The tool is limited to what it can offer out-of-the-box which is:
- Classify data: The idea of data discovery based on the column name is good but I needed more modular options that’s why I had to tune the query.
- Generate Report: Is the report that you are going to show to your manager
- Set Information Protection Policy File: You can add fields in the classification or search words through a JSON file (InformationProtectionPolicy).
- Export Information Protection Policy File: You can export it (…you see, Microsoft, we need to export this 🙂 )
- Reset Information Protection Policy to Default: Reset
In my case I have already classified 50 columns and I now need to export the same classification to 6 databases.
There is no way to do that.
SOLUTION
I mean, there was no way to do that till now.
I have categorized a bunch of columns on AdventureWorks in SQL Server 2019 and if I run this query the last column will print the exportation query for you:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT schema_name(O.schema_id) AS schema_name, O.NAME AS table_name, C.NAME AS column_name, label, information_type, rank, rank_desc ,'ADD SENSITIVITY CLASSIFICATION TO ' + schema_name(O.schema_id) + '.' + O.NAME + '.' + C.NAME + ' WITH ( LABEL=''' + CAST(label AS NVARCHAR(50)) + ''', INFORMATION_TYPE=''' + CAST(information_type AS NVARCHAR(50)) + ''', RANK=' + rank_desc + ' )' FROM sys.sensitivity_classifications sc JOIN sys.objects O ON sc.major_id = O.object_id JOIN sys.columns C ON sc.major_id = C.object_id AND sc.minor_id = C.column_id |
And because I’m awesome you are lucky (the project I’m working on is using SQL Server 2016 😐 ) I also have a query for SQL Server 2017 and earlier; hold your breath, this is going to be much longer: if you notice we need 4 queries in order to classify each field
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
SELECT schema_name(O.schema_id) AS schema_name ,O.NAME AS table_name ,C.NAME AS column_name ,information_type ,sensitivity_label ,'exec sp_addextendedproperty @name=N''sys_information_type_name'',@level0type=N''schema'',@level0name=N''' + schema_name(O.schema_id) + ''' ,@level1type=N''table'',@level1name=N''' + O.NAME + ''' ,@level2type=N''column'',@level2name=N''' + C.NAME + ''' ,@value=N''' + CAST(information_type AS NVARCHAR(50)) + ''';' FROM ( SELECT IT.major_id ,IT.minor_id ,IT.information_type ,L.sensitivity_label FROM ( SELECT major_id ,minor_id ,value AS information_type FROM sys.extended_properties WHERE NAME = 'sys_information_type_name' ) IT FULL OUTER JOIN ( SELECT major_id ,minor_id ,value AS sensitivity_label FROM sys.extended_properties WHERE NAME = 'sys_sensitivity_label_name' ) L ON IT.major_id = L.major_id AND IT.minor_id = L.minor_id ) EP JOIN sys.objects O ON EP.major_id = O.object_id JOIN sys.columns C ON EP.major_id = C.object_id AND EP.minor_id = C.column_id UNION ALL -- Second query UNION ALL SELECT schema_name(O.schema_id) AS schema_name ,O.NAME AS table_name ,C.NAME AS column_name ,information_type ,sensitivity_label , 'exec sp_addextendedproperty @name=N''sys_information_type_id'',@level0type=N''schema'',@level0name=N''' + schema_name(O.schema_id) + ''' ,@level1type=N''table'',@level1name=N''' + O.NAME + ''' ,@level2type=N''column'',@level2name=N''' + C.NAME + ''' ,@value=''' + CAST(NEWID() AS varchar(100)) + ''';' FROM ( SELECT IT.major_id ,IT.minor_id ,IT.information_type ,L.sensitivity_label FROM ( SELECT major_id ,minor_id ,value AS information_type FROM sys.extended_properties WHERE NAME = 'sys_information_type_name' ) IT FULL OUTER JOIN ( SELECT major_id ,minor_id ,value AS sensitivity_label FROM sys.extended_properties WHERE NAME = 'sys_sensitivity_label_name' ) L ON IT.major_id = L.major_id AND IT.minor_id = L.minor_id ) EP JOIN sys.objects O ON EP.major_id = O.object_id JOIN sys.columns C ON EP.major_id = C.object_id AND EP.minor_id = C.column_id UNION ALL -- Third query UNION ALL SELECT schema_name(O.schema_id) AS schema_name ,O.NAME AS table_name ,C.NAME AS column_name ,information_type ,sensitivity_label ,'exec sp_addextendedproperty @name=N''sys_sensitivity_label_name'',@level0type=N''schema'',@level0name=N''' + schema_name(O.schema_id) + ''' ,@level1type=N''table'',@level1name=N''' + O.NAME + ''' ,@level2type=N''column'',@level2name=N''' + C.NAME + ''' ,@value=N''' + CAST(sensitivity_label AS NVARCHAR(50)) + ''';' FROM ( SELECT IT.major_id ,IT.minor_id ,IT.information_type ,L.sensitivity_label FROM ( SELECT major_id ,minor_id ,value AS information_type FROM sys.extended_properties WHERE NAME = 'sys_information_type_name' ) IT FULL OUTER JOIN ( SELECT major_id ,minor_id ,value AS sensitivity_label FROM sys.extended_properties WHERE NAME = 'sys_sensitivity_label_name' ) L ON IT.major_id = L.major_id AND IT.minor_id = L.minor_id ) EP JOIN sys.objects O ON EP.major_id = O.object_id JOIN sys.columns C ON EP.major_id = C.object_id AND EP.minor_id = C.column_id UNION ALL -- Forth query UNION ALL SELECT schema_name(O.schema_id) AS schema_name ,O.NAME AS table_name ,C.NAME AS column_name ,information_type ,sensitivity_label , 'exec sp_addextendedproperty @name=N''sys_sensitivity_label_id'',@level0type=N''schema'',@level0name=N''' + schema_name(O.schema_id) + ''' ,@level1type=N''table'',@level1name=N''' + O.NAME + ''' ,@level2type=N''column'',@level2name=N''' + C.NAME + ''' ,@value=''' + CAST(NEWID() AS varchar(100)) + ''';' FROM ( SELECT IT.major_id ,IT.minor_id ,IT.information_type ,L.sensitivity_label FROM ( SELECT major_id ,minor_id ,value AS information_type FROM sys.extended_properties WHERE NAME = 'sys_information_type_name' ) IT FULL OUTER JOIN ( SELECT major_id ,minor_id ,value AS sensitivity_label FROM sys.extended_properties WHERE NAME = 'sys_sensitivity_label_name' ) L ON IT.major_id = L.major_id AND IT.minor_id = L.minor_id ) EP JOIN sys.objects O ON EP.major_id = O.object_id JOIN sys.columns C ON EP.major_id = C.object_id AND EP.minor_id = C.column_id order by 1,2,3 |
CONCLUSION
Is not over, next post will be about how to export the actual data masking.