Using hard-coded host name in logon trigger

authenticationdataguardoracletrigger

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:

Have a table which will check for combination of user + server + database hostname (name of machine where database is hosted).
Have a DB_ROLE_CHANGE trigger which will get activated if the production now runs from standby
The DB_ROLE_TRIGGER will 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.

Best 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)
..

Related Solutions

Sql-server – Can a username and password be hard-coded in a system DSN

A System DSN is stored in the registry and does not have the ability to store a password. If you want to store the password, you'd have to use a File DSN and that would require changing the CRM to use the file DSN which you say is not possible. Usually applications that use a System DSN, rely on SQL Server authentication of the users for their security. When the application opens it should prompt the user to login, and this is the username and password that is used with the System DSN information to connect to the database server. If the DSN uses Windows authentication and you are eliminating your Windows Domain, you are stuck. You would have to change the CRM to make it work a different way.

Database logon trigger

You might want to verify this in SQLPlus. If it still doesn't disconnect you then verify your assumptions by running the following after the trigger finishes:

SELECT 'Check This' FROM dual WHERE sys_context('USERENV','SESSION_USER') = 'xx';

SELECT 'Check This' FROM dual WHERE sys_context('USERENV','IP_ADDRESS') <> '10.0.30.219';

SELECT 'Check This' FROM user_role_privs WHERE granted_role='DBA';

SELECT 'Check This' FROM user_objects WHERE Object_Name='YOU_MAY_NOT_LOGIN' AND Object_Type='TRIGGER';

SELECT 'Check This' FROM User_sys_Privs WHERE Privilege='ADMINISTER DATABASE TRIGGER';

If any of these return Check This then one of your assumptions is incorrect.

For me all that was necessary to be disconnected was the RAISE_APPLICATION_ERROR, but another site uses an EXECUTE IMMEDIATE 'DISCONNECT'; as well. Perhaps this allows it to work even when the DBA role has been granted. I have not tested it to know.

You should also be aware that this should not be used for security in place of secure passwords and other measures as the IP_ADDRESS the client sends can be altered.

Best Answer

Related Solutions

Sql-server – Can a username and password be hard-coded in a system DSN

Database logon trigger

Related Question