Note: Question updated to narrow the scope a bit.
Client <-> Application server <-> Data server
- Users use the client to connect to the application server and build queries for execution.
- Application server handles authentication and authorization.
- Application server sends query definitions (not SQL) along with authorization information to the data server.
- Data server does what it is told.
Authorization dictates which tables and columns from tables a user is allowed or not allowed to see. And can also specify column values that a user is allowed or not allowed to see. A user may for example be restricted to see only order from client X. Another user may be allowed to see employee data for him/herself and her/his team members except salary information (columns).
This is a software suite which will be installed on our customer's machines. The installation may consist of multiple application servers and multiple data servers.
As the data server does not do any authentication or authorization of actual users, we need to ensure that:
- query execution requests are only originating from our own application servers
- get result data requests are only accepted from the same application server that sent that query execution request
- the authentication and encryption methods have as little overhead as possible (connections may be established on a per request basis and each query execution request can be followed by many get result data requests)
- the application server and the data server may be running on physically different machines
What would be the best way to set up the communication between the application server and the data server to ensure the requirements are met?
We are currently using HTTPS, but are open to other communication methods if these would make authentication and security of the communication between application and data server easier/better/faster.
We would like to steer clear of anything that a security conscious IT department would frown upon (trusting another CA) or would make the installation a lot harder/more painful (client certificates). But authentication of processes and the security of the data flowing between them is essential, so if they are the only way, we'll just have swallow the pill.