Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / fpc-pascal / 2009-03

proxy for sql

5 messages · 3 people ·

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

Andreas Berger

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?

Regards,
Andreas

Michael Van Canneyt
to Andreas Berger
Andreas Berger wrote 12 lines
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.

I seriously doubt this, unless you write an ODBC driver that connects to your application instead of the database.
There is no way to know in advance what tool the user will use, and ODBC is more or less the only standard that most reporting tools support...

2) Is it possible to configure the SQL server to query my app to see if a user can log on?

That depends on the database and how your login is defined. In e.g. firebird, I don't think this is possible.

IMHO Your best shot is to force a particular reporting tool. I have very good experiences with FastReport, however it currently does not work with FPC.

Michael.

Martin Friebe
to Andreas Berger: This isn't really a FPC question, but since the application is being written in FPC I…
Andreas Berger wrote 19 lines
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? Regards, Andreas

Well I imagine that will be hard to do, if you do it in FPC (since you may have to start from scratch).
I don't think there is a component emulating the mysql server interface. Rewriting the server (just the protocol part) is a lot of work.

You may want to look at the mysql site, they have a mysql-proxy, which can be controlled by LUA script. So that my do the trick for you. You could probably replace the client credentials inside the proxy. Make sure you forward using a restricted account. Otherwise you must ensure that nothing in the sql could ever violate the permissions.

Best Regards
Martin

Martin Friebe
to Andreas Berger: This isn't really a FPC question, but since the application is being written in FPC I…
Andreas Berger wrote 16 lines
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

Andreas Berger
to Martin Friebe
Martin Friebe wrote 23 lines
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

Thanks Martin for the idea. Actually I had already thought of this. The problem is that 3rd party report programs connect directly to the SQL server. I would need a password or some other ID to know that THIS 3rd party prog is allowed to enter, so a direct tunnel is not possible. The only way might be for me to capture the user and password handshake, test if the user\password is correct for my app and then substitute it with the real user\password or cut the connection. But for this I would have to interpret the logon sequence and I have no idea how that works.

Regards,
Andreas