Home » RDBMS Server » Security » username with @
username with @ [message #162961] Tue, 14 March 2006 06:16 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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 #162967 is a reply to message #162961] Tue, 14 March 2006 06:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@OP:
>>Is it possible, oracle to ignore this @study.com and authenticate just the username
May be, the application should do it. Strip away anything unwanted
and pass only username/password@serivcename.


Re: username with @ [message #162982 is a reply to message #162966] Tue, 14 March 2006 07:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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 Go to previous messageGo to next message
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 #162994 is a reply to message #162992] Tue, 14 March 2006 08:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Thanks.So a regular sqlplus 'Login' whines about this. May be there is a method to 'espape' the @ symbol. I am not aware of any.
Regards.


Re: username with @ [message #162998 is a reply to message #162994] Tue, 14 March 2006 09:04 Go to previous messageGo to next message
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.
Re: username with @ [message #163002 is a reply to message #162998] Tue, 14 March 2006 09:10 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Cool. That works. Many thanks
Previous Topic: SELECT access to VIEWS through ROLES
Next Topic: RMAN backup error
Goto Forum:
  


Current Time: Fri Sep 24 15:38:51 CDT 2021