On 11-4-2019 10:44, 'Check_Mail' check_mail@... [firebird-support]
wrote:

> I’ had a difficult problem.
>
> A few month before, we are using firebird 2.1. For 6 weeks, I have
> installed the firebird 3 superserver and add a new user and changed the
> sysdba-password. All works fine, till yesterday. Some applications could
> not connect to the firebird database. We have taken a restart, after
> this, only the old sysdba password works to connect to our database. I
> have nothing changed! Now, I have tried to add the user again, in
> ibexpert I have it delete before. I get the following error

It actually sounds like you did make a change to the AuthServer setting
(from its default of AuthServer=Srp to AuthServer=Legacy_Auth) without
ever restarting Firebird after making that change. You just forgot about it..

I vaguely recall that enabling legacy authentication made config changes
after starting Firebird server, which caused them to only become
effective after restarting Firebird, but I thought that was only in one
of the beta releases (although I haven't recently used the installer, I
usually use the zipkits).

If not, then it sounds like someone replaced your security database,
which could point to a security problem.

> Commit nicht möglich
>
> Unsuccessful execution caused by system error that does not preclude
> successful execution of subsequent statements.
>
> add record error.
>
> violation of PRIMARY or UNIQUE KEY constraint "INTEG_5" on table "PLG$SRP".
>
> Problematic key value is ("PLG$USER_NAME" = 'SATRON').

That indicates the user already exists (as a Srp user).

> The same message I get, If I try to change the sysdba password. I have
> backup the database and restore it, the same. I have deinstalled the
> server and installed it again, the same. Only the sysdba user with
> masterkey could connect, not the new user.

By default the users and passwords are stored in a separate security
database, so backing up and restoring your application database won't
fix anything.

> We have changed after this the firebird.conf ..authserver.. and add srp
> to legacy and now it works. But why worked it before and why sets the
> installer not this flag? I am confused. After I deleted the new user, in
> the access manager I have seen it strikethrough, after I have add this,
> I have seen it normally. One time, the database works not at all, I cant
> show some content, I must restore the database again.

It sounds like you enabled legacy authentication in the installer (or
manually changed it), because AuthServer = Srp is the default.

> Now, the new user works, but If I change the sysdba passwort with
>
> ALTER USER SYSDBA
>
> PASSWORD 'newpassword…'
>
> The new password works, but why works the masterkey again?

It is hard to tell from your problem description exactly what went
wrong, but it sounds like you were using Srp just fine, and now you did
something that disabled Srp and enabled Legacy_Auth.

Firebird 3 introduced authentication plugins: secure remote password
(Srp) and legacy authentication (Legacy_Auth). Users are created per
authentication plugin. The default security database contains a SYSDBA
user with password masterkey for the Legacy_Auth SYSDBA user (and no Srp
user).

If a SYSDBA account was created for Srp as well, it can have a different
password than the Legacy_Auth. When you authenticate, plugins will be
tried until the first successful login (or no more plugins). This means
that if you have AuthServer = Srp,Legacy_Auth (or AuthServer =
Legacy_Auth,Srp), then - for a client that supports both - two login
attempts can be made for SYSDBA: one with Srp and one with Legacy_Auth.

Using `ALTER USER` or `CREATE USER` without explicitly specifying the
user manager plugin (Srp or Legacy_UserManager) will use the default
plugin (the first in the UserManager list). So if you use `ALTER USER
SYSDBA ...` with setting `UserManager = Legacy_UserManager,Srp`, then
the password of the Legacy_Auth SYSDBA will be changed, but the Srp
SYSDBA keeps it previous password.

You should prefer to use Srp and drop the Legacy_Auth SYSDBA ASAP,
because it is simply less safe. This even applies if you need
Legacy_Auth users for applications that use clients without Srp support.

In any case, you should carefully review the settings for AuthServer and
UserManager. If you don't need Legacy_Auth, then set it to
AuthServer=Srp and UserManager=Srp. If you do need Legacy_Auth for some
applications, set AuthServer=Srp,Legacy_Auth and
UserManager=Srp,Legacy_Usermanager so that Srp is preferred.

Check SEC$USERS for your users and specifically which authentication
plugin(s) they exist for. Consider dropping Legacy_Auth users as much as
possible in favor of Srp users (that is assuming your application can
authenticate with Srp); if a user exists for Legacy_Auth. Drop it and
recreate it for Srp. If a user exists for both plugins, consider
dropping them for the other (prefer to retain the Srp user).

And when using ALTER/CREATE/DROP consider always using the USING PLUGIN
<name> clause, so you are sure you are handling a user for the right plugin.

Mark
--
Mark Rotteveel