All language subtitles for [SubtitleTools.com] Creating And Dropping Users - Learning Oracle 12c [Video]

In this lesson, we're going
to look at creating users

in Oracle 12 C. So creating
a user in Oracle 12

C is pretty similar
to the way it's

been in previous
versions of Oracle,

and what you see here
is a syntax tree.

So let's go through
this and then

we'll take the syntax
tree, substitute

some real values in it,
and then create this user.

So the Create User
statement is the syntax

that we use to create
a user in Oracle.

So we say create user,
we give a user name,

and then we say identified
by and then the method.

There are several
methods that Oracle

can use to validate a user.

So it can use things
like external services,

such as Kerberos or NIS.

But typically, we use
this identified by clause

with a password, and we say
identified by a password.

Although we can use other
methods if they're setup.

Next is default table space.

This is going to be the
name of the table space

that by default will contain any
objects that the user creates.

So if our new user
creates a table

and does not specify what
tablespace it goes in,

then it will go in the default
table space that's listed here.

Next is the temporary
table space.

Oracle is capable of having
multiple temporary tablespaces,

and if the user executes
the sort operation,

then they're likely going to
need a temporary tablespace.

And here is where we specify
which temporary table

space will be used by the user.

Next is quota.

Quota is sometimes
used with table spaces.

So in this line, we have the
ability to assign a quota.

That is to say, a certain amount
of data or a limit on space

for a given tablespace.

So sometimes when
we create users,

we might give them a
default table space

and didn't give them a
quota on that table space.

They have the ability to use 100
megabytes of that table space.

Quotas can be
fairly problematic,

though because if they fill up,
if that table space quota is

reached, then any
operations by the users that

do space operations those
will, in fact, fail.

So quotas are sometimes not
used in normal administration.

The next is the profile
name, and the profile

is going to be a list
of resource limits

that we can put on a user.

Things like the amount of time
a password will stay valid,

things like the
amount of CPU that

can be used by a
given user session.

These types of things are
lumped together in a profile.

The next line says
password expire,

and so we can allow the
password for this user

to expire when we
create the user.

So why would we want to do that?

Well, oftentimes, a DPA will
create users and then give them

some sort of default password
here in identified by.

But in a strict
security environment,

the database
administrator does not

need to know the
password of the user.

And so if we expire the
password the first time

the user logs in, that
user will be required

to change their password.

So the password that the
DBA assigns to this user

will only be valid until
the user logs in and then

is prompted to change it.

And last is account,
either locked or unlocked.

So when we create
a user, by default,

that account is
going to be unlocked.

But we could create the
account automatically locked

from the very beginning.

So why would we do this?

Well, oftentimes, if a
DPA has a script that

creates a lot of users,
not all of those users

need to be used
that very moment.

So during a build out
of an application,

we may have tables and different
schemas that own those tables,

and each one of those schema
accounts is going to be a user

and it's created by a
create user command.

So that account we
may choose to lock

until it's needed later on.

All right, so we've brought
our syntax tree here.

So let's take this and fill
it out and create a user.

So we say create user Cara
identified by password.

We're going to use user,
temporary table space

will be temp, profile
name will be default,

password not expire,
and it's the default,

but we'll go ahead and
take account unlock.

All right, so we're going to
attempt to create this user,

then we can do research if
we have any problems with it.

All right, so here's our error.

Invalid default table
space identifier.

An identifier does not
follow default table space.

All right, so let's
look at our table spaces

and make sure we have
them properly named.

All right, so notice
here we have a default

tablespace called user,
but we have no table space

name with that name.

Also, temporary table
space temp and we

do have a temporary table
space with that name.

So let's make sure we have
the right name for the default

table space and attempt
to create our user again.

So this is common
practice when you

create a user to make sure that
you have all the right default

setup for it.

Our profile is set to be
default and that profile

exists in the database,
but we may want

to set it to something else.

So sometimes there's
a little research

that goes into creating a user.

So what if we want
to change a user?

Well, let's look at a
very common command.

We want to change
the password of Cara.

Let's say we have a weak
password here certainly,

we could use the alter user
command and we say identified

and that user is now changed.

We'll take this and make this
a simple one because we're

going to use it later.

Another alter user
command that we can use

is the account unlock.

So by default, our
account is unlocked.

If a user attempts to
log in to that account

and enters the wrong password
a certain number of times,

then the account may lock and
then their account is locked

and they can't get in,
and so we use alter user

Cara account unlock.

So those are just some simple
examples of creating a user

and changing that user's status.