[fpc-pascal] proxy for sql
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 ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] proxy for sql
On Thu, 19 Mar 2009, 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. 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. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
off-topic [Re: [fpc-pascal] proxy for sql]
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? 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 ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] proxy for sql
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 ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] proxy for sql
Martin Friebe wrote: 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 ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
