All language subtitles for [SubtitleTools.com] Object Privileges - Learning Oracle 12c [Video]

In this lesson, we're going
to look at object privileges.

So the two basic
classes of privileges

in an Oracle database are
system privileges and object

privileges.

Object privileges are
specific to a given object.

So whereas system privileges
are system-wide abilities that

are given to a user,
object privileges

are going to relate to
a specific given object.

To use the object privilege
to give that to a user,

we use the GRANT command.

So we say grant privilege on
an object to a given user.

When we want to remove a
privilege, either a system

privilege or an
object privilege,

we use the REVOKE command.

So revoke privilege from user.

So let's get set up here.

We have three connections
in our database now.

We have our Kara user,
we have our Scott user,

and we have system.

When we're using GRANT
commands, the user

needs to have the
power or the ability

to grant those privileges
to another user.

So, certainly, system
will be able to.

But let's go ahead and work with
the objects in Scott's schema

and the Kara user.

I'm going to connect to my
Kara user and my Scott user.

So notice that, as
we're familiar with,

Scott has a number of tables,
bonus step, so on and so forth.

If we look at Kara's table,
she has a TEST TAB table.

So let's see if Scott
can give the ability

to see his objects
to the Kara user.

I'm going to click on
this tabs back and forth

here a little bit.

So the Kara user,
just to prove this,

let's say select
star from bonus.

Well, that's certainly
not going to work.

It says table or
view does not exist.

Because, when we use the
select command like this,

the assumption here is that the
bonus table is in Kara's schema

But we know the bonus table is
actually in the Scott schema.

So we need to have
a way of clarifying

that, of identifying the
schema that the table is in.

And we do that by
saying, Scott.bonus.

So we're saying, select
from the bonus table

that's in the Scott schema.

Let's try this.

Notice that we get
the same error.

Well, that's how Oracle
handles security.

Because, if the user does
not have the privilege

to look at the bonus
table in the Scott schema,

it treats it as if
it's not even there.

And that's a good
approach because it

doesn't give an
error like, you don't

have privileges on Scott bonus.

It just says the table
or view does not exist.

If it gave an error, like
you don't have privileges

on that table, then that
would give an attacker

a little bit of information.

So that would say, oh, there
is a bonus table in the Scott

schema and now I have to find
a way to get access to it.

This way is a
little more obscure

and prevents anyone from knowing
that that table is actually

there.

But Kara cannot select from
the bonus table because she

doesn't have privileges on it.

She doesn't have an
object privilege granted

to be able to see that table.

So let's move over
to the Scott schema

and use the GRANT command.

Grant select on bonus.

Notice that we can refer to the
bonus table without the schema

notation because we're actually
connected in this tab as Scott

to Kara.

Now we come back over
to the Kara session.

And we're able to
select that data.

This is what's known
as a direct grant.

So it's directly granted
from Scott to Kara

to be able to select
from that table.

So, now that Kara is moving,
let's insert some data

into the bonus table.

Notice that we have an
insufficient privileges error

message that comes out.

And that's because
Scott has granted Kara

the ability to select, but
not the ability to insert.

Let's have Scott grant
insert on bonus to Kara.

Grant succeeds.

Rerun the statement and
the row is inserted.

So the same would be true
of update and delete.

So if Kara, right
now, was to attempt

to update the bonus
table or delete from it,

she would again have the
insufficient privileges error.

So what Scott could do--

this would give Kara
select insert update

and delete on the bonus table.

So now back to Kara.

And we see this.

We could say,
update Scott bonus.

And she has that ability now.

So object privileges
are what we use

to give a user specific
privileges on a given object.