Issue Details (XML | Word | Printable)

Key: CORE-4760
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Can not create user with non-ascii (multi-byte) characters in it's name

Created: 20/Apr/15 07:16 PM   Updated: 31/May/15 11:45 AM
Component/s: Charsets/Collation
Affects Version/s: None
Fix Version/s: 3.0 Beta 2

QA Status: Deferred
Test Details:
Wait for Alex confirmation that checks can be resumed - see last letter 11-MAY-2015 13:39 (subj: "[FB-Tracker] Commented: (CORE-4760) ...")
Test Specifics: Platform (Windows/Linux) specific


 Description  « Hide
It seems that there is no explicit prohibition of creating user with name that containing non-ascii characters.
But currently it is not possible:

1) On Linux console, charset = UTF8:

$ /opt/fb30ss/bin/isql localhost/3333:/var/db/fb30/e30.fdb -z -ch utf8
ISQL Version: LI-T3.0.0.31797 Firebird 3.0 Beta 2
Server version:
LI-T3.0.0.31797 Firebird 3.0 Beta 2
LI-T3.0.0.31797 Firebird 3.0 Beta 2/tcp (oel64)/P13
LI-T3.0.0.31797 Firebird 3.0 Beta 2/tcp (oel64)/P13
Database: localhost/3333:/var/db/fb30/e30.fdb
SQL> create user "Кузьмин" password '123'; -- name of user typed in CYRILLIC letters
Statement failed, SQLSTATE = HY000
gsec error
SQL> exit;


2) GSEC also fails:

$ /opt/fb30ss/bin/gsec -user sysdba -pass masterke -add "Кузьмин" -pw 123
Bad international character in tag isc_spb_sec_username
Cannot transliterate character between character sets
Invalid or incomplete multibyte or wide character


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alexander Peshkov added a comment - 20/Apr/15 05:35 PM
Works for me:

# ./isql employee
Database: employee
SQL> create user "кузьмин" password '123';
SQL>

./isql -user "кузьмин" -pas 123 localhost:employee
Database: localhost:employee, User: кузьмин
SQL> select current_user from rdb$database;

USER
===============================
КУЗЬМИН

SQL>

Is locale info correct on your box?

Alexander Peshkov added a comment - 20/Apr/15 05:39 PM
Works for me:

# ./isql -user "кузьмин" -pas 123 localhost:employee
Database: localhost:employee, User: кузьмин
SQL> select current_user from rdb$database;

USER
===============================
КУЗЬМИН

SQL>

Is locale info correct on your box? (when non-ascii text is typed in command line it should match, not connection charset).

Pavel Zotov added a comment - 20/Apr/15 07:34 PM
Sorry, my firebird.conf contained Legacy_UserManager.

But after changing it to default value and restart Firebird I get:

$ /opt/fb30ss/bin/isql localhost/3333:/var/db/fb30/e30.fdb -ch utf8
Database: localhost/3333:/var/db/fb30/e30.fdb
SQL> create user "Кузьмин" password '123';
SQL> commit;
SQL> exit;

$ /opt/fb30ss/bin/isql localhost/3333:/var/db/fb30/e30.fdb -ch utf8 -user Кузьмин -pas 123
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
Use CONNECT or CREATE DATABASE to specify a database
SQL> quit;

$ /opt/fb30ss/bin/isql localhost/3333:/var/db/fb30/e30.fdb -ch utf8 -user "Кузьмин" -pas 123
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
Use CONNECT or CREATE DATABASE to specify a database

Pavel Zotov added a comment - 20/Apr/15 07:53 PM
Sorry, my firebird.conf contained Legacy_UserManager.

But after changing it to default value and restart Firebird I get:

$ /opt/fb30ss/bin/isql localhost/3333:/var/db/fb30/e30.fdb -ch utf8
Database: localhost/3333:/var/db/fb30/e30.fdb
SQL> create user "Кузьмин" password '123';
SQL> commit;
SQL> exit;

$ /opt/fb30ss/bin/isql localhost/3333:/var/db/fb30/e30.fdb -ch utf8 -user Кузьмин -pas 123
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
Use CONNECT or CREATE DATABASE to specify a database
SQL> quit;

