Original subtitles

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.