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:

  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.

1 Answer 1


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)
  • So in that case using a USERNAME and PROFILE based approach will not work. All username and profiles will get replicated to standby and we will not be able to allow users to log into standby. Correct ?
    – oradbanj
    Commented Jun 5, 2018 at 15:23
  • I'm not really sure what you are asking - I thought this question was about an after logon on database trigger to prevent users on the primary DG node. This doesn't involve profiles.
    – user3629
    Commented Jun 6, 2018 at 7:55
  • the PROFILE based solution was an alternative. Somebody in my team suggested having different username and PROFILES at production and standby sites. And these PROFILE control login behavior. But my thinking (which you seem to support) is these usernames and PROFILE will get replicated. To summarize - the PROFILE based approach will not work.
    – oradbanj
    Commented Jun 6, 2018 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.