1
00:00:00,620 --> 00:00:01,310
Hello again.

2
00:00:02,090 --> 00:00:05,870
So in this part, we're going to learn how to set user permissions with commands.

3
00:00:06,810 --> 00:00:09,880
Now, there are three commands that we're going to need to learn.

4
00:00:09,990 --> 00:00:13,110
Grant, deny and revoke.

5
00:00:14,410 --> 00:00:16,630
So when we get started with a grant command.

6
00:00:19,270 --> 00:00:20,200
Grant command.

7
00:00:21,120 --> 00:00:26,220
The grant command applies a positive permission to users roles or schemas.

8
00:00:27,420 --> 00:00:30,120
So here's the syntax of the grant command.

9
00:00:31,840 --> 00:00:35,950
Privileges can be select, update, insert, delete and so on.

10
00:00:37,260 --> 00:00:38,820
The object can be a table name.

11
00:00:41,960 --> 00:00:43,580
And here are some privileges.

12
00:00:44,490 --> 00:00:48,890
Select, which is the ability to perform select statements on the table.

13
00:00:49,870 --> 00:00:57,340
Insert is the ability to perform insert statements on the table, update is the ability to perform update

14
00:00:57,340 --> 00:00:58,500
statements on the table.

15
00:00:59,650 --> 00:01:03,600
The lead gives the ability to perform delete statements on the table.

16
00:01:04,760 --> 00:01:10,250
References gives the ability to create a constraint that refers to the table.

17
00:01:11,490 --> 00:01:17,940
Alter gives the ability to perform alter table statements to change the table definition.

18
00:01:19,420 --> 00:01:27,100
All now, it does not grant all permissions for the table, rather it grants the NCAA 92 permissions,

19
00:01:27,100 --> 00:01:31,810
which are select, insert update, delete and references.

20
00:01:36,500 --> 00:01:39,860
Multiple privileges can be used in a single query.

21
00:01:41,300 --> 00:01:45,560
So let's jump over to as a and start to use the grant command.

22
00:01:49,130 --> 00:01:54,890
And before we start using the grant command, let's learn what kind of permissions that a user ought

23
00:01:54,890 --> 00:01:55,460
to have.

24
00:01:57,430 --> 00:02:05,530
Database permissions and database principles tables will be joined in order to get the list of all the

25
00:02:05,530 --> 00:02:08,080
permissions that can be applied to the user.

26
00:02:15,100 --> 00:02:19,090
And we have to type the name of the user that we want to see, the list of permissions of it.

27
00:02:20,100 --> 00:02:26,070
So our user was admin and we want to get all the columns of the database permissions table.

28
00:02:27,580 --> 00:02:30,130
Execute the query and we see the list.

29
00:02:32,260 --> 00:02:39,610
So the admin user has only database connection permissions with a grant command, so it means that this

30
00:02:39,610 --> 00:02:42,430
user is allowed to log in to escarole server.

31
00:02:44,020 --> 00:02:50,950
Now, let's assign a permission to admin user in order to be able to create a table in the database

32
00:02:50,950 --> 00:02:52,240
with the grant command.

33
00:02:53,960 --> 00:02:58,310
Execute it and get the permission list great.

34
00:03:00,610 --> 00:03:07,960
So we allow the user admin to be able to create a table in the Adventure Works 2017 database.

35
00:03:09,840 --> 00:03:11,530
I hear you think it over there.

36
00:03:12,520 --> 00:03:16,480
Is it possible that we can assign permissions to a table?

37
00:03:20,500 --> 00:03:26,830
Yes, so remember that we have created table one in the previous section, so why don't we go ahead

38
00:03:26,830 --> 00:03:30,010
and assign some privileges to this table with commands?

39
00:03:31,830 --> 00:03:38,130
Select insert update and lead our permissions, it can be given to a user with a single query.

40
00:03:39,940 --> 00:03:43,630
And we can execute it and list the permissions again.

41
00:03:46,080 --> 00:03:53,280
Right, so these privileges in the table are now assigned to the user admin with a grant command, which

42
00:03:53,280 --> 00:03:57,330
means the admin user was allowed to use these commands.

43
00:03:59,090 --> 00:04:05,120
Deny command, so this command applies a negative permission to users roles or schemas.

44
00:04:06,170 --> 00:04:09,830
So here we will deny permission for a user to use privileges.

45
00:04:11,570 --> 00:04:14,090
And here's the syntax of the Dinni command.

46
00:04:15,170 --> 00:04:16,790
It's similar to the grant command.

47
00:04:18,600 --> 00:04:20,640
So let's jump on over to SSME.

