We have a payroll application that we are currently porting to Servoy. However, one of my concerns is that in the existing set-up, the access control to the application is actually handled by the database. In other words, when someone supplies a username and password to our application, those credentials are actually fed to the backend database (which is sybase 8, btw) and the backend database uses that login to control what the user has acces to.
On the other hand, Servoy seems to require that we set up a generic username and password in the DBSERVERS configuration area, and Servoy then uses that username and password to access the backend database.
My question is, is it possible to programmatically control how Servoy accesses its databases? In other words, could we configure things such that when someone logs into Servoy, they also log into the backend database with those same credentials?
You could create one DB server connection for each user and use the databaseManager.switchServer() function to use the right one based on the user login.
Keep in mind though that Servoy will open a few connections to the database for EACH user profile and this will costs quite a lot of RAM if you have a large number of users.
In my opinion you should get a “super account” from your DB manager to use with Servoy and limit each user privileges using Servoy security.
Unfortunately, multiple DB server connections in Servoy is not an option. The application in question is not an inhouse application, but rather a commercial product, which means that there is no way for use to know beforehand what usernames will exist. As well, as you rightly pointed out, the number of such connections that may be necessary at each client will not make it a viable option.
As well, the data itself is actually restricted based on the logged in user, so moving to using Servoy to restrict the data will require a very significant amount of rework.
I’m afraid but I can’t imagine other options.
Anyway take a look at Servoy security model: it’s really easy to implement your policies at Servoy level, in one of my apps I easily manage 6 different groups (user profiles) in a very complicated environment.
Keep also in mind that if your setup is using DB views to manage different user privileges you can reuse the views in servoy and have half of the work already done.
In one of our commercial products, we restrict user data access based on the database login used. In other words, when a user logs into our application, those login credentials are passed to the database and used to log into the database.
By doing this, we are able to restrict which parts of our application a user has access to. I understand that Servoy has a fairly comprehensive security model that can probably achieve the same effect, but it will require us to rewrite a rather significant part of our application to adapt to using Servoy’s security model.
In addition, our security system is also used in a number of places to restrict the number of records returned to the user. As an example, a report showing a list of employees in the company may only return a subset of all employees if the user running the report is restricted from accessing certain departments. Thus our security restricts not just at the ‘object’ level, but also at the data level where necessary.
Thus, we really need to be able to dynamically tell Servoy what username and password to use to log into the backend database - a lack of such functionality is extremely close to a deal breaker in terms of using Servoy as a development platform for that product.
Is there some way of achieving the effect we need… dynamically entering database login credentials?
This can’t currently be done in servoy. This is because the client where you login doesn’t have the database connection. The database connection for a server is pooled by the Servoy Application Server and that is configured at the server.
But what do you want to restrict? Because security or the flow of the application is done in the Servoy client. so your own code.
So when should what fail? Or are you saying that those logins also filter data for queries?
Our application currently uses a large body of stored procedures for all CRUD operations (along with other things), and a major part of our security model is based on restricting access to these various stored procedures to restrict what CRUD operations users can do against the backend DB. I know this part of our security system can be duplicated in Servoy without too much of a problem.
My concern is the fact that we also restrict the data returned by the DB based on the logged in user.
I guess we can possibly re-work those data restriction queries to use Servoy’s logged in user rather than the database’s logged in user…
That might be a viable option… I’ll look into it in more detail.
Thanks though, for confirming that we can’t change the user name Servoy uses. At least we know what our options are moving forward.
Do you know that Servoy has built in Tablefilter functions, that allow you to set a filter on a table at login and then everywhere where data is shown from that table, it will be filtered according to the filter you set.
Offcourse, that filter can be based on the (profile of the) user that logs in.
So. I think this would make it quite easy to also create the record level access restrictions you need.