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

In this lesson, we'll be
talking about the password

file and administrative
privileges in Oracle.

So in order to do the
operations that a DBA must do,

it's often necessary
that you connect

with the highest administrative
privileges possible.

So there's two types
of ways that we

could make an administrative
connection to an Oracle

database.

One is locally-- that is to say,
actually on the server itself--

or remote.

So you would be connecting in
with a remote tool of some sort

in order to administer
the database.

Now, not every action
that a DBA takes

requires administrative
privileges.

Normally, we think of things
like shutdown and startup,

the creation of a new
database, backup and recovery,

those types of
operations, as requiring

administrative privileges.

The thing that either a
local or a remote connection

will require is that the user
have the SYSDBA privilege.

And that's the
highest role that we

can give to an administrator
in the database.

So local connections are
made using the SYSDBA role.

And that might be
something like,

if we're using a
Linux database, we

might use Secure Shell to
connect to the server itself.

And then we're
actually on the server.

And we connect using
the SYSDBA role.

And even though that is a
connection from a remote place,

or can be, that
we don't actually

have to be using the keyboard
at the server itself,

we call that a local
connection because you're

using SSA, Telnet,
or, in Windows,

you might use Remote Desktop
connection to being virtually

on the machine.

And so we call those
a local connection.

And those are made
using the SYSDBA role.

Remote connections, however,
require a password file.

A password file is
going to be an encrypted

file out on the operating system
actually in the Oracle home

directory that will store
information about users that

can connect with remote
administrative privileges

and the password that they have.

And so we use a tool,
a command line tool,

called orapwd to
create a password file.

The password file will
be located in the Oracle

$ORACLE_HOME/dbs on Linux.

And the name of the file will
be orapwd and then the SID.

On Windows, it will be in
the Oracle home directory

under the database directory
with a slightly different name.

So even though we can't open the
password file and look at it,

we can see information about the
users from the v$pwfile_users

dynamic data dictionary view.

So let's take a
look here at making

a local administrative
connection.

So when we do this, we're
going to use something

like the sqlplus user.

And we'll do sqlplus
slash as sysdba.

We're connected to the database
with the system, highest system

privileges.

And we're able to work on
the database from there--

startups, shutdowns,
those types of things.

But let's look at this again.

So what are we actually
logging in as whenever

we use sqlplus slash as sysdba?

Well, another way that we can
make a connection with SQL Plus

would be username/password.

So it's username slash password.

And if we put in the
incorrect password,

we get an invalid
username/password.

So what are we doing
when we do this,

because we haven't logged in
with any given particular user?

What we're actually doing is
something called operating

system authentication.

So operating system
authentication

is not authenticating our user
with a username and password.

So it's basically like a blank
username and a blank password

separated by a slash.

What we're actually
doing is authenticating

to the user or group on
the operating system.

So on-- the best example,
probably, is in Linux,

where the user that you're
logged into on the system, not

the database, but the
system, server itself,

belongs to a group called DBA.

And if you belong to
that group, than you

are able to connect as SYSDBA.

So if we connect
sqlplus slash as sysdba,

let's do a select
user from dual.

And that tells us that we're
logged in using the SYS user.

So what if we were
to log in as SYS?

Well, that's legitimate as well.

We could do it that way.

However, just to give an
example of how this works,

let's put a wrong
password in there.

And it connects.

So SYS does have a password.

And that would be necessary
for a remote connection.

But since we're using operating
system authentication,

then the username and password
are essentially ignored.

So let's talk about
remote authentication.

So here we are in the
Oracle home directory.

So what we said on Windows--

that the password file necessary
for a remote connection

would be in the Oracle home
directory in the database

directory--

we can see PWDorcl.ora.

And that's the password file.

If we want to look at the users
that are granted the ability

to be a SYSDBA, we would
look from v$pwfile_users.

And so at this point, we see the
user SYS, as we might expect,

and then a couple of
other SYS-related ones

that are fairly new in 12c.

But we don't see
any other users.

So what if I was to do
grant sysdba to scott

and now select from it again?

And now we can see that
the SCOTT user can connect

in remotely as a SYSDBA.

And that validation would
occur when his information

is in the password file
and is read from that

and validates him
as a SYSDBA that

can connect to the database.