I need to write a trigger which prevents certain users from logging into production database.

We use Active Data Guard.

If I hardcode linux hostname in trigger to verify if host is production, then in case of "Role Change", the trigger may not work.

Correct ?

  • 1
    If you don't want person A to log into Server B then don't create their account on Server B and disallow sharing of name/passwords. – Michael Kutz Jun 1 '18 at 18:56
  • this is a three-tier system. The end user communicate via application server - which will use a generic name – oradbanj Jun 1 '18 at 19:01
  • IMHO - The Middle Tier Production system should "access deny" them. – Michael Kutz Jun 1 '18 at 19:16
  • 1
    If it is a three-tier system and connections to the database are with a 'generic' name, how do you expect the database to even identify if they are allowed or not. From the database's view, all connections are for user "APPSCHEMA" and coming from server 'APPSERVER'. – EdStevens Jun 2 '18 at 2:06
  • @Edstevens - here is 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 3. The APPSERVER and APPUSER will match OK. But since the hostname is now production - it will disallow login. – oradbanj Jun 4 '18 at 16:43

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)
| improve this answer | |
  • If I create a user on production and assign a PROFILE to it - will it be replicated exactly on standby ? – oradbanj Jun 5 '18 at 15:10
  • Yes, users and profiles will be replicated. – user3629 Jun 5 '18 at 15:14
  • 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 Jun 5 '18 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 Jun 6 '18 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 Jun 6 '18 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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