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

Usernames with '.' character [CORE1810] #2240

Closed
firebird-automations opened this issue Mar 29, 2008 · 26 comments
Closed

Usernames with '.' character [CORE1810] #2240

firebird-automations opened this issue Mar 29, 2008 · 26 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Kevin Smith (kevinsmith)

Is related to QA212

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

Commits: 9461927 f35bc47 c1f5e79 4a91988

@firebird-automations
Copy link
Collaborator Author

Commented by: Claudio Valderrama C. (robocop)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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.
Comments?

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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'
abvc
fbs bin #⁠ echo "abvc"
abvc
And only with double set of quotes:
fbs bin #⁠ echo '"abvc"'
"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';
SQL>

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @helebor

c:\Program Files\Firebird\Firebird_1_5\bin>gsec
GSEC> add john.smit -pw johnsmit
GSEC> display
user name uid gid full name
------------------------------------------------------------------------------
----------
SYSDBA 0 0
REPL 0 0
HELEN 0 0
JOHN.SMIT 0 0

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

GSEC>

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';

RDB$USER

REPL
JOHN.SMIT

SQL>

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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 http://user.name 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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

priority: Minor [ 4 ] => Major [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Alpha 1 [ 10224 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Reopened for backporting

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

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

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Reopened [ 4 ] => Reopened [ 4 ]

Target: 2.1.1, 1.5.6, 2.0.5 [ 10223, 10225, 10222 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

Fix Version: 2.1.1 [ 10223 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Alex, just a reminder - this issue is still awaiting its backport into v2.0.5.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Target: 2.1.1, 1.5.6, 2.0.5 [ 10223, 10225, 10222 ] => 2.1.1, 2.0.5, 1.5.6 [ 10223, 10222, 10225 ]

status: Reopened [ 4 ] => Reopened [ 4 ]

Fix Version: 2.0.5 [ 10222 ]

Fix Version: 1.5.6 [ 10225 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

Target: 2.1.1, 2.0.5, 1.5.6 [ 10223, 10222, 10225 ] => 1.5.6 [ 10225 ]

status: Reopened [ 4 ] => Reopened [ 4 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Reopened [ 4 ] => Reopened [ 4 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

All 4 branches are fixed.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue block progress on QA212 [ QA212 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA212 [ QA212 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue block progress on QA212 [ QA212 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

QA test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test => Done successfully

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