Original subtitles

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.