48
00:04:22,940 --> 00:04:31,220
And we will deny this user to create a table in the database or simply deny create table to men executed.

49
00:04:32,190 --> 00:04:35,730
And check the list of the permissions so the user admen.

50
00:04:36,990 --> 00:04:38,130
Wonderful.

51
00:04:39,880 --> 00:04:45,580
As we can see here, the create table permission has been denied, which means the admin user will not

52
00:04:45,580 --> 00:04:48,430
be able to create any table in the database anymore.

53
00:04:50,210 --> 00:04:57,440
All right, so let's refresh the admin connection to update the folder and try to create a new table

54
00:04:57,890 --> 00:05:01,880
in the Adventure Works 2017 database just to see if it's possible.

55
00:05:04,990 --> 00:05:06,050
And there you go.

56
00:05:06,160 --> 00:05:11,540
We have learned how to create a new table in Object Explore in the previous section, right.

57
00:05:11,560 --> 00:05:14,770
So one column is enough in order to create a table.

58
00:05:16,400 --> 00:05:22,070
So this will be a demo, so it's just not really important what the column name is.

59
00:05:23,040 --> 00:05:24,420
So let's try to save it.

60
00:05:26,470 --> 00:05:27,560
Oh, there it is.

61
00:05:28,120 --> 00:05:31,100
We have no permission to create a new table to the database.

62
00:05:31,690 --> 00:05:32,220
Excellent.

63
00:05:32,230 --> 00:05:34,000
So everything seems to be working well.

64
00:05:36,470 --> 00:05:41,600
Now, if you look at the tab of the query, Ed, you will see the login connection name.

65
00:05:42,550 --> 00:05:47,370
Which means the queries in this editor will execute for that connection.

66
00:05:48,900 --> 00:05:56,850
So we're going to use the select statement on the admin log in, so we have to open a query, Ed, which

67
00:05:56,850 --> 00:05:58,890
is related to the admin login.

68
00:05:59,820 --> 00:06:03,000
Right, click on he had been login and select new query.

69
00:06:04,950 --> 00:06:10,290
Here it is, and the queries in this editor will execute in the admin log in.

70
00:06:12,070 --> 00:06:16,540
So now let's check to see if the admin user is able to retrieve data from the table one.

71
00:06:17,600 --> 00:06:23,030
The answer is yes, so let's deny this permission to admin user with a command.

72
00:06:24,550 --> 00:06:32,140
And we have the edit query in the era user query, Ed, because the era user has sys admin permission.

73
00:06:34,740 --> 00:06:37,800
Deny select on table one to admen.

74
00:06:38,780 --> 00:06:40,220
So that's very simple.

75
00:06:41,350 --> 00:06:44,770
And execute and check the permissions of the admin user.

76
00:06:57,250 --> 00:07:01,930
Now let's flip over to admin user query editor and try to retrieve data again.

77
00:07:03,030 --> 00:07:06,540
And we have no more permissions to retrieve data from table one.

78
00:07:12,410 --> 00:07:16,220
Now it is possible to assign permissions to a user graphicly.

79
00:07:17,450 --> 00:07:23,690
So that's right, click on table one and choose properties from the list and we're going to see permissions

80
00:07:23,690 --> 00:07:24,090
page.

81
00:07:24,830 --> 00:07:30,980
And here is the admin user in the list because we have recently assigned some permissions.

82
00:07:33,070 --> 00:07:39,040
We can see all the permissions that have been assigned to the admin user before.

83
00:07:40,050 --> 00:07:47,280
And we've assigned insert delete update privileges by grant, unlike select privileges deny.

84
00:07:48,550 --> 00:07:53,710
And here we can also see all of this from the properties pop up windows.

85
00:07:55,920 --> 00:08:00,690
Now, when we click on the select column, permissions will be enabled.

86
00:08:02,660 --> 00:08:07,160
It is possible that we can assign permissions to each column of the table.

87
00:08:08,300 --> 00:08:12,860
So table one has an I.D. and a name on the column.

88
00:08:14,050 --> 00:08:19,120
The admin user will be able to see the name column, but not be able to see the ID column.

89
00:08:21,520 --> 00:08:22,000
Now.

90
00:08:23,360 --> 00:08:28,160
Flip to the admin log and query ed and try to retrieve data from table one.

91
00:08:31,110 --> 00:08:33,990
The had been user is still not allowed to retrieve data.

92
00:08:35,300 --> 00:08:42,710
But if we retrieve only the name of the column, then the data list will be listed.

93
00:08:46,090 --> 00:08:51,820
There is no permission to retrieve the ID column because we denied it from the user properties pop up

94
00:08:51,820 --> 00:08:52,240
window.

