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

Anomalies between 3 methods of user management regarding valid user names and passwords [CORE3717] #4065

Closed
firebird-issue-importer opened this issue Jan 5, 2012 · 7 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Jan 5, 2012

Submitted by: @helebor

Is related to CORE4811
Is related to CORE3782

1. With CREATE USER, cannot create a user name starting with a numeral, unless it is double-quoted:

C:\Programs\Firebird\Firebird_2_5>bin\isql empdb -user sysdba -password masterkey
Database: empdb, User: sysdba
SQL> create user 1234 password 'silence';
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 13
-1234
SQL> create user alfred password 'silence';
SQL> create user "1234" password 'silence';
SQL> drop user 1234;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 11
-1234
SQL> drop user "1234";
SQL>

2. Using gsec, can add a user name starting with a numeral but cannot submit a password using Norwegian characters:

c:\Programs\Firebird\Firebird_2_5\bin>gsec -database babe:secdb -user sysdba -password masterkey
GSEC> add 1qwerty -pw æøåØ
invalid parameter, no switch defined
error in switch specifications
GSEC> add qwerty -pw æøåØ
invalid parameter, no switch defined
error in switch specifications
GSEC> add qwerty -pw "æøåØ"
invalid parameter, no switch defined
error in switch specifications
GSEC> add qwerty -pw rubberdk
GSEC> display
user name uid gid admin full name
--------------------------------------------------------------------------------
----------------
SYSDBA 0 0 Sql Server Administrator
WOMBAT 0 0 admin Cute Little Marsupial
SETI 0 0 admin Svein Erling Tysvaer
QWERTY 0 0
GSEC> add 1qwerty -pw rubberdk
GSEC> display
user name uid gid admin full name
--------------------------------------------------------------------------------
----------------
SYSDBA 0 0 Sql Server Administrator
WOMBAT 0 0 admin Cute Little Marsupial
SETI 0 0 admin Svein Erling Tysvaer
ALFRED 0 0
QWERTY 0 0
1QWERTY 0 0
GSEC>quit

3. Returning to isql, cannot create a user starting with a numeral, even if character set is something other than NONE. However, we can submit a password containing any number of lower and upper case Norwegian characters without exceptions (even when we have 9 characters):

c:\Programs\Firebird\Firebird_2_5\bin>isql -user sysdba -password masterkey
Use CONNECT or CREATE DATABASE to specify a database
SQL> set names ISO8859_1;
SQL> connect babe:empdb;
Database: babe:empdb, User: sysdba
SQL> create user 2qwerty password 'æøåØ';
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 13
-2
SQL> create user qwerty2 password 'æøåØ';
SQL> create user qwerty3 password 'æøåÆØÅ';
SQL> create user qwerty4 password 'æøåÆØÅ123';
SQL>exit;

4. But now, returning to gsec, we cannot modify the -lname parameter to a string containing a Norwegian character:

c:\Programs\Firebird\Firebird_2_5\bin>gsec -database babe:secdb -user sysdba -password masterkey
GSEC> display
user name uid gid admin full name
------------------------------------------------------------------------------------------------
SYSDBA 0 0 Sql Server Administrator
WOMBAT 0 0 admin Cute Little Marsupial
SETI 0 0 admin Svein Erling Tysvaer
QWERTY 0 0
1QWERTY 0 0
QWERTY2 0 0
QWERTY3 0 0
QWERTY4 0 0
GSEC> modify seti -lname Tysvår
invalid parameter, no switch defined
error in switch specifications
GSEC>quit

5. Back in isql, ALTER USER allows the Norwegian character in the LASTNAME parameter:

c:\Programs\Firebird\Firebird_2_5\bin>isql -user sysdba -password masterkey
Use CONNECT or CREATE DATABASE to specify a database
SQL> set names ISO8859_1;
SQL> CONNECT EMPDB;
Database: EMPDB, User: sysdba
SQL> alter user seti lastname 'Tysvår';
SQL> quit;

c:\Programs\Firebird\Firebird_2_5\bin>gsec -database babe:secdb -user sysdba -pa
ssword masterkey
GSEC> display
user name uid gid admin full name
------------------------------------------------------------------------------------------------
SYSDBA 0 0 Sql Server Administrator
WOMBAT 0 0 admin Cute Little Marsupial
SETI 0 0 admin Svein Erling Tysv-år
QWERTY 0 0
1QWERTY 0 0
QWERTY2 0 0
QWERTY3 0 0
QWERTY4 0 0
GSEC>

6. At this point I copy the gsec output to my ansi text file and get a warning that it contains Unicode characters.

7. More anomalies occur when isc_add_user is the means of adding a user (we used IB_SQL for this, client charset as IS08859_1).

a. Tried to add a user with a "traditionally legal" name and a password consisting of a mix of upper and lower case Norwegian characters;

IB_SQL (isc_add_user):
Using password æøåÆØÅ
ISC ERROR CODE:335544748
SQL ERROR CODE:-85

SQL ERROR MESSAGE:
An error occurred while trying to update the security database
ISC ERROR MESSAGE:
The password specified is too long. Maximum length is 8 bytes.

b. Using password æøåÆ : operation completed successfully

c. Using username 6qwerty: operation completed successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 5, 2012

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 5, 2012

Commented by: @AlexPeshkoff

Will answer questions one by one.

1. This is normal behavior. We treat username clause of create/drop/alter user in the same way as it's done in GRANT/REVOKE commands. It is illogical to have different syntax rules for username to satisfy old gsec utility. Enclosing into double quotes is valid way to get valid symbol name from a string which is not valid symbol name.

2. Using non-ascii password and names in gsec should be fixed.

3. The fact that name starting with digit is not valid SQL name does not depend upon character set used. Passwords are not limited to 8 characters in SQL, though in 2.5 only first 8 bytes are used to produce hash. This is fixed in FB3 with SRP.

4. Using non-ascii password and names in gsec should be fixed.

5. This is expected behavior.

6. I do not see a problem with it.

7. Not sure does it make much sense in fixing isc_xxx_user() functions - they were deprecated in interbase 6, more than 11 years ago. But as long as well known products use them looks like we will have to keep in sync. Looks like it's worth checking what do that deprecated functions do with long passwords.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 7, 2015

Modified by: @AlexPeshkoff

Link: This issue is related to CORE4811 [ CORE4811 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 7, 2015

Modified by: @AlexPeshkoff

Link: This issue is related to CORE3782 [ CORE3782 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 7, 2015

Commented by: @AlexPeshkoff

Issues with non-ascii user names and passwords are solved/explained when working with related issues

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 7, 2015

Modified by: @AlexPeshkoff

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 18, 2016

Modified by: @pavel-zotov

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

QA Status: No test

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