SYSDBA continue to open other user database ?

[Yordan Yanakiev]

Hello.

I've switched the privileges from SYSBA to a new user AGRONN on a db, yet SYSDBA continue to manage it dissregarding that in the roles it shows that it have no permissions.
What I could be gone wrong ?

[Mark Rotteveel]

SYSDBA always has all rights. If you really want to prevent SYSDBA from
logging in, you need to create role with the name SYSDBA in that
database (but do be sure that another user has the RDB$ADMIN role before
you do that).

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

Yordan Yanakiev wrote 19.03.2024 13:05:
> What I could be gone wrong ?

A couple possible things:

1. You used embedded access.
2. You didn't delete SYSDBA user from security database.

If SYSDBA user exists it cannot be stopped because this name is hardcoded in
Firebird engine.

--
WBR, SD.

[Yordan Yanakiev]

Can You give me an example of creating a role which will do the trick ?

[Mark Rotteveel]

On 19/03/2024 14:17, Yordan Yanakiev wrote:
> Can You give me an example of creating a role which will do the trick ?

I'm sorry, I forgot that route has been closed in recent versions: you
can't create a role with the name SYSDBA, and the renaming through the
system tables that used to be possible is also no longer allowed.

Mark
--
Mark Rotteveel

[Yordan Yanakiev]

It is about Firebird 2.5

[Dimitry Sibiryakov]

Yordan Yanakiev wrote 19.03.2024 17:53:
> It is about Firebird 2.5

Still you can (and should) delete SYSDBA from security database.
Also to create trigger there that would prevent SYSDBA recreation is a good idea.

--
WBR, SD.

[Mark Rotteveel]

On 19/03/2024 17:53, Yordan Yanakiev wrote:
> It is about Firebird 2.5

Create a role:

create role DUMMY;
commit;
update RDB$ROLES set RDB$ROLE_NAME = 'SYSDBA'
where RDB$ROLE_NAME = 'DUMMY';
commit;

After this, SYSDBA will no longer be able to login to that specific
database.

Mark
--
Mark Rotteveel

[Carlos H. Cantu]

Please note that it is very easy to get over such poor "protection"... anyone
can easily "remove" the SYSDBA role using an hex editor (many years ago I tested
it using notepad instead of hex editor, and it also worked, lol).

[]s
Carlos
www.firebirdnews.org

MRvfs> On 19/03/2024 17:53, Yordan Yanakiev wrote:
>> It is about Firebird 2.5

MRvfs> Create a role:

MRvfs> create role DUMMY;
MRvfs> commit;
MRvfs> update RDB$ROLES set RDB$ROLE_NAME = 'SYSDBA'
MRvfs> where RDB$ROLE_NAME = 'DUMMY';
MRvfs> commit;

MRvfs> After this, SYSDBA will no longer be able to login to that specific database.

MRvfs> Mark
MRvfs> --
MRvfs> Mark Rotteveel

[Mark Rotteveel]

On 19/03/2024 20:38, Carlos H. Cantu wrote:
> Please note that it is very easy to get over such poor "protection"... anyone
> can easily "remove" the SYSDBA role using an hex editor (many years ago I tested
> it using notepad instead of hex editor, and it also worked, lol).

Sure, but that requires that you have access to the database file itself.

Mark
--
Mark Rotteveel

[Yordan Yanakiev]

So .. I tried it. It worked, but as I am intending to distribute the db around. Is there a way to actually prevent SYSDBA to connect to my database?

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 19.03.2024 20:39:
>
> Sure, but that requires that you have access to the database file itself.

No. In 2.5 there is no protection for system table. Anyone logged in can
update RDB$ROLES again.
Though I may be wrong and only database owner can do that but still...

--
WBR, SD.

[Tomasz Tyrakowski]

On 19.03.2024 at 21:06, Yordan Yanakiev wrote:
> So .. I tried it. It worked, but as I am intending to distribute the db
> around. Is there a way to actually prevent SYSDBA to connect to my database?
>

If you allow users to have physical access to the database file,
encrypting sensitive data is probably the only way to be safe. Still not
a barrier for a determined attacker - the key can be extracted from the
executable of your application, but preventing casual users from messing
with data they're not supposed to.
With the physical access to the fdb file, anyone (with enough knowledge)
can open it in embedded mode, so server-based security (users,
passwords, roles) is useless in that scenario, since there is no server
in embedded mode.

regards
Tomasz

[Yordan Yanakiev]

So far thank you fellows for the help. I really appreciate it.
Tomasz Tyrakowski - I am still failing to understand this part of " embedded mode". Can You describe how It can be done ?
( At this point this start to sound important to me, and I am not sure I have seen such usage this far )

[Tomasz Tyrakowski]

On 19.03.2024 at 21:35, Yordan Yanakiev wrote:
> So far thank you fellows for the help. I really appreciate it.
> Tomasz Tyrakowski - I am still failing to understand this part of "
> embedded mode". Can You describe how It can be done ?
> ( At this point this start to sound important to me, and I am not sure I
> have seen such usage this far )

