Hello again.
So in this part, we're going to learn how to set user permissions with commands.
Now, there are three commands that we're going to need to learn.
Grant, deny and revoke.
So when we get started with a grant command.
Grant command.
The grant command applies a positive permission to users roles or schemas.
So here's the syntax of the grant command.
Privileges can be select, update, insert, delete and so on.
The object can be a table name.
And here are some privileges.
Select, which is the ability to perform select statements on the table.
Insert is the ability to perform insert statements on the table, update is the ability to perform update
statements on the table.
The lead gives the ability to perform delete statements on the table.
References gives the ability to create a constraint that refers to the table.
Alter gives the ability to perform alter table statements to change the table definition.
All now, it does not grant all permissions for the table, rather it grants the NCAA 92 permissions,
which are select, insert update, delete and references.
Multiple privileges can be used in a single query.
So let's jump over to as a and start to use the grant command.
And before we start using the grant command, let's learn what kind of permissions that a user ought
to have.
Database permissions and database principles tables will be joined in order to get the list of all the
permissions that can be applied to the user.
And we have to type the name of the user that we want to see, the list of permissions of it.
So our user was admin and we want to get all the columns of the database permissions table.
Execute the query and we see the list.
So the admin user has only database connection permissions with a grant command, so it means that this
user is allowed to log in to escarole server.
Now, let's assign a permission to admin user in order to be able to create a table in the database
with the grant command.
Execute it and get the permission list great.
So we allow the user admin to be able to create a table in the Adventure Works 2017 database.
I hear you think it over there.
Is it possible that we can assign permissions to a table?
Yes, so remember that we have created table one in the previous section, so why don't we go ahead
and assign some privileges to this table with commands?
Select insert update and lead our permissions, it can be given to a user with a single query.
And we can execute it and list the permissions again.
Right, so these privileges in the table are now assigned to the user admin with a grant command, which
means the admin user was allowed to use these commands.
Deny command, so this command applies a negative permission to users roles or schemas.
So here we will deny permission for a user to use privileges.
And here's the syntax of the Dinni command.
It's similar to the grant command.
So let's jump on over to SSME.
And we will deny this user to create a table in the database or simply deny create table to men executed.
And check the list of the permissions so the user admen.
Wonderful.
As we can see here, the create table permission has been denied, which means the admin user will not
be able to create any table in the database anymore.
All right, so let's refresh the admin connection to update the folder and try to create a new table
in the Adventure Works 2017 database just to see if it's possible.
And there you go.
We have learned how to create a new table in Object Explore in the previous section, right.
So one column is enough in order to create a table.
So this will be a demo, so it's just not really important what the column name is.
So let's try to save it.
Oh, there it is.
We have no permission to create a new table to the database.
Excellent.
So everything seems to be working well.
Now, if you look at the tab of the query, Ed, you will see the login connection name.
Which means the queries in this editor will execute for that connection.
So we're going to use the select statement on the admin log in, so we have to open a query, Ed, which
is related to the admin login.
Right, click on he had been login and select new query.
Here it is, and the queries in this editor will execute in the admin log in.
So now let's check to see if the admin user is able to retrieve data from the table one.
The answer is yes, so let's deny this permission to admin user with a command.
And we have the edit query in the era user query, Ed, because the era user has sys admin permission.
Deny select on table one to admen.
So that's very simple.
And execute and check the permissions of the admin user.
Now let's flip over to admin user query editor and try to retrieve data again.
And we have no more permissions to retrieve data from table one.
Now it is possible to assign permissions to a user graphicly.
So that's right, click on table one and choose properties from the list and we're going to see permissions
page.
And here is the admin user in the list because we have recently assigned some permissions.
We can see all the permissions that have been assigned to the admin user before.
And we've assigned insert delete update privileges by grant, unlike select privileges deny.
And here we can also see all of this from the properties pop up windows.
Now, when we click on the select column, permissions will be enabled.
It is possible that we can assign permissions to each column of the table.
So table one has an I.D. and a name on the column.
The admin user will be able to see the name column, but not be able to see the ID column.
Now.
Flip to the admin log and query ed and try to retrieve data from table one.
The had been user is still not allowed to retrieve data.
But if we retrieve only the name of the column, then the data list will be listed.
There is no permission to retrieve the ID column because we denied it from the user properties pop up
window.
Revoke command.
So this command removes a granted or denied permission.
And the syntax of this command is pretty similar to other commands, let's jump on over to SSME and
revoke all grant and deny commands.
So we have denied create table permission to the user admin.
Now, let's revoke the denied create table command.
Execute the query and update the list of permissions.
There you go.
They create table permission has been removed from the list you had been user will be able to create
a new table to the database again.
As you may remember, we have denied the ID column and granted the name column of table one.
So this is a region where there are two Selex in the list, one is 49 and one is for granted.
Just don't take it for granted.
Now, let's revoke all privileges on table one.
The revokable command will remove all privileges from the table, so note that revokable doesn't mean
that it's going to remove all granter denied privileges.
It means only to remove the SELEK update, delete and insert commands.
So now in the database, we're going to see a schema folder under the database level security folder.
And it's possible that we can create a new schema and assign it to the user.
The name of the schema will be test underscore schema and the schema owner will be the admin user under
their permissions page on the left of the popup window.
We can assign user permissions for this schema.
So let's allow it to alter, insert and delete data.
And we won't allow any of the other permissions.
Click OK to save it.
Now open the user properties, pop up window and open the owner schemas page, we're going to see that
that test schema has been on.
And just like last time, we can change the default schema to test schema under the general page.
And he admin user will be enforced to insert a new table with test schema.
So let's allow the user to be able to create a table in the database.
Now try to create a new table.
So let's create a table with heated and first name columns.
So same table at table one.
Refresh to follow the updates and.
So here is table one, one of them was added with the DBO schema and the other one has been recently
added with the test schema.
Rose.
So a role is created to ease set up and maintenance of the security model.
It's named group of related privileges that can be granted to the user.
Now, when there are many users in a database, it becomes difficult to grant or revoke privileges to
users.
So therefore you're going to really enjoy, if you will, defining roles.
You can grant or revoke privileges to roles, thereby automatically granting or revoking privileges.
So you can either create roles or use the system roles predefine.
The database roles are listed under the database level security folder.
Access admin.
Members with his role can manage Windows groups and Escorial server logins.
Backup operator members can backup the database.
Data reader members can read all the data.
Data writer members can add, delete or modify data in the table.
DDL admen.
Members can run Dynamic Link Library or DL statements.
Deny data, reader members can't view data within the database.
Deny data writer members can't change or delete data in tables or views.
Owner members have full access.
Security admin members can modify, roll membership and manage permissions.
And, of course, public the public role sets the basic default permissions for all users, so let's
create a new role by right clicking on the rules folder.
And click on the database for.
Instead of giving permission to each user one by one, we can create a role and add users to that role.
So the name of our role will be test role.
And we will add the admin user to this role by adding a new role member.
Now, we will assign permissions to this role under the security page.
So click on Search then, OK, while specified objects are selected, then object types.
Select table from the list of the object types, because we're going to assign permissions to the tables.
Like, OK, to save it and then browse to get all the tables, it's like table one with the test schema.
So first, let's allow the user admen to be able to change anything on this table, so we'll now click
OK to save it.
And here's our new rule.
Now, let's open the admin user properties pop up window.
And we can see that the test rule is selected under the membership page, which means that this user
has the permission of the test role.
Now, let's make sure to uncheck all roles except for tests role.
And the admin user will have only the permissions of the test roll with the settings.
Refresh admin, login to load the updates.
Great, our role has been assigned to yuzu, and this is a reason why only table one is in the list.
So let's see what happens if we try to retrieve data from table one.
It will be retrieved.
So now let's flip to the eerie log in and deny select on table one to the test role instead of user.
So excuse the query and try to retrieve it again.
And see, that's it, user has no permission to do that.
Now you can create a rolls and assign users to the roles it's simple to use and very simple to manage.
Now, lastly, let's check the permissions of the admin user and the permissions of test rule.
So here are the positions of the admin user, and it has only the grant create table permission.
The test role has all permissions that we have recently assigned.
Now remember that we have assigned only select privilege with the nickman.
So what do you think we've completed our subjects for school server basics?
It's a great cause.
I'm really pleased that you got all the way through.
It gets detailed, but it certainly is worth it.
So in the next section, we're going to just review what we have learned together.
So I want to see in the next section.
All right.
It's important to cement the knowledge.