Shulou(Shulou.com)06/01 Report--

[methods] the login problem when the password of Oracle user contains special characters

1.1 BLOG document structure map

1.2.2 related article links [password] Oracle user password series: http://blog.itpub.net/26736162/viewspace-2129595/

-Chapter II Experimental Section 2.1 introduction to the experimental environment

Source db

Db Typ

RAC

Db version

11.2.0.3.0

Db storage

ASM

OS version and kernel version

RHEL 6.5

2.2 the goal of the experiment is to test the login and password modification of sqlplus and exp, imp, expdp and impdp when the user's password contains special characters.

2.3 Experimental process 2.3.1 sqlplus connection ordinary user connection:

[oracle@orcltest admin] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 24 09:25:35 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SYS@lhrdb > alter user lhr identified by "l@hr"

User altered.

SYS@lhrdb > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

[oracle@orcltest ~] $sqlplus' lhr/ "l@hr"'@ LHRDB

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 24 09:27:05 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

LHR@LHRDB > EXIT

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

[oracle@orcltest ~] $

Sys user connection:

[oracle@orcltest admin] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 24 09:29:09 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SYS@lhrdb > alter user sys identified by "l@hr"

User altered.

SYS@lhrdb > exit

[oracle@orcltest ~] $sqlplus' sys/ "l@hr"'@ LHRDB as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 24 09:29:35 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SYS@LHRDB > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

[oracle@orcltest ~] $

2.3.2 the expdp connection password is enclosed in double quotes, the user name and password are enclosed in single quotation marks, then [user name] + [password] + [tn] + [as sysdba] are enclosed in single quotation marks, and the last single quotation mark is escaped in\

Expdp\ 'sys/ "l@hr" @ LHRDB as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT%U.dmp SCHEMAS=SCOTT COMPRESSION=all metrics=y

[oracle@orcltest admin] $expdp\''sys/ "l@hr"' @ LHRDB as sysdba\ 'DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT%U.dmp SCHEMAS=SCOTT COMPRESSION=all metrics=y

Export: Release 11.2.0.3.0-Production on Fri Feb 24 09:32:22 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYS". "SYS_EXPORT_SCHEMA_01": "sys/*@LHRDB AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFIL

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Completed 4 TABLE_STATISTICS objects in 14 seconds

. . Exported "SCOTT". "DEPT" 4.976 KB 4 rows

. . Exported "SCOTT". "EMP" 5.617 KB 14 rows

. . Exported "SCOTT". "SALGRADE" 4.890 KB 5 rows

. . Exported "SCOTT". "BONUS" 0 KB 0 rows

Master table "SYS". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

*

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

/ u02/app/oracle/admin/lhrdb/dpdump/SCOTT01.dmp

Job "SYS". "SYS_EXPORT_SCHEMA_01" successfully completed at 09:33:19

2.3.3 the modified password contains the symbol "@" [oracle@orcltest ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:10:26 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SYS@lhrdb > alter user scott identified by "tiger&123"

Enter value for 123:

Old 1: alter user scott identified by "tiger&123"

New 1: alter user scott identified by "tiger"

User altered.

SYS@lhrdb >

SYS@lhrdb > set define off

SYS@lhrdb > alter user scott identified by "tiger&123"

User altered.

SYS@lhrdb > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

[oracle@orcltest ~] $sqlplus' scott/ "tiger&123"'@ LHRDB

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:14:00 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SCOTT@LHRDB >

SCOTT@LHRDB > set define off

SCOTT@LHRDB > alter user scott identified by "$tiger&123l@h\ rdeband 0% sjime d$"

User altered.

SCOTT@LHRDB > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

[oracle@orcltest ~] $sqlplus' scott/ "$tiger&123l@h\ r LHRDB 0% spentry d$"'@ LHRDB

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:20:12 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SCOTT@LHRDB >

2.3.4 modify the password with double quotation marks to change the password of the scott user to: a "b

[oracle@orcltest ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:39:18 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SYS@lhrdb > password scott

Changing password for scott

New password:

Retype new password:

Password changed

SYS@lhrdb > conn scott/a "b

Connected.

SCOTT@lhrdb >

[oracle@orcltest ~] $sqlplus scott/a\ "b

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:42:34 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SCOTT@lhrdb >

Note: because general special characters can be handled with double quotes, but if the password contains double quotation marks, it cannot be processed with double quotation marks. You can use password to modify the password directly.

2.4 the conclusions of this paper refer to the following table:

Linux platform

Sqlplus tool

Data pump tools (exp, expdp)

Ordinary user

No tns

Sqlplus' lhr/ "lumbh\ rdeb0"'

Expdp 'lhr/ "lumbh\ rdeb0"'

There is tns.

Sqlplus' lhr/ "lumbh\ rAccord 0"'@ LHRDB

Expdp 'lhr/ "lumbh\ rAccord 0"' @ LHRDB

Sys user

No tns

Sqlplus / as sysdba

Expdp\'/ AS SYSDBA\'

There is tns.

Sqlplus' sys/ "lumbh\ rAccord 0"'@ LHRDB as sysdba

Expdp\ 'sys/ "lumbh\ rCompare 0"' @ LHRDB as sysdba\'

Normal password

Sqlplus sys/lhr@lhrdb as sysdba

Expdp\ 'sys/lhr@LHRDB as sysdba\'

Note: the password containing special characters is: lhr,tns\ r as sysdba 0, the normal password is: LHRDB, the general principle is: the password is enclosed in double quotes, the user name and password are enclosed in single quotes, then [user name] + [password] + [tns] + [as sysdba] is enclosed in single quotation marks, and the last single quotation mark is escaped in\

Alter user lhr identified by "lumbh\ rbin0"

Alter user sys identified by "lumbh\ rbin0"

Set define off

Alter user scott identified by "$tiger&123l@h\ rbind 0% sjigma d$"

Alter user scott identified by "$? `$% * H\ @ f'\

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.