Oracle Password file Management

Password file function:

The password file is used for the DBA user's login authentication.

DBA User: A user with sysdba and Sysoper permissions, that is, the SYS and system users of Oracle.

Log on Locally:

1) Operating system certification:

[Email protected] ~]$ sqlplus "/as sysdba"

[Email protected] ~]$ Sqlplus/as SYSDBA

[Email protected] ~]$ Sqlplus Sys/tiger as Sysdba

2) Password file authentication:

[Email protected] ~]$ sqlplus Sys/[email protected] as Sysdba

Remote Password file login:

[Email protected] ~]$ sqlplus sys/[email PROTECTED]:1521/ORCL as Sysdba

Password file location:

Linux/unix:[[email protected] ~]$ ls $ORACLE _home/dbs/orapw$oracle_sid

/u01/oracle/10g/dbs/orapworcl

/u01/oracle/10g/dbs/orapwrezin

Windows: $ORACLE _home/oradate/orapw$oracle_sid

Password file Lookup order:

1) opapw<sid>

2) ORAPW

None of the above two can be found, and the validation fails.

password file authentication or OS Certifications:

1) parameter file: remote_login_passwordfile=none|exclusive|shared

None: Do not use password file authentication

Exclusive: Use password file authentication, own exclusive use (default)

Shared: Using password file authentication, different instances of DBA users can share password files (must be used under ASM)

2) under $ORACLE _home/network/admin/sqlnet.ora file:

Sqlnet. Authentication_services =none|all|nts (not set by default under Linux)

None: Turn off OS authentication, only password file authentication

All:linux platform shut down the local password file authentication, operating system authentication, but remote (different machine) can be password file authentication

Use under Nts:windows (Bucket Linux All)

Practice:

1) Configuration: remote_login_passwordfile=exclusive

Sqlnet. Authentication_services =none

Result: can password file authentication (local/Far ultra), not operating system authentication

[[email protected] ~]$ sqlplus Sys/tiger as sysdba(local password file login)

[[email protected] ~]$ sqlplus Sys/[email protected] as SYSDBA(local password file login)

Sql*plus:release 10.2.0.1.0-production on Sat 14 19:00:39 2015

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

???:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options

Sql> exit

? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options??

[[email protected] ~]$ sqlplus/as sysdba(OS authentication)

Sql*plus:release 10.2.0.1.0-production on Sat 14 19:00:51 2015

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

ERROR:

Ora-01031:insufficient Privileges

Enter User-name:

Or

[[email protected] ~]$ sqlplus "/as sysdba"(OS authentication)

Sql*plus:release 10.2.0.1.0-production on Sat 14 19:01:04 2015

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

ERROR:

Ora-01031:insufficient Privileges

Enter User-name:

2) Configuration: remote_login_passwordfile=exclusive

Sqlnet. Authentication_services =all

Result: The local password file authentication is not available, but the remote password file authentication can be used, the native OS authentication is available

[[email protected] ~]$ sqlplus "/as sysdba"(native OS Authentication login succeeded)

Sql*plus:release 10.2.0.1.0-production on Sat 14 19:45:35 2015

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options

[[email protected] ~]$ sqlplus Sys/[email protected] as Sysdba(native password file authentication failed)

Sql*plus:release 10.2.0.1.0-production on Sat 14 19:46:52 2015

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

ERROR:

Ora-12641:authentication Service failed to initialize

Enter User-name:

C: >sqlplus sys/[email PROTECTED]:1521/ORCL as Sysdba( remote password file login successful)

Sql*plus:release 11.2.0.1.0 Production on Saturday March 14 11:58:38 2015

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

Connect to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options

Sql>

[[email protected] ~]$ sqlplus scott/tiger(normal user local OS login succeeded)

Sql*plus:release 10.2.0.1.0-production on Sat 14 20:01:57 2015

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options

Sql>

[[email protected] ~]$ sqlplus scott/[email protected](login failed)

[[email protected] ~]$ sqlplus scott/t (Login failed)

Sql*plus:release 10.2.0.1.0-production on Sat 14 20:02:52 2015

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

ERROR:

Ora-12641:authentication Service failed to initialize

Enter User-name:

Password file Management:

Password file creation:orapwd command usage (not recommended)

[Email protected] ~]$ orapwd

Usage:orapwd file=<fname> password=<password> entries=<users> force=<y/n>

where

File-name of password file (mand), and create the name of the password files:orapw<sid>

Password-password for SYS (mand),-->sys user password

Entries-maximum number of distinct DBA and--how many SYSDBA and Sysoper users can be placed inside the password file (in binary mode, that is, input 1 means a minimum of 4, to remove duplicates)

Force-whether to overwrite existing file (opt),-->oracle 10g after the new parameter, usage: force=n or force=y, indicates whether the password file exists to overwrite, Before 10g can only delete the original password file, and then create.

Opers (opt),

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

For example: [[email protected] ~] $orapwd file=orapworcl Password=rezin Entries=1 force=y

Password File modification: for example Modify SYS user Password or grant SYSDBA , Sysoper Permissions

orapwd rebuild password file: Not recommended, may make other SYS users unable to log in

ALTER user SYS identified by <new password>

Grant Sysdba|sysoper to <user>;

Revoke Sysdba|sysoper from <user>

View Password file contents: Strings instruction to view the contents of a binary file.

[Email protected] dbs]$ strings ORAPWORCL

]\[z

ORACLE Remote Password File

INTERNAL

9d9ff9fdafb17385

e6baa2164c375c09

SYSDBA and Sysoper Specific differences: View official documents

To view system permissions through the System_privilege_map view:

Sql> SELECT * from System_privilege_map

2 where name like '%sys% ';

PRIVILEGE NAME Property

---------- ---------------------------------------- ----------------------------------------------------------

-3 ALTER SYSTEM 0

-4 AUDIT SYSTEM 0

-83 SYSDBA 0

-84 Sysoper 0

View user system permissions through the password file View V$pwfile_user view:

Sql> select * from V$pwfile_users;

USERNAME Sysdb SYSOP

------------------------------ ----- -----

SYS true True

Through the above query can know, SYS user login mode can either through as SYSDBA login schema display ' SYS ', or through as Sysoper login schema to display ' public '.

Last Verification:

1. By combination:

1) Remote_login_passwordfile=none Sqlnet.authentication_services=none

2) remote_login_passwordfile=exclusive Sqlnet.authentication_services=none

3) Remote_login_passwordfile=none Sqlnet.authentication_services=all

If it is win, please change all to NTS

4) remote_login_passwordfile=exclusive Sqlnet.authentication_services=all

Test separately:

This machine: Sqlplus/as SYSDBA

Sqlplus sys/<pswd> as Sysdba

Sqlplus sys/<pswd>@<sid> as Sysdba

Remote: Sqlplus sys/<pswd>@<sid> as Sysdba

Sqlplus sys/<pswd> @ip:p ort/<sid> as Sysdba

Test which combinations can log on successfully and which cannot log on successfully.

Summarizes if OS verification is turned off, how to turn off password file validation, how to turn off local password file validation, and how to turn off remote password file validation.

2. Modify remote_login_passwordfile=shated and then use ALTER USER SYS identified by <pswd>; to modify the password to test the success of the modification.

3. What do you do if the SYS password is missing or incorrect?

4, SYSDBA, sysoper distinguish between, ordinary users how to use the password file has been SYSDBA or sysoper login.

Oracle Password file Management