$ /opt/fb30ss/bin/isql localhost/3333:/var/db/fb30/e30.fdb -ch utf8 -user "Кузьмин" -pas 123
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
Use CONNECT or CREATE DATABASE to specify a database

Alexander Peshkov added a comment - 28/Apr/15 02:27 PM
Once again - did you check how is locale set on your machine?

Pavel Zotov added a comment - 28/Apr/15 02:49 PM
$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

Alexander Peshkov added a comment - 30/Apr/15 01:51 PM
Support for non-ascii logins is checked only when using SQL interface to manage users. Use of deprecated gsec and/or services API may be not possible. Appropriate locale should be correctly setup on both client and server.

Non-ascii logins now do not differ in any way from ascii logins, including no case sensitivity (i.e. logins Zotov or ZOTOV are the same logins even when typed in russian).

Pavel Zotov added a comment - 30/Apr/15 04:49 PM
On LINUX with console charset = UTF8 it works fine:

$ /opt/fb30ss/bin/isql -q
SQL> set names utf8;
SQL> create database 'localhost/3333:/var/db/fb30/c4760.fdb';
SQL> create or alter user "СИДОРОВ" password '123';
SQL> create or alter user "СиДоРов" password '456';
SQL> commit;
SQL> set list on;

SQL> select sec$user_name from sec$users;
. . .
SEC$USER_NAME СИДОРОВ
SEC$USER_NAME СиДоРов
. . .

SQL> commit; connect 'localhost/3333:/var/db/fb30/c4760.fdb' user СИДОРОВ password '123';
Database: 'localhost/3333:/var/db/fb30/c4760.fdb', User: СИДОРОВ
SQL> commit; connect 'localhost/3333:/var/db/fb30/c4760.fdb' user СиДоРов password '456';
Database: 'localhost/3333:/var/db/fb30/c4760.fdb', User: СиДоРов
SQL> select current_user from rdb$database;

USER СИДОРОВ

SQL> select * from mon$attachments where mon$attachment_id=current_connection;
. . .
MON$USER СИДОРОВ
MON$AUTH_METHOD Srp
. . .


But on Windows command prompt with console charset = DOS866 it looks worse:

c:\>chcp
Текущая кодовая страница: 866 // cyrillic message here that current codepage = 866

C:\>C:\MIX\firebird\fb30\isql -q
SQL> set names dos866;
SQL> create database 'localhost/3333:c:\temp\c4760.fdb';
SQL> create or alter user "СИДОРОВ" password '123';
SQL> create or alter user "СиДоРов" password '456';
SQL> set list on;
SQL> set width usrname 12;
SQL> select sec$user_name as usrname from sec$users;

USRNAME SYSDBA

USRNAME СИДЎРЎВ -------------- distortion of original given name!

USRNAME СШДнРнв -------------- distortion of original given name!


SQL> commit;
SQL> connect 'localhost/3333:c:\temp\c4760.fdb' user "СИДОРОВ" password '123';
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
SQL> connect 'localhost/3333:c:\temp\c4760.fdb' user СИДОРОВ password '123';
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
SQL> connect 'localhost/3333:c:\temp\c4760.fdb' user СИДЎРЎВ password '123';
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
SQL> connect 'localhost/3333:c:\temp\c4760.fdb' user СИДЎРЎВ password '456';
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.

Pavel Zotov added a comment - 30/Apr/15 04:53 PM
PS. One need to be noted that UserManager = Srp is mandatory setting to add users with such non-ascii names.
Otherwise we get:

C:\>C:\MIX\firebird\fb30\isql -q
SQL> set names dos866;
SQL> create database 'localhost/3333:c:\temp\c4760.fdb';
SQL> set list on; select MON$AUTH_METHOD from mon$attachments where mon$attachment_id = current_connection;

MON$AUTH_METHOD Legacy_Auth

SQL> create or alter user "СИДОРОВ" password '123';
Statement failed, SQLSTATE = HY000
gsec error

Pavel Zotov added a comment - 30/Apr/15 05:06 PM
PPS.

If change codepage from dos866 to win1251 than things become more interesting.
Following was done in "cmd.exe" window:

C:\>chcp
Текущая кодовая страница: 1251 // cyrillic message that codepage now is 1251

