In-depth analysis of Oracle Object Name sensitivity

Note:
1. This document uses oracle9.2 as the testing and analysis version.
2. The Oracle object names mentioned in this article include the table name, view name, field name, and function name in Oracle.

The following is an example of using different naming methods for table creation and access. It is the test result on my machine:

Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected *****

SQL> Create Table mytable1
2 (
3 C1 varchar2 (6)
4 );
Table created
SQL> select * from "mytable1 ";
C1
------
SQL> select * From mytable1;
C1
------
SQL> select * from "mytable1 ";
Select * from "mytable1"
ORA-00942: Table or view does not exist
SQL> drop table mytable1;
Table dropped
SQL>
SQL>
SQL> Create Table "mytable1"
2 (
3 C1 varchar2 (6)
4 );
Table created
SQL> select * from "mytable1 ";
C1
------
SQL> select * From mytable1;
Select * From mytable1
ORA-00942: Table or view does not exist
SQL> select * From mytable1;
Select * From mytable1
ORA-00942: Table or view does not exist

The following table lists the test results of the author:
√ Indicates allowed access, and × indicates not allowed access.
Instance:
Read
Create mytable1 mytable1 "mytable1" "mytable1"
Mytable1 √ × √
Mytable1 √ × √
"Mytable1" ××√ ×
"Mytable1" √ × √

Summary:
Read
Create lowercase letters, uppercase letters, quotation marks, lowercase letters, and quotation marks, uppercase letters
Lowercase letter √ × √
Capital letter √ × √
Quote lowercase letters ××√ ×
Quote an uppercase letter √ × √

The following is a summary and analysis conclusion based on the Oracle data dictionary and actual test:
If no quotation marks are added to the image creation process in Oracle, the lowercase letters of the image name are converted to uppercase letters for storage when the image is saved to the data dictionary. For example, mytable is converted to mytable; if quotation marks are added during creation, the actual characters in the quotation marks are stored.
If no quotation marks are added for access, lowercase letters are converted into uppercase letters for access. For example, mytable is converted to mytable. If quotation marks are added, actual characters in quotation marks are used for access.
When reading a data dictionary, Oracle considers the image name to be case sensitive if it contains lowercase letters or characters starting with a letter or a Chinese character, and requires quotation marks during access.

 

====================================== Below is a small experiment I have made myself ====== ======================================

 

SQL> Create Table "xiaoxie" <br/> 2 (<br/> 3 ID number, <br/> 4 name varchar2 (20) <br/> 5) <br/> 6; </P> <p> table created. </P> <p> SQL> select * From xiaoxie; <br/> select * From xiaoxie <br/> * <br/> error at line 1: <br/> ORA-00942: Table or view does not exist </P> <p> SQL> select * from "xiaoxie "; </P> <p> No rows selected </P> <p> SQL> ALTER TABLE "xiaoxie" add ("ADDR" varchar2 (20 )); </P> <p> table altered. </ P> <p> SQL> DESC "xiaoxie" <br/> name null? Type <br/> --------------------------------------- -------- ---------------------------- </P> <p> ID number <br/> name varchar2 (20) <br/> ADDR varchar2 (20) </P> <p> SQL> select ADDR from "xiaoxie"; <br/> select ADDR from "xiaoxie" <br/> * <br/> error at line 1: <br/> ORA-00904: "ADDR": Invalid identifier </P> <p> SQL> select "ADDR" from "xiaoxie "; </P> <p> No rows selected <br/>

 

====================================== New Features of Oracle 11g -- password case sensitivity ======== ==================================

 

Password case sensitivity is a new feature of the 11g database by default. The database configuration Assistant (dbca) allows you to return this setting to functions earlier than 11g during database creation.

 

 

 

The sec_case_sensitive_logon initialization parameter controls whether the password is case sensitive. If the existing application conflicts with the 11g authentication process, you can use the alter system command to disable this function.

 

SQL> show parameter sec_case_sensitive_logon Name type value ----------------------------------------------------------------------------- Sec_case_sensitive_logon Boolean true SQL> SQL> alter system set sec_case_sensitive_logon = false; System altered. SQL>

 

 

The following code describes the password case sensitivity function. First, set the sec_case_sensitive_logon initialization parameter to true, and then create a new user whose password contains uppercase and lowercase letters.

 

 

Conn/As sysdba Alter system set sec_case_sensitive_logon = true; Create user Test2 identified by Test2; Grant connect to Test2;

 

 

Use the correct connection characters and wrong passwords of the newly created users to connect. The case sensitivity function is effective. (Www.syue.com)

SQL> conn Test2/Test2 Connected. SQL> conn Test2/Test2 Error: ORA-01017: invalid username/password; logon denied Warning: you are no longer connected to Oracle. SQL>

When the sec_case_sensitive_logon initialization parameter is set to false, the password is case insensitive.

 

 

Conn/As sysdba Alter system set sec_case_sensitive_logon = false; SQL> conn Test2/Test2 Connected. SQL> conn Test2/Test2 Connected. SQL>

 

 

Remember that even if the case-sensitive password parameter is not enabled, it is case-sensitive when saving the password so that it will be valid when the case-sensitive parameter is enabled in the future, the following code first disables the case-sensitive password, and then creates a user that contains the case-sensitive character password. Www.syue.com

Conn/As sysdba Alter system set sec_case_sensitive_logon = false; Create user test3 identified by test3; Grant connect to test3;

[Source ""]

As you expected, you do not need to consider the password case when logging on.

 

 

If case-sensitive parameters are enabled, the authentication process immediately enables case-sensitive authentication.

 

Conn/As sysdba Alter system set sec_case_sensitive_logon = true; SQL> conn test3/test3 Connected. SQL> conn test3/test3 Error: ORA-01017: invalid username/password; logon denied Warning: you are no longer connected to Oracle. SQL>

The dba_users view contains a password_versions column, which indicates which database version of the password was created or last modified.

 

SQL> select username, password_versions from dba_users; Username Password -------------------------------------- Test 10g 11g Spatial_wfs_admin_usr 10g 11g Spatial_csw_admin_usr 10g 11g Apex_public_user 10g 11g . . . System 10g 11g Sys 10g 11g

Text from "year-month alliance"

If the user imports data from 10 Gb, 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.

 

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.

 

 

To create a password in the password file, use the ingnorecase = Y option to recreate the password file. Information Source "year-month alliance"

$ Orapwd file = orapwdb11gb entries = 100 ignorecase = y Password = mypassword

 

 

The database connection password is case sensitive. The following lists the issues that need to be paid attention to when connecting different database versions.

 

• 11G connection: when creating a database connection, the password must be the same as that of the remote database user.
• Connect to a database before 11g: the password used to create a data stream connection is case-insensitive, because the remote database ignores case-insensitive.
• Connect the database before 11g to 11G: You must change all passwords of remote users to uppercase. Only in this way can you pass database verification before 11g.