In short, instead of connecting to "localhost:/path/database.fdb" and
talking to the Firebird server process, you attach using just
"/path/database.fdb" and your Firebird client library (together with
some other supporting libraries, depending on the version) opens the
database file directly, creating a "private" server inside your client
application process - something similar to how SQLite works (but while
SQLite works only this way, in Firebird it's just one of the modes of
operation). In this mode all user/password/role authentication is
bypassed and you act as a dabase owner with full privileges (but it
requires physical access to the fdb file, that's why in a real
client-server scenario the actual database file is separated from client
applications).
See also here:
https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/ufb/using-firebird.html#ufb-cs-embedded
because I might not be clear enough. Or just try it yourself: in
embedded mode you can attach to any database you've got access to on the
file system level, without providing any auth data (user/password/role).
IMHO, if you give away the database file together with your application,
you'd better focus on employing some kind of encryption instead of
relying on security by obscurity (however smart that obscurity might
seem). Of course it all depends on how important the data is, and what
harm can be done by circumventing your application and accessing the
data directly (and also who might actually want to make the effort to do
that).

regards
Tomasz

[Mark Rotteveel]

On 19/03/2024 21:04, Yordan Yanakiev wrote:
> Oh.. Just got what it goes there.. It is a fake role, which is killable
> just by the thing which I did.
> Stupid me. So.. Is there some other way to protect the file actually,
> since it will be distributed around ?

Depends on the protection you're looking for, but ultimately, if people
have access to the actual database file (or a backup), they will be able
to do what they want with it, either by finding out which user they need
to create to assume sufficient access, or by hex-editing the database to
replace a user, etc.

With Firebird 3.0 and higher, you can make it harder by encrypting
databases and their backups (you will need a third-party plugin), but
then the problem just moves to key management: if someone has access to
the key and the database file or backup, they can do anything they want.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 19/03/2024 21:19, Tomasz Tyrakowski wrote:
> With the physical access to the fdb file, anyone (with enough knowledge)
> can open it in embedded mode, so server-based security (users,
> passwords, roles) is useless in that scenario, since there is no server
> in embedded mode.

Roles also apply in embedded mode, as roles are per-database.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 19/03/2024 22:02, Tomasz Tyrakowski wrote:
> SQLite works only this way, in Firebird it's just one of the modes of
> operation). In this mode all user/password/role authentication is
> bypassed and you act as a dabase owner with full privileges (but it
> requires physical access to the fdb file, that's why in a real
> client-server scenario the actual database file is separated from client
> applications).

In embedded mode, you only act as the database owner *if* you specify
the right username, and/or you only get other privilege *if* you specify
the right username and role.

So, just connecting with embedded is not sufficient in and of itself:
the privilege system still applies, it is just authentication which
works for any provided username irrespective of the password used. Given
you can query the metadata tables with any username, you can then simply
find out which users or roles you need to use.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 20.03.2024 10:36:
> With Firebird 3.0 and higher, you can make it harder by encrypting databases and
> their backups (you will need a third-party plugin), but then the problem just
> moves to key management: if someone has access to the key and the database file
> or backup, they can do anything they want.

Not only that.
The main problem of any security is a legit access. Absolutely secure system
is absolutely inaccessible. If anybody can access data they are insecure because
the data can be exported and whole database recreated from scratch in any
suitable form.
Because of this "distribution" and "protection" is a contradiction that is
very hard to solve and the best solution is DO NOT DISTRIBUTE database but
provide a controllable access to data via the Internet.

--
WBR, SD.

[Yordan Yanakiev]

Not possible. Actually we've chose initially Firebird just because we are distributing it to users without Internet on their computers ( POS ).

Probably we have missed this part that our database is easy to be breached.

[Tomasz Tyrakowski]

On 20.03.2024 at 22:45, Yordan Yanakiev wrote:
> Not possible. Actually we've chose initially Firebird just because we are
> distributing it to users without Internet on their computers ( POS ).
> Probably we have missed this part that our database is easy to be breached.

If the data you need to protect is related to e.g. licensing or
something similar, you can use public key encryption. Encrypt with the
private key and embed the public key in your app. This way the app can
decrypt the data and check the license, but nobody can change it in the
database without the valid private key. That should work for relatively
small amount of critical data (public key encryption is computationally
expensive). The app can probably still be cracked to bypass license
check altogether, though ;)
If you need to protect the actual data yoyr app is processing (but why,
if the users of the app can see the data in the app anyway), just put
DMCA, DMA and a bunch of other scary looking three letter acronyms,
warning the users not to abuse your IP and there's not much more you can
do against a determined user with enough knowledge and resources, who
has direct access to your data and application files.

regards
Tomasz

[Mark Rotteveel]

On 20/03/2024 22:45, Yordan Yanakiev wrote:
> Not possible. Actually we've chose initially Firebird just because we
> are distributing it to users without Internet on their computers ( POS ).
> Probably we have missed this part that our database is easy to be breached.

This applies to *any* database where the users have filesystem access to
the database file. If companies tell you otherwise, they're lying.

Mark
--
Mark Rotteveel