Pass context variables on connect

[Lucas Schatz]

During the connection, is it possible to pass info as a DPB parameter to retrieve latter at get_context('SYSTEM' or 'USER')?

What I was planning is to pass some kind of additional info directly in the connection to be read at `ON CONNECT` trigger

Thanks

[Dimitry Sibiryakov]

Lucas Schatz wrote 23.09.2025 22:05:
> During the connection, is it possible to pass info as a DPB parameter to
> retrieve latter at get_context('SYSTEM' or 'USER')?

No, there is no way.

--
WBR, SD.

[Lucas Schatz]

ok, thank you

[Pavel Zotov]

Lucas Schatz wrote 23.09.2025 22:05:
> During the connection, is it possible to pass info as a DPB parameter to
> retrieve latter at get_context('SYSTEM' or 'USER')?

1) add (if needed) into firebird.conf:  ExternalFileAccess = Restrict c:\temp

2) restart FB

3) create file C:\temp\external_info_for_connect.txt and put there this lines:

....:....1....:....2....:....3

qwerty........................
........................asdfgh
................foo...........

(NOTE: at least one EOL character must present after last line with "....foo....")

4) run following .sql:

set bail on;
shell del c:\temp\tmp4test.fdb 2>nul;
create database 'localhost:c:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

create table external_info_for_connect external 'c:\temp\external_info_for_connect.txt' (
    msg char(30),
    lf char(2)
);
create table db_saved_info(
    id int generated by default as identity constraint pk_saved_info primary key,
    msg varchar(30)
);

set term ^;
create trigger t_connect on connect as
begin
    for select msg from external_info_for_connect as cursor c
    do insert into db_saved_info(msg) values( upper(c.msg) );
end
^
set term ;^
commit;
------------------
connect 'localhost:c:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
set count on;
select * from db_saved_info;
--------------------

4) you will see output like this:

          ID MSG
============ ==============================
           1 ....:....1....:....2....:....3
           2 QWERTY........................
           3 ........................ASDFGH
           4 ................FOO...........

Records affected: 4

PS.

Yes, this way looks very ugly...  :-/

[Lucas Schatz]

Hi, unfortunately I need that user/client to send this data, so it will be of no use to me this way.

Thank you anyway

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/79f349d0-b55b-4f1a-833c-1ccb306097e9n%40googlegroups.com.

[Роман Симаков]

What's the problem to call set_context after connecting?

 

----------------

Кому: firebir...@googlegroups.com (firebir...@googlegroups.com);

Тема: [firebird-devel] Pass context variables on connect;

24.09.2025, 21:29, "Lucas Schatz" <lucas...@gmail.com>:

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/CAOuxYbd8AVgL3x4bfwoMmcB6yvUhOASqos6MTM%2BckhHDBBwPgA%40mail.gmail.com.

[Pavel Zotov]

>  What's the problem to call set_context after connecting?

AFAIU, author wants smth like this:

echo set term #; execute block as begin rdb$set_context('USER_SESSION', 'MY_OWN_PI', '31.415826' ); end # set term ;# | isql localhost:r:\temp\tmp4test.fdb

-- with assumption that DB-level trigger will see this context variable and its value. 

But in fact this trigger will see nothing.

среда, 24 сентября 2025 г. в 22:39:33 UTC+3, Роман Симаков:

[Adriano dos Santos Fernandes]

On 9/24/25 17:38, Pavel Zotov wrote:
>>  What's the problem to call set_context after connecting?
>
> AFAIU, author wants smth like this:
>
> echo set term #; execute block as begin rdb$set_context('USER_SESSION',
> 'MY_OWN_PI', '31.415826' ); end # set term ;# | isql localhost:r:
> \temp\tmp4test.fdb
>

Very fancy...

I'd say he wants to pass context in DPB and that sounds very doable for me.


Adriano

[Dmitry Yemanov]

25.09.2025 03:53, Adriano dos Santos Fernandes wrote:
>
> I'd say he wants to pass context in DPB and that sounds very doable for me.

Agreed. Not sure how much important is this, but surely doable.


Dmitry

[Pavel Zotov]

>  pass context in DPB and that sounds very doable for me.

Commands 'set names ...'  and  'set sql dialect ...'   already do similar thing.

But it will be great to have ability to use somewhat like:

===========

set min_date = $1; -- input arg #1 for this script

set oper_id = $2; -- input arg #2

set author_name = $3; -- input arg #3

-- take from OS environment:

set tmppath = get_env('temp');

-- local variables:

set dsn = 'localhost:employee';

