How to REVOKE ALL ON ALL when running an embedded database?

[Wavey Maple]

Hi there,

I have a legacy application with legacy database files, running in embedded mode. GRANTs were applied to a specific user in those legacy files which I need to remove.

When we were running Firebird 2.5.9 in embedded mode, it was simply a matter of running the SQL command:
REVOKE ALL ON ALL FROM [USER] username;

and all GRANTs were removed.

When trying to do this in Firebird 3.0 in embedded mode, I get the following error:

REVOKE ALL ON ALL FROM CANFIT_USER;
Statement failed, SQLSTATE = 08001
unsuccessful metadata update
-REVOKE failed
-I/O error during "CreateFile (open)" operation for file "D:\MyApp\SECURITY3.FDB"
-Error while trying to open file
-The system cannot find the file specified.
After line 150 in file D:\MyApp\SQLUpdate01.sql

This is documented in Helen Borrie's Firebird 3.0 Release Notes v.0300-38, p.61:
https://firebirdsql.org/file/documentation/release_notes/Firebird-3.0.0-ReleaseNotes.pdf

Is there something special that needs to be done for embedded, in order for this to work?

Thanks in advance!
David

[Mark Rotteveel]

This sounds like a bug to me, please report it in the tracker at
http://tracker.firebirdsql.org/browse/CORE

It looks like Firebird 3 is also trying to revoke privileges in the
security database, but doesn't take into account a Firebird Embedded
without security database. A possible workaround would seem to copy a
'clean' security3.fdb (eg from a zipkit) to the root of your
application. Alternatively, you could try if changing the
SecurityDatabase setting in firebird.conf or databases.conf of your
embedded install to point to the database itself helps.

Mark

[Wavey Maple]

Thanks for the speedy reply Mark. I was asking because I didn't know if I was doing something incorrectly and this was behaving as expected.

To follow up:

1. Copying Security3.fdb to the same folder as where MyApp.fdb sits resolves this issue.
2. I cannot get REVOKE all to work by configuring databases.conf - the error message is the same originally posted.

So on the first point, I am wondering whether it IS a bug or not? From the Quick Start Guide, section '5.1.3. The security database', p.19
https://www.firebirdsql.org/file/documentation/pdf/en/firebirddocs/qsg3/firebird-3-quickstartguide.pdf
it states "Except in the case of so-called embedded connections (more about those later in this guide), connecting to a database always involves the security database..." which would infer Security3.fdb does not need to sit beside MyApp.fdb. So a follow-on question is;
a) Is Security3.fdb supposed to be deployed in an embedded configuration?

On point #2, I am not sure if the syntax I am using is correct. In databases.conf, I have set up (NB: running in Windows 10):
MyAppAlias = MyApp.fdb
{
SecurityDatabase = MyAppAlias
}
however I still get the original error where it seems databases.conf is not being used. So a couple more questions:
b) Is databases.conf even used in embedded?
c) If so, is the syntax above correct?

Thanks in advance,

David

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/fcf24c267cd57a6d4f2493c195b3ade1%40lawinegevaar.nl.

[DougC]

---- On Tue, 16 Feb 2021 19:05:41 -0500 Wavey Maple <wavey...@gmail.com> wrote ----

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CANNHrU6uTT3m45VY%2B%3DaRxfo6ygeF05Gh%2BA1WmasBwXh6Og929g%40mail.gmail.com.

[Mark Rotteveel]

On 17-02-2021 01:05, Wavey Maple wrote:
> Thanks for the speedy reply Mark. I was asking because I didn't know if
> I was doing something incorrectly and this was behaving as expected.
>
> To follow up:
> 1. Copying Security3.fdb to the same folder as where MyApp.fdb sits
> resolves this issue.
> 2. I cannot get REVOKE all to work by configuring databases.conf - the
> error message is the same originally posted.
>
> So on the first point, I am wondering whether it IS a bug or not? From
> the Quick Start Guide, section '5.1.3. The security database', p.19
> https://www.firebirdsql.org/file/documentation/pdf/en/firebirddocs/qsg3/firebird-3-quickstartguide.pdf

> <https://www.firebirdsql.org/file/documentation/pdf/en/firebirddocs/qsg3/firebird-3-quickstartguide.pdf>
> it states "/Except in the case of so-called embedded connections (more

> about those later in this guide), connecting to a database always

> involves the security database.../" which would infer Security3.fdb does
> *not* need to sit beside MyApp.fdb. So a follow-on question is;

> a) Is Security3.fdb supposed to be deployed in an embedded configuration?

I think it's a bug because Firebird Embedded doesn't need to have a
security database, but it looks like REVOKE ALL ON ALL performs actions
that require a security database. As I mentioned in my previous reply,
please report it in the tracker.

If connected with an embedded connection, at best this should produce a
warning, or just work.

> On point #2, I am not sure if the syntax I am using is correct. In
> databases.conf, I have set up (NB: running in Windows 10):
> MyAppAlias = MyApp.fdb
> {
> SecurityDatabase = MyAppAlias
> }
> however I still get the original error where it seems databases.conf is
> not being used. So a couple more questions:
> b) Is databases.conf even used in embedded?
> c) If so, is the syntax above correct?

Yes, databases.conf is used for Firebird Embedded, the problem is that
you specified a relative path, and relative paths don't work in
databases.conf. You need to specify an absolute path, or use one of the
variables specified in
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-configparams-macrosubs

For example, use $(root)\MyApp.fdb (which means the database must be in
the directory containing Firebird Embedded), or specify an absolute path.

And of course, you need to specify the alias and not the database name
in your connection string.

Mark
--
Mark Rotteveel