Hi,
I am struggling to understand the characters that can be used in a user name.
Would like my data base user to have the email address as user name - e.g. thomas.hill@acme.com.
While in principle it seems to be possible to define this user name using SQL*PLUS, this user name seems to be causing problems with different tools.
Defining the user: - working as expected
SQL*Plus: Release 11.2.0.1.0 Production on So Mai 26 18:34:13 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE USER "thomas.hill@acme.com" IDENTIFIED BY xxx
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp
4 QUOTA UNLIMITED ON users;
Benutzer wurde erstellt.
SQL>>
SQL*Plus: Release 11.2.0.1.0 Production on So Mai 26 18:34:13 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE USER "thomas.hill@acme.com" IDENTIFIED BY xxx
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp
4 QUOTA UNLIMITED ON users;
Benutzer wurde erstellt.
SQL>
>
Granting connect permission - working as expected (Note: no escaping needed)
>
SQL> grant connect to "thomas.hill@acme.com";
Benutzerzugriff (Grant) wurde erteilt.
SQL>>
login in using SQL*PLUS - working when escaping the user name
C:\Daten\Anwendungen\PMT\Oracle\01-DDL>sqlplus \"thomas.hill@acme.com\"/xxx@localhost:1521/pmt
SQL*Plus: Release 11.2.0.1.0 Production on So Mai 26 18:39:44 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>>
<font color="red">
HOWEVER</font>
it seems as only SQL*PLUS can handle such user names
1) Oracle SQL Developer can't as no matter which combination I try, the login attempt fails with error <font color="red">ORA-01017: invalid username/password; login denied</font>
2) the JDBC driver only seems to allow connection when using connection method with signature URL, user, password (and not when trying to include user and password in one string and using signature URL)
3) loadjava doesn't seem to support this at all
4) Oracle SQL Developer, when logging in as system and then trying to revoke the connect permission from thomas.hill@acme.com throws an error
....
Combinations I tried were:
unquoted, i.e. thomas.hill@acme.com,
user name included in double quotes, i.e. "thomas.hill@acme.com",
user name included in escaped double-quotes, i.e. \"thomas.hill@acme.com\",
user name included in single quotes, i.e. 'thomas.hill@acme.com',
user name included in single quotes, then double quotes, i.e. '"thomas.hill@acme.com"',
user name included in double quotes two times, i.e. ""thomas.hill@acme.com"",
user name included in double quotes, then escaped double quotes, i.e. "\"thomas.hill@acme.com\"",
--- then I was running out of ideas.
So is the conclusion that it is impossible to have a user name enclosed in quotes?? which prevents use of email addresses, to have small letter user or schema names, ....
and the observation that implementation seems to be inconsistent across tool set??
Thanks
Edited by: ThomasH on May 26, 2013 9:53 AM
Edited by: ThomasH on May 26, 2013 9:55 AM
Edited by: ThomasH on May 26, 2013 10:02 AM
Edited by: ThomasH on May 26, 2013 10:04 AM
Edited by: ThomasH on May 26, 2013 10:17 AM