| username with @ [message #162961] |
Tue, 14 March 2006 06:16  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
Good Morning:
Here is the question. Is it possible to include & in the username: say username like rkl@study.com.As it happens, the study.com is added by the application while login to the database.so if the username is rkl1 then the login name sends to database for authentication is rkl1@study.com. Is it possible, oracle to ignore this @study.com and authenticate just the username. Any help will be greatly appreciated.
Thanks.
|
|
|
|
| Re: username with @ [message #162964 is a reply to message #162961] |
Tue, 14 March 2006 06:28  |
Gerardo Fernandez Herrera
Messages: 58
Registered: January 2006
Location: Montevideo, Uruguay
|
Member |
|
|
SQL>
create user "rkl@study.com" identified by pwd;
User created.
SQL> select username from dba_users where username like '%@%';
USERNAME
------------------------------
rkl@study.com
|
|
|
|
| Re: username with @ [message #162966 is a reply to message #162964] |
Tue, 14 March 2006 06:37 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
@ Symbol is not allowed in username.
Oracle consider anything after '@' as a servicename ( as identified by tnsnames.ora entry).
Ofcourse, you create the use within quotes ( another violation of standards). But cannot login.
[Updated on: Tue, 14 March 2006 06:37] Report message to a moderator |
|
|
|
|
|
| Re: username with @ [message #162982 is a reply to message #162966] |
Tue, 14 March 2006 07:32 |
Gerardo Fernandez Herrera
Messages: 58
Registered: January 2006
Location: Montevideo, Uruguay
|
Member |
|
|
| Quote: |
Oracle consider anything after '@' as a servicename ( as identified by tnsnames.ora entry).
Ofcourse, you create the use within quotes ( another violation of standards). But cannot login.
|
Not sure what you mean, but as I see, you can create a user with '@' symbol, and connect if you have a service name involved.
SQL> create user "rkl@study.com" identified by pwd;
User created.
SQL> grant create session to "rkl@study.com";
Grant succeeded.
SQL> conn "rkl@study.com"/pwd@gfh.com.uy;
Connected.
SQL> show user
USER is "rkl@study.com"
Standars violation ? Well, we can discuss if it is a good practice to have such symbols in usernames, (I agree with you) but that is not the main topic of the post. In that case, we can say Oracle By Example series are violating its own standars...
http://www.oracle.com/technology/obe/obe10gdb/security/approles/approles.htm
Regards.
|
|
|
|
| Re: username with @ [message #162984 is a reply to message #162982] |
Tue, 14 March 2006 07:47 |
|
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Agree with you.
sql*plus CONN command is a different case.
May be there are new methods/tricks that i am not aware off.
scott@9i > connect "scott@tiger"/tiger;
Connected.
scott@9i > exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
oracle@mutation#sqlplus "scott@tiger"/tiger;
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Mar 14 08:44:52 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve service name
EDIT:
Could you also reproduce your case with a regular sqlplus login?
or a JDBC connect string?
CONN is an sql*plus command, which will not work outside sql*plus
Regards
And my rant was not about using symbols. It about anything created within quotes which will lead to confusion.
scott@9i > select username from dba_users where username like ('%@%');
USERNAME
------------------------------
scott@tiger
SCOTT@TIGER
[Updated on: Tue, 14 March 2006 08:19] Report message to a moderator |
|
|
|
| Re: username with @ [message #162992 is a reply to message #162984] |
Tue, 14 March 2006 08:29 |
Gerardo Fernandez Herrera
Messages: 58
Registered: January 2006
Location: Montevideo, Uruguay
|
Member |
|
|
Yes, you can also connect via JDBC.
--- JAVA side ---
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
class test_user
{
public static void main(String args[])throws Exception
{
try{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection
("jdbc:oracle:thin:@tcsuywt222:1521:gfh",
"\"rkl@study.com\"",
"pwd");
OracleCallableStatement cstmt =
(OracleCallableStatement)conn.prepareCall
( "begin scott.p1(?); end;" );
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
String data;
int i;
for( i = 0; rset.next(); i++ )
{
data = rset.getString(1);
System.out.println(data);
}
rset.close();
cstmt.close();
conn.close();
}
catch(Exception e) {e.printStackTrace();}
}
}
--- DBMS side ---
SQL> conn scott/tiger
Connected.
SQL> create or replace procedure p1(p_cursor in out sys_refcursor) as
2 begin
3 open p_cursor for
4 select ename from emp;
5 end;
6 /
Procedure created
SQL> grant execute on p1 to "rkl@study.com";
Grant succeeded.
--- CMD ---
C:\>java test_user
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
Regards.
|
|
|
|
|
|
| Re: username with @ [message #162998 is a reply to message #162994] |
Tue, 14 March 2006 09:04 |
Gerardo Fernandez Herrera
Messages: 58
Registered: January 2006
Location: Montevideo, Uruguay
|
Member |
|
|
Use the '/' trick
C:\>sqlplus "\"rkl@study.com\""/pwd
SQL*Plus: Release 9.2.0.6.0 - Production on Mar Mar 14 12:00:04 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL>
Regards.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
