I am new to Oracle security, and I would like to know how to connect to database using proxy users and what is the benefit of it? Can someone give me some advice. I have experience with Oracle database and security.
What you want to know about proxy users exactly? They are used in three-tier computing when application server needs to access the database, and you want to audit each application user inside the database.
That means that application server can make a connection to database on behalf of real user that is authenticated to application.
ALTER USER APPUSER
Then APPSERVER can connect APPUSER:
Now audit trail will show APPSERVER user and real (APPUSER) that connected to database.
The standard use case is that you want to have a connection pool in the middle tier where all the application users are logging in to the database as the same user. This allows you to avoid the overhead of creating and destroying a physical database connection every time an application user requests a web page (creating and closing a physical connection every time someone requested a page would generally take more time and potentially much more time than actually generating the page would take) and it allows you to avoid having tons of physical connection in your connection pool which can only be reused by one particular application user meaning that you end up with way, way more connections to the database than you need.
In just about any three-tier environment, that means that you end up with a connection pool that uses a single shared user database account to do everything. If you don't use proxy users, that means that your application has to know how to create application users, it needs to manage privileges, it needs to implement all the security you need, etc. And it means that the shared user account that everyone uses needs to have all the privileges that any application user would ever have which means that the shared user account generally ends up running with way more privileges than it really needs 99.999% of the time because there is one time every year that some admin user needs to do something. If the shared database account gets compromised (either because your application has a security hole or because a someone got access to the shared user account password) the attacker has a much wider array of attacks they can perform.
If you are using proxy users, however, you get the best of both worlds. The middle tier connection pool connects to the database as the shared user A but all the actual interactions happen as user B. Your application doesn't need to build in the ability to manage and apply privileges-- it uses what the database provides. Your application doesn't need to build in all the security, it can leverage Oracle's ability to grant particular privileges to particular users. You don't end up with a hugely overpowerful shared user account, you just grant all the different individual accounts whatever privileges they actually need so admin users can do some things and power business users can do a separate set of things and no one account can do everything.
Tom Kyte has a nice discussion on proxy users as well