I need to write a trigger which prevents certain users from logging into the production database.
We use Active Data Guard.
If I hardcode the Linux hostname in the trigger to verify the host is production, then in case of "Role Change", the trigger may not work.
This is a three-tier system. The end users communicate via an application server, which will use a generic name.
Here is a solution I had been contemplating implementing:
- Have a table which will check for combination of user + server + database hostname (name of machine where database is hosted).
- Have a
DB_ROLE_CHANGEtrigger which will get activated if the production now runs from standby
DB_ROLE_TRIGGERwill modify the table so that the database hostname now reflects new production server hostname
- The APPSERVER and APPUSER will match OK. But since the hostname is now production - it will disallow login.