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

In this lesson,
we're going to take

a look at system privileges.

So Oracle has two
classes of privileges

that can be given to a user
account, system privileges

and object privileges.

So system privileges
are system-wide,

so they tend to be
non-object-specific and rather

apply to an entire system.

And whenever we give a
privilege, a system privilege

or an object privilege,
we use the GRANT command.

So these are some of the
types of system privileges

that are available to us.

And there are many,
many more but this

can give us kind of an idea
of what a system privilege is.

So the first is CREATE SESSION.

So in order to log
into a database at all,

a user must have the
CREATE SESSION system

privilege, the ability to
create a session in a database.

Without that, they
can't even log in

even if the password is correct.

The account is all
non-locked and OK--

still, the CREATE
SESSION privilege

must be granted to the user.

ALTER DATABASE is
the system privilege

that lets the user work
with the physical structure

of the database.

So this is going to be things
like tablespaces and data

files, and really more
specifically, data

files, the ability
to resize data

files, those types of things.

ALTER SYSTEM will be
involved in the system

configuration of the database.

So this is going to be a
privilege that allows the user

to change database parameters.

So these two, ALTER DATABASE and
ALTER SYSTEM, are very powerful

and should be granted
out with caution.

The next is either CREATE
TABLE or ALTER TABLE.

So these are
separate privileges.

CREATE TABLE allows the
user to create a table.

And by doing so, they
will form their schema,

so that table will
be in their schema.

And ALTER TABLE allows
them to change that table,

so if they were to add a column,
remove a column, so on and so

forth, change data types, that
is an ALTER TABLE privilege.

Conversely, we have
CREATE ANY TABLE.

So how does CREATE ANY TABLE
differ from CREATE TABLE?

Well, CREATE ANY
TABLE allows the user

to create a table in any
schema in the database.

So if we have Sam and Joe,
and Sam has CREATE ANY TABLE,

Sam can create a
table in Joe's schema,

and he can also
create one in his own.

So this is another
system privilege

that should be
used with caution,

but sometimes is necessary
in environments where people

work together and need to
be able to log in and create

a table in a different schema.

Next is DROP ANY TABLE.

And that does kind of
what we might think,

based on the CREATE ANY TABLE.

DROP ANY TABLE allows us to
drop any table from any schema

within the database.

So again, Sam and Joe--

if Sam has DROP
ANY TABLE, he can

drop a table in Joe's schema.

Again, should be
used with caution.

Note here that there is
not a DROP TABLE privilege.

There is no such thing as a
DROP TABLE system privilege.

The ability to drop a table
comes along with the ability

to create a table.

So if you can create a
table in your schema,

you can also drop it.

The last example is
SELECT ANY TABLE.

So with SELECT ANY TABLE,
that allows the user

to select data from any
table in any schema.

Again, that should be used with
a certain amount of caution.

So let's get set up here to take
a look at system privileges.

What I'm going to do is create
a connection for my Kara user.

Username kara, give them
the password Oracle.

Save the password,
database name orcl.

And then we click Test.

Notice the error
that we get here.

User Kara lacks CREATE
SESSION privilege.

So the user Kara was created
but lacks the CREATE SESSION

privilege, and so Kara cannot
connect to the database.

So for an example
here, I'm going

to bring up the command line.

And we can certainly do this in
SQL Developer as well, but just

to give you a little
look at using SQL Plus,

let's connect into the
database as a privileged user.

And we'll use SQL Plus to grant
the necessary system privileges

to Kara.

So we say grant create
session to the user.

Let me come back here
and test, and now Kara

has CREATE SESSION.

We'll save, connect.

Kara is logged
into the database.

So let's see if Kara can
create a simple table.

So we get an error that says,
insufficient privileges.

Why?

Because Kara lacks the CREATE
TABLE system privilege.

So again, back at our
administrator command line,

grant create table to Kara.

Execute again, and she
can create a table.

What about inserting
into the table?

Does she needs some
kind of system privilege

to insert data into that table?

Let's find out.

Even though Kara doesn't
need any privileges

on the particular table,
such as an insert privilege,

notice that we have
a reference here

about no privileges
on tablespace users.

So the users tablespace is
Kara's default tablespace.

And even though
that's been assigned,

she has no privileges on it.

In other words,
she has no ability

to log in and allocate space
in the tablespace called users.

Notice that we can
create the table,

but once space needs
to be allocated,

we need to have that privilege.

Now, there are a number of
ways that we can do this.

We can set up quotas for
her on that tablespace,

but we can also use
a system privilege.

We say grant unlimited
tablespace to Kara.

This allows Kara to use
the data in any tablespace.

So she could create a table
in another table space

and be able to allocate
space within that.

Clear this, and the
row is inserted.