Go Oracle 10g/11g Password Policy user password is case sensitive

A. Oracle 10g1.1Description

Under Oracle 10g, by default, passwords are case-insensitive. This is due to the default parameters for the password file.

C:\users\administrator.daviddai>orapwd

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

where

File-name of password file (required),

Password-password for SYS would is prompted if not specified at CommandLine,

Entries-maximum number of distinct DBA (optional),

Force-whether to overwrite existing file (optional),

Ignorecase-passwords is case-insensitive (optional),

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

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

This is the parameter description of the password file Creation command, here we look at a parameter:

Ignorecase-passwords is case-insensitive (optional),

Attention:

This parameter is used to specify whether the case is sensitive, and the default is Insentive, which is insensitive, so for oracle10g databases, the passwords are case insensitive by default. however , in Oracle 10g , we are unable to use this parameter, which is explained in subsequent tests.

Although only users with SYSDBA and Oper permissions are saved in the password file, other users will also be affected.

1.2Example

Here we do a test on Oracle 10g, in order to avoid the impact of OS certification, we can disable OS authentication here.

Oracle OS certified password file loss handling

http://blog.csdn.net/tianlesoftware/article/details/4698293

Sql> select * from V$version;

BANNER

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

Oracle Database 10g Enterprise editionrelease 10.2.0.5.0-64bi

PL/SQL Release 10.2.0.5.0-production

CORE 10.2.0.5.0 Production

TNS for Linux:version 10.2.0.5.0-production

Nlsrtl Version 10.2.0.5.0-production

-- Create 2 a test User:

Sql> Create user dave1 identified Bydave;

User created.

Sql> Create user dave2 identified Bydave;

User created.

Sql> Grant Connect,resource to dave1;

Grant succeeded.

Sql> Grant Connect,resource to Dave2;

Grant succeeded.

Sql> Grant Sysdba,sysoper to dave1;

Grant succeeded.

Sql> select * from V$pwfile_users;

USERNAME Sysdb SYSOP

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

SYS true True

DAVE1 true True

Sql> Conn Dave1/dave;

Connected.

Sql> Conn Dave2/dave;

Connected.

Sql> Conn Dave1/dave;

Connected.

Sql> Conn dave1/dave1;

ERROR:

Ora-01017:invalid Username/password; Logondenied

Warning:you is no longer connected tooracle.

As can be seen here, under Oracle 10g, passwords are case-insensitive.

-- Here we can continue to test:

We will SYSDBA permissions from the DAVE1 user revoke, and then test:

Sql> Conn/as sysdba;

Connected.

Sql> revoke sysdba,sysoper from dave1;

Revoke succeeded.

Sql> select * from V$pwfile_users;

USERNAME Sysdb SYSOP

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

SYS true True

We continue to use dave1 to test:

Sql> Conn Dave1/dave;

Connected.

Sql> Conn dave1/dave1;

ERROR:

Ora-01017:invalid Username/password; Logondenied

Warning:you is no longer connected tooracle.

From here we can see that it's just simple Revoke Does not solve the problem, we rebuild a password file, and then test it to see:

Rac2:/u02/app/oracle/product/10.2.0/db_1/dbs>orapwd file=orapwanqing password=oracle force=y;

Sql> Conn Dave1/dave;

Connected.

-- still can continue to use, no case

put DB Reboot to see:

sql> shutdown Immediate

Ora-01031:insufficient Privileges

Sql> Conn/as sysdba;

Connected.

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup

ORACLE instance started.

Total System Global area 583008256 bytes

Fixed Size 2097984 bytes

Variable Size 159386816 bytes

Database buffers 415236096 bytes

Redo buffers 6287360 bytes

Database mounted.

Database opened.

Sql> Conn Dave1/dave;

Connected.

So we rebuild the password file does not solve the problem. So in Oracle 10g, passwords are case-insensitive, and the status quo is truly improved in Oracle 11g.

We can verify it in Oracle 10g:

Rac2:/u02/app/oracle/product/10.2.0/db_1/dbs>orapwd file=orapwanqing password=oracle force=y ignorecase=y

usage:orapwdfile=<fname> entries=<users> force=<y/n>ignorecase=<y/n> nosysdba=<y/n>

where

File-name of password file (required),

Password-password for SYS would beprompted if not specified on command line,

Entries-maximum number of distinct DBA (optional),

Force-whether to overwrite existing file (optional),

Ignorecase-passwords is case-insensitive (optional),

Nosysdba-whether to shut out of the Sysdbalogon (optional Database Vault only).

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

after we set the Ingroecase parameter here, the ORAPWD command does not work.

So, as you can see, IgnoreCase is a truly new feature in Oracle 11g that can be used in Oracle 11g. This parameter is used to set whether the SYSDBA or Sysoper permissions are case-sensitive when logged on through a password file.

two. Oracle 11g2.1Description2.1.1 Sec_case_sensitive_logonParameters

In Oracle 11g, Oracle begins to differentiate between password case, which is determined by the parameters: Sec_case_sensitive_logon, the official website of this parameter description:

Http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams221.htm

Property	Description
Parameter type	Boolean
Default value	True
Modifiable	ALTER SYSTEM
Range of values	true | False
Basic	No

Sec_case_sensitive_logon enables ordisables password case sensitivity in the database.

Values:

(1) True

Database Logonpasswords is case sensitive.

(2) False

Database Logonpasswords is not case sensitive.

