The middle tier proxy user just needs the "create session" and "connect through to xxx user" rights. It is just a regular user that enables the real user account to impersonate itself and connect to the DB. The proxy user will appear as real user during the session. It will only have the rights that the real user has.
Example:
User1 is using the application and the application needs to access the Oracle DB. It will use the middle tier proxy user to connect to the DB. It will request that the middle tier proxy user pretends to be User1 for the session.
Since the DB knows the middle tier proxy user and it has the right to connect through User1 it accepts the impersonation. The session now only has the rights that are configured for User1 in the DB meaning that it can only do what User1 is allowed to do.