95
00:08:56,460 --> 00:08:57,660
Revoke command.

96
00:08:59,600 --> 00:09:03,320
So this command removes a granted or denied permission.

97
00:09:05,500 --> 00:09:12,100
And the syntax of this command is pretty similar to other commands, let's jump on over to SSME and

98
00:09:12,100 --> 00:09:14,680
revoke all grant and deny commands.

99
00:09:16,220 --> 00:09:21,050
So we have denied create table permission to the user admin.

100
00:09:22,040 --> 00:09:26,180
Now, let's revoke the denied create table command.

101
00:09:27,180 --> 00:09:30,360
Execute the query and update the list of permissions.

102
00:09:31,620 --> 00:09:32,360
There you go.

103
00:09:33,900 --> 00:09:39,570
They create table permission has been removed from the list you had been user will be able to create

104
00:09:39,570 --> 00:09:41,850
a new table to the database again.

105
00:09:44,580 --> 00:09:51,000
As you may remember, we have denied the ID column and granted the name column of table one.

106
00:09:52,880 --> 00:10:00,410
So this is a region where there are two Selex in the list, one is 49 and one is for granted.

107
00:10:01,520 --> 00:10:02,900
Just don't take it for granted.

108
00:10:05,240 --> 00:10:08,630
Now, let's revoke all privileges on table one.

109
00:10:10,480 --> 00:10:18,760
The revokable command will remove all privileges from the table, so note that revokable doesn't mean

110
00:10:18,760 --> 00:10:22,570
that it's going to remove all granter denied privileges.

111
00:10:22,960 --> 00:10:27,820
It means only to remove the SELEK update, delete and insert commands.

112
00:10:33,640 --> 00:10:38,740
So now in the database, we're going to see a schema folder under the database level security folder.

113
00:10:39,850 --> 00:10:43,300
And it's possible that we can create a new schema and assign it to the user.

114
00:10:49,600 --> 00:10:56,620
The name of the schema will be test underscore schema and the schema owner will be the admin user under

115
00:10:56,620 --> 00:10:59,780
their permissions page on the left of the popup window.

116
00:11:00,160 --> 00:11:03,760
We can assign user permissions for this schema.

117
00:11:06,590 --> 00:11:10,520
So let's allow it to alter, insert and delete data.

118
00:11:12,530 --> 00:11:15,800
And we won't allow any of the other permissions.

119
00:11:16,870 --> 00:11:17,940
Click OK to save it.

120
00:11:20,170 --> 00:11:26,920
Now open the user properties, pop up window and open the owner schemas page, we're going to see that

121
00:11:26,920 --> 00:11:28,870
that test schema has been on.

122
00:11:30,290 --> 00:11:37,700
And just like last time, we can change the default schema to test schema under the general page.

123
00:11:39,380 --> 00:11:44,750
And he admin user will be enforced to insert a new table with test schema.

124
00:11:47,540 --> 00:11:51,050
So let's allow the user to be able to create a table in the database.

125
00:11:53,720 --> 00:11:55,940
Now try to create a new table.

126
00:12:03,340 --> 00:12:06,910
So let's create a table with heated and first name columns.

127
00:12:14,770 --> 00:12:16,870
So same table at table one.

128
00:12:21,290 --> 00:12:24,200
Refresh to follow the updates and.

129
00:12:25,950 --> 00:12:33,180
So here is table one, one of them was added with the DBO schema and the other one has been recently

130
00:12:33,180 --> 00:12:35,280
added with the test schema.

131
00:12:37,360 --> 00:12:38,110
Rose.

132
00:12:39,330 --> 00:12:45,030
So a role is created to ease set up and maintenance of the security model.

133
00:12:46,140 --> 00:12:50,790
It's named group of related privileges that can be granted to the user.

134
00:12:51,860 --> 00:12:57,170
Now, when there are many users in a database, it becomes difficult to grant or revoke privileges to

135
00:12:57,170 --> 00:12:57,650
users.

136
00:12:57,650 --> 00:13:04,130
So therefore you're going to really enjoy, if you will, defining roles.

137
00:13:04,940 --> 00:13:11,600
You can grant or revoke privileges to roles, thereby automatically granting or revoking privileges.

138
00:13:12,670 --> 00:13:16,420
So you can either create roles or use the system roles predefine.

139
00:13:19,020 --> 00:13:22,860
The database roles are listed under the database level security folder.

140
00:13:24,240 --> 00:13:25,230
Access admin.

141
00:13:26,720 --> 00:13:30,890
Members with his role can manage Windows groups and Escorial server logins.

142
00:13:32,260 --> 00:13:35,380
Backup operator members can backup the database.

