Issue Details (XML | Word | Printable)

Key: CORE-1810
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Kevin Smith
Votes: 0
Watchers: 0

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

Usernames with '.' character

Created: 29/Mar/08 07:21 AM   Updated: 19/Jan/16 06:05 AM
Component/s: Engine, GSEC
Affects Version/s: 2.0.3, 1.5.5
Fix Version/s: 2.5 Alpha 1, 2.1.1, 2.0.5, 1.5.6

Environment: Tested both on Linux and Windows XP.
Issue Links:

QA Status: Done successfully

 Description  « Hide
I've created Firebird user in security database. The user name
contains '.' character for example: kevin.smith

When I try to grant privileges to that user I've got error:
SQL> grant select on table to kevin.smith;
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 33

When I run query against user without '.' it works fine:
grant select on table to UserNameWithotDot;

GSEC allows me to add kevin.smith user and ISQL allows me to log to database using that username.

Tested on:
ISQL Version: LI-V2.0.3.12981 Firebird 2.0
Server version:
Firebird/linux Intel (access method), version "LI-V2.0.3.12981
Firebird 2.0"
on disk structure version 11.0

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Claudio Valderrama C. added a comment - 29/Mar/08 07:43 AM
Users are identifiers. Same as you can't use a table with a period in the name without double quotes, for special characters in user names, you need to surround them by double quotes (and they become case sensitive, too).
For quoted identifiers, you need a database in dialect 3.

Alexander Peshkov added a comment - 30/Mar/08 03:29 AM
Taking into an account, that user names are stored in central repository (security database), and it's impossible to prredict are there dialect3 databases (which can work with such names) or not, I suggest to leave it 'as is', and do not change anything.

Alexander Peshkov added a comment - 30/Mar/08 04:21 AM
Let me be more specific.

Modifying rules according to which lives gsec makes no sense - it's anyway dinosaur, which is supported just to provide backward compatibility with old versions. Changing it's rules will only break that compatibility, nothing more. Moreover, putting that quotes in unix command line is nightmare:
fbs bin # echo 'abvc'
fbs bin # echo "abvc"
And only with double set of quotes:
fbs bin # echo '"abvc"'
Who ever needs it?

Let people better use SQL commands to work with users. And there are no problems/inconsistencies with '.' when using them:

SQL> create user john.smit password 'abc';
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 17
SQL> create user "john.smit" password 'abc';
SQL> create user john password 'abc';

What I see as a real problem here is that it's impossible to login with user name john.smit with password abc. The reason is clear - double quoted identifiers are not upper cased, and username gets into security database in lower case. I will fix it before 2.5 A1.

Helen Borrie added a comment - 30/Mar/08 05:34 AM
c:\Program Files\Firebird\Firebird_1_5\bin>gsec
GSEC> add john.smit -pw johnsmit
GSEC> display
     user name uid gid full name
REPL 0 0

GSEC> add "john.smit" -pw johns
GSEC> display
     user name uid gid full name
REPL 0 0


isql: (REPL_ADMIN is a role)

SQL> grant repl_admin to john.smit;
Statement failed, SQLCODE = -104

Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, char 25
SQL> grant repl_admin to "john.smit";
SQL> commit;
SQL> select rdb$user from rdb$user_privileges where rdb$relation_name = 'REPL_ADMIN';




Alexander Peshkov added a comment - 30/Mar/08 05:51 AM
Looks like GRANT command has the correct behavior - it let's enter user with dot in a name when in double quotes, strips that quotes and puts it into upper case. I suggest to have same behavior in CREATE USER and almost same in gsec. An exception in it is ability to enter without quotes in order to be backward compatible.

I.e. entered john.smit username in any acceptable form should be always stored in security database as JOHN.SMIT - upper case, no quotes.

Alexander Peshkov added a comment - 04/Apr/08 12:02 PM - edited
I do not add full SQL identifiers parser to gsec. If one enters correct name (i.e. 'John.Smit') he will get correct result - JOHN.SMIT. But modifying backward compatibility utility too much is not our main aim for today.

Alexander Peshkov added a comment - 17/Apr/08 05:41 AM
Reopened for backporting

Dmitry Yemanov added a comment - 10/Jun/08 02:03 AM
Alex, just a reminder - this issue is still awaiting its backport into v2.0.5.

Alexander Peshkov added a comment - 29/Oct/08 05:04 AM
All 4 branches are fixed.

Pavel Cisar added a comment - 29/Jun/11 09:45 AM
QA test added.