[fpc-pascal] proxy for sql

[Martin Friebe]

Andreas Berger wrote:
> This isn't really a FPC question, but since the application is being 
> written in FPC I thought I would ask here.
>
> I am developing an application that controls machinery. The 
> application will store everything that happens in a SQL database. Some 
> clients (restricted) should have permission to access the database to 
> generate their own reports via some 3rd party report generation 
> software of their choice. However I want to restrict who has access 
> without placing a user password on the SQL server since some clients 
> purchase multiple instances of the SW but not all options.
>
> 1) Is it possible to place my app between the 3rd party app and the 
> SQL server? How would I do this.
> 2) Is it possible to configure the SQL server to query my app to see 
> if a user can log on?
> 3) Any other ideas?
Here is another idea.

You would still need a user on the DB, to limit what can be accessed. 
But to check if access should be granted (allow or deny tcp) you can use 
your own software.
All you need is some sort of Proxy/socket forwarder, that you can 
implement using FPC.

The Tool would run on the users local PC or Server. It would accept 
connections for the database, and forward the Connection straight to the 
real DB (or maybe encrypt it, using whatever access control). Of course 
if the user is not allowed to connect at all, you do not make the forward.

The user could have a key(public/private), so if you remove the opposite 
on the server, the user can no longer connect.

However this acts like a tunnel, so you do not get any control on the 
SQL itself.  The SQL server needs to be configured to restrict access to 
the permitted data.

Best Regards
Martin