servlet connection to DB

Question

Initially, after reading books on the subject, I firmly believed that the algorithm for working with a database from a servlet is as follows: create a connection -> connect to the database -> form a request -> send the request to the database -> get the query results -> process them -> close connection -> OK.

Now, with a better understanding of the practical side, I realized that nobody does it that way, and everything happens through a connection pool according to the following algorithm:

initialize the servlet -> create a connection pool -> a request comes from a user -> take a free connection from the pool -> form a request -> send the request to the database -> get the query results -> process them -> return the connection back to the pool -> ok.

Now I have this problem: We have 100 users, they are divided into 10 groups, each group has it's own username and password to connect to the database. Moreover, each group may have different rights to the database. How am I supposed to use a connection pool in this situation? If I understand correctly, a pool is nothing more than just a group of similar connections with a single login and password. And here I have 10 pairs of username / password. It looks like I cannot use the pool in this situation. What should I do?

Answer 1

Use 10 connection pools, one per group.

Answer 2

If you are using a servlet, I assume some front end screens will be what the 10 end user groups will see? Wont there be varying functionality for each user group with the Authorization rights being controlled by the servlet?

In that case, the DB connection would be the same for all. Why is the code actually needing separate database connection rights? Are they using separate schemas altogether in which case you would need separate connections?

Answer 3

The usual way is the three-tier scenario, where the servlet connects to the DB using a dedicated DB user account for itself, and does all the work on behalf of the users. Access control is in this case left to the servlet, the DB will just do anything the servlet asks it to. This dedicated account will need to have access to all of the users' data.

This approach does have drawbacks, especially if you already have a culture/infrastructure of managing user permissions in the database. If you want to preserve this, you'd have to configure separate pools for each user group, which unless the number of groups is fixed and known in advance cannot be done with simple (file-based) configuration, or forgo pooling and hope that the DB is fast enough with the fresh connections.