set dba = 'SYSDBA';

set pwf = '/path/to/password_file.txt';

connect '${dsn}' user ${dba} fetch_password '${pwf}';

out ${tmppath}/report.${min_date}.txt;

select * from some_table where operation_date = '${min_date}' and operation_id = ${oper_id} and author_name = q'#${author_name}#';

out;

...

===========

And run it like this:

/path/to/isql -q -i <this_script> -arg1:"23.09.2025" -arg2:"1234" -arg3:"john o'hara"

(where `-argN:` serves as prefix for appropriate parameter value)

See also https://github.com/FirebirdSQL/firebird/issues/8232

(but this ticket 'extends' similar idea with f-notation which seems to be too complex to implement (?))

четверг, 25 сентября 2025 г. в 08:12:17 UTC+3, Dmitry Yemanov:

[Mark Rotteveel]

That would be ISQL gold-plating nor is it what is really asked for here.
Keep in mind most users/applications don't use ISQL in the normal course
of things.

Mark
--
Mark Rotteveel

[livius...@poczta.onet.pl]

Hi

you can do this in different way. Use user parameters and read it in the on connect trigger.

e.g.

ALTER USER superhero SET TAGS (MayVar1='x', MayVar2='d');

and in ON CONNECT TRIGGER

SELECT * FROM SEC$USER_ATTRIBUTES SA WHERE SA.SEC$USER_NAME=CURRENT_USER

--

Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:

https://www.firebirdsql.org/donate

---

You received this message because you are subscribed to the Google Groups "firebird-devel" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/9e39c6aa-f154-493a-bc1f-b18a804c5cddn%40googlegroups.com.

[Mark Rotteveel]

On 25/09/2025 13:36, liviuslivius via firebird-devel wrote:
> Hi
>
> you can do this in different way. Use user parameters and read it in the
> on connect trigger.
> e.g.
>
> ALTER USER superhero SET TAGS (MayVar1='x', MayVar2='d');
>
> and in ON CONNECT TRIGGER

> *SELECT* * *FROM* SEC$USER_ATTRIBUTES SA
> *WHERE* SA.SEC$USER_NAME=CURRENT_USER
Keep in mind that

1) A user can modify its own tags
2) A user can view its own tags
3) Users with admin access can view a different users tags

Mark
--
Mark Rotteveel

[livius...@poczta.onet.pl]

Good points, but without a description of the goal, we can only guess at the acceptable concept ;-)

If it is some security check, then better will be:

https://github.com/FirebirdSQL/firebird/issues/8570

On 25/09/2025 13:36, liviuslivius via firebird-devel wrote:
> Hi
> 
> you can do this in different way. Use user parameters and read it in the 
> on connect trigger.
> e.g.
> 
> ALTER USER superhero SET TAGS (MayVar1='x', MayVar2='d');
> 
> and in ON CONNECT TRIGGER
> *SELECT* * *FROM* SEC$USER_ATTRIBUTES SA 
> *WHERE* SA.SEC$USER_NAME=CURRENT_USER
Keep in mind that

1) A user can modify its own tags
2) A user can view its own tags
3) Users with admin access can view a different users tags

Mark
-- 
Mark Rotteveel

-- 
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:

https://www.firebirdsql.org/donate
--- 
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com

.
To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/f1bccedb-f08b-4efd-98ba-afcc59132de7%40lawinegevaar.nl.

[Lucas Schatz]

So, I need to add more audit data for my own application, Today I already log who connected, IP, available OS data and which application.

As proposed By Adriano, I already send the app version as a subsequent SQL with set_context, but this way I'm unable to block anything as I cannot guarantee that it'll be done by external applications

Also, by our internal design, every person has their own Firebird User, and I need to be sure that he is using a valid app, the only way I can think of blocking unauthorized apps is this way.

If he connected, the user is "low profile", isn't using a valid/signed/allowed application I'll block him

In summary, the kind of data I was thinking to send is:

App version/git hash that I would validate with a table

And a signature that would be validated in the connect trigger to avoid fake data

This way, the user is allowed to use a "MyApplication" but not Flamerobin, DBeaver, IBExpert or some other shady app

Thanks

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/940165cc-084e-d62b-d6fa-54eb69bfddc7%40poczta.onet.pl.

[Роман Симаков]

AFAIU you want check that an app user uses is correct? So  the right app might set the right set_context secret. In the trigger on transaction start you can check this secret and prevent the use of your database with incorrect app.

----------------

Кому: firebir...@googlegroups.com (firebir...@googlegroups.com);