C:\>C:\MIX\firebird\fb30\isql.exe -q
SQL> set names win1251;
SQL> create database 'localhost/3333:c:\temp\c4760.fdb';
SQL> create or alter user "СИДОРОВ" password '123';
SQL> create or alter user "СиДоРов" password '456';
SQL> commit;
SQL> set list on;
SQL> select * from sec$users where sec$user_name<>'SYSDBA';

SEC$USER_NAME СИДЎРЎВ
SEC$FIRST_NAME <null>
SEC$MIDDLE_NAME <null>
SEC$LAST_NAME <null>
SEC$ACTIVE <true>
SEC$ADMIN <false>
SEC$DESCRIPTION <null>
SEC$PLUGIN Srp

SEC$USER_NAME СШДнРнв
SEC$FIRST_NAME <null>
SEC$MIDDLE_NAME <null>
SEC$LAST_NAME <null>
SEC$ACTIVE <true>
SEC$ADMIN <false>
SEC$DESCRIPTION <null>
SEC$PLUGIN Srp

SQL> connect 'localhost/3333:c:\temp\c4760.fdb' user СИДОРОВ password '123';
Database: 'localhost/3333:c:\temp\c4760.fdb', User: СИДОРОВ ------------------------------- passed! It's surely good...
SQL> select current_user from rdb$database;

Statement failed, SQLSTATE = 22018
arithmetic exception, numeric overflow, or string truncation -------------------------------------- Why ?
-Cannot transliterate character between character sets

SQL> connect 'localhost/3333:c:\temp\c4760.fdb' user СиДоРов password '456';
Database: 'localhost/3333:c:\temp\c4760.fdb', User: СиДоРов --------------------------- also passed...
SQL> select current_user from rdb$database;

USER РЎРЁР"РЅР РЅРІ -------------------------------------- Displayed without error but with distortion :(

Dimitry Sibiryakov added a comment - 04/May/15 12:02 PM
User names must pass the same routine that passwords according to CORE-3782

Alexander Peshkov added a comment - 04/May/15 12:10 PM
No, passwords were always case-sensitive

Dimitry Sibiryakov added a comment - 04/May/15 01:38 PM
It is not about case-sensitivity, but character set interoperability. User must be able to log in no matter what console locale is used - WIN1251, CP866, KOI8-R or UTF-8.

Pavel Zotov added a comment - 05/May/15 10:23 AM
It seems that before build #31810 it was allowed to create two DIFFERENT logins with non-ascii characters that distinct from each other in case of letters ( e.g."SMIRNOFF" vs "SmIRnOFf" -- but typed in cyrillic letters).
As of build 31814 and later, these logins are considered as unique and attempt to enter camel-styled login as NEW one will "silently" fail if there is already old login contained the same characters without taking account of letters case: this new "SmIrNoFf" can only overwrite password of "SMIRNOFF" but can not be added as new record in sec$users.

Sample:
=======
SQL> set list on; select sec$user_name from sec$users;

SEC$USER_NAME SYSDBA

SQL> create or alter user "СиДоРОв" password '123'; commit;
SQL> create or alter user "сиДОРоВ" password '456'; commit;
SQL> create or alter user "СИДОРОВ" password '789'; commit;
SQL> create or alter user "СИДорОв" password '890'; commit;
SQL> set list on; select sec$user_name from sec$users;

SEC$USER_NAME SYSDBA

SEC$USER_NAME СИДОРОВ

SQL> commit; connect 'localhost/3333:e30' user "СИДорОв" password '890';
Database: 'localhost/3333:e30', User: СИДорОв

SQL> commit; connect 'localhost/3333:e30' user "СИДОРОВ" password '789';
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.

SQL> commit; connect 'localhost/3333:e30' user "СИДОРОВ" password '890';
Use CONNECT or CREATE DATABASE to specify a database
Database: 'localhost/3333:e30', User: СИДОРОВ

SQL> commit; connect 'localhost/3333:e30' user "СиДоРОв" password '890';
Database: 'localhost/3333:e30', User: СиДоРОв
SQL>


IMHO, current behaviour is much better than it was before. I can`t imagine practical necessity when there are two users which logins differ only in case (or accents :)) of their characters.