- Databases use several authentication and authorization
techniques to protect the sensitive data that they contain.
Database authentication uses many of the same concepts
that we've already talked about.
You can create database users who may then authenticate
to the database using passwords
or other authentication techniques.
The specific technologies available to you will depend
upon the database platform you choose.
For example, Microsoft sequel server supports
three different types of user authentication.
In sequel server authentication, the database uses
local user accounts that are created
inside of the database server.
In Windows authentication mode, the database uses
the Windows accounts available through the underlying
operating system which may be using centralized
authentication through active directory.
And in mixed authentication mode, you can use both local
accounts and Windows accounts.
Other database platforms use similar authentication
techniques that combine local accounts with those
in a central authentication system.
There are also two major techniques that you can use
to control database authorization.
You may assign an account to be a member of a database
role that comes with predefined or
administrator define permissions.
For example, one role may allow a read only
access to the entire database.
Another role might allow full administrative access
while a third role might allow the limited
administrative permissions necessary
to create and manage backups.
You can also assign very granular access
permissions to an individual account.
For example, you might grant a user permission
to select objects from one table
and insert objects into another table.
We won't go into all the specifics of database security
in this course, as you only need to be familiar
with these concepts for the exam.
Let's take a look at database authorization
on Microsoft sequel server.
I'm here at the sequel server management studio
for a database server.
I'm going to go ahead and do a couple of things.
First, I'd like to work with roles.
I want to add an account to a role.
I'm going to go ahead and expand the security folder
for this database and then I'm going to expand
the server roles folder and I can see here a list
of the various roles available to me.
I'm going to add a user to this
role here, called sys admin.
I'm going to double click on the role and I can see
in the window that pops up with the server role
properties that this role already has seven members.
These are mostly built in accounts
that sequel server uses to manage itself.
I'm going to go ahead and explicitly
add another account to this role.
I'm going to go ahead an click the add button
then I'm going to use browse to search
through the current user accounts
and find the one that I'm interested in.
I'd like to add the student one account to this role,
so I'm going to go ahead and check
the check box for it and click okay.
I click okay again and as you can see, I've now added
the student one user to this sys admin role.
I can remove this account by simply clicking
on it and clicking remove.
Now let's take a look at how we can give permissions
on a specific table to a specific account.
Let's use the student one account again and let's
give it permission to insert orders into
a table in the sales database.
I'm going to do this by writing a query
in the structured query language, SQL.
I'll click the new query button and the first command
I'll type is use sales orders example.
That's the name of the database that I'd like to use
and then the command that I'd like to execute
is granting permission on
the orders table to student one.
So I first type the grant command and then I tell
sequel server what permission I would like to grant.
I'd like to grant the insert permission to give the user
the ability to insert records into a table.
Next I need to include which table I'm talking about.
So I'm going to type on and then the name of the table,
which is dbo.orders and then I have to tell the database
who I would like to grant this permission to.
So grant insert on dbo orders to student one.
I can go ahead and execute this command
and I receive a message saying that
the command was completed successfully.
The student one account now has the ability to insert
records into the orders table in
the sales order example database.
I can remove this permission by simply changing the grant
command to a revoke command.
And when I execute that command, the student one user
no longer has insert permission on that table.
That's just a brief example of granting
and revoking database permissions.
The database administrators in your organization
should be very familiar with these concepts
but as a security professional, you should have a basic
understanding of how database authentication
and authorization function.