3 Replies Latest reply: May 27, 2013 1:59 AM by ThomasH RSS

    Rules for a valid user name? - e.g. user name can't be an email address?

    ThomasH
      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
        • 1. Re: Rules for a valid user name? - e.g. user name can't be an email address?
          Srini Chavali-Oracle
          I understand what you are trying to do - however, using special characters like @ in the username is not recommended - using quotes may work in tools like SQL*Plus, but other tools may not recognize such quoted objects (as you have experienced) - see the first Note section here - http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#i27570

          HTH
          Srini
          • 2. Re: Rules for a valid user name? - e.g. user name can't be an email address?
            user8606054
            @ is not valid for a SQL99 compliant schema name.

            you're basically limited to:

            A <regular identifier> is a character string, up to 128 characters long, that consists only of letters, digits, and underscore characters. It must begin with a letter.

            i wouldnt expect @ to be supported anytime soon that isn't "problem prone" because it's clearly in violation of the standrad.
            • 3. Re: Rules for a valid user name? - e.g. user name can't be an email address?
              ThomasH
              Thanks for the help.

              I realize that Oracle 11G is not as liberal here as other databases (my application is using Apache Derby and PostgreSQL as data base back ends and I haven't come accross problems while using quoted object names [e.g. small letter schema names] there and also have not seen any problem after using email addresses as user names [e.g. using the @ in the user name]). For the email address as user name issue I understand Derby and PostgreSQL might not have problems with this as there a user is not equal to a schema.

              I will do some more research on using smalll letter schema names and try to see if I can get the loadjava utility to load classes into such schemas.
              Moving away from email addresses as user names would be quite a change for my application, so I need to decide what I will be doing here - maybe conclude my app can't run on Oracle 11g (in the nearer future).

              Kind regards
              Thomas