If the user is importing from Oracle 10g, then the value of Password_versions is "10G", and maintaining a case-sensitive password does not depend on the Sec_case_sensitive_logon parameter setting. Assuming that the Sec_case_sensitive_logon parameter is set to True, the password becomes immediately case sensitive.

Sql> SELECT * from V$version whererownum=1;

BANNER

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

Oracle Database 11g Enterprise editionrelease 11.2.0.1.0-64bit Production

Sql> Show Parameter Password_versions

Sql>

2.1.2Password file

C:\users\administrator.daviddai>orapwd

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

where

File-name of password file (required),

Password-password for SYS would is prompted if not specified at CommandLine,

Entries-maximum number of distinct DBA (optional),

Force-whether to overwrite existing file (optional),

Ignorecase-passwords is case-insensitive (optional),

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

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

The ignorecase parameter of the Orapwd tool allows you to control if the password in the password file is case sensitive, and its default value is "N", which is the default case sensitivity. If privileged users (SYSDBA and Sysoper) are imported from a previous version of the database, their passwords are also included in the password file, which will retain the case-sensitive password until the password is modified.

　　

If you specify Ignorecase=y, Oracle ignores the case of passwords when password authentication occurs.

2.2Example One

Sql> Show Parameter Sec_case_sensitive_logon

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Sec_case_sensitive_logon Boolean TRUE

-- To create a test User:

Sql> Create user Dave identified Bydave;

User created.

Sql> Grant Connect,resource to Dave;

Grant succeeded.

Sql> Conn Dave/dave;

ERROR:

Ora-01017:invalid Username/password; Logondenied

Warning:you is no longer connected tooracle.

Sql> Conn Dave/dave;

Connected.

-- Case sensitive here

-- Modify Parameters:

Sql> Conn/as sysdba;

Connected.

Sql> alter system set SEC_CASE_SENSITIVE_LOGON=FALSE;

System altered.

Sql> Show Parametersec_case_sensitive_logon

NAME TYPE VALUE

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

Sec_case_sensitive_logon Boolean FALSE

Sql> Conn Dave/dave;

Connected.

--Disabling Sec_case_sensitive_logon is not case sensitive.

2.3Example Two

In the above we are set by Sec_case_sensitive_logon, here we use the password file parameters to set.

In Oracle 11g, the ignorecase parameter of the password file is used to set whether SYSDBA or Sysoper permissions are case-sensitive when logged on through a password file. By default, the ignorecase parameter is case-sensitive.

Note that the Ingorecase parameter here simply creates a case-sensitive password in the password file.

To disable OS authentication, please refer to the following:

Oracle OS certified password file loss handling

http://blog.csdn.net/tianlesoftware/article/details/4698293

Sql>show Parametersec_case_sensitive_logon

NAME TYPE VALUE

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

Sec_case_sensitive_logon Boolean TRUE

C:\users\administrator.daviddai>orapwdfile=d:\app\administrator\product\11.2.0\dbhome_1\database\ Pwddave.orapassword=oracle Force=y Ignorecase=y

-- Test ignorecase parameter pairs give SYSDBA Impact of rights users:

C:\users\administrator.daviddai>

Sql> Conn/as SYSDBA

Connected.

Sql> Create user dave1 identified Bydave;

User created.

Sql> Grant Connect,resource to dave1;

Grant succeeded.

Sql> Conn Dave1/dave;

ERROR:

Ora-01017:invalid Username/password; Logondenied

Warning:you is no longer connected tooracle.

Sql> Conn Dave1/dave;

Connected.

-- You must also use the correct password, including case.

Sql> Conn/as sysdba;

Connected.

Sql> Grant Sysdba,sysoper to dave1;

Grant succeeded.

Sql> select * from V$pwfile_users;

USERNAME Sysdb SYSOP Sysas

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

SYS true True FALSE

DAVE1 true True FALSE

Sql> Conn Dave1/dave;

Connected.

Sql> Conn Dave1/dave;

ERROR:

Ora-01017:invalid Username/password; Logondenied

Warning:you is no longer connected to ORACLE.

-- with this test we can see that when creating a password file, it is later given SYSDBA permission is not affected by the user, you cannot specify whether it ignores the case.

-- Test SYS User:

When we created the password file, we were competing against this user.

Sql> Conn Sys/oracle as SYSDBA;

Connected.

Sql> Conn Sys/oracle as SYSDBA;

Connected.

Sql> Conn Sys/dave as SYSDBA;

ERROR:

Ora-01017:invalid Username/password; Logondenied

Warning:you is no longer connected tooracle.

--The SYS user has successfully ignored the case problem.

Summary:

Through the above 2 tests, we can draw a conclusion:

(1) The Sec_case_sensitive_logon parameter can specify whether the user's password is case-sensitive.

(2) The ignorecase parameter in the password file only disputes the case when it is created, in short, only the SYS user.

three. Oracle 10g,11gwith theDBlink

With the previous instructions, you can see the Oracle 10g and 11g password out of the authentication policy is different, so if you build dblink between different repositories, then the password will become a problem, there may be a wrong password, the specific considerations are as follows:

(1) 11g connected to 11g:

When you create a database connection, the password must match the password case of the remote database user.

(2) 11g connected to the previous 11g database:

The password used to create the data flow connection is arbitrary, because the remote database ignores the case.

(3) 11g previous database connected to 11g:

Change the password of the remote user to uppercase, or enclose it in quotation marks, in order to authenticate with the previous 11g database.

http://blog.csdn.net/tianlesoftware/article/details/7383110