143
00:13:36,910 --> 00:13:39,160
Data reader members can read all the data.

144
00:13:40,340 --> 00:13:44,430
Data writer members can add, delete or modify data in the table.

145
00:13:45,740 --> 00:13:46,730
DDL admen.

146
00:13:47,940 --> 00:13:52,290
Members can run Dynamic Link Library or DL statements.

147
00:13:53,720 --> 00:13:57,620
Deny data, reader members can't view data within the database.

148
00:13:59,070 --> 00:14:04,440
Deny data writer members can't change or delete data in tables or views.

149
00:14:05,570 --> 00:14:08,510
Owner members have full access.

150
00:14:09,780 --> 00:14:15,090
Security admin members can modify, roll membership and manage permissions.

151
00:14:17,150 --> 00:14:23,480
And, of course, public the public role sets the basic default permissions for all users, so let's

152
00:14:23,480 --> 00:14:27,040
create a new role by right clicking on the rules folder.

153
00:14:28,390 --> 00:14:30,160
And click on the database for.

154
00:14:31,270 --> 00:14:38,520
Instead of giving permission to each user one by one, we can create a role and add users to that role.

155
00:14:40,890 --> 00:14:43,200
So the name of our role will be test role.

156
00:14:44,700 --> 00:14:49,980
And we will add the admin user to this role by adding a new role member.

157
00:14:52,330 --> 00:14:56,830
Now, we will assign permissions to this role under the security page.

158
00:14:58,130 --> 00:15:04,100
So click on Search then, OK, while specified objects are selected, then object types.

159
00:15:05,140 --> 00:15:10,930
Select table from the list of the object types, because we're going to assign permissions to the tables.

160
00:15:12,160 --> 00:15:19,000
Like, OK, to save it and then browse to get all the tables, it's like table one with the test schema.

161
00:15:20,000 --> 00:15:27,680
So first, let's allow the user admen to be able to change anything on this table, so we'll now click

162
00:15:27,680 --> 00:15:28,730
OK to save it.

163
00:15:30,260 --> 00:15:31,490
And here's our new rule.

164
00:15:33,920 --> 00:15:37,340
Now, let's open the admin user properties pop up window.

165
00:15:38,590 --> 00:15:44,290
And we can see that the test rule is selected under the membership page, which means that this user

166
00:15:44,740 --> 00:15:47,650
has the permission of the test role.

167
00:15:50,750 --> 00:15:55,100
Now, let's make sure to uncheck all roles except for tests role.

168
00:15:56,080 --> 00:16:01,590
And the admin user will have only the permissions of the test roll with the settings.

169
00:16:06,710 --> 00:16:09,740
Refresh admin, login to load the updates.

170
00:16:16,510 --> 00:16:24,370
Great, our role has been assigned to yuzu, and this is a reason why only table one is in the list.

171
00:16:26,800 --> 00:16:30,790
So let's see what happens if we try to retrieve data from table one.

172
00:16:34,590 --> 00:16:37,170
It will be retrieved.

173
00:16:39,460 --> 00:16:47,440
So now let's flip to the eerie log in and deny select on table one to the test role instead of user.

174
00:16:53,400 --> 00:16:56,450
So excuse the query and try to retrieve it again.

175
00:17:01,650 --> 00:17:05,070
And see, that's it, user has no permission to do that.

176
00:17:06,070 --> 00:17:12,580
Now you can create a rolls and assign users to the roles it's simple to use and very simple to manage.

177
00:17:14,970 --> 00:17:20,340
Now, lastly, let's check the permissions of the admin user and the permissions of test rule.

178
00:17:22,330 --> 00:17:29,980
So here are the positions of the admin user, and it has only the grant create table permission.

179
00:17:33,050 --> 00:17:37,340
The test role has all permissions that we have recently assigned.

180
00:17:37,700 --> 00:17:43,070
Now remember that we have assigned only select privilege with the nickman.

181
00:17:46,430 --> 00:17:50,750
So what do you think we've completed our subjects for school server basics?

182
00:17:50,870 --> 00:17:51,980
It's a great cause.

183
00:17:52,730 --> 00:17:55,310
I'm really pleased that you got all the way through.

184
00:17:55,760 --> 00:17:58,590
It gets detailed, but it certainly is worth it.

185
00:17:59,180 --> 00:18:05,510
So in the next section, we're going to just review what we have learned together.

186
00:18:06,030 --> 00:18:07,460
So I want to see in the next section.

187
00:18:07,490 --> 00:18:07,940
All right.

188
00:18:08,360 --> 00:18:10,220
It's important to cement the knowledge.