Тема: [firebird-devel] Pass context variables on connect;

25.09.2025, 20:03, "Lucas Schatz" <lucas...@gmail.com>:

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/CAOuxYbdY0L%3DrmPupgxt3QuSKVAwB2HsPkdYgwR7dYcR37vUC1w%40mail.gmail.com.

[Dimitry Sibiryakov]

Роман Симаков wrote 25.09.2025 21:13:
> AFAIU you want check that an app user uses is correct? So  the right app might
> set the right set_context secret. In the trigger on transaction start you can
> check this secret and prevent the use of your database with incorrect app.

...and a wrong app just connect as SYSDBA with no-db-triggers option.

--
WBR, SD.

[Lucas Schatz]

but this way would cause unnecessary load for every transaction started as we have a lot of open/closing transactions

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/a1ee6748-665a-4157-9c6a-db5f167d56a2%40ibphoenix.com.

[Lucas Schatz]

I'm OK with `no-db-triggers` as long as only SYSDBA is able to use it, as it is the case (AFAIK, only admin can use this flag, correct?) 

On Thu, Sep 25, 2025 at 4:24 PM 'Dimitry Sibiryakov' via firebird-devel <firebir...@googlegroups.com> wrote:

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/a1ee6748-665a-4157-9c6a-db5f167d56a2%40ibphoenix.com.

[Tomasz Tyrakowski]

On 25.09.2025 at 19:02, Lucas Schatz wrote:
> [...]

> This way, the user is allowed to use a "MyApplication" but not Flamerobin,
> DBeaver, IBExpert or some other shady app

Look, I know it's security by obscurity, but have you thought about
something really simple, like mangling passwords in your application in
some way and use the mangled ones as the real FB passwords? Without the
knowledge of the mangling algorithm, the users won't be able to connect
with any other app than yours.
Of course it won't stop a really determined, tech-savvy user, capable of
using a debugger, but should be sufficient to prevent an average office
worker from trying to be clever and side-connect to your database.
So it all depends on the domain of your app. If it's really critical to
prevent users from connecting with other clients, probably even sending
some additional, secret data in PDB on connect is not a fully safe
solution - someone may just intercept network traffic and deduce what
extra data to send, debug your app step by step, or swap fbclient for a
custom-built one, which would log all API calls. In that case I'd rather
implement an application server and physically cut the users off the
database.

regards
Tomasz

p.s. At this point this conversation looks like a better fit for
firebird-support than firebird-devel, if you feel like replying, maybe
consider switching over to firebird-support?

[Dimitry Sibiryakov]

Tomasz Tyrakowski wrote 25.09.2025 20:20:
> In that case I'd rather implement an application server and physically cut the
> users off the database.

...and it would still change nothing because the traffic is still
intercepted, extra data deduced and application is debugged.
Finally everything will come to the main security question: what exactly
attack scenario they want to prevent. And "any" is not the answer.

--
WBR, SD.

[Mark Rotteveel]

But such a web service can limit _what_ can be done more thoroughly than
the database, as it doesn't provide access to the database itself, and
can provide more options for auditing.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 26.09.2025 10:20:
> But such a web service can limit _what_ can be done more thoroughly than the
> database, as it doesn't provide access to the database itself, and can provide
> more options for auditing.

Can you show an example what web service can that SQL security and Stored
Procedures or views cannot?

--
WBR, SD.

[Роман Симаков]

I hope you don't suggest to protect from sysdba, do you? Who knows sysdba password have much more responsibility.
 
Кому: "firebir...@googlegroups.com" <firebir...@googlegroups.com>;

Тема: [firebird-devel] Pass context variables on connect;

22:24, 25 сентября 2025 г., "'Dimitry Sibiryakov' via firebird-devel" <firebir...@googlegroups.com>:

--
   WBR, SD.

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/a1ee6748-665a-4157-9c6a-db5f167d56a2%40ibphoenix.com.

--
Отправлено из мобильного приложения Яндекс Почты

[Tomasz Tyrakowski]

On 26.09.2025 at 09:50, 'Dimitry Sibiryakov' via firebird-devel wrote:
> Tomasz Tyrakowski wrote 25.09.2025 20:20:
>> In that case I'd rather implement an application server and physically
>> cut the users off the database.
>
>   ...and it would still change nothing because the traffic is still
> intercepted, extra data deduced and application is debugged.

