Are you using proxy logon when connecting to your Oracle database? Why not?
What’s proxy logon or more correct: Proxy user authentication?
Proxy logon let’s you login in to another schema by connecting as yourself. This is of course only possible if you have been granted the privilege to do that.
Why is this a good thing to do?
- Password security: Users use their own passwords and don’t need to know application-schema-passwords.
- The password to the application-schema does not have to be distributed to a group of users.
- The application-schema can be locked for direct logons.
- If the password for the app-schema is changed, I don’t have to notify a bunch of users.
- I can revoke privileges for one user, without having to change the password.
- Knowing my password is not enough to access the schema in question. You also need to know the schema-name. Admittedly this is security by obfuscation.
- You act totally as the schema-owner and all user_-views work as expected, as opposed to when you use set current_schema.
- It increases transparency: As an example – I catch both schema-names in Logger.
As usual, Tim Hall’s Oracle-Base has an excellent article on how to do it: Proxy User Authentication and Connect Through in Oracle Databases