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

In this lesson, we're going to
take a look at database roles.

So a role is very important
to the concept of security,

but particularly from a
manageability perspective.

A role is just a
container for privileges.

So a role contains
privileges, and then users

are assigned roles.

So to look at the importance
of this, let's take an example.

For instance, let's say we
had 100 users in our database

and we had 1,000 tables.

If we had to directly grant
the privileges for 1,000 tables

to 100 different users
using object privileges,

I mean, that's a
lot of scripting.

That's a lot of work.

And it's also a recipe for
disaster to some degree

because, with that many
individual direct grants,

there's room for mistakes,
and so a user may be given

more privileges than they need.

And that's where
roles come into play.

So a role, being a
container for privileges,

is going to be
assigned to a user

based on whatever
their job role is.

So typically speaking,
in any environment,

we can classify database users
into particular job roles,

if you will.

So we have a developer.

We have a project manager.

We have an application user.

We have a database
administrator.

So rather than having
individual grants

tailored for every one of
the people in those roles,

we can create a role that
encapsulates the privileges

that that job needs.

So we might have
a developer role

that has all the privileges that
a developer needs and a project

manager role for the privileges
that a project manager needs

because they're different.

We do need the
separation of security.

We don't want to give
everyone every privilege.

But we also don't want
to be in the business

of having to micromanage
all those individual grants.

And so that's why
roles are so powerful

and really aren't used as often
as they probably should be.

So Oracle has two types of
roles, system roles and user

roles.

So system roles are provided
by the Oracle RDBMS itself.

They're default roles that
exist in the database.

They don't encapsulate
object privileges as much

as they do system privileges.

Now, a user role can have
system privileges or object

privileges.

But by default,
these system roles

generally encapsulate
system-level privileges.

So let's talk about
the main system roles

that Oracle has available.

First is CONNECT.

Now, CONNECT is very deceiving
because, by that name,

you would think that the
CONNECT role just allows a user

to connect to the database.

Unfortunately, it does allow
that with the CREATE SESSION

system privilege, but it
also allows a lot more.

It allows the ability to create
a table, create an index,

and many other database objects.

So the CONNECT system role
should be used very carefully.

And, in fact,
because of the sort

of confusion with that
name, a lot of DBAs

choose not to use
CONNECT at all.

If you want to give the user
the ability to create a session,

then you give them the
CREATE SESSION privilege, not

the CONNECT role.

The next system
role is RESOURCE.

So RESOURCE is additional object
privileges mainly in the PL SQL

range, the ability to create a
procedure, to create a package,

create a function,
those types of things.

Again, RESOURCE is probably
a little too powerful.

It should be pared
down a little bit.

Oracle has acknowledged
the problems

with CONNECT and RESOURCE.

Although they do still exist
for backward compatibility,

they generally persuade
against their use.

The next system role
is the one that's

probably the most
familiar to people,

and that is the DBA role.

DBA role is full admin
rights in the database.

There's very little that a user
that's been granted the DBA

role cannot do.

There are a couple of
things, such as starting up

and shutting down a database,
but for the most part

DBA has full admin
rights in the database.

So if we create a user and
grant them the DBA role,

then they're going to have a
lot of power in that database.

So the DBA role should
be used very sparingly.

However, in my experience,
it is quite the opposite.

Because people know that the
DBA role has full admin rights,

I've often seen a lot of
users being granted the DBA

role because no one wanted to
go through the work involved

in finding out exactly what
privileges the user needed.

You give them the DBA
role, and then they

have no roadblocks to do
anything they need to do.

And that is true,
however, they also

have more admin rights than
they really should have.

The next system role
is SELECT_CATALOG_ROLE,

and this is the role
that you give to a user

when they want to see
data dictionary views.

It used to be that you
would give SELECT ANY

TABLE to a user in order
for them to see the data

dictionary.

In recent versions, Oracle
created the SELECT_CATALOG_ROLE

because they recognized that a
user may need to see the data

dictionary but may not need to
see every table in the database

to be able to select
from every table.

So SELECT_CATALOG_ROLE role
allows the data dictionary

to be viewed by a user.

The lab system role
is a little different.

It's called PUBLIC.

And PUBLIC is sort
of like a super role.

The PUBLIC role
is already a part

of every user that's
connected to the database.

So any privilege
granted to PUBLIC

will be granted, by default,
to any user in the database.

An example of where we
might want to do this

is something like
CREATE SESSION,

a very simple privilege.

If we create a database user,
we may be under the assumption

that any user created
needs to be able to connect

to the database.

So we might grant CREATE
SESSION to PUBLIC,

and, thereby, any user
created in the database

will have the ability to create
a session, nothing more than

that unless we grant
them specific privileges

or grant those
privileges to PUBLIC.

But they would have the
ability to create a session.

The other type of
roles are user roles.

And so those are
created for users.

So these are more specific
to the objects that

are within the database itself.

We use the term RBAC for
Role-Based Access Control.

And this is a very good approach
at administering security

in Oracle database because
it encapsulates privileges

into particular roles.

So let's look at
an example of this.

We have Kara and Scott.

I'm also going to open
a connection to System.

System is going to have the
ability to create these roles.

So when we create a role,
we simply say, create role,

and the role is created.

Now the role is
essentially useless

because it's not been
granted to a user

and it has no privileges.

So let's test some things
out with Kara again.

So Scott's tables
are BONUS and DEPT.

We've dealt with
Kara's access to BONUS.

What if we try to do select
star from scott.dept?

We get table or
view does not exist.

How about EMP?

Again, table or
view does not exist.

So Kara doesn't have access to
Scott's tables DEPT and EMP.

Now, we could give
her direct grants,

but then we're starting to
go down the road that we've

discussed of having all those
different individual grants

that need to be managed.

We already have a role here.

Let's grant some
privileges to the role.

But we don't grant them to Kara.

We grant them to
the role, similarly.

So now select privilege on the
DEPT table in the Scott schema

has been granted to the
role called new_role.

So let's try this again.

Again, we get the same failure.

Why?

Because even though we've
granted them to the role,

we haven't granted
that role to Kara.

So we have to make the
individual grants to the role,

and we have to grant
the role to the user.

We'll grant new_role to Kara.

And now, if Kara selects,
table or view does not exist.

We're going to right-click
on Kara's connection

and open a new connection here.

We have a problem
with this, and this

is sort of a bug in SQL
Developer, if you will.

So let's try to verify that
this role in fact does work.

Let's go ahead and close
out Kara's session.

We're going to click Disconnect.

And we'll allow Commit
Changes and reconnect.

And you can see that we
have given the role to Kara,

but SQL Developer
required us to reconnect.

Now, if this was an
application or SQL*Plus,

it does not require that, and
that is a bug in SQL Developer.

So we want to demonstrate that,
because if you attempt this

yourself, you will see that.

But in fact, Kara does have that
access through that role now.

And we can also look
at another one that

was granted through
the role to see

that, in granting privileges
to the role and then the role

to the user, we're achieving
role-based security.