Yes, but cutting the users off the database and making them go through
an application server gives you another layer of control. Users cannot
connect directly to the DB and execute any SQL query they wish, they
need to talk to your service, which may decide to do something or not
(based on any number of factors, not the database alone). For example,
there is no way a user can drop a table (because your app server never
does drops). Another example: a user has a certain clearance level to
access only some documents from a table. The app server checks the
clearance and either sends a requested document or not (and it may get
even more complicated: user clearance level may be direct or inherited
from her/his department, etc. - put any convoluted business logic you
wish here). You can't do the same on the DB level alone, unless it
supports record-level permissions (and still inherited permissions and
"any convoluted business logic" would be difficult to do if possible at
all).
There's a reason why web apps have server-side code (which is just
another name for an application server), and not just in-browser
JavaScript talking directly to databases. I wouldn't trust my bank if it
did that ;)
So I think we don't need to argue whether using application server (or
an equivalent) makes any difference, because it does. It just requires
more work and is not always needed.

>   Finally everything will come to the main security question: what
> exactly attack scenario they want to prevent. And "any" is not the answer.
>

Exactly. That's why I suggested a very simple solution, which would
prevent only casual users from trying to do something funny, but there's
probably no safe solution if you allow the users to talk to the database
server directly, and, at the same time, give them control over your
client application, so that they can analyze it any way they wish (so
there's no hidden server-side code, to which the users have no access).
A determined user can always mimic your app to authorize her/himself to
the DB server and, once logged in, do whatever the grants in the DB allow.

regards
Tomasz

[Tomasz Tyrakowski]

Obtain an OAuth token from Google? Send an e-mail? Query LDAP services
to obtain detailed permissions for a user?

Yes, techically you _can_ write an UDF/UDR which can execute any
external code, but then it's just a matter of naming: you basically
implement an application server (server-side business logic), which just
happen to run inside your database process (which IMHO is more risky and
more difficult).

regards
Tomasz

[Dimitry Sibiryakov]

Tomasz Tyrakowski wrote 26.09.2025 10:40:
> A determined user can always mimic your app to authorize her/himself to the DB
> server and, once logged in, do whatever the grants in the DB allow.

These grants usually don't permit dropping of tables.
Fine-grained access is usually implemented via views that won't let users see
documents outside of allowed to them.

--
WBR, SD.

[Tomasz Tyrakowski]

OK, we're walking in circles (examples and counter-examples).
If you say server-side code (a "service") is unnecessary, I respect your
opinion, but I'll stick to mine for the time being, at least until I see
a real world example, in which an RDBMS is available world-wide on a
public IP and the clients connect via internet directly do the RDBMS and
execute SQL statements. And all this works and is secure. Then I'll
definitely change my mind :)

Thank you for an interesting discussion, though.

regards
Tomasz

[Adriano dos Santos Fernandes]

On 9/26/25 07:36, Tomasz Tyrakowski wrote:
> On 26.09.2025 at 12:08, 'Dimitry Sibiryakov' via firebird-devel wrote:
>> Tomasz Tyrakowski wrote 26.09.2025 10:40:
>>> A determined user can always mimic your app to authorize her/himself
>>> to the DB server and, once logged in, do whatever the grants in the
>>> DB allow.
>>
>>    These grants usually don't permit dropping of tables.
>>    Fine-grained access is usually implemented via views that won't let
>> users see documents outside of allowed to them.
>>
>
> OK, we're walking in circles (examples and counter-examples).

You'll always found that here with the against-everything people.

I think you had a good feature request which complements ON CONNECT
triggers, which BTW you said about DPB usage since the principle.

It's implementable IMO. There are things to develop on the idea, for
example, I think this must be usable at least by ISQL.

I'd put it as an issue in the tracker if you're not going to fully
specify it or implement it as PR. ;)


Adriano

[Mark Rotteveel]

On the chance that I'm considered one of the "against-everything
people", I like this feature *if* generally usable through a DPB item.
The suggestion by Pavel to make some ISQL specific thing, I dislike
severely.

If anything the ISQL CONNECT syntax should be extended to be able to
specify more connection properties (preferably even any and all DPB
items), and not try to build on the IMHO historic mistakes of SET NAMES
and SET DIALECT, as also suggested in this thread.

However, absent this feature, I think the rest of this thread should be
taking as suggestions how to solve it with current functionality, and
not so much rejection of the suggestion. To me, the need to restrict and
audit things, suggests that the application design needs to be revisited
with a middleware layer between client application and database.

Alternatively, a bit freewheeling, I wonder if maybe a custom
authentication plugin would allow for the OP to set additional
information; but I don't know enough about the architecture of
authentication plugins.

Mark