Now that we have an idea of what a database is,
in particular, a relational database that we been looking at, well,
now we want to go back to what is relevant to securing it.
What are the things that we have talked about?
Remember, we are going to have users who want to access the database,
we would have to do authentication so we know who the request is coming from.
And once you do authentication, then comes access control.
So, let's talk about how access control is done in databases.
So actually, the two basic commands and different variations of SQL.
Details may differ but we are basically talking about the key concepts.
So you can grant or revoke.
So this is sort of an example of how access could be granted.
So, this says grant either set of privileges.
So, for example, selecting some tables.
So, privilege may be to do a select, for example.
So you can grant a privilege on a table or
you can grant a role to a certain user.
That could be in the context of a given table.
That's the resource we're talking about.
This is what you can do with the resource.
This access has been granted to a certain user,
it could be granted to a role.
We talked about role based access control, so
if you have that it could be granted the role.
Public is granted to everyone.
So this is the world, every user that we have in the system.
So some of these things are optional, for example what's in this parenthesis
here, so if you don't specify a table, that's basically saying on all tables.
Okay, so you can either specify a particular table or
if you don't then this is optional part here.
If it's omitted, then that would mean all the tables.
Similarly there's an optional thing here that says identified by password, so
that means if you ever revoke this access that's being
granted as a result of this.
You will need the password to do that.
So somebody else can't revoke it if they don't have the password.
And another thing that you can do here which is also optional is,
that whoever you granting this access to.
The user or to the role, they can actually further propagate, so
that's the GRANT option, saying, well, I give you access,
you can give this access to somebody else, if this is specified.
If you have the access with the GRANT option.
So an example of this would be for
example a statement that says you grant the select privilege on any table,
so we are omitting this choosing a particular table,
because this is optional, to, the user part here is Alice.
Remember we didn't include the grant option here,
that means Alice will not be able to further propagate
this select access that she has on any table to somebody else.
We're going to talk about securing data, of course,
you have to worry about access control.
So access control,
to make good things happen, of course authorized users need to have access.
So this is how you can grant access to them.
We talked about both a mandatory access control and
discretionary access control.
If your company controls centrally who should have access to what kind of
things they should be able to do with various databases and tables and
the databases they have, then of course it'll be centrally managed access.
So that would be mandatory access control.
Discretionary means if you created a table,
then of course you are the owner and you can decide who else can access it.
But this is how access is granted.
So we saw the example of a privilege, but
privileges can also be operations such as you can insert, update,
delete tables and insert new tables and things like that.
All the different things we said we can do under the basis.
These are operations you can perform and basically access control says
a certain user role is allowed to perform that operation or not.
So the other side is, the side of grant is of course you may want to revoke.
So revoking is again privilege we were talking about.
The example we had was select, but it could be one of the other ones or
you being able to take on a certain role optionally on a table.
And you take it from a user or a role or from everybody.
Granting is giving someone access, revoking is taking that access back, and
an example would be, revoke the select privilege on any table,
so we omit this from user Alice.
Granting access rights to perform various operations you can do on
a table, and revoking those, this is how we manage access control in databases.