Services cannot work with user names containing space

[pavel-zotov]

If a database:

• was created as self-security,
• and its owner has:
  • either name that does not look like SQL-identifier (spaces, punctuation signs),
  • or name does look as SQL-identifier but has at least one lowercase character and was intentionally enclosed in quotes
• and its state has been changed to full shutdown

-- then it can not be returned to online using Services API.

Steps to reproduce:
0) open firebird.conf and set there:

AuthClient = Srp
AuthServer = Srp
UserManager = Srp

1. open databases.conf and make alias for self-secutity database:

ss_alias = c:\temp\ss_test.fdb
{
    SecurityDatabase = ss_alias
}

2. open batch file from attached .zip (its encoding: utf8) and adjust there:
   2.1) home path to FB instance (variable fbc)
   2.2) uncomment any of following cases:

@rem set DB_OWNR=john_smith
@rem set DB_OWNR="john o'hara"
@rem set DB_OWNR="john_low"
@rem set DB_OWNR="Джон О'Хара"

One may to start uncomment this: set DB_OWNR="john o'hara"

Successful completion of this batch must show following tail:

C:\FB\60SS\gfix ss_alias -user \""john o'hara"\" -pas "yes, it's me" -online
        Attributes              force write
Step #4 +++ passed +++
Bye-bye, retcode=0

Commands that fail are commented out and gathered under title

@rem ######################################################
@rem ###    P R O B L E M A T I C     C O M M A N D S   ###
@rem ######################################################

All these commands try to bring DB online.
Uncomment any line from this block that looks like
@rem set run_cmd=%fbc%\fbsvcmgr ...
-- and run batch.

You will see somewhat like:
data base file name (o'hara) already given
or
no permission for bring online access to database

Checked on WI-6.0.0.1394
self-sec-shutdown-online.zip

This is overall outcome:

(see also attached .zip, there is appropriate Excel file)

PS.
NOTE: batch assumes that there is no important DB with name c:\temp\ss_test.fdb (it will drop / create DB with such name).

PPS.
SQL standard definition of "SQL identifier":

SQL-2011, page 173 "5.4 Names and identifiers"
-----------------------------------------------
    <SQL language identifier> ::= <SQL language identifier start>[ <SQL language identifier part>... ]
        <SQL language identifier start> ::= <simple Latin letter>
        <SQL language identifier part> ::= <simple Latin letter> | <digit> | <underscore>
...
2) An <SQL language identifier>is equivalent to an <SQL language identifier>in which every letter that is
a lower-case letter is replaced by the corresponding upper-case letter or letters.
-----------------------------------------------

[mrotteveel]

The error "data base file name (o'hara) already given" means that the command line was incorrect and the o'hara part was seen as a separate argument, and that would mean it was not properly quoted or escaped. Though I would expect the error to mention ss_alias, not o'hara, as ss_alias is given first.

In any case, I think the second error is entirely expected, and should also occur for usernames that are regular identifiers: the security database is shutdown, so you cannot authenticate. You need to use an embedded connection to bring it up.

On a separate note, I think you're combining too many issue in a single ticket. The mention of "if DB owner has non-ascii characters then CONNECT statement fails" should be a separate ticket, though I would first double-check if that isn't an issue with the codepage of the command prompt.

[mrotteveel]

The error "data base file name (o'hara) already given" means that the command line was incorrect and the o'hara part was seen as a separate argument, and that would mean it was not properly quoted or escaped. Though I would expect the error to mention ss_alias, not o'hara, as ss_alias is given first.

I had missed that this is about the service manager. IIRC, the services manager does something odd with recombining and reparsing a command line string, so the problem might very well lie either in fbsvcmgr, or in the services manager.

[AlexPeshkoff]

That's not about only services manager - that's a mix of more or less unrelated problems. I suggest to close this issue as unexpected and add separate issues for each bug.

[pavel-zotov]

[Mark] The mention of "if DB owner has non-ascii characters then CONNECT statement fails" should be a separate ticket
[Alex] mix of more or less unrelated problems

#8861

I suggest to close this issue as unexpected

Why to close this ticket ? i can remove from it small part related to non-ascii characters in user name.

[aafemt]

Shrink it to "Services cannot work with user names containing space".

[AlexPeshkoff]

Shrink it to "Services cannot work with user names containing space".

+1

[AlexPeshkoff]

dpbItemUpper() not to be called for login passed to service thread