I have been asked to investigate the impact of syncing our oracle user names with our AD names .. which would mean putting a period (dot) in the username. (example - john.doe). First attempt to create one returned an error which lead to the following testing:
SQL> conn system/****
Connected.
SQL> --
SQL> -- create a user with a dot in the name
SQL> --
SQL> create user ed.stevens identified by tiger;
create user ed.stevens identified by tiger
*
ERROR at line 1:
ORA-01936: cannot specify owner when creating users or roles
Ok, the '.' is seen as a delimiter, so must be quoted, probably leading to case sensitivity.
SQL> create user "ed.stevens" identified by tiger;
User created.
SQL> grant create session to ed.stevens;
grant create session to ed.stevens
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> grant create session to "ed.stevens";
Grant succeeded.
SQL> select username
2 from dba_users
3 where upper(username) like 'ED%'
4 order by 1;
USERNAME
------------------------------
ed.stevens
1 row selected.
Now let's do it with an upper case name
SQL> --
SQL> create user ED.STEVENS identified by lion;
create user ED.STEVENS identified by lion
*
ERROR at line 1:
ORA-01936: cannot specify owner when creating users or roles
SQL> create user "ED.STEVENS" identified by lion;
User created.
SQL> grant create session to ED.STEVENS;
grant create session to ED.STEVENS
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> grant create session to "ED.STEVENS";
Grant succeeded.
SQL> select username
2 from dba_users
3 where upper(username) like 'ED%'
4 order by 1;
USERNAME
------------------------------
ED.STEVENS
ed.stevens
2 rows selected.
Everything to here makes sense. Now lets' try to connect as these two nefarious characters!
SQL> --
SQL> conn ed.stevens/tiger
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> select user from dual;
SP2-0640: Not connected
SQL> conn "ed.stevens"/tiger
Connected.
SQL> select user from dual;
USER
------------------------------
ed.stevens
1 row selected.
SQL> --
SQL> conn ED.STEVENS/lion
Connected.
Whaat?? Why wasn't the '.' seen as a delimiter here? This is the question that this example was leading to.
SQL> select user from dual;
USER
------------------------------
ED.STEVENS
1 row selected.
SQL> conn "ED.STEVENS"/lion
Connected.
SQL> select user from dual;
USER
------------------------------
ED.STEVENS
1 row selected.
SQL> --
SQL> spool off
SQL> conn system/****
Connected.
SQL> drop user "ed.stevens";
User dropped.
SQL> drop user "ED.STEVENS";
User dropped.