All language subtitles for [SubtitleTools.com] Oracle Schema Based Approach - Learning Oracle 12c [Video]

In this lesson,
we're going to take

a look at Oracle's schema based
approach to user accounts.

So traditionally, there
are two classifications

of database accounts,
that is to say,

accounts that are
stored in the database.

The first type is
an object owner.

So an object owner is
going to be exactly what it

would sound like.

The account is the
owner of objects.

And, by objects, we mean things
like tables, and indexes,

and sequences, and synonyms.

The second type of
database account

we have is an interactive user.

And that's going to be a user
that connects into the database

and does work--

issues queries, creates tables,
inserts data, deletes data.

So even though there are
those two classifications

of database account, Oracle uses
a slightly different approach.

And it's called a
schema based approach.

And, in this approach,
the two types

of classifications that we've
mentioned are one and the same.

So a user is also an object
owner or, at least, it can be.

So let's take a look at this.

So in Oracles, we have schemas.

So let's say we
have a user, Kara,

who connects to the database.

She can connect to
the database and do

whatever work she has the
permissions and the rolls

to do.

But if Kara creates objects,
such as an EMP table, a DEPT,

or an IDx1 index,
for instance, we

say that Kara now has a schema.

And so we say, this
is Kara schema.

We say the EMP table
is in the Kara schema,

and the IDx1 index is
in the Kara schema.

So this is the schema
based approach.

So there are a couple
of problems with this.

Generally, it's bad security
to mix users and objects.

And that's mainly
because external accounts

can be compromised.

So let's say we have a
development house where

we develop applications, and
part of those applications

work with database data.

If we have a developer that
goes into the database,

and creates tables
and indexes, those

will belong to that
developer schema.

So, if Kara, for
instance, is a developer

and she goes in, logs in, and
creates some of the application

tables, indexes, sequences,
so on and so forth,

those are going to
belong in her schema.

Now, it is possible to go
ahead and make that workable.

The application would just need
to connect to Kara's schema.

However, it's generally
bad security because,

if Kara's account is
compromised and she's

using it to connect
into the database,

then the attacker
could have access

to all of that data that's
stored in her schema.

So Oracle allows
us with the schema

based approach the flexibility
to be either very secure or not

very secure.

And really the
choice is up to us.

So, in the previous example,
that's not very secure,

having a user with an
interactive account that

also owns objects.

But Oracle's schema
based approach

does allow for a separation
of user accounts.

Object owners and
database users, we just

have to build it that way.

So let's say we have object
owners, such as HR for Human

Resources, FIN for Finance,
OE for Order Entry,

and each one of those
schemas has objects in it.

So EMP and bonus in the HR
schema, account and receivable

in the finance scheme,
so on and so forth.

We can allow those
users HR, OE, and FIN

to be object owners
and nothing else,

and then we can create database
users such as Kara, Lee,

and Saul, and those
users will connect

in given the proper privileges
and use those schemas

or, rather, the objects
in those schemas.

What we can then do is lock
the object owner accounts.

So those accounts
would actually be

locked so that, even if
you did know the password,

the account will
need a password,

but even if you
know the password,

you couldn't log in
with that account.

So object owners
are locked out so

that no one can connect
directly into the object schema

and see the data.

So each one of
these database users

must have the given
permissions to be

able to look at these objects.

And, in doing so, we can
use Oracle schema based

approach for greater security.