Oracle 10g/11g password policy user password case sensitive description

 

1. Oracle 10g1. 1 Description

In Oracle 10 Gb, passwords are case-insensitive by default. This is due to the default parameters of the password file.

 

C: \ Users \ administrator. David Dai> 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 will be prompted if not specified at CommandLine,

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 vaultonly ).

 

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

 

This is a parameter description of the command used to create the password file. Here we will look at a parameter:

Ignorecase-passwords are case-insensitive (optional ),

 

Note:

This parameter is used to determine whether the password is case sensitive. The default value is insentive, which means the password is case insensitive for Oracle10g databases. However, we cannot use this parameter in Oracle 10 Gb, which will be used in subsequent tests.

Although only users with sysdba and role permissions are saved in the password file, other users are also affected.

 

1.2 example

Here we conduct a test on Oracle 10 Gb. To avoid the impact of OS authentication, we can disable OS authentication here.

 

Oracle OS authentication password file and password loss

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 two test users:

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

Dave1 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 are no longer connected tooracle.

 

It can be seen from this that the password in Oracle 10 Gb is case-insensitive.

 

-- Here we can continue the test:

We will grant sysdba permissions from 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

 

 

We continue to use dave1 for testing:

 

SQL> conn dave1/Dave;

Connected.

SQL> conn dave1/dave1;

Error:

ORA-01017: invalid username/password; logondenied

 

 

Warning: you are no longer connected tooracle.

 

From this we can see that a simple revoke cannot solve the problem. We need to recreate a password file and 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.

-- Continue to use, case-insensitive

 

Restart the database to see the following:

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.

 

Therefore, rebuilding the password file cannot solve the problem. Therefore, in Oracle 10g, passwords are case-insensitive. This situation can be improved in Oracle 11g.

 

We can verify in Oracle 10 GB:

 

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 will beprompted 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 sysdbalogon (optional Database Vault only ).

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

 

After the ingroecase parameter is set, the orapwd command cannot run normally.

 

Therefore, it can be seen that ignorecase is a new feature in Oracle 11g, which can be used only in Oracle 11g. This parameter is used to set whether the sysdba or sysoper permission is case sensitive when logging on to a password file.

 

 

Ii. Oracle 11g2. 1 Description 2.1.1 sec_case_sensitive_logon Parameter

In Oracle 11g, the password is case sensitive. this parameter is determined by sec_case_sensitive_logon. The official website will describe this parameter:

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 sensitivein the database.

 

Values:

(1) True

Database logonpasswords are case sensitive.

(2) False

Database logonpasswords are not case sensitive.

 

If the user imports 10 GB Data from Oracle, the value of password_versions is "10 Gb". Maintaining a case-sensitive password does not depend on the sec_case_sensitive_logon parameter settings. If the sec_case_sensitive_logon parameter is set to true, the password is 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.2 Password File

C: \ Users \ administrator. David Dai> 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 will be prompted if not specified at CommandLine,

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 vaultonly ).

 

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

 

 

The ignorecase parameter of the orapwd tool allows you to determine whether the password in the password file is case sensitive. The default value is "N", that is, the default value is case sensitive. If privileged users (sysdba and sysoper) are imported from previous database versions, their passwords will also be included in the password file, and these users will keep the password in case sensitive, the password is modified.

　　

If ignorecase = y is specified, Oracle ignores the case sensitivity of the password during password verification.

 

2.2 Example 1

 

SQL> show parameter sec_case_sensitive_logon

Name type value
-----------------------------------------------------------------------------
Sec_case_sensitive_logon Boolean true

 

-- 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 are no longer connected tooracle.

SQL> conn Dave/Dave;

Connected.

-- Case sensitive

 

 

-- 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 case insensitive.

 

 

2.3 Example 2

In the preceding settings, we use sec_case_sensitive_logon. Here we use the password file parameters to set the parameters.

 

In Oracle 11g, The ignorecase parameter of the password file is used to set whether the sysdba or sysoper permission is case sensitive when logging on to the password file. By default, the ignorecase parameter is case sensitive.

Note that the ingorecase parameter is only used to create a password in the password file.

 

Disable OS authentication first. For more information, see:

Oracle OS authentication password file and password loss

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. David Dai> orapwdfile = D: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ database \ pwddave. orapassword = Oracle force = y ignorecase = y

 

 

-- Test the impact of ignorecase parameters on sysdba users:

C: \ Users \ administrator. David Dai>

 

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 are no longer connected tooracle.

SQL> conn dave1/Dave;

Connected.

-- The correct password must also be used, including case sensitivity.

 

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 false

Dave1 true false

 

SQL> conn dave1/Dave;

Connected.

SQL> conn dave1/Dave;

Error:

ORA-01017: invalid username/password; logondenied

 

 

Warning: you are no longer connected to Oracle.

-- Through this test, we can see that when creating a password file, it does not affect users who subsequently grant sysdba permissions. Therefore, you cannot specify whether to ignore the case sensitivity.

 

 

-- Test sys User:

We compete for this user when creating the password file.

 

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 are no longer connected tooracle.

-- In this case, the Sys user successfully ignores the case sensitivity issue.

 

 

Summary:

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

(1) The sec_case_sensitive_logon parameter specifies whether the user's password is case sensitive.

(2) The ignorecase parameter in the password file is only used to determine whether the password is case-sensitive during creation. In short, it is only applicable to SYS users.

 

 

Iii. Oracle 10g, 11g and dblink

As described above, we can see that the password verification policies for Oracle 10g and 11G are different. Therefore, if dblink is created between libraries of different versions, the password will become a problem, the password may be incorrect. The specific precautions are as follows:

 

(1) connect 11g to 11g:

When creating a database connection, the password must be the same as the password of the remote database user.

 

(2) connect to the database before 11g:

The password used to create a data stream connection is case-insensitive, because the remote database ignores case-insensitive.

 

(3) connect the database before 11 GB to 11 GB:

Change the password of a remote user to uppercase or enclose it in quotation marks. Only in this way can the password pass database verification before 11 GB.

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940