This video covers database authorization.
As we'll see its a relatively straight
forward topic, but it is a very important one.
Authorization has to do
with first making sure that
users only see the data that they're supposed to see.
And second, guarding the database
from being modified by malicious users.
Now, one thing that we're not
covering in this video are
system or programming security issues,
such as SQL injection errors, what
we are focusing on is security
of the data access itself.
So the way database authorization work
is that users of the database
have specific privileges, and then
they can only operate on data
for which they're authorized through those privileges.
So it's similar to file
system, for example, privileges and
authorization, except that
it is specific to the database constructs,
database contents, and tends to
be more fine grained access than we see with file systems.
Specifically, for databases that
privileges that are possible are
on a particular relation to
select the data for that
relation or maybe just select
the specific attributes, so that's read privileges.
As far as write privileges or
modifications, we can set
up the privilege to insert a
non-relation or even insert specific
attributesonly of a relation.
Most SQL implementations do allow
you to insert data with only specific attributes specified.
We can have privileges to update
a relation or update specific attributes,
and finally the privilege to delete from a relation.
So let's go straight to some examples.
We'll be using the standard college
admission sample database that we've used in other videos.
The schema is shown here at the bottom.
Let's suppose we have a
user who wants to issue the update command shown.
They want to find students
whose GPA is greater than
3.9, and if those students have
applied anywhere, they want
to update the application record
and set the decision to be yes.
So, let's look at what
privileges would be needed
by the user to execute this command.
So, clearly, we're going to
have to have some privileges on the
Apply relation and some privileges on the Student relation.
In the Apply relation, they're going
to need to be able to
update the decision attribute, but
there's no other update privileges that are needed.
In terms of select privileges
or reading the Apply relation,
the only attribute that's being
read here is the student ID so, that's what they need.For
the student relation, they're going
to need to read the GPA,
as well as the student ID,
so the privilege needed there is
the select privilege over
the student ID and the GPA.
So with this set of privileges the
user would be allowed to execute this operation.
In our next example, suppose the
user wants to delete all students who haven't applied anywhere.
So they're deleting from the student
relation where the student ID
is not in the set
of student IDs in the applied relation.
So, for this one again, they'll
need privileges on the student
relation and on the apply relation.
On the student relation, the user
would need the delete privilege and
delete never has attributes associated
with it because you are always deleting entire tuples.
The only tuple that's
actually, the only attribute that's
actually being read from the
student relation is the Student ID.
So, the user would also need
the select privilege on Student ID.
And, then in the applied A relation,
again only the student ID is being read.
So the user would need the
select privilege on apply of the student ID.
And with these the user
will be permitted to execute this operation.
So far so good, but now let's introduce a little twist.
Let's suppose that we have
a user that we want to
authorize to access information in
the student relation, but only
for students who have applied to Stanford.
How can we possibly do that?
Why don't you give that a thought.
Well, I'll give you the answer right away.
The way we do that is actually by using views.
So we can create a view
that gives us the student
information for Stanford applicants only.
Then we can grant users
privileges on the view, rather than directly on the relations.
So here's our view.
It says we'll find all students
where their student is in
the IDs of the students
who have applied to Stanford and
we'll call that view SS, for Stanford students.
Now we can give a
particular user the select
privilege on SS.
And that allows them to select
the data that's in the view,
but not see any of the data outside of the view.
Specifically, they won't be able
to see student information if the students didn't apply to Stanford.
Let's take a look at a second example that involves views.
Let's suppose that we want
to authorize a user to
delete applications, but, only
applications that are to Berkley.
So, again, we'll set up a view.
This one's a little simpler.
It's just the view of the
app, of the records of
the apply relation where the college name is Berkeley.
And we'll call the view BA.
And then what we want to
grant to the user is the
ability to delete the delete
privilege from the B A view.
Now in this case we
do need to have that view
be updatable by the system that's supporting it.
So, in our video about views, we discuss this issue.
Those are the only examples I'm going
to give for now, but I do
want to emphasize that views are
quite important for customizing authorization
to specific user needs.
And in fact authorization is one
of the most important uses of views in database systems.
Now let's look how privileges are obtained.
When a relation is created, the
creator of that relation becomes the owner of the relation.
And the owner of the relation
has all privileges, and furthermore,
may grant privileges to other users.
So there's a grant statement in
the sequel standard and it looks like this.
We grant privileges on a
particular relation to one or
more users and we have the
option of allowing those users
to further grant privileges to others.
Specifically, the privileges themselves are
the ones that we defined earlier.
And we can have a comma-separated list of them.
So for example, we could say
here something like "select student
ID, comma, delete," and that would give those two privileges.
The users are a list
of actual user names on the data base.
There's also the user, pre-defined
user, called  "public" and that
would grant the authorization to any user of the database.
And finally, the grant option
allows the users who
are getting the privileges to grant
the same or lesser privileges to other users.
Now what do I mean by lesser?
Well it's pretty simple.
If we have, say select and
attributes ABC.
then a lesser privilege would
be something like select A and
B. Now, how about the revoking of privileges.
This gets a little more interesting.
So the command is "revoke privileges."
Again, it would be a list
of privileges on a particular
relation from users, and
again that would be a list of
user names with the possibility
of the special name public
and then there are two
option for revoking privileges, called cascade and restrict.
And they may have to do
with what happens when privileges
are being revoked from a user
who was granted the ability to
grant privileges to others.
So, let's take a look pictorially at what can happen.
Let's suppose that we have
a user who has a privilege
to say select on a
particular relation, R, and we'll
draw that is the root of a graph.
And let's suppose that's say user U1.
And let's suppose that
user grants to user U2
the same privileges, select on
'R' and let's suppose
that's with the grant option
and this is user 'U2.'
So user U2 is allowed
to further grant privileges to other users.
And those may further grant privileges to others.
And we may get a big sub-tree here.
Now let's suppose user U1
decides to revoke the
privilege that was granted to user U2.
So what cast it says,
is if there is revocation
of that form then it will cascade down the tree.
So if you too further granted
privileges, then those would be
revoked and so would
any privileges down below, so
this entire sub tree is
effectively removed all of
those privileges, however, we
have to be a little
bit careful, because it's possible
that say U2 was granted
the select privilege by a
separate user, I guess we'll call
this one U3, who also granted
exactly the same privilege, and
in that case, if U1 does
the revoke, we don't want
to revoke U2's privilege, because
U2 got it from another source.
So technically what cascade says
is that, when we revoke a
privilege, we revoke any
privileges transitively when they
weren't also granted by another source.
So, what your seeing here is
actually called a grant diagram and
I'm not giving you all
the details of grant diagrams, but, you can see basically, what they do.
And, their used is to properly cascade the revoking of privileges.
So again, the cascade option and
the revoke command says to also
revoke any privileges that were
granted from the ones being
revoked transitively, unless they were also granted form another source.
What the restrict option says is
that the revoke command is not
allowed to execute if cascade
would revoke any other privileges.
So if we have any of those
transitive cases, so if
we do have the transitive cases
and we want to use restrict
and we have to manually revoke those
privileges effectively bottom-up through that graph that we say.
Incidentally restrict is the default in the revoke command.
So, if neither of these options
are specified, then, restrict is the one that will be enforced.
Lastly, let me talk a
little bit about where privileges actually reside in reality.
So we have our data that's
being managed by a database system,
and typically we'll have application
developers who are working
directly with the database system, often
developing modules that will be invoked by the end users.
So those application developers have
to have privileges on the database to create the modules.
But then we have the
software that sits above
the database system that is
used by end users and the
end users typically don't have privileges themselves.
They might have separate privileges privileges
to access the modules but
they're not going to be using
the privilege system of the database system.
And similiarly, there may be even
more software layered on top
of the software that the application developer builds.
And again that software itself wouldn't
be having database privileges but
might have an authorization system for the software that it's accessing.
To summarize, database base authorization is important.
It makes sure that users only see the data that they're authorized to see.
It guards the database against being modified by malicious users.
There's a privileged system similar
to file system privileges but specific
to database constructs and users
can only operate on the
data for which they're authorized via their privileges.
There's a grant statement and
a revoke statement in the sequel
standard for granting privileges and revoking privileges.
And again when a relation is
created the owner of the
relation starts with all privileges,
so that's where the granting would begin.
And finally, for having
privileges that go beyond simple
table level operations, views are
a very important construct and in
fact authorization is one of
the most important uses of database views.