1 00:00:00,400 --> 00:00:01,660 This video covers database authorization. 2 00:00:02,800 --> 00:00:03,970 As we'll see its a relatively straight 3 00:00:04,280 --> 00:00:06,240 forward topic, but it is a very important one. 4 00:00:07,110 --> 00:00:08,230 Authorization has to do 5 00:00:08,350 --> 00:00:09,530 with first making sure that 6 00:00:09,670 --> 00:00:12,090 users only see the data that they're supposed to see. 7 00:00:12,800 --> 00:00:14,250 And second, guarding the database 8 00:00:14,740 --> 00:00:16,910 from being modified by malicious users. 9 00:00:17,900 --> 00:00:18,690 Now, one thing that we're not 10 00:00:19,060 --> 00:00:20,410 covering in this video are 11 00:00:20,580 --> 00:00:22,310 system or programming security issues, 12 00:00:23,060 --> 00:00:24,860 such as SQL injection errors, what 13 00:00:25,000 --> 00:00:26,050 we are focusing on is security 14 00:00:26,710 --> 00:00:27,630 of the data access itself. 15 00:00:28,540 --> 00:00:30,320 So the way database authorization work 16 00:00:30,620 --> 00:00:31,610 is that users of the database 17 00:00:32,080 --> 00:00:33,730 have specific privileges, and then 18 00:00:33,860 --> 00:00:35,140 they can only operate on data 19 00:00:35,410 --> 00:00:37,040 for which they're authorized through those privileges. 20 00:00:38,310 --> 00:00:39,690 So it's similar to file 21 00:00:40,050 --> 00:00:41,760 system, for example, privileges and 22 00:00:41,860 --> 00:00:44,290 authorization, except that 23 00:00:44,470 --> 00:00:45,770 it is specific to the database constructs, 24 00:00:46,480 --> 00:00:47,600 database contents, and tends to 25 00:00:48,160 --> 00:00:50,700 be more fine grained access than we see with file systems. 26 00:00:51,620 --> 00:00:53,530 Specifically, for databases that 27 00:00:53,770 --> 00:00:55,270 privileges that are possible are 28 00:00:55,730 --> 00:00:57,360 on a particular relation to 29 00:00:57,560 --> 00:00:58,520 select the data for that 30 00:00:58,730 --> 00:00:59,890 relation or maybe just select 31 00:01:00,080 --> 00:01:01,930 the specific attributes, so that's read privileges. 32 00:01:02,990 --> 00:01:04,200 As far as write privileges or 33 00:01:04,250 --> 00:01:05,770 modifications, we can set 34 00:01:05,960 --> 00:01:06,880 up the privilege to insert a 35 00:01:06,980 --> 00:01:08,980 non-relation or even insert specific 36 00:01:09,600 --> 00:01:11,090 attributesonly of a relation. 37 00:01:11,680 --> 00:01:13,700 Most SQL implementations do allow 38 00:01:14,030 --> 00:01:16,430 you to insert data with only specific attributes specified. 39 00:01:17,590 --> 00:01:18,800 We can have privileges to update 40 00:01:19,290 --> 00:01:20,600 a relation or update specific attributes, 41 00:01:21,220 --> 00:01:23,640 and finally the privilege to delete from a relation. 42 00:01:24,840 --> 00:01:25,850 So let's go straight to some examples. 43 00:01:26,910 --> 00:01:28,340 We'll be using the standard college 44 00:01:28,820 --> 00:01:30,890 admission sample database that we've used in other videos. 45 00:01:31,460 --> 00:01:32,720 The schema is shown here at the bottom. 46 00:01:33,690 --> 00:01:34,550 Let's suppose we have a 47 00:01:34,650 --> 00:01:37,170 user who wants to issue the update command shown. 48 00:01:37,910 --> 00:01:39,410 They want to find students 49 00:01:40,250 --> 00:01:41,510 whose GPA is greater than 50 00:01:41,700 --> 00:01:43,300 3.9, and if those students have 51 00:01:43,410 --> 00:01:44,880 applied anywhere, they want 52 00:01:45,120 --> 00:01:46,430 to update the application record 53 00:01:46,920 --> 00:01:48,340 and set the decision to be yes. 54 00:01:49,170 --> 00:01:49,970 So, let's look at what 55 00:01:50,360 --> 00:01:51,380 privileges would be needed 56 00:01:51,680 --> 00:01:53,070 by the user to execute this command. 57 00:01:53,880 --> 00:01:54,780 So, clearly, we're going to 58 00:01:54,890 --> 00:01:56,250 have to have some privileges on the 59 00:01:56,320 --> 00:01:59,040 Apply relation and some privileges on the Student relation. 60 00:02:00,190 --> 00:02:01,840 In the Apply relation, they're going 61 00:02:02,100 --> 00:02:03,160 to need to be able to 62 00:02:03,380 --> 00:02:05,260 update the decision attribute, but 63 00:02:05,410 --> 00:02:07,290 there's no other update privileges that are needed. 64 00:02:08,390 --> 00:02:09,970 In terms of select privileges 65 00:02:10,650 --> 00:02:11,770 or reading the Apply relation, 66 00:02:12,590 --> 00:02:13,700 the only attribute that's being 67 00:02:14,080 --> 00:02:16,170 read here is the student ID so, that's what they need.For 68 00:02:17,340 --> 00:02:19,230 the student relation, they're going 69 00:02:19,490 --> 00:02:20,450 to need to read the GPA, 70 00:02:21,370 --> 00:02:22,370 as well as the student ID, 71 00:02:22,790 --> 00:02:24,280 so the privilege needed there is 72 00:02:24,520 --> 00:02:26,730 the select privilege over 73 00:02:27,120 --> 00:02:28,530 the student ID and the GPA. 74 00:02:29,450 --> 00:02:30,710 So with this set of privileges the 75 00:02:30,810 --> 00:02:32,850 user would be allowed to execute this operation. 76 00:02:34,480 --> 00:02:36,140 In our next example, suppose the 77 00:02:36,320 --> 00:02:38,980 user wants to delete all students who haven't applied anywhere. 78 00:02:39,550 --> 00:02:40,600 So they're deleting from the student 79 00:02:40,910 --> 00:02:42,140 relation where the student ID 80 00:02:42,730 --> 00:02:43,640 is not in the set 81 00:02:43,840 --> 00:02:45,320 of student IDs in the applied relation. 82 00:02:46,130 --> 00:02:47,320 So, for this one again, they'll 83 00:02:47,450 --> 00:02:48,540 need privileges on the student 84 00:02:48,930 --> 00:02:50,840 relation and on the apply relation. 85 00:02:51,830 --> 00:02:52,970 On the student relation, the user 86 00:02:53,200 --> 00:02:54,450 would need the delete privilege and 87 00:02:55,060 --> 00:02:56,580 delete never has attributes associated 88 00:02:57,200 --> 00:02:58,920 with it because you are always deleting entire tuples. 89 00:02:58,990 --> 00:03:00,930 The only tuple that's 90 00:03:01,120 --> 00:03:02,520 actually, the only attribute that's 91 00:03:02,650 --> 00:03:03,910 actually being read from the 92 00:03:03,980 --> 00:03:05,660 student relation is the Student ID. 93 00:03:06,130 --> 00:03:07,260 So, the user would also need 94 00:03:07,970 --> 00:03:10,230 the select privilege on Student ID. 95 00:03:11,210 --> 00:03:12,390 And, then in the applied A relation, 96 00:03:12,730 --> 00:03:14,390 again only the student ID is being read. 97 00:03:14,610 --> 00:03:15,830 So the user would need the 98 00:03:15,900 --> 00:03:18,730 select privilege on apply of the student ID. 99 00:03:19,080 --> 00:03:20,860 And with these the user 100 00:03:21,280 --> 00:03:22,640 will be permitted to execute this operation. 101 00:03:24,010 --> 00:03:26,370 So far so good, but now let's introduce a little twist. 102 00:03:27,000 --> 00:03:27,820 Let's suppose that we have 103 00:03:28,060 --> 00:03:29,010 a user that we want to 104 00:03:29,370 --> 00:03:31,130 authorize to access information in 105 00:03:31,320 --> 00:03:32,940 the student relation, but only 106 00:03:33,380 --> 00:03:34,910 for students who have applied to Stanford. 107 00:03:36,040 --> 00:03:37,420 How can we possibly do that? 108 00:03:38,650 --> 00:03:40,090 Why don't you give that a thought. 109 00:03:40,110 --> 00:03:41,480 Well, I'll give you the answer right away. 110 00:03:42,140 --> 00:03:44,210 The way we do that is actually by using views. 111 00:03:45,250 --> 00:03:46,460 So we can create a view 112 00:03:46,920 --> 00:03:48,200 that gives us the student 113 00:03:48,520 --> 00:03:50,200 information for Stanford applicants only. 114 00:03:50,880 --> 00:03:52,030 Then we can grant users 115 00:03:52,920 --> 00:03:55,550 privileges on the view, rather than directly on the relations. 116 00:03:56,370 --> 00:03:56,950 So here's our view. 117 00:03:57,110 --> 00:03:58,290 It says we'll find all students 118 00:03:58,720 --> 00:04:00,250 where their student is in 119 00:04:00,690 --> 00:04:01,590 the IDs of the students 120 00:04:01,980 --> 00:04:03,690 who have applied to Stanford and 121 00:04:03,880 --> 00:04:06,200 we'll call that view SS, for Stanford students. 122 00:04:07,390 --> 00:04:08,940 Now we can give a 123 00:04:08,960 --> 00:04:10,130 particular user the select 124 00:04:10,750 --> 00:04:11,680 privilege on SS. 125 00:04:12,650 --> 00:04:13,750 And that allows them to select 126 00:04:13,990 --> 00:04:15,130 the data that's in the view, 127 00:04:15,570 --> 00:04:17,680 but not see any of the data outside of the view. 128 00:04:17,920 --> 00:04:19,070 Specifically, they won't be able 129 00:04:19,270 --> 00:04:21,840 to see student information if the students didn't apply to Stanford. 130 00:04:23,080 --> 00:04:25,360 Let's take a look at a second example that involves views. 131 00:04:26,000 --> 00:04:26,810 Let's suppose that we want 132 00:04:27,190 --> 00:04:28,270 to authorize a user to 133 00:04:28,420 --> 00:04:29,940 delete applications, but, only 134 00:04:30,370 --> 00:04:31,440 applications that are to Berkley. 135 00:04:32,500 --> 00:04:33,770 So, again, we'll set up a view. 136 00:04:34,170 --> 00:04:34,720 This one's a little simpler. 137 00:04:35,160 --> 00:04:36,360 It's just the view of the 138 00:04:36,680 --> 00:04:37,870 app, of the records of 139 00:04:37,950 --> 00:04:39,780 the apply relation where the college name is Berkeley. 140 00:04:40,130 --> 00:04:41,500 And we'll call the view BA. 141 00:04:42,850 --> 00:04:43,760 And then what we want to 142 00:04:44,010 --> 00:04:45,300 grant to the user is the 143 00:04:45,460 --> 00:04:46,860 ability to delete the delete 144 00:04:47,320 --> 00:04:48,900 privilege from the B A view. 145 00:04:49,580 --> 00:04:50,570 Now in this case we 146 00:04:50,730 --> 00:04:51,850 do need to have that view 147 00:04:52,280 --> 00:04:54,930 be updatable by the system that's supporting it. 148 00:04:55,120 --> 00:04:58,040 So, in our video about views, we discuss this issue. 149 00:04:59,370 --> 00:05:00,410 Those are the only examples I'm going 150 00:05:00,600 --> 00:05:01,380 to give for now, but I do 151 00:05:01,810 --> 00:05:02,910 want to emphasize that views are 152 00:05:03,180 --> 00:05:05,020 quite important for customizing authorization 153 00:05:06,200 --> 00:05:07,270 to specific user needs. 154 00:05:07,870 --> 00:05:09,290 And in fact authorization is one 155 00:05:09,480 --> 00:05:11,930 of the most important uses of views in database systems. 156 00:05:13,350 --> 00:05:14,950 Now let's look how privileges are obtained. 157 00:05:15,890 --> 00:05:17,460 When a relation is created, the 158 00:05:17,630 --> 00:05:19,540 creator of that relation becomes the owner of the relation. 159 00:05:20,100 --> 00:05:20,990 And the owner of the relation 160 00:05:21,450 --> 00:05:23,560 has all privileges, and furthermore, 161 00:05:23,760 --> 00:05:25,520 may grant privileges to other users. 162 00:05:26,580 --> 00:05:27,960 So there's a grant statement in 163 00:05:28,080 --> 00:05:29,800 the sequel standard and it looks like this. 164 00:05:30,900 --> 00:05:32,680 We grant privileges on a 165 00:05:32,900 --> 00:05:34,320 particular relation to one or 166 00:05:34,500 --> 00:05:35,500 more users and we have the 167 00:05:35,810 --> 00:05:36,720 option of allowing those users 168 00:05:37,130 --> 00:05:38,590 to further grant privileges to others. 169 00:05:39,440 --> 00:05:41,380 Specifically, the privileges themselves are 170 00:05:41,510 --> 00:05:43,030 the ones that we defined earlier. 171 00:05:43,380 --> 00:05:45,150 And we can have a comma-separated list of them. 172 00:05:45,390 --> 00:05:46,610 So for example, we could say 173 00:05:46,890 --> 00:05:48,410 here something like "select student 174 00:05:48,870 --> 00:05:51,630 ID, comma, delete," and that would give those two privileges. 175 00:05:52,750 --> 00:05:54,150 The users are a list 176 00:05:54,280 --> 00:05:56,170 of actual user names on the data base. 177 00:05:56,420 --> 00:05:58,220 There's also the user, pre-defined 178 00:05:58,760 --> 00:06:00,140 user, called "public" and that 179 00:06:00,290 --> 00:06:02,600 would grant the authorization to any user of the database. 180 00:06:04,040 --> 00:06:05,600 And finally, the grant option 181 00:06:06,300 --> 00:06:07,860 allows the users who 182 00:06:07,960 --> 00:06:09,570 are getting the privileges to grant 183 00:06:10,200 --> 00:06:13,900 the same or lesser privileges to other users. 184 00:06:15,620 --> 00:06:16,550 Now what do I mean by lesser? 185 00:06:16,870 --> 00:06:17,620 Well it's pretty simple. 186 00:06:17,940 --> 00:06:20,310 If we have, say select and 187 00:06:21,140 --> 00:06:21,980 attributes ABC. 188 00:06:22,810 --> 00:06:24,720 then a lesser privilege would 189 00:06:24,910 --> 00:06:26,730 be something like select A and 190 00:06:26,880 --> 00:06:28,930 B. Now, how about the revoking of privileges. 191 00:06:29,560 --> 00:06:30,420 This gets a little more interesting. 192 00:06:30,850 --> 00:06:32,150 So the command is "revoke privileges." 193 00:06:32,680 --> 00:06:33,410 Again, it would be a list 194 00:06:33,630 --> 00:06:35,240 of privileges on a particular 195 00:06:35,440 --> 00:06:36,890 relation from users, and 196 00:06:37,070 --> 00:06:37,810 again that would be a list of 197 00:06:37,890 --> 00:06:38,680 user names with the possibility 198 00:06:39,230 --> 00:06:40,470 of the special name public 199 00:06:41,070 --> 00:06:42,130 and then there are two 200 00:06:42,530 --> 00:06:45,580 option for revoking privileges, called cascade and restrict. 201 00:06:46,620 --> 00:06:47,390 And they may have to do 202 00:06:47,530 --> 00:06:49,500 with what happens when privileges 203 00:06:50,210 --> 00:06:51,350 are being revoked from a user 204 00:06:51,820 --> 00:06:53,300 who was granted the ability to 205 00:06:53,580 --> 00:06:54,870 grant privileges to others. 206 00:06:55,580 --> 00:06:58,070 So, let's take a look pictorially at what can happen. 207 00:06:59,000 --> 00:06:59,990 Let's suppose that we have 208 00:07:00,240 --> 00:07:01,550 a user who has a privilege 209 00:07:01,930 --> 00:07:03,250 to say select on a 210 00:07:03,480 --> 00:07:04,860 particular relation, R, and we'll 211 00:07:05,130 --> 00:07:06,660 draw that is the root of a graph. 212 00:07:07,280 --> 00:07:09,180 And let's suppose that's say user U1. 213 00:07:09,800 --> 00:07:11,080 And let's suppose that 214 00:07:11,380 --> 00:07:14,250 user grants to user U2 215 00:07:15,150 --> 00:07:16,970 the same privileges, select on 216 00:07:17,190 --> 00:07:18,530 'R' and let's suppose 217 00:07:18,990 --> 00:07:20,230 that's with the grant option 218 00:07:23,010 --> 00:07:23,730 and this is user 'U2.' 219 00:07:24,770 --> 00:07:26,160 So user U2 is allowed 220 00:07:26,680 --> 00:07:29,000 to further grant privileges to other users. 221 00:07:29,520 --> 00:07:31,920 And those may further grant privileges to others. 222 00:07:32,310 --> 00:07:34,230 And we may get a big sub-tree here. 223 00:07:35,260 --> 00:07:36,930 Now let's suppose user U1 224 00:07:37,550 --> 00:07:39,130 decides to revoke the 225 00:07:39,230 --> 00:07:40,890 privilege that was granted to user U2. 226 00:07:42,100 --> 00:07:43,670 So what cast it says, 227 00:07:44,250 --> 00:07:45,550 is if there is revocation 228 00:07:46,510 --> 00:07:49,150 of that form then it will cascade down the tree. 229 00:07:49,830 --> 00:07:51,170 So if you too further granted 230 00:07:51,600 --> 00:07:52,750 privileges, then those would be 231 00:07:52,910 --> 00:07:53,960 revoked and so would 232 00:07:54,100 --> 00:07:56,180 any privileges down below, so 233 00:07:56,290 --> 00:07:57,360 this entire sub tree is 234 00:07:57,470 --> 00:07:58,870 effectively removed all of 235 00:07:58,970 --> 00:08:01,040 those privileges, however, we 236 00:08:01,180 --> 00:08:02,000 have to be a little 237 00:08:02,060 --> 00:08:03,390 bit careful, because it's possible 238 00:08:04,510 --> 00:08:06,470 that say U2 was granted 239 00:08:07,220 --> 00:08:09,140 the select privilege by a 240 00:08:09,280 --> 00:08:10,080 separate user, I guess we'll call 241 00:08:10,310 --> 00:08:12,670 this one U3, who also granted 242 00:08:13,120 --> 00:08:14,730 exactly the same privilege, and 243 00:08:15,010 --> 00:08:16,010 in that case, if U1 does 244 00:08:16,660 --> 00:08:17,970 the revoke, we don't want 245 00:08:18,240 --> 00:08:20,140 to revoke U2's privilege, because 246 00:08:20,450 --> 00:08:21,910 U2 got it from another source. 247 00:08:22,820 --> 00:08:24,700 So technically what cascade says 248 00:08:25,100 --> 00:08:26,430 is that, when we revoke a 249 00:08:26,710 --> 00:08:28,590 privilege, we revoke any 250 00:08:28,790 --> 00:08:30,850 privileges transitively when they 251 00:08:30,960 --> 00:08:32,560 weren't also granted by another source. 252 00:08:33,460 --> 00:08:34,400 So, what your seeing here is 253 00:08:34,500 --> 00:08:36,610 actually called a grant diagram and 254 00:08:37,040 --> 00:08:38,150 I'm not giving you all 255 00:08:38,460 --> 00:08:41,270 the details of grant diagrams, but, you can see basically, what they do. 256 00:08:41,950 --> 00:08:44,780 And, their used is to properly cascade the revoking of privileges. 257 00:08:46,180 --> 00:08:47,860 So again, the cascade option and 258 00:08:48,040 --> 00:08:49,420 the revoke command says to also 259 00:08:49,830 --> 00:08:51,090 revoke any privileges that were 260 00:08:51,180 --> 00:08:52,020 granted from the ones being 261 00:08:52,170 --> 00:08:55,330 revoked transitively, unless they were also granted form another source. 262 00:08:56,510 --> 00:08:58,030 What the restrict option says is 263 00:08:58,150 --> 00:08:59,320 that the revoke command is not 264 00:08:59,650 --> 00:09:01,240 allowed to execute if cascade 265 00:09:01,660 --> 00:09:02,790 would revoke any other privileges. 266 00:09:03,330 --> 00:09:04,200 So if we have any of those 267 00:09:04,410 --> 00:09:06,040 transitive cases, so if 268 00:09:06,220 --> 00:09:07,310 we do have the transitive cases 269 00:09:07,360 --> 00:09:08,400 and we want to use restrict 270 00:09:08,870 --> 00:09:10,480 and we have to manually revoke those 271 00:09:10,740 --> 00:09:13,440 privileges effectively bottom-up through that graph that we say. 272 00:09:14,170 --> 00:09:17,240 Incidentally restrict is the default in the revoke command. 273 00:09:17,600 --> 00:09:18,640 So, if neither of these options 274 00:09:19,300 --> 00:09:21,630 are specified, then, restrict is the one that will be enforced. 275 00:09:22,640 --> 00:09:23,820 Lastly, let me talk a 276 00:09:23,910 --> 00:09:26,200 little bit about where privileges actually reside in reality. 277 00:09:27,010 --> 00:09:28,180 So we have our data that's 278 00:09:28,350 --> 00:09:29,570 being managed by a database system, 279 00:09:30,230 --> 00:09:31,220 and typically we'll have application 280 00:09:31,970 --> 00:09:33,310 developers who are working 281 00:09:33,310 --> 00:09:35,040 directly with the database system, often 282 00:09:35,410 --> 00:09:37,830 developing modules that will be invoked by the end users. 283 00:09:38,550 --> 00:09:40,280 So those application developers have 284 00:09:40,310 --> 00:09:42,590 to have privileges on the database to create the modules. 285 00:09:43,240 --> 00:09:44,350 But then we have the 286 00:09:44,420 --> 00:09:45,510 software that sits above 287 00:09:45,620 --> 00:09:46,830 the database system that is 288 00:09:47,010 --> 00:09:48,590 used by end users and the 289 00:09:48,740 --> 00:09:51,080 end users typically don't have privileges themselves. 290 00:09:51,920 --> 00:09:53,310 They might have separate privileges privileges 291 00:09:53,440 --> 00:09:55,860 to access the modules but 292 00:09:55,980 --> 00:09:56,650 they're not going to be using 293 00:09:56,970 --> 00:09:58,500 the privilege system of the database system. 294 00:09:59,370 --> 00:10:00,230 And similiarly, there may be even 295 00:10:00,510 --> 00:10:01,770 more software layered on top 296 00:10:02,060 --> 00:10:04,240 of the software that the application developer builds. 297 00:10:05,100 --> 00:10:06,490 And again that software itself wouldn't 298 00:10:06,730 --> 00:10:08,400 be having database privileges but 299 00:10:08,720 --> 00:10:11,180 might have an authorization system for the software that it's accessing. 300 00:10:12,570 --> 00:10:14,590 To summarize, database base authorization is important. 301 00:10:15,110 --> 00:10:18,020 It makes sure that users only see the data that they're authorized to see. 302 00:10:18,630 --> 00:10:21,500 It guards the database against being modified by malicious users. 303 00:10:22,410 --> 00:10:24,240 There's a privileged system similar 304 00:10:24,600 --> 00:10:26,090 to file system privileges but specific 305 00:10:26,610 --> 00:10:28,360 to database constructs and users 306 00:10:28,690 --> 00:10:30,130 can only operate on the 307 00:10:30,210 --> 00:10:31,800 data for which they're authorized via their privileges. 308 00:10:32,840 --> 00:10:34,460 There's a grant statement and 309 00:10:34,610 --> 00:10:35,790 a revoke statement in the sequel 310 00:10:36,150 --> 00:10:38,290 standard for granting privileges and revoking privileges. 311 00:10:38,870 --> 00:10:39,860 And again when a relation is 312 00:10:39,970 --> 00:10:40,990 created the owner of the 313 00:10:41,030 --> 00:10:42,330 relation starts with all privileges, 314 00:10:42,850 --> 00:10:44,210 so that's where the granting would begin. 315 00:10:44,520 --> 00:10:46,930 And finally, for having 316 00:10:47,440 --> 00:10:48,840 privileges that go beyond simple 317 00:10:49,250 --> 00:10:51,360 table level operations, views are 318 00:10:51,520 --> 00:10:53,370 a very important construct and in 319 00:10:53,460 --> 00:10:54,870 fact authorization is one of 320 00:10:54,900 --> 00:10:56,550 the most important uses of database views.