Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can not create user with non-ascii (multi-byte) characters in it's name [CORE4760] #5063

Closed
firebird-automations opened this issue Apr 20, 2015 · 19 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

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

Commits: 2e3438f 1351b11 3921d43 0ac40f7 FirebirdSQL/fbt-repository@838d32a FirebirdSQL/fbt-repository@39456cf FirebirdSQL/fbt-repository@0fea0fb FirebirdSQL/fbt-repository@979bc1f

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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).

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

1 similar comment
@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Once again - did you check how is locale set on your machine?

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

$ 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=

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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).

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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 :(

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

User names must pass the same routine that passwords according to CORE3782

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

No, passwords were always case-sensitive

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

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: (CORE4760) ...")

Test Specifics: [Platform (Windows/Linux) specific]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Deferred => Done successfully

Test Details: Wait for Alex confirmation that checks can be resumed - see last letter 11-MAY-2015 13:39 (subj: "[FB-Tracker] Commented: (CORE4760) ...") =>

Test Specifics: [Platform (Windows/Linux) specific] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants