Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the role of oracle local authentication and password files". In daily operation, I believe that many people have doubts about the role of oracle local authentication and password files. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "oracle local authentication and password files". Next, please follow the editor to study!

The password file of oracle is used to authenticate DBA permissions.

When the database is turned on to the mount state, the database must have a very important password file / password file, which is stored under $ORACLE_HOME/dbs by default and the default name is orapw. If the password file is lost, there will be an error when the database boots to mount.

The password file contains the user name and password of the sysdba/sysoper user:

[oracle@localhost dbs] $strings orapworcl

]\ [Z

ORACLE Remote Password file

INTERNAL

6A75B1BBE50E66AB

4DE42795E66117AE

IL H

Before the database is started, the built-in users of the database cannot verify their identity through the database itself, through the password file

Oracle can authenticate users, log in before the database is started, and then start the database.

Password files can be rebuilt through the orapwd tool, so password files do not have to be included in the usual backup strategy.

Oracle has two authentication methods: operating system authentication (requires the user to belong to the local DBA group, and then login to oracle through operating system authentication to start the database), password file authentication

The authentication method used by oracle depends on two parameters:

(1) remote_login_passwordfile=none | exclusive | shared

None: password file authentication is not used. If you select this value, it is equivalent to masking the contents of the password file.

Exclusive: to authenticate the password file, use it exclusively (default)

Shared: to authenticate password files, dba users with different instances can share password files

(2) located at $ORACLE_HOME/network/admin/sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=none | all | nts

None: turn off operating system authentication, only password authentication

All: for linux/unix platform, turn off local password file authentication and use operating system authentication

Nts: for windows platform

Experiment:

The oracle server is located in the Linux operating system and the client is located in the windows operating system.

First, look at the remote_login_passwordfile parameter values:

SYS@orcl 11-SEP-14 > show parameter remote_login_passwordfile

NAME TYPE VALUE

-

Remote_login_passwordfile string EXCLUSIVE

Find the sqlnet.ora in the $ORACLE_HOME/network/admin directory and add the following at the end of the file

SQLNET.AUTHENTICATION_SERVICES=NONE

# Purpose: Use parameter SDP.PF_INET_SDP to specify the protocol family or

# address family constant for the SDP protocol on your system.

#

# Supported since: 11.0

#

SQLNET.AUTHENTICATION_SERVICES=none

Even with password file authentication, if we use sqlplus "/ as sysdba" locally, we will get an error message:

[oracle@localhost ~] $sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 12 22:45:56 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:

ORA-01031: insufficient privileges

At this point, we must use the sys username and password to log in:

[oracle@localhost ~] $sqlplus "sys/sys as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 12 22:47:08 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enter sqlnet.ora and change SQLNET.AUTHENTICATION_SERVICES=none to "= all", and then save and exit.

Local authentication is used when logging in again using sqlplus "/ as sysdba":

[oracle@localhost ~] $sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 12 22:49:51 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

In addition, we can use orapwd as a tool to generate password files.

First, take a look at the use of orapwd:

[oracle@localhost ~] $orapwd

Usage: orapwd file= entries= force= ignorecase= nosysdba=

Where

File-name of password file (required)

Password-password for SYS will be prompted if not specified at command line

Entries-maximum number of distinct DBA (optional)

Force-whether to overwrite existing file (optional)

Ignorecase-passwords are case-insensitive (optional)

Nosysdba-whether to shut out the SYSDBA logon (optional Database Vault only).

There must be no spaces around the equal-to (=) character.

We move the original orapworcl located in the $ORACLE_HOME/dbs directory to another directory. Note that remote_login_passwordfile=exclusive, and SQLNET.AUTHENTICATION_SERVICES=none in sqlnet.ora

Try the remote oracle on the client on windows:

SQL > conn sys/sys@win as sysdba

ERROR:

ORA-01031: insufficient privileges

Now let's use orapwd to rebuild the password file:

[oracle@localhost dbs] $orapwd file=$ORACLE_HOME/dbs/orapworcl password=sys entries=5

[oracle@localhost dbs] $ls

Hc_DBUA0.dat initorcl lkORCL peshm_DUMMY_0 spfileorcl.ora

Hc_orcl.dat initorcl.ora orapworcl peshm_orcl_0

Init.ora lkDUMMY peshm_DBUA0_0 snapcf_orcl.f

Try to connect to oracle remotely again:

SQL > conn sys/sys@win as sysdba

Connected.

Look at the contents of this password file:

[oracle@localhost dbs] $strings orapworcl

]\ [Z

ORACLE Remote Password file

INTERNAL

6A75B1BBE50E66AB

4DE42795E66117AE

Create a new user in the database and grant permissions to sysdba:

SYS@orcl 11-SEP-14 > create user sunny identified by sunny

User created.

SYS@orcl 11-SEP-14 > grant sysdba to sunny

Grant succeeded.

Then take a look at the content of orapworcl and find that there is more information about sunny:

[oracle@localhost dbs] $strings orapworcl

]\ [Z

ORACLE Remote Password file

INTERNAL

6A75B1BBE50E66AB

4DE42795E66117AE

SUNNY

53801465943A91BE

You can also see which users have sysdba permissions through the dynamic performance view v$pwfile_users:

SYS@orcl 11-SEP-14 > select * from v$pwfile_users

USERNAME SYSDB SYSOP SYSAS

SYS TRUE TRUE FALSE

SUNNY TRUE FALSE FALSE

At this point, the study on "what is the role of oracle local authentication and password files" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.