Firebird Documentation IndexFirebird 3.0.2 Release NotesSecurity → International Character Sets for User Accounts
Firebird Home Firebird Home Prev: Mapping of Users to ObjectsFirebird Documentation IndexUp: SecurityNext: SQL Features for Managing Access

International Character Sets for User Accounts

Conditions for Non-ASCII Logins
Alex Peshkov

For creating user names and passwords, the new authentication plug-ins bring a degree of flexibility with regard to character sets. To make use of international credentials support, it is necessary to use SRP or any other set of authentication plug-ins that supports the new model.

Advice

Use of the legacy authentication plug-in is not recommended, except where it is necessary for connecting a client to a server version older than Firebird 3. Legacy authentication has never supported international characters in user names and passwords. This restriction is unchanged for Firebird 3 legacy authentication.

When writing authentication plug-ins it is unnecessary to be concerned about character sets, since all data exchange with the new plug-ins is done using UTF8. Just be prepared to handle user names and passwords that contain characters beyond the range of the 7-bit ASCII character subset.

Conditions for Non-ASCII Logins

The simple rule to follow for using non-ASCII user names and passwords is that the character set and code page selected in the client for SQL input must match those in which the credentials are stored on the server and, obviously, the credentials must be supplied in the specified character set. On POSIX terminals and most GUI clients this condition is satisfied. However, there are three fairly common situations where problems could arise:

  1. connecting via a Windows text console with the default OEM code page selected
  2. omitting the SET NAMES command in isql
  3. running scripts

Examining these situations in more detail:

  1. All operating systems except Windows use same the same character set (code page, locale), by default, for all programs. In Windows, for historical reasons, GUI applications use an ANSI code page, but applications started on the command-line console emulator use the corresponding OEM code page. As an example, the Russian version of Windows is uses code page 1251 (ANSI Russian) for GUI applications and code page 866 (OEM Russian) for command-line applications.

    The Firebird client always uses the ANSI code page. In order to provide the fully functional behaviour of a console application, the console must be switched to the ANSI codepage (chcp 1251 for our Russian example).

    The Windows weirdness does not stop there. Parameters passed to a Windows application, but not to 16-bit DOS, are always passed in the ANSI encoding, even if it was started from a command-line console running in the default OEM mode. This might give the illusion that Firebird utilities run correctly out-of-the-box on an OEM console. However, they run correctly only as far as the international information supplied in a command line, e.g.,

      C:> isql server:database -user <intl-login> -pas <intl-password>
                  

    This works. However, as soon as you try to use a CONNECT or CREATE DATABASE command inside isql, or to enter the password from the OEM terminal, you will encounter problems. This is inconvenient and we apologise for that. The OEM console issues are in the plan to be fixed in a future version.

  2. Currently, command-line parameters and the CONNECT and CREATE DATABASE commands in isql are not affected by SET NAMES or the -CHarset parameter. All the other commands, particularly CREATE/ALTER/DROP USER, are affected by them and thus, the character set must be specified. This is very important because, in future versions, the plan is to make all usage of international credentials depend on a character set that is explicitly specified.

    Take careful note of one very confusing use case, an attempt to set a non-ASCII password for the current user:

      SQL> alter current user set password '<intl-password>';
                  

    This command will succeed, even if the character set has not been set correctly. However, a subsequent attempt to log in with the modified password will fail.

  3. In scripts, the first requirement is to set the correct character set for the SQL server that is to run the script, using a SET NAMES statement. For Firebird 3, it is essential to pay attention to the locale and code page settings in the environment in which the script is to execute. They must match the character set that is set in the script.

    Setting the locale correctly affects particularly the credentials passed to the server when attaching to it. The rest of the script should proceed successfully even without configuring the environment properly.

    In summary, if you plan to use international character credentials in the script, you DO need to attend to the international settings in your environment.

Reminder

User names are SQL identifiers and thus conform to the same rules, i.e., enclosed in double quotes when containing international characters or when case-sensitivity is required.

Prev: Mapping of Users to ObjectsFirebird Documentation IndexUp: SecurityNext: SQL Features for Managing Access
Firebird Documentation IndexFirebird 3.0.2 Release NotesSecurity → International Character Sets for User Accounts