Logon Trigger Security

Jeff Hunter’s post this morning reminds me of a recent situation with a logon trigger designed to limit direct access to a production database.

The logon trigger in question was designed to stop application users using their username and password via tools like Toad to access the database directly and issue their own queries. It did this by getting the PROGRAM from V$SESSION via AUDSID for the connecting session and raising an error if the program was not in a permitted list of connecting applications.

During a recent performance problem, I requested the password for a privileged account to have a look around within the data dictionary tables but, due to an oversight when the database user was set up previously, the trigger refused access via Toad (the oversight was that the trigger only checked the program if the connecting user did not have a particular profile associated. This username which should have had the profile associated didn’t hence the program check kicked in).

Given the pressing need to connect, I renamed Toad.exe to one of the applications in the permitted list and Bang! I’m in. Now that’s security…