1 00:00:00,000 --> 00:00:06,200 Welcome to the Azure Security Podcast, 2 00:00:06,200 --> 00:00:09,360 where we discuss topics relating to security, privacy, 3 00:00:09,360 --> 00:00:13,720 reliability, and compliance on the Microsoft Cloud Platform. 4 00:00:13,720 --> 00:00:17,080 Hey everybody, welcome to Episode 64. 5 00:00:17,080 --> 00:00:20,600 This week's recording is a special Outer Band episode, 6 00:00:20,600 --> 00:00:22,920 where I get to talk to one of my colleagues, 7 00:00:22,920 --> 00:00:25,800 Andreas Walter, about SQL Server permissions. 8 00:00:25,800 --> 00:00:29,240 So it's only me, so Andreas and I can sort of geek out, 9 00:00:29,240 --> 00:00:31,200 and there's also no news. 10 00:00:31,200 --> 00:00:33,520 There will be plenty of news in our next episode, 11 00:00:33,520 --> 00:00:35,360 though, thanks to Microsoft Ignite. 12 00:00:35,360 --> 00:00:37,360 So Andreas, welcome to the podcast. 13 00:00:37,360 --> 00:00:40,480 Would you like to take a moment and introduce yourself to our listeners? 14 00:00:40,480 --> 00:00:43,080 Yeah. So my background is actually quite practical. 15 00:00:43,080 --> 00:00:46,360 I have been working in the industry in SQL Server for about 20 years, 16 00:00:46,360 --> 00:00:48,280 before I joined Microsoft, 17 00:00:48,280 --> 00:00:52,640 and I have been dealing with all kinds of scenarios, 18 00:00:52,640 --> 00:00:54,720 including security, of course, 19 00:00:54,720 --> 00:00:56,840 and now my security PM and the team 20 00:00:56,840 --> 00:01:00,600 are mainly of the permission system. 21 00:01:00,600 --> 00:01:02,160 SQL Server is kind of interesting, right? 22 00:01:02,160 --> 00:01:03,640 It's a relatively complex product, 23 00:01:03,640 --> 00:01:06,080 and it has a relatively complex permission model. 24 00:01:06,080 --> 00:01:07,120 And I'm going to be honest with you, 25 00:01:07,120 --> 00:01:10,360 and I want to see if you agree with this or not, you know? 26 00:01:10,360 --> 00:01:11,680 And that is, you know, 27 00:01:11,680 --> 00:01:13,160 I've worked with a lot of customers over the years 28 00:01:13,160 --> 00:01:16,320 on various SQL Server related projects, 29 00:01:16,320 --> 00:01:19,520 both SQL Server and Azure SQL Database. 30 00:01:19,520 --> 00:01:21,680 I don't think I've ever come across a single customer 31 00:01:21,680 --> 00:01:24,960 who has really taken what I would say full advantage 32 00:01:24,960 --> 00:01:27,600 of the richness of the permission model 33 00:01:27,600 --> 00:01:29,320 that is built into the product. 34 00:01:29,320 --> 00:01:30,960 Is that a fair comment, 35 00:01:30,960 --> 00:01:32,520 or am I like just way off base 36 00:01:32,520 --> 00:01:35,080 or been speaking to the wrong customers? 37 00:01:35,080 --> 00:01:38,520 I think as a general observation, 38 00:01:38,520 --> 00:01:41,280 it's probably not incorrect, 39 00:01:41,280 --> 00:01:44,200 but I have met a couple of customers, 40 00:01:44,200 --> 00:01:47,040 and I have worked with a couple of customers 41 00:01:47,040 --> 00:01:50,480 in projects to actually make use of it. 42 00:01:50,480 --> 00:01:53,240 But that has to end to do with my speciality, 43 00:01:53,240 --> 00:01:57,360 so I was never the average consultant for customers, 44 00:01:57,360 --> 00:01:58,720 so they used to call me 45 00:01:58,720 --> 00:02:03,720 when they needed specific security compliance, 46 00:02:03,800 --> 00:02:05,080 and that's where I have. 47 00:02:05,080 --> 00:02:08,240 But this is really the exception. 48 00:02:08,240 --> 00:02:10,840 Yeah, so I agree on the general statement. 49 00:02:11,920 --> 00:02:14,520 However, there are a few customers 50 00:02:14,520 --> 00:02:17,080 who actually go down to the very last 51 00:02:17,080 --> 00:02:21,120 and squeeze out what you can out of the permission system. 52 00:02:21,120 --> 00:02:22,760 So let's start at the very, very beginning. 53 00:02:22,760 --> 00:02:24,240 So we're talking authorization here. 54 00:02:24,240 --> 00:02:25,400 We're not talking about authentication. 55 00:02:25,400 --> 00:02:28,480 We're not talking about audit or cryptographic controls. 56 00:02:28,480 --> 00:02:31,760 We're talking purely about authorization. 57 00:02:31,760 --> 00:02:36,760 So a user access is SQL Server. 58 00:02:38,080 --> 00:02:40,480 There's a login and there's a user. 59 00:02:40,480 --> 00:02:41,920 Actually, can you explain the difference there 60 00:02:41,920 --> 00:02:44,200 between a login and a user? 61 00:02:44,200 --> 00:02:47,160 Sure, so if you'd like to be precise 62 00:02:47,160 --> 00:02:49,360 whenever I communicate with someone, 63 00:02:49,360 --> 00:02:53,000 I very carefully choose my words. 64 00:02:53,000 --> 00:02:57,640 So login is reserved for accessing the server. 65 00:02:57,640 --> 00:03:01,880 That's the first point of contact to the server. 66 00:03:01,880 --> 00:03:04,840 And then the principal, the user who is behind, 67 00:03:04,840 --> 00:03:06,400 okay, so that was incorrect. 68 00:03:06,400 --> 00:03:09,360 When the account that is behind that login, 69 00:03:09,360 --> 00:03:12,440 let's say in AD or AAD, Windows AD, 70 00:03:12,440 --> 00:03:15,520 and Windows Active Directory or Azure Active Directory, 71 00:03:15,520 --> 00:03:18,360 account has passed this first level 72 00:03:18,360 --> 00:03:19,880 of the server login. 73 00:03:19,880 --> 00:03:21,520 He then accesses a database 74 00:03:21,520 --> 00:03:23,680 and the access of a specific database 75 00:03:23,680 --> 00:03:25,640 is then a user account. 76 00:03:25,640 --> 00:03:29,040 The user account is backed or matched with a login 77 00:03:29,040 --> 00:03:30,680 on the server level usually. 78 00:03:30,680 --> 00:03:34,200 And Azure SQL Database, you can do it differently, 79 00:03:34,200 --> 00:03:37,280 but in general SQL Server or managed instance, 80 00:03:37,280 --> 00:03:39,640 it's common to have the login at the server 81 00:03:39,640 --> 00:03:43,120 and then you access a specific database, 82 00:03:43,120 --> 00:03:47,100 a user database mostly, with the user account. 83 00:03:47,100 --> 00:03:50,100 So when you said user database there, 84 00:03:50,100 --> 00:03:53,060 is it therefore possible that one login 85 00:03:53,060 --> 00:03:56,380 could lead to more than one user account? 86 00:03:56,380 --> 00:03:57,220 Is that the case? 87 00:03:57,220 --> 00:04:02,220 Or must it always, like if you've got a login of say ABC, 88 00:04:02,460 --> 00:04:07,460 will the user account for database A be ABC 89 00:04:07,460 --> 00:04:12,460 and the user account for database B also be ABC? 90 00:04:12,540 --> 00:04:14,500 Or can they be different? 91 00:04:14,500 --> 00:04:17,460 Yeah, so you can switch the names 92 00:04:17,460 --> 00:04:19,380 if you really want to make your life hard. 93 00:04:19,380 --> 00:04:22,260 You can call the login ABC and in the database 94 00:04:22,260 --> 00:04:27,060 you call this DEF, but that's certainly not a good practice. 95 00:04:27,060 --> 00:04:29,340 So normally you would keep the account names 96 00:04:29,340 --> 00:04:32,060 or the username equal to the login name. 97 00:04:32,060 --> 00:04:35,460 And yes, each database is independent. 98 00:04:35,460 --> 00:04:39,180 So if you have 10 databases, you have maximum up 99 00:04:39,180 --> 00:04:41,020 to 10 independent user accounts, 100 00:04:41,020 --> 00:04:43,940 one in each database that matches with that login. 101 00:04:43,940 --> 00:04:44,900 But you don't have to, right? 102 00:04:44,900 --> 00:04:46,940 You can also say, well, he can only, 103 00:04:46,940 --> 00:04:49,340 like the actual account behind the login 104 00:04:49,340 --> 00:04:52,060 can only access five of those 10 databases, 105 00:04:52,060 --> 00:04:54,260 but then you only create five user accounts, 106 00:04:54,260 --> 00:04:57,060 one in each of those five user databases 107 00:04:57,060 --> 00:04:58,820 where you want them to access. 108 00:04:58,820 --> 00:05:00,140 I just want to just point, I didn't know that. 109 00:05:00,140 --> 00:05:01,180 So just because you have a login 110 00:05:01,180 --> 00:05:03,860 doesn't mean you have access to a database. 111 00:05:03,860 --> 00:05:04,700 Exactly. 112 00:05:04,700 --> 00:05:07,860 So to be specific in SQL Server, 113 00:05:07,860 --> 00:05:10,100 let's exclude Azure SQL Database for now. 114 00:05:10,100 --> 00:05:12,240 It has slightly different behavior. 115 00:05:12,240 --> 00:05:13,820 But in SQL Server managed instance, 116 00:05:13,820 --> 00:05:15,300 in the moment that you have a login, 117 00:05:15,300 --> 00:05:17,220 you can access the server 118 00:05:17,220 --> 00:05:19,820 and you will be in the context 119 00:05:19,820 --> 00:05:22,820 of the system database master usually, 120 00:05:22,820 --> 00:05:24,820 unless you change the default database. 121 00:05:24,820 --> 00:05:26,860 Yeah, there's always an exception, 122 00:05:26,860 --> 00:05:29,580 but you can access the server, the system, right? 123 00:05:29,580 --> 00:05:32,700 The system database is tempdb and master. 124 00:05:32,700 --> 00:05:35,540 You can always be in the context of them. 125 00:05:35,540 --> 00:05:37,340 You can't do anything in them. 126 00:05:37,340 --> 00:05:39,780 You don't have permissions yet, but you can exist. 127 00:05:39,780 --> 00:05:42,860 You can have a running session on the server. 128 00:05:42,860 --> 00:05:46,060 However, if you want to now access a user database, 129 00:05:46,060 --> 00:05:49,140 AdventureWorks, whatever, Wide World and Portals, 130 00:05:49,140 --> 00:05:52,380 you actually do need a user account in those databases. 131 00:05:52,380 --> 00:05:53,860 So this is the special case. 132 00:05:53,860 --> 00:05:55,740 There first comes the connection to the server 133 00:05:55,740 --> 00:05:57,300 and the login. 134 00:05:57,300 --> 00:05:59,780 And now depending on which database you have a user account, 135 00:05:59,780 --> 00:06:01,780 you can connect to those. 136 00:06:01,780 --> 00:06:04,140 Actually, it's interesting you just say user database there. 137 00:06:04,140 --> 00:06:06,620 And I'm very happy that you said, 138 00:06:06,620 --> 00:06:09,060 want to be really precise with your words. 139 00:06:09,060 --> 00:06:10,780 Because I know that a lot of people actually confuse 140 00:06:10,780 --> 00:06:12,900 database and server. 141 00:06:12,900 --> 00:06:15,220 But I think that the database is like a SQL server. 142 00:06:15,220 --> 00:06:16,340 But it's not. 143 00:06:16,340 --> 00:06:19,300 You've got a server that can have multiple user databases. 144 00:06:19,300 --> 00:06:22,980 You're essentially connecting to or being authenticated 145 00:06:22,980 --> 00:06:27,180 to the server and then to one or more databases, 146 00:06:27,180 --> 00:06:29,060 user databases, to use your parlance. 147 00:06:29,060 --> 00:06:30,900 OK. 148 00:06:30,900 --> 00:06:33,020 Maybe this confusion somehow comes. 149 00:06:33,020 --> 00:06:35,420 Some people who are used to work with Oracle, 150 00:06:35,420 --> 00:06:37,140 yeah, the concept is different. 151 00:06:37,140 --> 00:06:39,900 So that's maybe where the confusion is. 152 00:06:39,900 --> 00:06:43,220 Like you have a database that equals the instance. 153 00:06:43,220 --> 00:06:46,700 And SQL server, you have an instance, a server. 154 00:06:46,700 --> 00:06:48,820 And on the server, you have multiple databases 155 00:06:48,820 --> 00:06:50,180 that you can connect to. 156 00:06:50,180 --> 00:06:51,180 Right. 157 00:06:51,180 --> 00:06:56,820 So a database server called ABC could have Contoso 158 00:06:56,820 --> 00:07:01,060 database and AdventureWorks database. 159 00:07:01,060 --> 00:07:02,060 OK. 160 00:07:02,060 --> 00:07:03,220 So I'm into a database now. 161 00:07:03,220 --> 00:07:04,700 Let's just go with AdventureWorks, 162 00:07:04,700 --> 00:07:05,500 as everyone knows it. 163 00:07:05,500 --> 00:07:07,900 I didn't realize that AdventureWorks LT, which 164 00:07:07,900 --> 00:07:10,140 is a lightweight version of AdventureWorks. 165 00:07:10,140 --> 00:07:14,060 My guess is it doesn't have as many columns and tables 166 00:07:14,060 --> 00:07:14,620 and columns. 167 00:07:14,620 --> 00:07:15,140 I don't know. 168 00:07:15,140 --> 00:07:16,260 But anyway. 169 00:07:16,260 --> 00:07:17,340 So OK. 170 00:07:17,340 --> 00:07:19,900 So now I'm into AdventureWorks. 171 00:07:19,900 --> 00:07:22,100 And I have a user account that lets me do stuff 172 00:07:22,100 --> 00:07:22,980 in AdventureWorks. 173 00:07:22,980 --> 00:07:26,980 So how do I start locking down what people can do inside 174 00:07:26,980 --> 00:07:29,060 of that database? 175 00:07:29,060 --> 00:07:31,540 Thanks for putting it that way, because I can use it 176 00:07:31,540 --> 00:07:32,820 as a correction. 177 00:07:32,820 --> 00:07:35,180 You say start locking down. 178 00:07:35,180 --> 00:07:39,860 The actual correct way would be start granting the right 179 00:07:39,860 --> 00:07:41,940 permissions or start opening. 180 00:07:41,940 --> 00:07:44,100 Because by default, you start with no permissions. 181 00:07:44,100 --> 00:07:46,420 Just because you have a user account in a database 182 00:07:46,420 --> 00:07:47,260 doesn't mean anything. 183 00:07:47,260 --> 00:07:49,300 You can connect out of the database. 184 00:07:49,300 --> 00:07:51,420 So having user accounts automatically 185 00:07:51,420 --> 00:07:53,100 gives it a connect permission. 186 00:07:53,100 --> 00:07:53,900 So far, so good. 187 00:07:53,900 --> 00:07:55,140 Now you're connected. 188 00:07:55,140 --> 00:07:58,260 You can run some queries on system level, 189 00:07:58,260 --> 00:08:01,460 like, oh, what's my current databases name? 190 00:08:01,460 --> 00:08:02,460 But that's about it. 191 00:08:02,460 --> 00:08:04,940 You can't even find out which user tables exist. 192 00:08:04,940 --> 00:08:06,100 And it's not a specific permission. 193 00:08:06,100 --> 00:08:07,620 It's a separate database. 194 00:08:07,620 --> 00:08:10,620 Until you get now specific permissions. 195 00:08:10,620 --> 00:08:12,020 So that would be the correct way. 196 00:08:12,020 --> 00:08:15,260 Instead of opening it out and granting something like, 197 00:08:15,260 --> 00:08:16,420 hey, you have all permissions. 198 00:08:16,420 --> 00:08:18,940 Or you have a role which has all permissions, 199 00:08:18,940 --> 00:08:21,020 you should start with, well, what do I really 200 00:08:21,020 --> 00:08:22,460 need this person to have? 201 00:08:22,460 --> 00:08:26,220 And then pick either the right role that already exists. 202 00:08:26,220 --> 00:08:31,980 Or you create a new role where you add the permissions 203 00:08:31,980 --> 00:08:35,060 user account to have. 204 00:08:35,060 --> 00:08:36,660 Would it be fair to say that most customers will just 205 00:08:36,660 --> 00:08:40,900 stick with default roles that are built into SQL Server 206 00:08:40,900 --> 00:08:44,620 rather than starting creating their own custom roles? 207 00:08:44,620 --> 00:08:46,900 Yeah, I don't know the actual percentage. 208 00:08:46,900 --> 00:08:48,340 But there is a fair chance. 209 00:08:48,340 --> 00:08:51,100 So let's say the database reader role, and DB reader, 210 00:08:51,100 --> 00:08:54,740 and DB writer role, they are certainly widely used 211 00:08:54,740 --> 00:08:58,020 in almost any environment. 212 00:08:58,020 --> 00:08:59,820 However, there are many customers 213 00:08:59,820 --> 00:09:02,340 who start using their own specific roles. 214 00:09:02,340 --> 00:09:05,500 The executor would be one that's very commonly created. 215 00:09:05,500 --> 00:09:08,900 You're allowed to execute all procedures in the database. 216 00:09:08,900 --> 00:09:12,340 But then customers also start creating custom roles who 217 00:09:12,340 --> 00:09:17,820 can only access specific schemas or have specific permissions 218 00:09:17,820 --> 00:09:21,340 to create objects in certain schemas and so on. 219 00:09:21,340 --> 00:09:25,580 So now it really depends on how advanced the database model is, 220 00:09:25,580 --> 00:09:28,780 how much security inside the database is necessary. 221 00:09:28,780 --> 00:09:33,380 But yeah, as a start, those built-in roles are very commonly used. 222 00:09:33,380 --> 00:09:35,300 So I want to just hold that thought just for a minute 223 00:09:35,300 --> 00:09:37,620 about database reader and so on, and just talk 224 00:09:37,620 --> 00:09:39,300 about schemas for a minute. 225 00:09:39,300 --> 00:09:41,380 So I found out not too long ago, I'm 226 00:09:41,380 --> 00:09:44,260 sad to say, like about nine months ago, 227 00:09:44,260 --> 00:09:47,900 that I thought schema was like a table definition file 228 00:09:47,900 --> 00:09:50,740 or an XML schema or a JSON schema. 229 00:09:50,740 --> 00:09:54,020 In the case of SQL databases, that's not true. 230 00:09:54,020 --> 00:09:58,100 A schema is something completely different. 231 00:09:58,100 --> 00:10:01,740 Yeah, that's unfortunately a double use of the same word. 232 00:10:01,740 --> 00:10:04,700 It's not incorrect to say, hey, my database has a certain schema. 233 00:10:04,700 --> 00:10:07,660 And then you understand by the way I'm formulating it, 234 00:10:07,660 --> 00:10:10,420 that it means, hey, we have certain tables laid out 235 00:10:10,420 --> 00:10:11,260 in this database. 236 00:10:11,260 --> 00:10:12,580 That's a schema of a database. 237 00:10:12,580 --> 00:10:13,820 You can say that. 238 00:10:13,820 --> 00:10:14,900 It's not incorrect. 239 00:10:14,900 --> 00:10:18,100 However, in SQLSore, we also have an object code schema, 240 00:10:18,100 --> 00:10:21,340 a specific object type code schema. 241 00:10:21,340 --> 00:10:23,900 And the plural is schemas. 242 00:10:23,900 --> 00:10:26,220 So in that moment, you understand, hey, 243 00:10:26,220 --> 00:10:27,380 there's multiple schemas. 244 00:10:27,380 --> 00:10:29,500 So these are the objects versus, hey, 245 00:10:29,500 --> 00:10:36,060 my table has a certain schema could lead you the wrong path. 246 00:10:36,060 --> 00:10:38,820 So either a table is part of a certain schema 247 00:10:38,820 --> 00:10:41,220 or is under a certain schema. 248 00:10:41,220 --> 00:10:43,300 Then it's clear it's in the object hierarchy. 249 00:10:43,300 --> 00:10:46,580 Schema comes after database and before the table. 250 00:10:46,580 --> 00:10:50,340 It's like you can think of a container, logical container, 251 00:10:50,340 --> 00:10:54,300 namespace for tables, which has a security border, 252 00:10:54,300 --> 00:10:57,340 versus the schema the way that the table is designed. 253 00:10:57,340 --> 00:10:58,740 So the word is not incorrect. 254 00:10:58,740 --> 00:11:03,020 It's database, database developer terminology. 255 00:11:03,020 --> 00:11:05,460 But in SQLSore, we have to be a bit more careful 256 00:11:05,460 --> 00:11:08,020 how you phrase it. 257 00:11:08,020 --> 00:11:13,620 So you might have a schema, I guess, that could be, say, HR. 258 00:11:13,620 --> 00:11:15,540 Let's just say, simple example, say, HR. 259 00:11:15,540 --> 00:11:17,380 Underneath that, you could have multiple databases 260 00:11:17,380 --> 00:11:20,620 that support HR. 261 00:11:20,620 --> 00:11:22,500 Is that a fair analogy? 262 00:11:22,500 --> 00:11:25,540 Yeah, so let's just briefly talk about the schema. 263 00:11:25,540 --> 00:11:28,060 So in SQLSore, again, it's different to Oracle. 264 00:11:28,060 --> 00:11:32,260 So really just reset your brains here and start a new. 265 00:11:32,260 --> 00:11:34,540 A database can contain many tables. 266 00:11:34,540 --> 00:11:37,380 And all of these tables need to be part of a certain schema. 267 00:11:37,380 --> 00:11:41,300 So schema is sort of a logical container for objects. 268 00:11:41,300 --> 00:11:44,500 So if you look at the naming style in SQLSore, 269 00:11:44,500 --> 00:11:47,620 the three-part naming style would be database, schema, 270 00:11:47,620 --> 00:11:48,980 and table. 271 00:11:48,980 --> 00:11:51,260 The two-part naming style is schema and table. 272 00:11:51,260 --> 00:11:53,100 Once inside the database, you would 273 00:11:53,100 --> 00:11:55,260 work with the two-part naming style. 274 00:11:55,260 --> 00:11:57,580 So the schema is what comes before the table. 275 00:11:57,580 --> 00:11:58,660 And you cannot omit that. 276 00:11:58,660 --> 00:12:00,140 You need that. 277 00:12:00,140 --> 00:12:03,100 So you need to decide when you develop databases which part, 278 00:12:03,100 --> 00:12:06,300 which schema a table is part of. 279 00:12:06,300 --> 00:12:08,380 Now, yes, we do have a default schema. 280 00:12:08,380 --> 00:12:10,340 If you don't decide, it will still 281 00:12:10,340 --> 00:12:13,420 lend in the schema and the default schema is DBO. 282 00:12:13,420 --> 00:12:16,060 However, you can create your own custom schema, 283 00:12:16,060 --> 00:12:17,220 like in Adventureworks. 284 00:12:17,220 --> 00:12:19,460 They did that in this example database. 285 00:12:19,460 --> 00:12:22,780 They have various schemas like HR. 286 00:12:22,780 --> 00:12:26,620 Person as in is another one, sales, and so on. 287 00:12:26,620 --> 00:12:29,700 So in the Adventureworks database, to be honest, 288 00:12:29,700 --> 00:12:32,020 is not the best use case for schema. 289 00:12:32,020 --> 00:12:36,780 It's purely done for logical separation of objects. 290 00:12:36,780 --> 00:12:40,780 There's no further effect on the actual security. 291 00:12:40,780 --> 00:12:44,780 So that's not the exactly best way to do it. 292 00:12:44,780 --> 00:12:46,740 But the general idea of a schema is 293 00:12:46,740 --> 00:12:50,500 to separate out objects inside the database 294 00:12:50,500 --> 00:12:53,500 in different security areas. 295 00:12:53,500 --> 00:12:56,180 Where you can then have an easier life granting permissions 296 00:12:56,180 --> 00:12:59,300 on one schema versus the other schema. 297 00:12:59,300 --> 00:13:01,380 That's the idea behind the schemas. 298 00:13:01,380 --> 00:13:04,540 So it is an isolation boundary on which you can put, 299 00:13:04,540 --> 00:13:06,980 essentially, access policies. 300 00:13:06,980 --> 00:13:08,140 OK. 301 00:13:08,140 --> 00:13:12,020 So let's go back to the database reader and database writer. 302 00:13:12,020 --> 00:13:20,180 So if I create a login, Mary, and I have a user account, Mary, 303 00:13:20,180 --> 00:13:24,020 by default, Mary has, let's just say Mary has, 304 00:13:24,020 --> 00:13:26,500 I've just created a brand new user account. 305 00:13:26,500 --> 00:13:27,820 Nothing funky whatsoever. 306 00:13:27,820 --> 00:13:30,260 So just using whatever tools I would normally 307 00:13:30,260 --> 00:13:34,740 use like TSQL or SSMS, SQL Server Management Studio, 308 00:13:34,740 --> 00:13:36,260 whatever. 309 00:13:36,260 --> 00:13:39,580 Does that account have any roles associated with it 310 00:13:39,580 --> 00:13:42,340 by default or not? 311 00:13:42,340 --> 00:13:48,140 Not really, with the exception of the system role public. 312 00:13:48,140 --> 00:13:49,580 It's like an active directory. 313 00:13:49,580 --> 00:13:51,940 Everyone is member of the everyone group. 314 00:13:51,940 --> 00:13:52,860 You can't avoid that. 315 00:13:52,860 --> 00:13:55,340 And SQL Server, that group is called, or that role, 316 00:13:55,340 --> 00:13:57,100 is called public. 317 00:13:57,100 --> 00:13:59,700 And the public role does have a view permissions, 318 00:13:59,700 --> 00:14:02,820 like, for example, to view system objects. 319 00:14:02,820 --> 00:14:03,700 But that's it. 320 00:14:03,700 --> 00:14:06,820 And you can't avoid being a member of public. 321 00:14:06,820 --> 00:14:08,580 You view the system objects, but not 322 00:14:08,580 --> 00:14:10,580 the contents of the objects. 323 00:14:10,580 --> 00:14:11,140 It depends. 324 00:14:11,140 --> 00:14:12,740 There are some very generic ones. 325 00:14:12,740 --> 00:14:14,780 For example, you can easily detect 326 00:14:14,780 --> 00:14:17,740 the name of your current database and such. 327 00:14:17,740 --> 00:14:22,060 But other than that, you can't see which user objects exist. 328 00:14:22,060 --> 00:14:24,460 And of course, not the contents of them either. 329 00:14:24,460 --> 00:14:27,100 But you can just see that there is an object that contains data, 330 00:14:27,100 --> 00:14:29,380 but you can't actually necessarily see the content. 331 00:14:29,380 --> 00:14:29,740 Yeah. 332 00:14:29,740 --> 00:14:32,580 I mean, you can, for example, write it clearly. 333 00:14:32,580 --> 00:14:34,180 Hey, which other user accounts exist? 334 00:14:34,180 --> 00:14:35,900 And it will not give you all user accounts. 335 00:14:35,900 --> 00:14:38,460 It will only give you return you the system user 336 00:14:38,460 --> 00:14:40,260 accounts or the system roles. 337 00:14:40,260 --> 00:14:41,940 So it's not like it's returning an error. 338 00:14:41,940 --> 00:14:43,220 You can't see anything. 339 00:14:43,220 --> 00:14:47,980 But it will limit you, in that case, to the built-in 340 00:14:47,980 --> 00:14:49,460 functionalities of SQL Solace. 341 00:14:49,460 --> 00:14:53,660 So your queries don't fail, but it will be filtered. 342 00:14:53,660 --> 00:14:56,620 Actually, that's an interesting thought, actually. 343 00:14:56,620 --> 00:14:58,540 You said filtered and not failed. 344 00:14:58,540 --> 00:14:59,420 That's really interesting. 345 00:14:59,420 --> 00:15:01,820 And I want to come back to that a little bit later on, 346 00:15:01,820 --> 00:15:05,180 because I want to ask you a question about columns. 347 00:15:05,180 --> 00:15:06,980 But we'll come on to that a little bit later on. 348 00:15:06,980 --> 00:15:11,180 OK, so I've granted Mary database reader. 349 00:15:11,180 --> 00:15:14,340 So that now gives her read access to, 350 00:15:14,340 --> 00:15:16,540 let's just keep it nice and simple, 351 00:15:16,540 --> 00:15:18,780 table objects in the database. 352 00:15:18,780 --> 00:15:24,500 If she has database X, was it executor or something, 353 00:15:24,500 --> 00:15:26,780 database execute, then they have access to store procedures. 354 00:15:26,780 --> 00:15:28,180 But if she has just database reader, 355 00:15:28,180 --> 00:15:30,860 she can't call store procedures. 356 00:15:30,860 --> 00:15:31,780 Yes, that's correct. 357 00:15:31,780 --> 00:15:34,500 But you can also, let's be a little bit more precise, 358 00:15:34,500 --> 00:15:37,500 the data reader also allows access to views, 359 00:15:37,500 --> 00:15:41,300 basically anything that can be accessed with the select statement 360 00:15:41,300 --> 00:15:44,180 could even be table-valued functions. 361 00:15:44,180 --> 00:15:46,780 OK, and of course, the store procedure is not a, you know, 362 00:15:46,780 --> 00:15:47,420 you don't just. 363 00:15:47,420 --> 00:15:48,900 Store procedures would not be possible. 364 00:15:48,900 --> 00:15:50,020 Yeah, OK. 365 00:15:50,020 --> 00:15:54,500 So let's say Mary now has database reader. 366 00:15:54,500 --> 00:15:56,220 So that gives an only database reader. 367 00:15:56,220 --> 00:16:01,020 So it gives her read access to all tables and all views. 368 00:16:01,020 --> 00:16:06,220 Like she can invoke a view and view something through the view. 369 00:16:06,220 --> 00:16:08,540 So now we're down to individual tables. 370 00:16:08,540 --> 00:16:11,820 Is it possible to, so if she has database reader, 371 00:16:11,820 --> 00:16:15,180 she has access to all tables, what would you do at this point? 372 00:16:15,180 --> 00:16:17,060 Would you say, let's say there's 10 tables, 373 00:16:17,060 --> 00:16:19,300 let's just keep it simple, and let's say that Mary only really 374 00:16:19,300 --> 00:16:21,940 has access to nine tables. 375 00:16:21,940 --> 00:16:24,820 Would you, can you deny Mary, 376 00:16:24,820 --> 00:16:28,620 a database reader on one table? 377 00:16:28,620 --> 00:16:33,260 Yeah, so first of all, yeah, in this case, you could do, 378 00:16:33,260 --> 00:16:35,420 she would be a member of dvDataReader. 379 00:16:35,420 --> 00:16:38,540 And if it's only one exception out of 10, or let's say out of 100 380 00:16:38,540 --> 00:16:42,380 to make it even more extreme, you could revert to the deny. 381 00:16:42,380 --> 00:16:43,900 Probably simple in that case, right? 382 00:16:43,900 --> 00:16:47,300 Just deny this one table on top of being granted everything 383 00:16:47,300 --> 00:16:50,300 on reading on all other tables. 384 00:16:50,300 --> 00:16:52,460 So in that case, you have two statements, right? 385 00:16:52,460 --> 00:16:56,900 Make Mary a member of dvReader and deny access, deny read, 386 00:16:56,900 --> 00:16:59,700 deny select on that table, that specific table. 387 00:16:59,700 --> 00:17:00,980 So that's one way. 388 00:17:00,980 --> 00:17:03,300 In this case, it's effective. 389 00:17:03,300 --> 00:17:06,780 However, normally I wouldn't recommend relying on denies. 390 00:17:06,780 --> 00:17:11,540 Normally it would be more clean to work with just specific grants 391 00:17:11,540 --> 00:17:13,500 and not rely on a deny. 392 00:17:13,500 --> 00:17:15,140 Now, in this case, a bit more extreme, 393 00:17:15,140 --> 00:17:17,300 if you have 100 versus one table, 394 00:17:17,300 --> 00:17:19,100 deny is probably quite simple. 395 00:17:19,100 --> 00:17:22,660 But let's say the other way, if you have like 100 tables 396 00:17:22,660 --> 00:17:24,740 and 50 of them, she should have access to, 397 00:17:24,740 --> 00:17:26,180 and the other 50 is not. 398 00:17:26,180 --> 00:17:28,700 You see, I'm getting to, you don't want to have 50 denies, 399 00:17:28,700 --> 00:17:29,200 right? 400 00:17:29,200 --> 00:17:30,540 It's just not manageable. 401 00:17:30,540 --> 00:17:37,260 So the clean way is to create a schema for the first 50 tables 402 00:17:37,260 --> 00:17:39,980 and another schema for the other 50 tables. 403 00:17:39,980 --> 00:17:41,500 Now, of course, it is very simplified. 404 00:17:41,500 --> 00:17:43,700 There will be other users in the database. 405 00:17:43,700 --> 00:17:49,220 So hopefully that same access restriction applies to everyone. 406 00:17:49,220 --> 00:17:51,260 Probably has a reason why it's 50 tables 407 00:17:51,260 --> 00:17:52,980 versus the other 50 tables. 408 00:17:52,980 --> 00:17:55,700 Maybe in the one schema, we have data 409 00:17:55,700 --> 00:17:58,540 that concerns financial transactions, et cetera, 410 00:17:58,540 --> 00:18:01,540 versus the other ones, the other schema containing 411 00:18:01,540 --> 00:18:04,540 general product information, not so sensitive. 412 00:18:04,540 --> 00:18:07,300 So you could expect that the same logic having access 413 00:18:07,300 --> 00:18:10,060 to this bulk of tables versus the other bulk of tables 414 00:18:10,060 --> 00:18:13,660 applies hopefully to almost everyone in that database. 415 00:18:13,660 --> 00:18:15,780 And then you would basically have one schema for, 416 00:18:15,780 --> 00:18:17,260 what did I say, financials. 417 00:18:17,260 --> 00:18:19,780 Financials.tableName would be the outcome, 418 00:18:19,780 --> 00:18:22,340 and the other schema would be products.tableName, 419 00:18:22,340 --> 00:18:24,820 product.tableName, and so on. 420 00:18:24,820 --> 00:18:28,220 And then you would only have one grant per schema. 421 00:18:28,220 --> 00:18:32,540 You can say, Mary, grant, select on schema product. 422 00:18:32,540 --> 00:18:33,940 And you don't have to say anything 423 00:18:33,940 --> 00:18:35,820 about the non-granted tables, because she 424 00:18:35,820 --> 00:18:39,660 doesn't have access by default to the financials tables. 425 00:18:39,660 --> 00:18:42,340 So you only have one statement for grant access 426 00:18:42,340 --> 00:18:44,340 to schema product. 427 00:18:44,340 --> 00:18:45,620 So that's the clean way. 428 00:18:45,620 --> 00:18:48,620 But of course, you need to do that from beginning on, 429 00:18:48,620 --> 00:18:51,900 from implementation, from architecture, 430 00:18:51,900 --> 00:18:56,220 from designing your database on to create a proper schema. 431 00:18:56,220 --> 00:18:58,260 So if you're designing a database, 432 00:18:58,260 --> 00:19:02,260 you should already understand the different type of use cases 433 00:19:02,260 --> 00:19:08,820 and user access paths that the end product will support. 434 00:19:08,820 --> 00:19:12,100 Because then, if you want to support that very easily, 435 00:19:12,100 --> 00:19:15,820 then you really need to create the schemas from beginning 436 00:19:15,820 --> 00:19:17,300 in the proper way. 437 00:19:17,300 --> 00:19:20,100 So that means if I've got a schema of products 438 00:19:20,100 --> 00:19:22,940 and another schema of financials, 439 00:19:22,940 --> 00:19:28,620 and I'm granting Mary, Reed on financials, or whatever, 440 00:19:28,620 --> 00:19:31,180 whichever one, that means she's not got database, 441 00:19:31,180 --> 00:19:32,380 Reed, all right? 442 00:19:32,380 --> 00:19:32,860 Exactly. 443 00:19:32,860 --> 00:19:35,020 So now you don't need this role at all, 444 00:19:35,020 --> 00:19:37,700 because that role would open the whole database, all tables, 445 00:19:37,700 --> 00:19:39,500 and no matter which schema. 446 00:19:39,500 --> 00:19:43,780 It's a database level select versus we want a schema level 447 00:19:43,780 --> 00:19:45,460 select permission. 448 00:19:45,460 --> 00:19:46,060 Right. 449 00:19:46,060 --> 00:19:48,420 OK, that's all falling, all signs have fallen into place now. 450 00:19:48,420 --> 00:19:49,380 OK, next one. 451 00:19:49,380 --> 00:19:51,740 I think you know where I'm going to go next. 452 00:19:51,740 --> 00:19:54,900 OK, so let's say Mary has access to these 50 tables 453 00:19:54,900 --> 00:19:56,780 you mentioned, and let's say she doesn't have access 454 00:19:56,780 --> 00:20:00,180 to one column in one table. 455 00:20:00,180 --> 00:20:02,460 Let's say social security number, just to pick on something 456 00:20:02,460 --> 00:20:05,060 pathological. 457 00:20:05,060 --> 00:20:06,140 How would you do that? 458 00:20:06,140 --> 00:20:07,220 How would you design that? 459 00:20:07,220 --> 00:20:11,060 And what would the outcome sort of look like for Mary? 460 00:20:11,060 --> 00:20:14,620 Yeah, so if I'm smart from the very beginning, 461 00:20:14,620 --> 00:20:17,740 I would actually not ever grant access to tables. 462 00:20:17,740 --> 00:20:21,940 I would create a specific schema that contains views 463 00:20:21,940 --> 00:20:24,860 that select the data from those tables. 464 00:20:24,860 --> 00:20:27,220 And in those views, you can basically do whatever you want. 465 00:20:27,220 --> 00:20:28,700 You can filter out columns. 466 00:20:28,700 --> 00:20:31,340 You can leave away, leave out columns 467 00:20:31,340 --> 00:20:34,420 that you don't want the users to access, et cetera. 468 00:20:34,420 --> 00:20:36,460 So I call this an access schema. 469 00:20:36,460 --> 00:20:38,380 You have a schema which only contains views, 470 00:20:38,380 --> 00:20:39,940 and the views do the logic in terms 471 00:20:39,940 --> 00:20:42,900 of which columns are actually necessary to be seen. 472 00:20:42,900 --> 00:20:46,580 Like ID columns, for example, are not usually necessary 473 00:20:46,580 --> 00:20:49,700 for users, depends on the application, of course. 474 00:20:49,700 --> 00:20:51,740 And in this case, maybe the social security number, 475 00:20:51,740 --> 00:20:57,620 you don't ever want to disclose it for specific rows, maybe. 476 00:20:57,620 --> 00:20:58,740 So that would be one way. 477 00:20:58,740 --> 00:21:02,460 You create specific schemas, specific views, 478 00:21:02,460 --> 00:21:03,780 and a specific schema. 479 00:21:03,780 --> 00:21:05,860 You can create multiple views pointing 480 00:21:05,860 --> 00:21:08,260 to the same object in a different schema. 481 00:21:08,260 --> 00:21:12,420 So maybe you have a schema general users. 482 00:21:12,420 --> 00:21:15,140 There you have a view that omits this column 483 00:21:15,140 --> 00:21:21,500 versus another schema, let's say, elevated users, 484 00:21:21,500 --> 00:21:24,900 which has another view, but which basically just select 485 00:21:24,900 --> 00:21:28,620 all of these columns from that table. 486 00:21:28,620 --> 00:21:31,900 So that would be one way to do that. 487 00:21:31,900 --> 00:21:33,340 An alternative to views, by the way, 488 00:21:33,340 --> 00:21:35,060 is using stored procedures. 489 00:21:35,060 --> 00:21:39,260 And stored procedures, you can do even much more than just 490 00:21:39,260 --> 00:21:40,380 leaving out columns. 491 00:21:40,380 --> 00:21:42,860 You can put much more logic into these procedures 492 00:21:42,860 --> 00:21:44,980 as a much more advanced way. 493 00:21:44,980 --> 00:21:50,420 So this would be the way to implement this in terms 494 00:21:50,420 --> 00:21:53,860 of by using a smart database design. 495 00:21:53,860 --> 00:21:57,580 Now, the other way is, if you can't split it in any way, 496 00:21:57,580 --> 00:22:01,060 or maybe it's already done, you basically 497 00:22:01,060 --> 00:22:04,260 need to block access to this column. 498 00:22:04,260 --> 00:22:07,060 So let's say you can't do anything in both her 499 00:22:07,060 --> 00:22:08,740 not having access to the table. 500 00:22:08,740 --> 00:22:10,660 She has access to the table. 501 00:22:10,660 --> 00:22:15,740 So you can now either put a deny on the column level. 502 00:22:15,740 --> 00:22:20,820 Now, the nine column level is very tricky to manage, 503 00:22:20,820 --> 00:22:21,820 to be honest. 504 00:22:21,820 --> 00:22:25,060 So I would in general never recommend working 505 00:22:25,060 --> 00:22:28,540 with column level permissions, because if you want to basically 506 00:22:28,540 --> 00:22:32,220 at the end have an oversight, who has access to which data, 507 00:22:32,220 --> 00:22:33,820 it's very hard to figure out if you 508 00:22:33,820 --> 00:22:37,580 have lots of these specific selects, denies, and column 509 00:22:37,580 --> 00:22:38,080 level. 510 00:22:38,080 --> 00:22:41,220 It just makes it very hard to have an overview of the actual 511 00:22:41,220 --> 00:22:44,180 effective permissions for users. 512 00:22:44,180 --> 00:22:45,700 But it is an option. 513 00:22:45,700 --> 00:22:47,420 If you have the exception, if it's very documented, 514 00:22:47,420 --> 00:22:48,500 you can do that. 515 00:22:48,500 --> 00:22:51,140 The other option is you use encryption. 516 00:22:51,140 --> 00:22:53,580 So yeah, you can allow access to the column. 517 00:22:53,580 --> 00:22:54,380 It's just there. 518 00:22:54,380 --> 00:22:57,660 But the column content is encrypted properly. 519 00:22:57,660 --> 00:22:59,900 The social security number should be encrypted. 520 00:22:59,900 --> 00:23:02,660 I'm not talking about masking, really encryption. 521 00:23:02,660 --> 00:23:05,180 And then you're safe as well. 522 00:23:05,180 --> 00:23:08,100 That's actually really cool, actually. 523 00:23:08,100 --> 00:23:09,940 So I want to make sure I understand something here. 524 00:23:09,940 --> 00:23:13,780 So if I had a table, let's call it x, 525 00:23:13,780 --> 00:23:15,460 and it has three columns, a, b, and c. 526 00:23:15,460 --> 00:23:16,820 I'm not saying this is good practice. 527 00:23:16,820 --> 00:23:17,980 I'm just curious. 528 00:23:17,980 --> 00:23:20,900 So the table x has three columns, a, b, c. 529 00:23:20,900 --> 00:23:26,580 And Mary has basically read access to a and b. 530 00:23:26,580 --> 00:23:30,260 If I denied her access to column c, would she get an error? 531 00:23:30,260 --> 00:23:34,100 Or she just wouldn't even see that c is there? 532 00:23:34,100 --> 00:23:36,180 So in the case of deny on a column level, 533 00:23:36,180 --> 00:23:38,540 you actually do get an error. 534 00:23:38,540 --> 00:23:40,260 OK, let's be precise. 535 00:23:40,260 --> 00:23:41,180 Depends on the query. 536 00:23:41,180 --> 00:23:45,820 If Mary writes a query, select a and b from a table, 537 00:23:45,820 --> 00:23:46,780 whatever. 538 00:23:46,780 --> 00:23:49,460 Well, she's not even mentioning column c. 539 00:23:49,460 --> 00:23:52,260 So she will just see the data from column 1 and b. 540 00:23:52,260 --> 00:23:55,580 But either she says select a, b, and c, 541 00:23:55,580 --> 00:23:58,260 or she says select star from table, 542 00:23:58,260 --> 00:24:00,540 she would get all of the columns. 543 00:24:00,540 --> 00:24:02,060 And in that case, she gets an error 544 00:24:02,060 --> 00:24:03,820 because there is a deny on one column. 545 00:24:03,820 --> 00:24:04,980 She doesn't see anything. 546 00:24:04,980 --> 00:24:06,940 The error was out and doesn't omit columns. 547 00:24:06,940 --> 00:24:09,300 It just errs the whole statement. 548 00:24:09,300 --> 00:24:12,660 Yeah, that's why I asked the question earlier about filtering. 549 00:24:12,660 --> 00:24:13,980 You're going to filter the results, 550 00:24:13,980 --> 00:24:15,140 or you're going to get an error. 551 00:24:15,140 --> 00:24:16,700 So OK, that's interesting. 552 00:24:16,700 --> 00:24:18,620 Again, I suppose it does depend on the query. 553 00:24:18,620 --> 00:24:22,020 If you just select a, b, then you're not going to see column c 554 00:24:22,020 --> 00:24:22,500 at all. 555 00:24:22,500 --> 00:24:24,220 You basically filter yourself already. 556 00:24:24,220 --> 00:24:27,300 That's not good in way by the system to say, hey, there 557 00:24:27,300 --> 00:24:28,140 is a deny. 558 00:24:28,140 --> 00:24:31,460 We just return your still as I said, but omit columns. 559 00:24:31,460 --> 00:24:32,860 No, this does not exist in c. 560 00:24:32,860 --> 00:24:36,100 We always get the same columns that you would expect 561 00:24:36,100 --> 00:24:37,860 or error message. 562 00:24:37,860 --> 00:24:39,700 Also, you need to think about application. 563 00:24:39,700 --> 00:24:41,740 What if we actually would do something like that, 564 00:24:41,740 --> 00:24:44,420 that sometimes it returns three, and sometimes four columns? 565 00:24:44,420 --> 00:24:46,300 How is your application handling that? 566 00:24:46,300 --> 00:24:50,220 So this concept does not exist yet today. 567 00:24:50,220 --> 00:24:56,940 So that's kind of a database schema table column. 568 00:24:56,940 --> 00:25:02,540 And I can obviously filter columns by using views. 569 00:25:02,540 --> 00:25:05,620 So I could actually grant access to a view, 570 00:25:05,620 --> 00:25:09,100 but deny access to the underlying tables. 571 00:25:09,100 --> 00:25:10,660 And can I grant the view? 572 00:25:10,660 --> 00:25:14,540 Can I give the view access to the tables? 573 00:25:14,540 --> 00:25:18,780 Yeah, so let's not deny the table. 574 00:25:18,780 --> 00:25:20,940 Let's just imagine we have two schemas. 575 00:25:20,940 --> 00:25:22,740 We have a schema containing all the tables, 576 00:25:22,740 --> 00:25:25,020 and we have a schema containing all the views. 577 00:25:25,020 --> 00:25:28,060 The one that I bought in five minutes ago. 578 00:25:28,060 --> 00:25:31,860 So in that case, so let's say in this view schema, 579 00:25:31,860 --> 00:25:35,860 you have the view containing only column A and B, A and B, 580 00:25:35,860 --> 00:25:38,220 from the table in the table schema, which actually 581 00:25:38,220 --> 00:25:40,340 has columns A, B, and C. So the view 582 00:25:40,340 --> 00:25:44,980 has selecting select A and B from table x, y, z, 583 00:25:44,980 --> 00:25:47,900 omitting column C. So in this case, 584 00:25:47,900 --> 00:25:50,820 you simply grant the select on the schema that 585 00:25:50,820 --> 00:25:54,420 contains all these views, and then you're done. 586 00:25:54,420 --> 00:25:58,780 So if Mary now queries, tries to query the tables in the table 587 00:25:58,780 --> 00:26:01,340 schema, she will get denied, because I didn't give her 588 00:26:01,340 --> 00:26:03,220 any grant on that table schema. 589 00:26:03,220 --> 00:26:05,220 There's no reason she could access it. 590 00:26:05,220 --> 00:26:10,220 However, if she says select from views schema 591 00:26:10,220 --> 00:26:14,860 dot the name of the view, then she will get the data. 592 00:26:14,860 --> 00:26:17,380 And this is a specialty of SQLSaw. 593 00:26:17,380 --> 00:26:20,860 We have a concept so-called ownership chaining, 594 00:26:20,860 --> 00:26:24,220 as long as the objects in this query, 595 00:26:24,220 --> 00:26:26,780 let's say that the view and one table behind it, 596 00:26:26,780 --> 00:26:30,660 if they have the same owner, it is 597 00:26:30,660 --> 00:26:33,460 sufficient to have the permission 598 00:26:33,460 --> 00:26:35,980 granted on the outermost object, in this case, 599 00:26:35,980 --> 00:26:38,180 the view is the outermost object. 600 00:26:38,180 --> 00:26:40,620 And you will not be checked on the table. 601 00:26:40,620 --> 00:26:42,660 She doesn't have access to the table itself, 602 00:26:42,660 --> 00:26:44,860 but she has access to the view. 603 00:26:44,860 --> 00:26:47,180 And if the view has the same owner as the table, 604 00:26:47,180 --> 00:26:50,580 she can access the data off the table via the view, 605 00:26:50,580 --> 00:26:53,260 but not the table directly. 606 00:26:53,260 --> 00:26:55,820 Even if you were to put a deny on the table, 607 00:26:55,820 --> 00:26:58,180 it would actually be ignored, in this case, 608 00:26:58,180 --> 00:27:01,500 as long as the owners are identical. 609 00:27:01,500 --> 00:27:04,020 It's actually really skipping the permission checking 610 00:27:04,020 --> 00:27:05,020 on the table. 611 00:27:05,020 --> 00:27:06,020 This is actually pretty cool. 612 00:27:06,020 --> 00:27:08,380 You do the same with store procedures, right? 613 00:27:08,380 --> 00:27:10,020 Same as store procedures, yes. 614 00:27:10,020 --> 00:27:11,620 And so one thing I've sort of learned from this, 615 00:27:11,620 --> 00:27:13,820 actually, there's two things I've learned from this so far, 616 00:27:13,820 --> 00:27:16,180 but one of the big ones is basically just 617 00:27:16,180 --> 00:27:21,620 steer away from denies and just use schemas to segregate 618 00:27:21,620 --> 00:27:23,820 your information and then use things like views and store 619 00:27:23,820 --> 00:27:25,820 procedures or whatever to actually access that data. 620 00:27:25,820 --> 00:27:28,940 Where you're not giving directly access, 621 00:27:28,940 --> 00:27:31,220 you're not giving access to the underlying objects directly 622 00:27:31,220 --> 00:27:34,060 to your users. 623 00:27:34,060 --> 00:27:35,780 Is that a relatively common model if you've 624 00:27:35,780 --> 00:27:38,660 got relatively complex systems? 625 00:27:38,660 --> 00:27:40,740 So this is the ideal model. 626 00:27:40,740 --> 00:27:44,100 And I have seen it, and I know many customers do that, 627 00:27:44,100 --> 00:27:46,300 but there's probably the same amount of customers 628 00:27:46,300 --> 00:27:47,380 who don't do it. 629 00:27:47,380 --> 00:27:51,580 So it's a matter of how experienced your folks are 630 00:27:51,580 --> 00:27:57,740 with these concepts, how advanced your whole database 631 00:27:57,740 --> 00:28:01,460 system is if you even need such complex solutions, 632 00:28:01,460 --> 00:28:03,940 but it is really ideal model. 633 00:28:03,940 --> 00:28:05,860 Because in the end, it's the simpler model. 634 00:28:05,860 --> 00:28:08,860 It's simpler to come up with the proper permissions 635 00:28:08,860 --> 00:28:13,220 and to actually grant them in such a model versus a flat model 636 00:28:13,220 --> 00:28:17,020 where you have all the tables mixed with views and procedures 637 00:28:17,020 --> 00:28:20,820 in maybe the worst case, the DBO schema, 638 00:28:20,820 --> 00:28:25,380 or just one or two schemas that don't align at all with security. 639 00:28:25,380 --> 00:28:28,340 Because in that case, you have to, there's two options. 640 00:28:28,340 --> 00:28:31,580 You either have a lot of complex individual permissions 641 00:28:31,580 --> 00:28:34,020 picking a table here, picking out columns there, 642 00:28:34,020 --> 00:28:37,460 picking a view procedure there and denying it there. 643 00:28:37,460 --> 00:28:43,620 Lots of chaotic permissions, the nice mixed with grants 644 00:28:43,620 --> 00:28:46,740 on different levels versus the other one. 645 00:28:46,740 --> 00:28:47,260 Sorry. 646 00:28:47,260 --> 00:28:49,940 And if you don't do that, most people just give up and say, 647 00:28:49,940 --> 00:28:53,620 OK, I just use a general grant on the whole table, 648 00:28:53,620 --> 00:28:55,100 on the whole database. 649 00:28:55,100 --> 00:29:00,980 And yeah, let's just fill it out in the application maybe. 650 00:29:00,980 --> 00:29:04,380 So either it's very complex or it's not very secure. 651 00:29:04,380 --> 00:29:05,300 That's the end story. 652 00:29:05,300 --> 00:29:11,300 If you don't align your database design with security. 653 00:29:11,300 --> 00:29:11,900 Yeah. 654 00:29:11,900 --> 00:29:13,580 It's what you should say filtering out in the client. 655 00:29:13,580 --> 00:29:15,140 I mean, at the end of the day, the client now 656 00:29:15,140 --> 00:29:16,220 has all the data anyway. 657 00:29:16,220 --> 00:29:18,980 So you're sort of defeating the purpose against a, 658 00:29:18,980 --> 00:29:23,460 at least a relatively, well, somewhat skilled attacker. 659 00:29:23,460 --> 00:29:23,860 Yeah. 660 00:29:23,860 --> 00:29:26,740 You're ignoring one potential security 661 00:29:26,740 --> 00:29:27,740 forcing mechanism. 662 00:29:27,740 --> 00:29:30,900 And SQL Server is really strong, really good in that. 663 00:29:30,900 --> 00:29:32,620 If you don't have a grant on an object, 664 00:29:32,620 --> 00:29:34,540 you just don't get the data. 665 00:29:34,540 --> 00:29:36,620 You can't circumvent that. 666 00:29:36,620 --> 00:29:41,940 So we've talked about views, draw procedures, database 667 00:29:41,940 --> 00:29:43,980 schemas, tables, and so on. 668 00:29:43,980 --> 00:29:45,460 So what about row level? 669 00:29:45,460 --> 00:29:49,380 How can I restrict access to specific rows 670 00:29:49,380 --> 00:29:52,380 based on some information? 671 00:29:52,380 --> 00:29:52,660 Yeah. 672 00:29:52,660 --> 00:29:54,780 So for row level, we have a feature that's actually 673 00:29:54,780 --> 00:29:57,180 called row level security. 674 00:29:57,180 --> 00:30:00,660 That is a very nifty way to create a filter query, 675 00:30:00,660 --> 00:30:04,940 the filter clause that behind the scenes filters out 676 00:30:04,940 --> 00:30:09,260 the rows that the user account is not supposed to see. 677 00:30:09,260 --> 00:30:13,060 So you can think of it of a function that is behind the scenes 678 00:30:13,060 --> 00:30:17,340 joint with the table that the user is trying to query. 679 00:30:17,340 --> 00:30:20,980 And based on some attributes of the user account, 680 00:30:20,980 --> 00:30:26,380 you will see either or other rows of the table. 681 00:30:26,380 --> 00:30:29,460 So the attribute or the condition is usually 682 00:30:29,460 --> 00:30:32,740 solved with another helping table where you have maybe 683 00:30:32,740 --> 00:30:37,620 IDs that match to the account is locked in. 684 00:30:37,620 --> 00:30:40,540 So for example, you could have Michael taking 685 00:30:40,540 --> 00:30:42,740 care of specific customer accounts 686 00:30:42,740 --> 00:30:44,740 or the customer accounts have a customer ID. 687 00:30:44,740 --> 00:30:49,460 Michael has a accountant ID or user ID. 688 00:30:49,460 --> 00:30:52,300 And that matches to these bunch of customers. 689 00:30:52,300 --> 00:30:54,020 And then in the filter, you would 690 00:30:54,020 --> 00:30:58,420 be filtered out to see only customers whose ID matches 691 00:30:58,420 --> 00:31:01,980 in the matching table to the Michael's user ID 692 00:31:01,980 --> 00:31:03,860 in such as an error. 693 00:31:03,860 --> 00:31:07,580 So that's a way you can do it behind the scenes. 694 00:31:07,580 --> 00:31:11,300 It has been done before with the means of views. 695 00:31:11,300 --> 00:31:15,060 You can also write your own views and do the same thing. 696 00:31:15,060 --> 00:31:18,860 But views are not as safe as the whole security solution 697 00:31:18,860 --> 00:31:19,900 that we have. 698 00:31:19,900 --> 00:31:22,260 I like to think of row level security 699 00:31:22,260 --> 00:31:27,060 as it doesn't matter how you get to the data that 700 00:31:27,060 --> 00:31:29,140 often sometimes seen it referred to as a predicate 701 00:31:29,140 --> 00:31:32,860 or a function or whatever is going to get called. 702 00:31:32,860 --> 00:31:34,340 So whether you come in through Excel, 703 00:31:34,340 --> 00:31:35,940 let's say you've got a weakness in your permission model 704 00:31:35,940 --> 00:31:38,500 somewhere else and someone manages to connect to the system 705 00:31:38,500 --> 00:31:40,420 using Excel. 706 00:31:40,420 --> 00:31:42,260 That row level security predicate is still 707 00:31:42,260 --> 00:31:44,700 going to get called regardless. 708 00:31:44,700 --> 00:31:46,660 Whereas if you're through a view, 709 00:31:46,660 --> 00:31:49,820 you've got to come through the view to restrict the data. 710 00:31:49,820 --> 00:31:52,900 And if you can't guarantee that, then 711 00:31:52,900 --> 00:31:55,380 that's where the row level security stuff comes into it. 712 00:31:55,380 --> 00:31:59,500 I actually had a health care customer a couple of years ago. 713 00:31:59,500 --> 00:32:02,780 It was really interesting when we're designing their system. 714 00:32:02,780 --> 00:32:05,900 And there's crypto here and crypto there. 715 00:32:05,900 --> 00:32:09,060 But there's also a lot of permissions that came into play. 716 00:32:09,060 --> 00:32:12,860 And one of them was the system had 717 00:32:12,860 --> 00:32:15,740 to have a very, very high level of security 718 00:32:15,740 --> 00:32:18,140 because let's just say medical practitioners 719 00:32:18,140 --> 00:32:20,860 are not going to say what they are because they'll absolutely 720 00:32:20,860 --> 00:32:21,540 give it away. 721 00:32:21,540 --> 00:32:23,260 But medical practitioners had access 722 00:32:23,260 --> 00:32:28,900 to absolutely everybody's health care data going back. 723 00:32:28,900 --> 00:32:32,300 Let's pick a number, 20 years, probably more than that. 724 00:32:32,300 --> 00:32:33,740 Let's just say 20 years. 725 00:32:33,740 --> 00:32:36,580 And one of the reasons why the security bar was so high 726 00:32:36,580 --> 00:32:40,300 is because any practitioner had access to absolutely anything 727 00:32:40,300 --> 00:32:42,380 going back 20 years. 728 00:32:42,380 --> 00:32:44,500 So the decision was made not because we wanted 729 00:32:44,500 --> 00:32:45,820 to reduce the security at all. 730 00:32:45,820 --> 00:32:47,860 We wanted to reduce the risk. 731 00:32:47,860 --> 00:32:51,940 And we put a row level security predicates in place 732 00:32:51,940 --> 00:32:53,460 that basically said that by default, 733 00:32:53,460 --> 00:32:57,140 without some kind of management override, 734 00:32:57,140 --> 00:33:01,620 a practitioner would only have access to 12 months of data. 735 00:33:01,620 --> 00:33:02,980 That's it. 736 00:33:02,980 --> 00:33:06,460 And that actually really made the risk folks 737 00:33:06,460 --> 00:33:10,060 quite happy because it meant that in the case of someone 738 00:33:10,060 --> 00:33:12,980 getting in and deciding to print everyone's data out, 739 00:33:12,980 --> 00:33:14,420 they only had a year's worth of data. 740 00:33:14,420 --> 00:33:15,260 Now, don't get me wrong. 741 00:33:15,260 --> 00:33:18,580 A year's worth of data of medical data still sucks. 742 00:33:18,580 --> 00:33:21,980 But it sucks less than 20 years of medical data, right? 743 00:33:21,980 --> 00:33:26,420 And it was interesting seeing the security guys not so much 744 00:33:26,420 --> 00:33:30,940 pushback on the business, but pushback on the business. 745 00:33:30,940 --> 00:33:33,260 The pushback wasn't, no, we're not doing this. 746 00:33:33,260 --> 00:33:34,860 We're not going to allow this. 747 00:33:34,860 --> 00:33:37,980 The pushback was basically, would you be OK 748 00:33:37,980 --> 00:33:40,220 if you could reduce the amount of data 749 00:33:40,220 --> 00:33:42,540 that you were coughing up? 750 00:33:42,540 --> 00:33:46,380 And then the business was like, yeah, by default, yeah. 751 00:33:46,380 --> 00:33:48,940 And so that made the security guys really happy. 752 00:33:48,940 --> 00:33:50,220 But the only way we actually did that 753 00:33:50,220 --> 00:33:54,020 was through using row level security predicates. 754 00:33:54,020 --> 00:33:56,180 Yeah, so I've actually written a couple of these things. 755 00:33:56,180 --> 00:33:57,460 They're actually relatively straightforward. 756 00:33:57,460 --> 00:34:00,660 They look a lot like writing kind of like a trigger, 757 00:34:00,660 --> 00:34:02,780 that kind of idea, right? 758 00:34:02,780 --> 00:34:06,460 Yeah, like a select trigger sort of logic that runs when 759 00:34:06,460 --> 00:34:08,580 you're selecting it. 760 00:34:08,580 --> 00:34:13,620 OK, so I think we've covered off basically a lot of the core 761 00:34:13,620 --> 00:34:14,620 stuff. 762 00:34:14,620 --> 00:34:17,180 Is there any other sort of topic that you think people don't 763 00:34:17,180 --> 00:34:18,980 know much about or should know about, 764 00:34:18,980 --> 00:34:21,580 or any of your sort of pet features? 765 00:34:21,580 --> 00:34:26,340 So yeah, let's say what I just mentioned in the end, 766 00:34:26,340 --> 00:34:29,540 this ownership chaining, that is a very crucial concept 767 00:34:29,540 --> 00:34:32,620 in Secrecy that can make your life much easier 768 00:34:32,620 --> 00:34:35,580 if you implement your database design 769 00:34:35,580 --> 00:34:37,580 by making use of that. 770 00:34:37,580 --> 00:34:39,140 So that's an important thing to look up. 771 00:34:39,140 --> 00:34:41,140 And I will try to come up with a good link here. 772 00:34:41,140 --> 00:34:43,980 And no, it's not super well documented. 773 00:34:43,980 --> 00:34:47,540 I haven't my plate to do that. 774 00:34:47,540 --> 00:34:49,620 And you should probably be aware that we 775 00:34:49,620 --> 00:34:52,860 have been doing a lot of work in terms of more granular 776 00:34:52,860 --> 00:34:55,220 permissions in Secrecy over lately, 777 00:34:55,220 --> 00:34:57,300 splitting up permissions for objects 778 00:34:57,300 --> 00:35:00,860 into more granular permissions, or this trying to help customers 779 00:35:00,860 --> 00:35:03,020 to come up with just the right permissions 780 00:35:03,020 --> 00:35:09,100 without exposing unnecessary objects or commands, 781 00:35:09,100 --> 00:35:12,020 basically following the principle of least privilege. 782 00:35:12,020 --> 00:35:14,740 So this is something I will put a link out here, 783 00:35:14,740 --> 00:35:17,500 which new permissions we have come up with, 784 00:35:17,500 --> 00:35:19,780 and a bunch of new server roles, not database roles, 785 00:35:19,780 --> 00:35:21,740 but on the server level, roles that 786 00:35:21,740 --> 00:35:25,060 should help to by default or by built-in roles 787 00:35:25,060 --> 00:35:27,900 to support customer to easier or just pick the right role, 788 00:35:27,900 --> 00:35:30,140 without having to think too much about the permission 789 00:35:30,140 --> 00:35:32,700 system and which permissions to pick. 790 00:35:32,700 --> 00:35:35,860 So those links I will definitely put in. 791 00:35:35,860 --> 00:35:37,660 So on the chaining stuff, I just want 792 00:35:37,660 --> 00:35:38,780 to make sure I get this right. 793 00:35:38,780 --> 00:35:40,820 So this is where, in practical terms, 794 00:35:40,820 --> 00:35:44,780 where I might not have access to a resource, like a table. 795 00:35:44,780 --> 00:35:48,180 But if I call something or call something like a view, 796 00:35:48,180 --> 00:35:51,380 that view has access, even though I don't have access. 797 00:35:51,380 --> 00:35:52,460 Is that it? 798 00:35:52,460 --> 00:35:56,700 Yeah, you need to have access to the view. 799 00:35:56,700 --> 00:35:59,500 So somebody will grant you a select on the view. 800 00:35:59,500 --> 00:36:01,700 And this somebody also needs to understand now 801 00:36:01,700 --> 00:36:03,620 who the view is, the owner of the view, 802 00:36:03,620 --> 00:36:05,260 and who's the owner of the table. 803 00:36:05,260 --> 00:36:08,020 And the owner is something that's in the metadata. 804 00:36:08,020 --> 00:36:11,460 It's not like a logical thing. 805 00:36:11,460 --> 00:36:12,500 It's actually written down. 806 00:36:12,500 --> 00:36:14,860 So if, in the moment, you create a table on a view, 807 00:36:14,860 --> 00:36:17,900 the ownership is fixed, it's either you who creates that, 808 00:36:17,900 --> 00:36:21,700 or you explicitly pick an account who this belongs to. 809 00:36:21,700 --> 00:36:24,260 So let's say you're Fred, and you 810 00:36:24,260 --> 00:36:27,540 made sure that you create a table on the Fred ownership, 811 00:36:27,540 --> 00:36:30,780 and you create a view on a Fred ownership, 812 00:36:30,780 --> 00:36:32,500 then the owners are equal. 813 00:36:32,500 --> 00:36:35,540 That means if you now grant select to Michael 814 00:36:35,540 --> 00:36:40,740 on your Fred's view, Michael can access the table's data 815 00:36:40,740 --> 00:36:42,220 via the view. 816 00:36:42,220 --> 00:36:45,140 It doesn't need any additional permission. 817 00:36:45,140 --> 00:36:48,460 However, if Michael tries to access the table itself, 818 00:36:48,460 --> 00:36:51,300 it's owned by Fred as well, but Fred didn't grant access 819 00:36:51,300 --> 00:36:53,980 to the table, he will be denied. 820 00:36:53,980 --> 00:36:59,420 As long as soon as the table belongs to Ted, not Fred anymore, 821 00:36:59,420 --> 00:37:01,140 that concept will break. 822 00:37:01,140 --> 00:37:03,500 Then you can query your view as much as you like, 823 00:37:03,500 --> 00:37:06,660 we just get an error message, because Ted did not 824 00:37:06,660 --> 00:37:07,700 grant you permission. 825 00:37:07,700 --> 00:37:11,700 Fred granted you permission on the view, but Fred is not Ted. 826 00:37:11,700 --> 00:37:13,660 So that's why ownership change really 827 00:37:13,660 --> 00:37:17,300 requires to be the same owner in place. 828 00:37:17,300 --> 00:37:18,900 OK, that's really interesting, I think. 829 00:37:18,900 --> 00:37:20,500 Yeah, that's something that I'm definitely 830 00:37:20,500 --> 00:37:23,420 going to have to read up on. 831 00:37:23,420 --> 00:37:25,020 Yeah, because that sounds super powerful, 832 00:37:25,020 --> 00:37:26,380 but I think also at the same time, 833 00:37:26,380 --> 00:37:29,020 if you don't know how it works, you could either A, not use it 834 00:37:29,020 --> 00:37:32,420 properly, and B, things might not work the way 835 00:37:32,420 --> 00:37:33,620 you expect them to work. 836 00:37:33,620 --> 00:37:36,020 So yeah, I need to learn a little bit more about that. 837 00:37:36,020 --> 00:37:37,460 Any other pet projects? 838 00:37:37,460 --> 00:37:40,980 Do you want to just explain briefly some of these more 839 00:37:40,980 --> 00:37:43,900 granular permissions you're talking about? 840 00:37:43,900 --> 00:37:45,940 Yeah, so let's talk about that. 841 00:37:45,940 --> 00:37:50,540 So one of the highlights, or one of the most 842 00:37:50,540 --> 00:37:54,860 elegant concepts of SQL Soap permission system 843 00:37:54,860 --> 00:37:57,180 is it's very hierarchical. 844 00:37:57,180 --> 00:38:00,260 You have a database, you have a schema, you have a table, 845 00:38:00,260 --> 00:38:03,420 and we have lots of permissions that behave hierarchically. 846 00:38:03,420 --> 00:38:05,660 So you can have an auto on a database, 847 00:38:05,660 --> 00:38:07,100 auto database permission. 848 00:38:07,100 --> 00:38:08,340 There's auto database permission, 849 00:38:08,340 --> 00:38:10,460 since it's on the highest one. 850 00:38:10,460 --> 00:38:13,380 It's excluded to the server for now. 851 00:38:13,380 --> 00:38:15,540 Grants you access to anything below. 852 00:38:15,540 --> 00:38:18,740 So if you have auto database, you also 853 00:38:18,740 --> 00:38:21,940 have auto schema, auto table, you can do anything. 854 00:38:21,940 --> 00:38:23,980 That's because of this hierarchical concept 855 00:38:23,980 --> 00:38:27,380 that it inherits down to whichever permission fits. 856 00:38:27,380 --> 00:38:30,340 And auto is very powerful, which is why you get all these auto 857 00:38:30,340 --> 00:38:32,180 permissions on the other levels. 858 00:38:32,180 --> 00:38:33,620 If you have select on the database, 859 00:38:33,620 --> 00:38:37,460 you also get select on the schemas and tables, obviously. 860 00:38:37,460 --> 00:38:40,500 So this is very powerful on one hand. 861 00:38:40,500 --> 00:38:44,620 On the other hand, it somehow makes it difficult to come up 862 00:38:44,620 --> 00:38:49,060 with the principle of these privilege approach. 863 00:38:49,060 --> 00:38:50,740 Because in the principle of these privilege, 864 00:38:50,740 --> 00:38:54,620 you want to grant specific permissions, but not other ones. 865 00:38:54,620 --> 00:38:57,180 You don't like to have automatically 866 00:38:57,180 --> 00:39:01,900 granted other permissions or other options, other commands 867 00:39:01,900 --> 00:39:04,340 beside the one that you actually need. 868 00:39:04,340 --> 00:39:06,580 So this is the area where we're trying to improve. 869 00:39:06,580 --> 00:39:10,140 So I'm going away from this hierarchy model, 870 00:39:10,140 --> 00:39:11,580 because this is one of the downside. 871 00:39:11,580 --> 00:39:13,660 It's very nice, very elegant. 872 00:39:13,660 --> 00:39:17,740 But it doesn't help so much the principle of these privilege. 873 00:39:17,740 --> 00:39:20,940 This approach, so we are coming all new permissions 874 00:39:20,940 --> 00:39:22,980 that you will see from now on, basically, 875 00:39:22,980 --> 00:39:27,100 you can expect to be flat on a flat hierarchy directly 876 00:39:27,100 --> 00:39:28,060 under control. 877 00:39:28,060 --> 00:39:30,060 Control is always the highest permission on the server, 878 00:39:30,060 --> 00:39:31,380 on the database. 879 00:39:31,380 --> 00:39:34,580 So new permissions will be directly under control, 880 00:39:34,580 --> 00:39:37,820 not under auto and then schema and so on. 881 00:39:37,820 --> 00:39:39,900 They will be directly under control, 882 00:39:39,900 --> 00:39:43,060 which means you can pick them individually 883 00:39:43,060 --> 00:39:47,100 without granting other permissions at the same time. 884 00:39:47,100 --> 00:39:49,940 So this is the general strategy from now on. 885 00:39:49,940 --> 00:39:52,540 And that's going to simplify things, right? 886 00:39:52,540 --> 00:39:55,540 It's going to simplify things in terms of permissions, 887 00:39:55,540 --> 00:39:57,540 just picking the right ones. 888 00:39:57,540 --> 00:40:00,940 I have to admit, it will not make it much more nice 889 00:40:00,940 --> 00:40:03,100 if you want to plot permissions on a diagram, 890 00:40:03,100 --> 00:40:08,580 because the diagram will not be a fancy multi-level tree 891 00:40:08,580 --> 00:40:09,540 diagram anymore. 892 00:40:09,540 --> 00:40:12,820 As it would look today, it would be just a flat list. 893 00:40:12,820 --> 00:40:16,220 You've also got permissions at the control plane, right? 894 00:40:16,220 --> 00:40:18,380 We've mainly been talking about data plane stuff. 895 00:40:18,380 --> 00:40:19,780 What about control plane? 896 00:40:19,780 --> 00:40:20,780 Right. 897 00:40:20,780 --> 00:40:24,060 So this is a big, totally different area. 898 00:40:24,060 --> 00:40:26,820 So let's just say, what are we talking about here? 899 00:40:26,820 --> 00:40:29,420 So what we've just talked about was data plane in terms 900 00:40:29,420 --> 00:40:34,300 of what's inside the database inside a server 901 00:40:34,300 --> 00:40:36,420 in terms of Azure concepts. 902 00:40:36,420 --> 00:40:38,500 So in Azure, we have this differentiation 903 00:40:38,500 --> 00:40:40,340 between the control plane and the data plane, 904 00:40:40,340 --> 00:40:44,460 the data plane being anything below a database normally. 905 00:40:44,460 --> 00:40:46,340 Now a few edge cases. 906 00:40:46,340 --> 00:40:48,260 And the control plane is basically 907 00:40:48,260 --> 00:40:50,740 what you can see in the Azure portal. 908 00:40:50,740 --> 00:40:53,900 If you open the Azure portal, go to Azure SQL Database 909 00:40:53,900 --> 00:40:54,740 Managed Instance. 910 00:40:54,740 --> 00:40:58,420 You see certain things like creating replicas, 911 00:40:58,420 --> 00:41:00,020 creating backups. 912 00:41:00,020 --> 00:41:03,660 But you don't see things like create database user 913 00:41:03,660 --> 00:41:05,820 or grant access to table, because those 914 00:41:05,820 --> 00:41:08,820 are data plane permissions. 915 00:41:08,820 --> 00:41:10,820 These are the ones that we just talked about. 916 00:41:10,820 --> 00:41:14,340 For the control plane activities, 917 00:41:14,340 --> 00:41:17,300 like creating a new server or creating a new database 918 00:41:17,300 --> 00:41:21,740 in Azure SQL Database, you need a different permission 919 00:41:21,740 --> 00:41:22,300 system. 920 00:41:22,300 --> 00:41:24,420 You need to use a different permission system, which 921 00:41:24,420 --> 00:41:29,100 is the Azure IAM arm bank model today. 922 00:41:29,100 --> 00:41:31,300 And we are also working with Purview 923 00:41:31,300 --> 00:41:37,980 to bring an alternative to grant access to data plane 924 00:41:37,980 --> 00:41:41,780 permissions via Purview across multiple servers. 925 00:41:41,780 --> 00:41:44,020 Because this control plane permission system really 926 00:41:44,020 --> 00:41:47,900 works on the whole hierarchy of your Azure subscription. 927 00:41:47,900 --> 00:41:50,900 So you can grant a permission, for example, 928 00:41:50,900 --> 00:41:56,780 to create many servers in your whole subscription. 929 00:41:56,780 --> 00:41:59,460 But you can't create a permission to access tables 930 00:41:59,460 --> 00:42:02,380 in all of these servers, because that is data plane. 931 00:42:02,380 --> 00:42:05,140 And in Purview, we are closing this gap 932 00:42:05,140 --> 00:42:08,700 to have the ability to use the control plane view, 933 00:42:08,700 --> 00:42:10,900 this overview over the whole subscription, 934 00:42:10,900 --> 00:42:15,260 your enterprise objects, and then to grant actually 935 00:42:15,260 --> 00:42:19,420 data plane permissions for all of these servers 936 00:42:19,420 --> 00:42:26,340 in your subscription or resource group at once. 937 00:42:26,340 --> 00:42:26,860 Very cool. 938 00:42:26,860 --> 00:42:29,100 Very cool. 939 00:42:29,100 --> 00:42:32,020 Anything else you want to cover, or do you want to start 940 00:42:32,020 --> 00:42:33,540 just wrapping this thing up? 941 00:42:33,540 --> 00:42:35,940 I think if I would be a listener, I think I would be. 942 00:42:35,940 --> 00:42:38,740 I feel this was pretty deep. 943 00:42:38,740 --> 00:42:40,140 If you're interested in more, we'd 944 00:42:40,140 --> 00:42:41,340 happy to have another one. 945 00:42:41,340 --> 00:42:44,340 But I think as far as it goes to data plane permissions, 946 00:42:44,340 --> 00:42:45,500 we have gone pretty deep. 947 00:42:45,500 --> 00:42:48,060 That just allowed the audience to follow that 948 00:42:48,060 --> 00:42:51,820 and walk through that with the links that I will provide. 949 00:42:51,820 --> 00:42:52,980 I learned a great deal. 950 00:42:52,980 --> 00:42:54,980 I mean, one thing that I definitely, definitely 951 00:42:54,980 --> 00:42:57,260 took away from this, one thing that's really important 952 00:42:57,260 --> 00:42:58,900 is the value of schemers. 953 00:42:58,900 --> 00:43:00,620 I always knew that they're important, 954 00:43:00,620 --> 00:43:03,500 but I never really realized how you can model things 955 00:43:03,500 --> 00:43:05,180 around schemers. 956 00:43:05,180 --> 00:43:06,900 And I think from a security standpoint, 957 00:43:06,900 --> 00:43:09,140 access control from a standpoint, on the data plane, 958 00:43:09,140 --> 00:43:11,100 I think it's critically important. 959 00:43:11,100 --> 00:43:15,460 So I'm really glad that you sort of stress that in this talk. 960 00:43:15,460 --> 00:43:18,780 All right, so one thing we always ask all our guests 961 00:43:18,780 --> 00:43:21,260 is if they had just one little thought 962 00:43:21,260 --> 00:43:23,780 to leave our listeners with, what would it be? 963 00:43:23,780 --> 00:43:28,940 All right, so that goes back the way that I had explained 964 00:43:28,940 --> 00:43:32,220 creating the right schemas for your tables. 965 00:43:32,220 --> 00:43:35,620 The essence of that is that I would urge everyone 966 00:43:35,620 --> 00:43:37,820 working with database to make sure 967 00:43:37,820 --> 00:43:41,020 that the designers of your database, the engineers, 968 00:43:41,020 --> 00:43:44,780 do take into account security from the very start 969 00:43:44,780 --> 00:43:48,500 that people who design databases need 970 00:43:48,500 --> 00:43:52,660 to understand the access patterns in terms of which 971 00:43:52,660 --> 00:43:55,580 application, which group of users 972 00:43:55,580 --> 00:44:00,940 will access which objects in terms of general terms. 973 00:44:00,940 --> 00:44:02,980 There will always be some exceptions, 974 00:44:02,980 --> 00:44:05,020 but there are always typical patterns. 975 00:44:05,020 --> 00:44:10,420 And those patterns, they should be part of the architecture 976 00:44:10,420 --> 00:44:13,660 of the database, maybe even split databases. 977 00:44:13,660 --> 00:44:16,340 So for example, don't get me wrong here, Michael. 978 00:44:16,340 --> 00:44:20,980 You came pretty late in that project with these patients. 979 00:44:20,980 --> 00:44:22,860 But normally, I would expect a database 980 00:44:22,860 --> 00:44:25,660 where you want to limit what was it? 981 00:44:25,660 --> 00:44:28,900 Hascair providers to access only one year of data 982 00:44:28,900 --> 00:44:32,140 to actually not even have all these years in one database. 983 00:44:32,140 --> 00:44:34,380 So I would maybe have a database per year, 984 00:44:34,380 --> 00:44:38,180 and then it would be so simple to just say, well, 985 00:44:38,180 --> 00:44:40,220 you only have access to this database. 986 00:44:40,220 --> 00:44:43,020 You wouldn't need to revert to a low-level security 987 00:44:43,020 --> 00:44:45,780 even to filter out the other databases. 988 00:44:45,780 --> 00:44:48,700 So if you think about security from the very start, 989 00:44:48,700 --> 00:44:50,940 you create the right schemas, put your objects 990 00:44:50,940 --> 00:44:54,380 in these right schemas, and create the right accessing 991 00:44:54,380 --> 00:44:57,380 objects, like the views or procedures, 992 00:44:57,380 --> 00:45:00,220 then you will have an easy life coming up 993 00:45:00,220 --> 00:45:02,540 as the right permissions data on. 994 00:45:02,540 --> 00:45:03,300 Now, thanks for that. 995 00:45:03,300 --> 00:45:05,100 I really appreciate it. 996 00:45:05,100 --> 00:45:06,780 Yeah, and the split database idea, 997 00:45:06,780 --> 00:45:10,500 it's an even better idea because you've got absolutely 998 00:45:10,500 --> 00:45:14,580 physical separation of the data as well, which is awesome. 999 00:45:14,580 --> 00:45:17,140 So hey, Andreas, thank you so much for joining us this week. 1000 00:45:17,140 --> 00:45:21,260 Again, I realized this was a one-off special episode, 1001 00:45:21,260 --> 00:45:24,060 just to focus on one very specific topic. 1002 00:45:24,060 --> 00:45:26,020 I really appreciate you taking the time 1003 00:45:26,020 --> 00:45:28,420 to come on the podcast. 1004 00:45:28,420 --> 00:45:29,980 And to all our listeners out there, 1005 00:45:29,980 --> 00:45:31,220 thank you so much for listening. 1006 00:45:31,220 --> 00:45:32,540 And if you have any other scenarios 1007 00:45:32,540 --> 00:45:36,700 you'd like to sort of pose to Andreas or any of our other 1008 00:45:36,700 --> 00:45:38,180 products, especially the database products, 1009 00:45:38,180 --> 00:45:40,300 because now that I'm in that group, 1010 00:45:40,300 --> 00:45:43,900 I can get appropriate folks to talk about the area of expertise. 1011 00:45:43,900 --> 00:45:46,740 I see Andreas touched on Perview. 1012 00:45:46,740 --> 00:45:49,500 So perhaps we talk about Perview in depth as well. 1013 00:45:49,500 --> 00:45:51,620 So again, thank you so much for joining us this week. 1014 00:45:51,620 --> 00:45:53,340 Take care, and we'll see you next time. 1015 00:45:53,340 --> 00:45:56,220 Thanks for listening to the Azure Security Podcast. 1016 00:45:56,220 --> 00:45:59,980 You can find show notes and other resources at our website, 1017 00:45:59,980 --> 00:46:03,020 azsecuritypodcast.net. 1018 00:46:03,020 --> 00:46:05,300 If you have any questions, please find us 1019 00:46:05,300 --> 00:46:07,780 on Twitter at Azure Setpod. 1020 00:46:07,780 --> 00:46:11,540 Background music is from ccmixter.com and licensed 1021 00:46:11,540 --> 00:46:23,700 under the Creative Commons license.