TransWikia.com

Using hard-coded host name in logon trigger

Database Administrators Asked on December 22, 2021

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.

Correct?


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:

  1. Have a table which will check for combination of user + server + database hostname (name of machine where database is hosted).
  2. Have a DB_ROLE_CHANGE trigger which will get activated if the production now runs from standby
  3. The DB_ROLE_TRIGGER will modify the table so that the database hostname now reflects new production server hostname
  4. The APPSERVER and APPUSER will match OK. But since the hostname is now production – it will disallow login.

One Answer

If you only want users to connect to the standby then check the db_role value from v$database. The primary will be PRIMARY and the standby wont (it'll be PHYSICAL STANDBY or similar).

Add a check into your trigger to bump them.

...
SELECT database_role INTO db_role FROM v$database;
IF db_role = 'PRIMARY' THEN (deny those users a connection)
..

Answered by user3629 on December 22, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP